pgmcp

A hardened, read-only Model Context Protocol server over a PostgreSQL database. It gives an LLM client a guarded SQL query tool plus schema introspection — and nothing else. No writes, no filesystem, no admin surface.

Why

Handing an LLM a raw database connection is dangerous. pgmcp puts a database between the model and your data with defense-in-depth:

1. Least-privilege role. pgmcp connects as a role with SELECT-only grants on exactly the schemas you allow — not pg_read_all_data. Grants are the security boundary.
2. Single-statement SELECT guard. A pglast AST guard rejects everything that isn't one read-only SELECT: multi-statement SQL (the COMMIT; DROP … escape), DML, DDL, COPY, SELECT … INTO, data-modifying CTEs, and filesystem/admin functions.
3. READ ONLY transaction envelope around every query.
4. EXPLAIN cost pre-gate on the large schemas you designate — a runaway cartesian join is rejected before it runs.
5. Row cap with a truncation flag in the response.
6. Per-identity audit log — one structured JSON line per call, attributed to the authenticated user.

Everything domain-specific is configuration.

Tools

Tool	Purpose
run_query(sql)	Run one guarded, read-only SELECT; returns columns + rows + truncation flag.
list_objects(schema)	List tables/views in one allowlisted schema.
describe_table(schema, table)	Column names, types, nullability for one table/view.

Quickstart

# 1. Create the least-privilege role and grants (see db/README.md).
# db/03_grants.sql and db/02_curated_views.sql are examples to edit for your
# own schema; PGMCP_ALLOWED_SCHEMAS below must match whatever you actually grant.
psql -f db/01_role.sql
psql -f db/03_grants.sql
psql -c "ALTER ROLE pgmcp_ro PASSWORD 'choose-a-secret'"

# 2. Install
uv venv && uv pip install -e .

# 3. Configure and run
export PGMCP_DSN="postgresql://pgmcp_ro:choose-a-secret@127.0.0.1:5432/mydb"
export PGMCP_ALLOWED_SCHEMAS="app,analytics,public"
export PGMCP_COST_GATED_SCHEMAS="app,analytics"
pgmcp    # or: python -m pgmcp.server

The server binds 127.0.0.1:8765 and speaks Streamable HTTP. It trusts an X-Auth-User header injected by a reverse proxy — see deploy/ for an nginx + systemd example that adds TLS and per-user bearer auth.

Configuration

Env var	Meaning	Default
PGMCP_DSN	libpq conninfo — service=pgmcp_ro or postgresql://…	service=pgmcp_ro
PGMCP_ALLOWED_SCHEMAS	comma list of queryable schemas (required)	—
PGMCP_COST_GATED_SCHEMAS	subset that gets the EXPLAIN pre-gate	(none)
PGMCP_SCHEMA_CONTRACT	path to a TOML schema-contract; asserted on boot	(unset → skip)
PGMCP_INSTRUCTIONS_FILE	path to model instructions; overrides the default	(unset → default)
PGMCP_ROW_CAP	max rows returned	10000
PGMCP_EXPLAIN_COST_LIMIT	cost-gate ceiling	100000000
PGMCP_IDENTITY_HEADER	trusted identity header	X-Auth-User
PGMCP_HOST / PGMCP_PORT	bind address (loopback)	127.0.0.1 / 8765
PGMCP_ALLOWED_HOSTS / PGMCP_ALLOWED_ORIGINS	DNS-rebinding / Origin allowlists	hosts: loopback; origins: empty

PGMCP_ALLOWED_SCHEMAS must mirror the grants in db/03_grants.sql. The server refuses to boot with an empty allowlist.

Exposing a table with some sensitive columns

Don't grant the base table. Create a view in a curated schema that projects only the safe columns, grant the view, and add curated to PGMCP_ALLOWED_SCHEMAS. See db/02_curated_views.sql. The base table stays unreachable through pgmcp.

Optional: schema contract

Point PGMCP_SCHEMA_CONTRACT at a TOML file (see examples/schema-contract.toml) declaring expected column types. On boot pgmcp asserts them against information_schema and refuses to start on drift — a guard against a query tool silently outliving the schema it was written for.

Development

uv pip install -e ".[dev]"
pytest tests/            # unit tests need no database
PGMCP_DSN=... pytest tests/test_live.py   # opt-in end-to-end checks

Design

Architecture decisions and their rationale are in docs/adr/. Start with CONTEXT.md (glossary) and SETUP.md (setup runbook).

License

Released under the MIT License — see LICENSE.