postgres-postgis-docsbox-mcp

A Model Context Protocol (MCP) server giving an LLM agent safe, bounded read-only access to a PostgreSQL + PostGIS database, plus a curated PostGIS reference and a docs/manifest lookup.

Sibling project to python-docsbox-mcp and rust-docsbox-mcp; shares the same FastMCP streamable-HTTP shape.

Why

Generic psql MCPs run user-authored SQL with full ambient permissions. That's a footgun: an agent can DROP TABLE mid-conversation, or COPY ... TO PROGRAM 'rm -rf'. This server takes the opposite stance.

Defence in depth:

1. Static SQL validation — sqlglot parse + denylist of statement kinds, dangerous functions, and multi-statement payloads. Refuses anything that isn't top-level SELECT / WITH / EXPLAIN. Auto-injects LIMIT if missing.
2. Read-only transaction — every query runs inside SET TRANSACTION READ ONLY with statement_timeout, lock_timeout, and idle_in_transaction_session_timeout bounded; rollback is unconditional.
3. Bounded results — row caps, per-cell byte caps, geometry wrapped to GeoJSON for inspection.
4. No process fan-out — the only subprocess-shaped tool is run_locally, which is plan-only.

Tools

Each tool's docstring carries a worked example with the expected JSON shape; the agent doesn't have to guess.

Quick start

1. Spin up Postgres + PostGIS (Docker)

docker run -d --name postgis-docsbox \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  postgis/postgis:17-3.5

2. Load the sample dataset

PGPASSWORD=secret psql -h 127.0.0.1 -U postgres -d postgres \
  -f examples/sample_data.sql

3. Run the MCP server

pip install -e .
export PG_DOCSBOX_DSN='postgresql://postgres:[email protected]:5432/postgres'
postgres-postgis-docsbox-mcp
# listening on 127.0.0.1:7820 (mcp at /mcp)

4. Wire into your client

opencode:

{
  "mcp": {
    "postgres-postgis-docsbox": {
      "type": "remote",
      "url": "http://127.0.0.1:7820/mcp",
      "enabled": true
    }
  }
}

Claude Code (~/.claude.json):

{
  "mcpServers": {
    "postgres-postgis-docsbox": {
      "type": "http",
      "url": "http://127.0.0.1:7820/mcp"
    }
  }
}

Worked examples (against the sample dataset)

list_extensions()
-> {"ok": true, "extensions": [
     {"name":"plpgsql","version":"1.0","schema":"pg_catalog"},
     {"name":"postgis","version":"3.5.0","schema":"public"}
   ]}

list_tables()
-> {"ok": true, "tables": [
     {"schema":"public","name":"hospitals","kind":"table","row_estimate":4,
      "has_geom":true,"geom_column":"geom","srid":4326,"geom_type":"POINT"},
     {"schema":"public","name":"schools","kind":"table","row_estimate":8,
      "has_geom":true,"geom_column":"geom","srid":4326,"geom_type":"POINT"},
     {"schema":"public","name":"suburbs","kind":"table","row_estimate":5,
      "has_geom":true,"geom_column":"geom","srid":4326,"geom_type":"MULTIPOLYGON"}
   ], "count": 3}

pick_interesting_tables(limit=3)
-> top tables ordered by log10(rows) + spatial-index/FK-hub bonuses,
   each with a GeoJSON extent.

get_table_schema(table='public.suburbs', sample_rows=2)
-> DDL + columns + sample rows with geometry as GeoJSON.

execute_sql(sql=
  'SELECT s.name AS school, sub.name AS suburb '
  'FROM schools s JOIN suburbs sub ON ST_Contains(sub.geom, s.geom) '
  'ORDER BY school')
-> rows pairing each school with the suburb whose polygon contains it.

postgis_help(section='nearest')
-> the "nearest k" recipe (use KNN <-> on a GiST-indexed geometry).

Configuration

Recommended Postgres-side hardening

Even though the server forces READ ONLY, give it a role that physically cannot mutate:

CREATE ROLE docsbox LOGIN PASSWORD 'secret';
GRANT pg_read_all_data TO docsbox;

Docker

docker build -t postgres-postgis-docsbox-mcp .
docker run --rm -p 7820:7820 \
  -e PG_DOCSBOX_DSN='postgresql://docsbox:[email protected]:5432/mydb' \
  postgres-postgis-docsbox-mcp

Development

pip install -e '.[dev]'
pytest -q
ruff check src tests

License

MIT