pg-sentinel

A PostgreSQL MCP server built like production infrastructure: three independent safety layers, full query auditing, and query-plan intelligence. Let an LLM explore and query your database without losing sleep — every statement is parsed and proven read-only before it executes, inside a READ ONLY transaction, as a role that couldn't write even if it wanted to.

<!-- Demo: ask "which product category had the highest revenue last quarter?", then "why is that query slow?" — see demo/DEMO.md for the recording script. GitHub plays the MP4 inline; the GIF is the fallback for renderers that strip <video> (PyPI, npm, editors). --> <video src="https://github.com/anshujod/postgres_mcp_server/raw/main/demo/demo.mp4" autoplay loop muted playsinline width="960"> <img src="demo/demo.gif" alt="pg-sentinel demo" width="960"> </video>

Architecture

flowchart LR
    subgraph client["MCP client (Claude Desktop, …)"]
        LLM
    end

    LLM -- "tools & resources (stdio)" --> S

    subgraph server["pg-sentinel"]
        S[FastMCP server] --> A["Layer 1 · SQL analyzer<br/>(pglast parse tree)"]
        A -- "verdict + policy" --> E["Layer 2 · executor<br/>READ ONLY txn, always rolled back<br/>timeout + row cap"]
        S -. "audit log (structlog)" .-> L[(JSON logs)]
    end

    E -- "Layer 3 · read-only role<br/>(SELECT-only grants)" --> PG[(PostgreSQL)]

Quick start

Try the demo (one command)

git clone https://github.com/anshujod/postgres_mcp_server && cd postgres_mcp_server/demo
docker compose up --build

This starts Postgres 16 seeded with an e-commerce dataset (10k customers, 50k orders, ~125k line items) and builds the pg-sentinel image connected as the read-only sentinel_ro role. Postgres listens on host port 5433 (override with PG_SENTINEL_DEMO_PORT if you like).

Run against your own database

PG_SENTINEL_DATABASE_URL=postgresql://user:pass@host:5432/mydb uvx pg-sentinel

Best practice: create a dedicated read-only role first (layer 3), and connect as that:

CREATE ROLE sentinel_ro LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE mydb TO sentinel_ro;
GRANT USAGE ON SCHEMA public TO sentinel_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sentinel_ro;

Claude Desktop setup

1. Open your config file:

   • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
   • Windows: %APPDATA%\Claude\claude_desktop_config.json

2. Add pg-sentinel under mcpServers (uvx form shown; a docker form is in demo/claude_desktop_config.json):

   {
     "mcpServers": {
       "pg-sentinel": {
         "command": "uvx",
         "args": ["pg-sentinel"],
         "env": {
           "PG_SENTINEL_DATABASE_URL": "postgresql://sentinel_ro:sentinel_ro_demo@localhost:5433/demo"
         }
       }
     }
   }
   

3. Restart Claude Desktop. Ask something like “which product category had the highest revenue last quarter?” and watch it write and run the SQL.

The safety model: three independent layers

Any single defense can have a hole. pg-sentinel stacks three, each of which alone blocks writes — an attacker (or a confused LLM) has to get through all of them at once.

Layer	Mechanism	Catches
1 — SQL analyzer	Every statement is parsed with pglast (PostgreSQL's own parser). Only a single top-level SELECT passes; the whole tree is walked to reject embedded writes, SELECT … INTO, LOCK/COPY, and ~20 dangerous functions (pg_sleep, pg_read_file, dblink*, …), even schema-qualified.	Writable CTEs (WITH x AS (DELETE …) SELECT …), multi-statement injection (SELECT 1; DROP TABLE users), comment tricks, EXPLAIN ANALYZE DELETE …
2 — READ ONLY transaction	Every query runs inside SET TRANSACTION READ ONLY and is always rolled back, never committed, with a statement timeout and row cap.	Anything that somehow slips past the analyzer — Postgres itself rejects the write. Verified by test: an INSERT handed directly to the executor raises ReadOnlySQLTransactionError.
3 — Read-only DB role	The server connects as a role with only SELECT granted (sentinel_ro in the demo).	Bugs in pg-sentinel itself. Even with layers 1–2 gone, DELETE → permission denied.

An optional policy layer adds table allow/denylists (glob patterns like public.*, deny auth.*) and per-query limits. Every call is logged as structured JSON — SQL, verdict, duration, row count — for a full audit trail.

Tool reference

Tool	Arguments	What it does
query	sql, limit=100, format=markdown|json	Run a read-only SELECT. Rejections return Query rejected: <reason> instead of raising, so the LLM can relay and adapt.
explain_query	sql, analyze=false	Query plan with a human summary: total cost, actual time (if analyzed), most expensive node, join strategies, and seq scans on large tables flagged as possible missing indexes.
list_tables	schema="public"	Tables with approximate row counts and comments.
describe_table	table, schema="public"	Columns, primary key, foreign keys, indexes, comment.
find_relevant_tables	question, limit=5	Shortlist the tables most relevant to a natural-language question (ranked by name/column/comment overlap), so large schemas don't flood the context.
sample_rows	table, schema="public", n=5	A few example rows; uses TABLESAMPLE on large tables. Identifiers are validated against the catalog, never interpolated.

Write mode (opt-in)

Off by default. Set PG_SENTINEL_WRITE_MODE=1 (and connect as a role that can actually write) to expose a preview-and-confirm write path. The read tools stay read-only regardless.

Tool	Arguments	What it does
preview_write	sql	Runs a single INSERT/UPDATE/DELETE in an open transaction and returns a diff-style preview of the affected rows plus a token. Nothing is committed.
confirm_write	token	Commits the exact transaction that was previewed.
cancel_write	token	Rolls back a pending preview.

Unconfirmed previews auto-roll-back after PG_SENTINEL_WRITE_PREVIEW_TTL_SECONDS (Postgres' own idle_in_transaction_session_timeout enforces it), and only a handful may be pending at once.

Resources: schema://tables (all tables) and schema://tables/{schema}/{table} (one table in detail) expose the same introspection for resource-aware clients.

Configuration

All settings are environment variables with the PG_SENTINEL_ prefix:

Variable	Default	Meaning
PG_SENTINEL_DATABASE_URL	(required)	Postgres DSN.
PG_SENTINEL_QUERY_TIMEOUT_SECONDS	10	Per-query timeout.
PG_SENTINEL_MAX_ROWS	500	Hard cap on returned rows (results are marked truncated).
PG_SENTINEL_READ_ONLY	true	Safety switch; the server refuses to run queries if disabled.
PG_SENTINEL_WRITE_MODE	false	Opt-in preview/confirm write tools (see above).
PG_SENTINEL_WRITE_PREVIEW_TTL_SECONDS	120	How long an unconfirmed write preview is held before auto-rollback.
PG_SENTINEL_WRITE_MAX_PREVIEW_ROWS	50	Max affected rows shown in a write preview.
PG_SENTINEL_POOL_MIN_SIZE / _MAX_SIZE	1 / 5	Connection pool bounds.
PG_SENTINEL_LOG_LEVEL	INFO	Log level.
PG_SENTINEL_DEV	unset	1 = pretty console logs instead of JSON.

Design decisions

Why a real parser (pglast) instead of regex or keyword filtering? Regexes cannot see structure. WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x contains a destructive write with no leading DELETE; a dollar-quoted string SELECT $$DROP TABLE users$$ contains scary keywords but is harmless data. pglast wraps PostgreSQL's actual parser, so pg-sentinel makes decisions on the same syntax tree the database itself would execute — no false negatives from clever encodings, no false positives from string contents. The adversarial test suite (50+ cases) encodes exactly these attacks.

Why do rejections return messages instead of raising errors? The consumer is an LLM. A raised exception surfaces as an opaque protocol error; a returned Query rejected: only SELECT statements are allowed, got DELETE is something the model can read, relay to the user, and act on — usually by rewriting the query correctly on the next attempt.

The EXPLAIN ANALYZE rule. Plain EXPLAIN only plans a statement, but EXPLAIN ANALYZE executes it — EXPLAIN ANALYZE DELETE FROM users deletes your users. pg-sentinel therefore never accepts EXPLAIN as raw SQL; the explain_query tool analyzes the inner statement with the same layer-1 rules, so anything reaching ANALYZE true is already a proven-safe SELECT — which layers 2 and 3 then guard anyway.

Why fetch max_rows + 1 through a cursor? Fetching one row past the cap distinguishes "exactly 500 rows" from "truncated at 500" without COUNT(*) overhead, and the cursor keeps a SELECT * FROM huge_table from ever materializing in server memory.

Development

uv sync                        # install everything
uv run pytest -m "not integration"   # unit tests (no Docker needed)
uv run pytest                  # full suite (spins up Postgres 16 in Docker)
uv run ruff check src tests && uv run mypy src

Further reading: the blog-post outline covers the threat model and what the adversarial suite caught during development.

License

MIT