# pg-sentinel

[![CI](https://github.com/anshujod/postgres_mcp_server/actions/workflows/ci.yml/badge.svg)](https://github.com/anshujod/postgres_mcp_server/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/pg-sentinel)](https://pypi.org/project/pg-sentinel/)
[![Python 3.12+](https://img.shields.io/badge/python-3.12+-blue)](https://www.python.org/)
[![License: MIT](https://img.shields.io/badge/license-MIT-green)](LICENSE)

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

```mermaid
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)

```bash
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

```bash
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:

```sql
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`](demo/claude_desktop_config.json)):

   ```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

```bash
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](docs/blog-outline.md) covers the threat
model and what the adversarial suite caught during development.

## License

MIT