mcp-postgres

A Model Context Protocol server for PostgreSQL. Gives an agent (Claude Code, Cursor, etc.) a vetted set of read-only tools for schema introspection, query execution with row caps and timeouts, structured EXPLAIN analysis, pg_stat_statements summaries, and pgvector similarity search. Writable and admin tools exist behind opt-in flags.

Status: alpha (0.0.1). Tested on PostgreSQL 13–17.

Why bother with another one

Anthropic's reference Postgres MCP shipped a bypassable read-only mode in mid-2025 — Datadog disclosed that COMMIT; DROP SCHEMA public CASCADE; escaped its BEGIN TRANSACTION READ ONLY envelope (the underlying driver accepted multi-statement strings). The server was archived. The safety story here is the layered model in docs/safety.md: a least-privilege role grant at the database, an AST guard (via libpg_query) in the server, a per-transaction envelope, and an audit log. The role grant is the load-bearing one; everything else is defense in depth.

tests/adversarial/test_readonly_payloads.py runs 30 bypass attempts against the AST guard, including the original Datadog payload. PRs adding new payloads are welcome.

Install

pipx install mcp-postgres        # or: pip install mcp-postgres

Python 3.11+. Postgres 13+. The wheel includes pglast (libpg_query bindings) and psycopg[binary].

Run

mcp-postgres connect "postgresql://mcp_postgres_ro:****@host/mydb" \
    --readonly --row-limit 1000 \
    --statement-timeout 10s \
    --audit-log /var/log/mcp-pg/audit.log

Then register it with your MCP client. For Claude Code:

claude mcp add postgres -- \
  mcp-postgres connect "$DSN" --readonly --row-limit 1000

docs/quickstart.md walks through the bundled docker fixture.

Tools

The schema-introspection tools cover FKs, partial / expression / GIN / BRIN indexes, JSONB columns, views, materialised views, partitioning, RLS policies, triggers, stats. describe_table returns all of that in one call.

explain_query parses the JSON plan from PostgreSQL and walks it for common patterns: large seq scans on filtered tables, row-count mis-estimates, sorts that spill to disk, hash joins with multiple batches, nested loops with large outer sides, partition pruning failures, unindexed join keys, lateral subquery loops, temp-file spills. Suggestions include CREATE INDEX CONCURRENTLY candidates (equality-first then range) and work_mem bumps.

For pgvector: similarity_search runs EXPLAIN before the real query and emits a vector_scan_no_index warning when the plan won't use an hnsw/ivfflat index on a table with more than 100k rows.

Deployment

Five patterns documented with role grants in docs/deployment.md and grants/: local dev, CI, prod read-only (the common case), prod read-write on a sandbox schema, and multi-DB.

The default posture is:

• Read-only at the Postgres role level.
• AST guard rejects multi-statement input, data-modifying CTEs, SELECT FOR UPDATE, SELECT INTO, forbidden SET targets, COPY ... TO PROGRAM, and any non-SELECT top-level statement.
• SET LOCAL transaction_read_only = on, statement_timeout, idle_in_transaction_session_timeout, lock_timeout per transaction.
• Audit log to JSON-Lines file, syslog, or stderr.

Tests

# Spin up the fixture:
cd tests/ && docker compose up -d

# Run everything:
MCP_PG_TEST_DSN=postgresql://postgres:postgres@localhost:55432/hranalytics \
  pytest tests/

# Or just the units (no DB needed):
MCP_PG_NO_DB=1 pytest tests/unit/ tests/adversarial/

CI runs the full suite against PostgreSQL 13, 14, 15, 16, 17 with pgvector enabled.

License

Apache 2.0. See LICENSE.