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.

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)]

Related MCP server: MCP 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.

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

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.

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:

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