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.

Related MCP server: postgres-mcp

Tools

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

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.