pg-mcp

A read-only PostgreSQL MCP server for Claude Code and other Model Context Protocol clients. Point it at one or more Postgres databases and an AI assistant can safely explore schemas, inspect sample rows, draft queries, and read EXPLAIN plans — without any risk of writing.

Why "safely"? Because read-only is enforced across three independent layers:

1. The Postgres role pg-mcp connects as must have only SELECT grants (validated at startup via a CREATE TEMP TABLE probe that MUST fail with SQLSTATE 25006).

2. Every query runs inside BEGIN; SET TRANSACTION READ ONLY; …; ROLLBACK;.

3. Every SQL string is parsed with pglast (the real Postgres C parser) and walked by a Visitor that rejects any DML, utility statement, or deny-listed function anywhere in the AST — including smuggled inside CTEs or EXPLAIN.

Any single layer failing cannot result in a write.

Status

Alpha. The safety boundary has 127 unit tests covering ~every known SQL escape vector (CTE-DML smuggling, EXPLAIN ANALYZE on DML, schema-qualified pg_catalog.nextval(…), pg_read_file, pg_advisory_lock, dblink_exec, DO blocks, COPY, multi-statement, …). End-to-end stdio handshake is tested against the real MCP Python SDK.

Related MCP server: Postgres MCP Server

Install

# If you have uv:
uv tool install pg-mcp

# Or with pipx:
pipx install pg-mcp

# Or with pip in a venv:
pip install pg-mcp

Python 3.11+ is required.

Quick start (5 minutes)

1. Create the read-only Postgres role

On each database you want to expose, run (as a superuser):

pg-mcp grants myconn --role pg_mcp_ro --password CHANGE_ME

That prints a SQL snippet you can paste into psql:

CREATE ROLE pg_mcp_ro LOGIN PASSWORD 'CHANGE_ME';
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CONNECT ON DATABASE mydb TO pg_mcp_ro;
GRANT USAGE ON SCHEMA public TO pg_mcp_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO pg_mcp_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO pg_mcp_ro;
ALTER ROLE pg_mcp_ro SET default_transaction_read_only = on;

2. Create a config

pg-mcp init

Edit ~/.config/pg-mcp/config.yaml to describe your databases:

log_sql: hash   # hash | redacted | full  (see §Observability)

defaults:
  statement_timeout_ms: 120000
  row_limit: 1000
  byte_limit: 1048576
  cell_limit: 8192

connections:
  - name: prod
    description: Production analytics replica (read-only)
    dsn: postgresql://pgmcp_ro@prod-replica.example.com:5432/appdb?sslmode=require
    password: ${PROD_PG_PASSWORD}
    pool:
      min_size: 1
      max_size: 5
    # Optional: restrict which schemas this connection can see.
    allowed_schemas: [public, app]        # only these are visible
    # denied_schemas: [audit, pii]        # alternative: everything except these
    # Both case-insensitive. Deny beats allow.

  - name: analytics
    host: warehouse.example.com
    database: analytics
    user: pgmcp_ro
    password: ${ANALYTICS_PG_PASSWORD}
    sslmode: verify-full
    sslrootcert: ./certs/ca.pem   # relative to config file

allowed_schemas / denied_schemas apply at two layers:

1. Introspection tools (list_schemas, list_tables, describe_*, …) filter out disallowed schemas.

2. run_query / explain_query parse the SQL and reject any query that references a disallowed schema with a qualified name (SELECT * FROM audit.events). Unqualified refs (SELECT * FROM users) are NOT blocked by this layer — they resolve via the Postgres role's USAGE grants, which remain the final gate.

3. Validate

pg-mcp check

Every connection either reports [OK] (role grants verified, RO probe passed) or an actionable error message.

4. Register with Claude Code

claude mcp add --transport stdio pg-mcp -- pg-mcp serve

Or add to a project-scoped .mcp.json:

{
  "mcpServers": {
    "pg-mcp": {
      "type": "stdio",
      "command": "pg-mcp",
      "args": ["serve"]
    }
  }
}

Tools

All read tools are marked readOnlyHint=True, openWorldHint=False. The reconnect tool mutates server-internal pool state only (never the DB) and is marked readOnlyHint=False.

Run pg-mcp tools for the full, always-in-sync catalogue.

Result format

Every tool returns a markdown block preceded by a metadata preamble in an HTML comment:

<!-- pg-mcp result
connection: prod
duration_ms: 42
rows_returned: 27
truncated_rows: false
truncated_bytes: false
notices: []
-->

| id (integer) | email (text)         |
|---|---|
| 1            | alice@example.com    |
| 2            | bob@example.com      |

(27 rows)

Rendering contract

This contract is covered by snapshot tests; any change is a PR that must be reviewed.

Safety model

Layer 1 — Postgres role grants

The connection user must have only SELECT on the exposed schemas. At startup, pg-mcp attempts CREATE TEMP TABLE inside a wrapped READ ONLY transaction. Postgres must reply with SQLSTATE 25006 (read_only_sql_transaction). If the statement succeeds, pg-mcp refuses to use the connection and marks it unsafe — no query will ever run through it.

Layer 2 — READ ONLY transactions

Every query runs inside:

BEGIN;
SET LOCAL statement_timeout = <configured>;
SET LOCAL idle_in_transaction_session_timeout = 5000;
SET TRANSACTION READ ONLY;
-- … query …
ROLLBACK;

The pool's configure hook additionally pins default_transaction_read_only = on on every new backend, so even a connection used outside transaction() is read-only.

Layer 3 — SQL parser allow-list + function deny-list

Every user SQL is parsed with pglast (the real Postgres C parser) before it reaches Postgres. Only these top-level statement types are allowed:

• SelectStmt (without an INTO clause)

• ExplainStmt

• VariableShowStmt (SHOW …)

A Visitor walks the entire AST and rejects any forbidden node anywhere, including inside CTEs, subqueries, EXPLAIN.query, and set operations. This catches:

• WITH x AS (INSERT … RETURNING *) SELECT * FROM x — DML in CTE

• EXPLAIN ANALYZE UPDATE … — ANALYZE executes

• SELECT 1; DROP TABLE t — multi-statement

A function deny-list catches side-effect-ful calls that would parse as legal SELECTs. The deny-list is checked on the unqualified name so pg_catalog.pg_read_file(…) is blocked too:

pg_read_file, pg_read_binary_file, pg_ls_dir, pg_stat_file, pg_ls_logdir,
pg_ls_waldir, lo_export, lo_import, lo_put, lo_get, lo_from_bytea,
dblink, dblink_exec, dblink_send_query, dblink_connect,
pg_advisory_lock, pg_advisory_xact_lock, pg_try_advisory_lock,
pg_advisory_unlock_all, pg_notify, pg_terminate_backend, pg_cancel_backend,
pg_reload_conf, pg_rotate_logfile, pg_logical_emit_message,
set_config, nextval, setval,
pg_create_logical_replication_slot, pg_drop_replication_slot, …

(Plus any function whose name starts with pg_ls_, lo_, or dblink.)

What this model does NOT catch

• User-defined functions that write internally. E.g., a custom function that does INSERT inside its body. Layer 1 (role) and Layer 2 (RO txn) both block these — the parser alone cannot see their body. This is the main reason all three layers are required.

• Functions we haven't added to the deny-list. Postgres extensions (pg_cron, pg_audit, etc.) can add their own. Report anything missing as an issue.

Error codes

Every error surfaced to the LLM has one of these stable codes:

Sub-codes for sql_rejected_by_policy:

Common Postgres sqlstate values you'll see in postgres_error:

Observability

Audit log

Every tool call writes one JSON line to the audit log. Default path:

• macOS: ~/Library/Logs/pg-mcp/pg-mcp.log

• Linux: $XDG_STATE_HOME/pg-mcp/pg-mcp.log (falls back to ~/.local/state/pg-mcp/pg-mcp.log)

Rotated at 50 MiB × 5 files (gzip on rotation). WARN+ mirrored to stderr.

Example entry:

{
  "ts": "2026-04-23T10:00:00.123Z",
  "event": "tool_call",
  "request_id": "a1b2c3d4e5f6",
  "tool": "run_query",
  "connection": "prod",
  "params": {"limit": 1000},
  "sql_hash": "sha256:deadbeef12345678",
  "sql_preview": "SELECT id, email FROM users WHERE created_at > '2026-01-01'",
  "duration_ms": 412,
  "rows_returned": 27,
  "truncated_rows": false,
  "truncated_bytes": false,
  "status": "ok",
  "error_code": null,
  "sqlstate": null
}

SQL logging modes

Configured via log_sql: in the config file:

CLI

pg-mcp serve           # run MCP server over stdio (default)
pg-mcp init            # write a starter config to ~/.config/pg-mcp/config.yaml
pg-mcp check           # validate config + probe all connections
pg-mcp doctor          # comprehensive diagnostic: Python arch, deps, config,
                       #   connectivity, role grants, RO probe, extensions,
                       #   with remediation tips for every failure
pg-mcp info NAME       # detailed view of one connection: status, DSN,
                       #   pool stats, allowed/denied schemas, search_path
pg-mcp tools           # print the tool catalogue in markdown
pg-mcp grants NAME     # print DDL for creating the RO role
pg-mcp version         # print version info
pg-mcp --config PATH … # override config discovery

If something isn't working, run pg-mcp doctor first — it's designed to catch the common first-run traps (Python architecture mismatch on macOS, missing deps, unreachable DB, missing grants, missing pg_stat_statements, …) and tell you exactly what to do.

Development

git clone …
cd pg-mcp
python3.11 -m venv .venv
.venv/bin/pip install -e '.[dev]'
.venv/bin/pytest
.venv/bin/ruff check src tests
.venv/bin/ruff format src tests
.venv/bin/mypy src/pg_mcp/safety.py src/pg_mcp/config.py src/pg_mcp/errors.py

Integration tests require a live Postgres reachable via PG_MCP_TEST_DSN:

PG_MCP_TEST_DSN=postgresql://postgres@localhost:5432/postgres \
  .venv/bin/pytest tests/integration

License

MIT. See LICENSE.