Location Database — Usage

See also: location-db • schema • architecture

MCP tool: pg_query

Available on the misc MCP connector (https://www.critchley.biz/misc/mcp). pg_query accepts sql (one SQL statement) and optional login: owntracks_ro (default, reads), owntracks_rw (reads and writes), or owntracks_adm (also creates and alters permitted objects). PostgreSQL enforces permissions; there is no SELECT regex filter. SELECT, WITH queries and statements with RETURNING produce JSON row arrays, including [] for no rows. Statements without result columns return command status, for example {"status":"UPDATE 1"}. Use an explicit LIMIT when needed: the existing 500-row behavior was left unchanged and does not enforce a cap.

The server does not retry SQL automatically. The requesting LLM decides whether to retry after inspecting the error. "Please try again" is generic error text, not proof of a stale connection. For a write with an uncertain outcome, check the resulting state before repeating it.

PostGIS quick reference

Always cast geom to ::geography for metre-accurate distance. Use ST_AsGeoJSON(geom) to return geometry as readable GeoJSON.

Example queries

Nearest approach to a point (e.g. Bristol Temple Meads, lat=51.449, lon=-2.581):

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       lat, lon,
       round(ST_Distance(
           geom::geography,
           ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)::geography
       )::numeric, 0) AS dist_m,
       conn, ssid
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)
LIMIT 10;

All fixes within 500m of a point:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       lat, lon, acc, conn, ssid
FROM locations
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-2.581, 51.449), 4326)::geography,
    500
)
ORDER BY tst;

Speed between two timestamps (haversine via PostGIS):

WITH ordered AS (
  SELECT tst, geom,
         lag(tst)  OVER (ORDER BY tst) AS prev_tst,
         lag(geom) OVER (ORDER BY tst) AS prev_geom
  FROM locations
  WHERE tst BETWEEN 1783900000 AND 1783950000
)
SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       round((ST_Distance(geom::geography, prev_geom::geography)
              / (tst - prev_tst) * 2.23694)::numeric, 1) AS speed_mph
FROM ordered
WHERE prev_tst IS NOT NULL AND (tst - prev_tst) BETWEEN 1 AND 300
ORDER BY tst;

WiFi-only fixes in a time window:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       ssid, ST_AsGeoJSON(geom) AS geojson, batt
FROM locations
WHERE conn = 'w'
  AND tst > extract(epoch FROM now() - interval '24 hours')
ORDER BY tst;

Today's fixes with geometry as GeoJSON:

SELECT to_timestamp(tst) AT TIME ZONE 'Europe/London' AS t,
       ST_AsGeoJSON(geom)::json AS geojson,
       acc, conn, ssid, batt
FROM locations
WHERE tst >= extract(epoch FROM current_date AT TIME ZONE 'Europe/London')
ORDER BY tst;

When asked a location-dependent question ("where am I", "nearest station", "how far to X"), always re-query SELECT ... ORDER BY tst DESC LIMIT 1 fresh — even if a position was already fetched earlier in the same conversation. The phone keeps reporting fixes in the background, so an earlier answer goes stale within minutes, especially in a moving vehicle. Caught in practice on 15 July 2026: answered "nearest station" using a fix from ~30 min prior instead of re-checking, giving a wrong answer (Radyr instead of Pencoed after the car had moved ~15 miles further west).

Agent best practice: don't reuse a cached fix

SSID interpretation — identify the router before inferring movement

The meaning of conn='w' depends on WHICH SSID. c15 is John's fixed-line home router (normally plugged into Virgin Media or a fibre ISP such as TrueSpeed), so a fix on c15 does indicate a settled home/stationary location. c13 is a mobile router with a SIM in it (a portable cellular hotspot) — it travels with him, so a fix on c13 carries NO location implication and may be moving (e.g. in a vehicle). Exception: during a provider outage he plugs c15 into c13 for failover, so c13 then backhauls the fixed line and could also be at home. Rule: check the SSID first — c15 ⇒ likely home/stationary; c13 ⇒ position only, could be in motion. Never treat WiFi generically as a proxy for 'home', and always re-query the latest fix regardless.

Database logins for MCP access (2026-09-18)

Three logins now exist on the owntracks database on gravlax, so that write access can be granted to an MCP client by choosing an identity rather than by relaxing a filter in code. John's design (2026-09-18): "the mcp connection gets a new parameter which is the name of the database login. the default would be old behaviour… Now the RE will have to go; modifying it will get fiddly and error prone." The principle is that the database decides what each login may do, and the connector simply picks which one to connect as.

Login	SELECT	INSERT / UPDATE / DELETE	CREATE new objects	ALTER existing tables
owntracks_ro (default)	yes	no	no	no
owntracks_rw	yes	yes	no	no
owntracks_adm	yes	yes	yes	travel-owned tables only

All verified by direct login, not assumed. Each role was tested with a real statement: ro and we both get "permission denied for schema public" on CREATE TABLE; we successfully runs UPDATE and DELETE (inside a rolled-back transaction); adm creates, inserts, selects, drops, and can ALTER train_calls via its travel role membership.
Known gap: owntracks_adm cannot ALTER the three postgres-owned tables — gazetteer, locations, spatial_ref_sys ("must be owner of table locations"). Closing it needs an ownership reassignment, not a grant, which is a decision about live data rather than a permission tweak. John, 2026-09-18: "Don't stress about it right now. You can always login as postgres." Note spatial_ref_sys belongs to PostGIS and should be left alone regardless.
Authentication is trust over the unix socket, matching the existing owntracks_ro line. Added to pg_hba.conf (backed up as .bak-20260918) and applied with a reload, not a restart.
DEPLOYED — 2026-09-18. misc_mcp_server.py creates separate asyncpg pools for all three logins at startup (min=1, max=3 each). pg_query defaults to owntracks_ro; the SELECT regex is removed. Statement metadata is inspected before execution: result-bearing statements use prepared fetch(), while commands without result columns use execute(). No automatic SQL retry or post-execution fallback is performed. Tool descriptions point directly to location-db/usage and location-db/schema. The service was restarted on gravlax; live MCP checks verified default and explicit logins, the tool schema, and empty row results. DDL and DML were tested using a temporary table. Existing remote edits and the database-selection option were preserved. Rollback copy: /home/john/py/gdata-server/.misc_mcp_server.py.backup-codex-20260918.
Full CRUD permission test run 2026-09-18 (by Claude, at John's request). Confirmed live against a throwaway table crud_test_claude: ro and rw CREATE both denied ("permission denied for schema public"); adm CREATE succeeded. ro SELECT succeeded (default grant); ro INSERT/UPDATE/DELETE all denied ("permission denied for table crud_test_claude"); rw INSERT/UPDATE/DELETE all succeeded. DROP takes a different error path than the other write ops: ro and rw DROP TABLE both failed with "must be owner of table crud_test_claude" (an ownership check) rather than the schema/table permission-denied text seen for CREATE/INSERT/UPDATE/DELETE — same practical effect (denied), but the error text differs if scripting around it. adm DROP succeeded; table removed, database left as found.

Socket access restricted 2026-09-18. The PostgreSQL unix socket is now srwxrwx--- postgres:pgsocket (was srwxrwxrwx), set via unix_socket_group = 'pgsocket' and unix_socket_permissions = 0770. Only the OS users john and postgres can reach the database at all; www-data now gets "Permission denied" on connect. If a new service ever needs database access, add its OS user to the pgsocket group — otherwise it will fail to connect in a way that looks like a configuration error rather than a permission one. These are postmaster-context settings: changing them needs a restart, not a reload.

{"sql":"SELECT current_user AS login"}
{"sql":"SELECT current_user AS login","login":"owntracks_rw"}
{"sql":"SELECT current_user AS login","login":"owntracks_adm"}

Beta-test fixes deployed 2026-09-18. The existing owntracks_we role was renamed to owntracks_rw, preserving its grants; socket authentication and the MCP login enum now use owntracks_rw. For objects created as owntracks_adm in public, default privileges now grant SELECT on tables to owntracks_ro; SELECT/INSERT/UPDATE/DELETE on tables and USAGE/SELECT on sequences to owntracks_rw. These defaults apply to future objects created by that role, not objects created after SET ROLE to another owner. Equivalent grants were applied to the existing admin-owned mcptest table and sequence. The MCP description contains only a short purpose and pointers to this note and location-db/schema. Public HTTPS MCP discovery and real writes to a newly admin-created table passed, including default read-only access and denied read-only writes; the generated test table was removed. ChatGPT may retain an old schema until the Misc connector is refreshed/reconnected; that client-side refresh remains to be verified. Backups on gravlax: /home/john/tmp/owntracks-schema-before-rw-20260918.sql, /etc/postgresql/15/main/pg_hba.conf.bak-codex-rw-20260918, and /home/john/py/gdata-server/.misc_mcp_server.py.backup-codex-rw-20260918.