Skip to main content
Glama
pg-readonly-mcp

pg-readonly-mcp

by landaida
README.md
# pg-readonly-mcp

A **read-only PostgreSQL MCP server** with guardrails, plus a Claude Code hook and skill that use it.

## Why

An LLM agent with database access is useful and dangerous: it can write when it should only read, run an
unbounded `SELECT` that takes the primary down or floods its own context, wander into tables it was never
meant to see, and leave no record of what it did. This server gives the agent three tools (`list_tables`,
`describe_table`, `query`) and puts a stack of independent checks between the model and the database, so
that no single bug (a model prompt-injected into writing `DROP TABLE`, a parser gap, a role granted too much)
is enough to cause damage. Every call, allowed or refused, lands in an append-only JSONL audit log.

Threat model: the caller is an untrusted-ish text generator that is fully able to write arbitrary SQL. The
operator is trusted and supplies a DSN for a dedicated, least-privilege role. The server is *not* a
multi-tenant security boundary; see [Limitations](#limitations).

## Guardrails

Each guardrail is enforced on its own, so removing one still leaves the others.

| # | Guardrail | How it is enforced | Where | Tested in |
|---|---|---|---|---|
| 1 | Table allow-list | `sqlglot` parses the SQL (postgres dialect); every `Table` node, including joins, subqueries, set operations and CTEs, must be in `PG_ALLOWED_TABLES`. CTE names are resolved with sqlglot's scoping so a CTE cannot shadow a real table. Quoted identifiers keep their case, as in PostgreSQL. | `guard.py: _check_tables` | `test_guard.py` (join, subquery, CTE, shadowing, catalogs), `test_integration.py` |
| 2 | SELECT-only | Exactly one statement; root must be `SELECT` or a set operation of selects. Any `INSERT/UPDATE/DELETE/MERGE/CREATE/ALTER/DROP/TRUNCATE/GRANT/COPY/CALL/DO/SET/EXPLAIN`, `INTO`, `FOR UPDATE/SHARE`, or DML nested in a CTE anywhere in the tree is refused. Dangerous functions (`pg_read_file`, `pg_ls_dir`, `lo_*`, `pg_sleep`, `dblink`, `query_to_xml`, `set_config`, ...) are refused wherever they appear. Unparseable SQL is refused (fail closed). The SQL that runs is regenerated from the validated AST, not the caller's text. | `guard.py: validate_query` | `test_guard.py` |
| 3 | Read-only at the database | Every statement runs in `BEGIN READ ONLY`, and each connection first runs `SET default_transaction_read_only = on`. Transactions are always rolled back. | `db.py: _connect, _run` | `test_integration.py` (a `CREATE TEMP TABLE` and an `UPDATE` sent *around* the guard are rejected by PostgreSQL) |
| 4 | Statement timeout | `SET LOCAL statement_timeout` inside each transaction; a timeout returns a clean `statement_timeout` error. | `db.py: _run`, `server.py: _error_payload` | `test_integration.py` |
| 5 | Row cap | The query is wrapped as `SELECT * FROM (<sql>) AS _q LIMIT n+1`; `n+1` rows are fetched so `truncated` is exact. Per-call `max_rows` can only lower `PG_MAX_ROWS`. | `guard.py: wrap_with_limit, apply_row_cap, effective_row_limit` | `test_guard.py`, `test_integration.py` |
| 6 | Result size cap | The JSON-serialized result is capped at `PG_MAX_RESULT_BYTES`; excess rows are dropped, `truncated: true` and a `note` are set. | `guard.py: apply_byte_cap` | `test_guard.py`, `test_integration.py` |
| 7 | Audit log | One JSON line per tool call (`ts`, `tool`, `args` incl. the SQL, `ok`, `error`, `row_count`, `truncated`, `duration_ms`). The DSN and password are never written; an audit write failure never fails the tool. | `audit.py`, `server.py: run_tool` | `test_server_unit.py`, `test_integration.py` |
| 8 | Fail closed | Any unexpected exception becomes a structured `InternalError`; nothing is executed. Missing `PG_DSN` / `PG_ALLOWED_TABLES` stops the server at startup. | `server.py: run_tool`, `config.py` | `test_server_unit.py`, `test_config.py` |

A ninth layer lives outside the code: the DSN should use a role that can only `SELECT` the allow-listed
tables ([role SQL](#the-read-only-role)). The demo ships exactly that, and the integration tests check that
even a guard bypass cannot read the un-granted `secrets` table.

## Quickstart (2 minutes)

Needs Python 3.11+ and Docker.

```bash
make venv                   # .venv with the package and test deps
make demo-up                # postgres:16 on 127.0.0.1:55432 with a small demo schema
make run                    # the MCP server on stdio, against the demo DB
```

Register it with Claude Code (this repo already contains a project-scoped [`.mcp.json`](.mcp.json); just open
Claude Code in this directory and approve the `pg-readonly` server), or with Claude Desktop
([`examples/claude_desktop_config.example.json`](examples/claude_desktop_config.example.json)). Then ask:

> Which countries have the most customers, and how many orders has each placed?

Look at what the agent actually ran:

```bash
tail -n 3 audit.jsonl | jq -c '{tool, ok, sql: .args.sql, rows: .row_count, ms: .duration_ms}'
```

```
{"tool":"list_tables","ok":true,"sql":null,"rows":4,"ms":9.1}
{"tool":"describe_table","ok":true,"sql":null,"rows":null,"ms":6.4}
{"tool":"query","ok":true,"sql":"SELECT c.country, count(*) ...","rows":5,"ms":4.2}
```

Tear down with `make demo-down`. Stdio is the default transport; `pg-readonly-mcp --http --port 8000`
serves streamable HTTP on `127.0.0.1` (no authentication, so keep it on localhost or behind your own proxy).
`python scripts/smoke_stdio.py` runs an `initialize` + `tools/list` handshake and two calls for a quick check.

The demo uses obviously fake, local-only credentials (`demo-readonly-pw`, `demo-admin-not-a-secret`) that exist
only inside `docker-compose.yml` / `docker/init.sql`. Do not reuse them anywhere.

### Tools

| Tool | Returns |
|---|---|
| `list_tables()` | Allow-listed tables only: `schema.table`, row-count estimate from `pg_class.reltuples` (`null` if never analyzed), table comment. |
| `describe_table(table)` | Columns (name, type, nullable, default), primary key, indexes. Refuses tables not in the allow-list. |
| `query(sql, max_rows=None)` | `{columns, rows, row_count, truncated, duration_ms}` (+ `note` when truncated). |

Every tool returns `{"ok": true, ...}` or `{"ok": false, "error": {"type", "code", "message"}}`. Tracebacks are never returned.

## What it refuses

Errors are from the real guard (`GuardError`), returned as `{"ok": false, "error": {"type": "GuardError", "code": ..., "message": ...}}`:

| SQL | code | message |
|---|---|---|
| `DELETE FROM orders` | `not_select` | `DELETE is not allowed; only SELECT is permitted` |
| `SELECT 1; DROP TABLE orders` | `multiple_statements` | `exactly one statement is allowed, got 2` |
| `SELECT * FROM secrets` | `table_not_allowed` | `table public.secrets is not in the allow-list` |
| `SELECT * FROM orders o JOIN secrets s ON true` | `table_not_allowed` | `table public.secrets is not in the allow-list` |
| `WITH d AS (DELETE FROM orders RETURNING *) SELECT * FROM d` | `not_select` | `DELETE is not allowed; only SELECT is permitted` |
| `SELECT * FROM orders FOR UPDATE` | `not_select` | `LOCK is not allowed; only SELECT is permitted` |
| `SELECT pg_sleep(30)` | `function_not_allowed` | `function pg_sleep() is not allowed` |
| `SELECT count(*) FROM generate_series(1, 50000000)` with `PG_STATEMENT_TIMEOUT_MS=100` | `statement_timeout` | `query exceeded the statement timeout and was cancelled` |

## Configuration

| Variable | Default | Meaning |
|---|---|---|
| `PG_DSN` | required | libpq connection string or URL. Use a read-only role. Never logged. |
| `PG_ALLOWED_TABLES` | required | Comma-separated `schema.table` (schema defaults to `public`), e.g. `public.orders,sales.items`. |
| `PG_STATEMENT_TIMEOUT_MS` | `5000` | `SET LOCAL statement_timeout` for every statement. |
| `PG_MAX_ROWS` | `200` | Row cap. A per-call `max_rows` can only lower it. |
| `PG_MAX_RESULT_BYTES` | `1000000` | Cap on the JSON-serialized result. |
| `PG_AUDIT_LOG` | `./audit.jsonl` | JSONL audit file, appended to. |
| `PG_CONNECT_TIMEOUT_S` | `5` | Connect timeout. |

The server refuses to start (exit code 2, message on stderr) when `PG_DSN` or `PG_ALLOWED_TABLES` is missing or empty, or a number is invalid.

## The read-only role

```sql
CREATE ROLE mcp_readonly LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE mydb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON public.orders, public.customers TO mcp_readonly;   -- exactly the allow-list, nothing else
ALTER ROLE mcp_readonly SET default_transaction_read_only = on;    -- optional third lock
-- Do NOT grant CREATE, INSERT/UPDATE/DELETE, or membership in any other role.
-- Avoid GRANT ... ON ALL TABLES: the allow-list and the grants should match one-to-one.
```

## Use it from Claude Code / Claude Desktop

Three pieces, all in this repo:

- **MCP registration.** Claude Code reads project servers from [`.mcp.json`](.mcp.json) (committed here); `.claude/settings.json`
  pre-enables the `pg-readonly` server from it. For Claude Desktop copy
  [`examples/claude_desktop_config.example.json`](examples/claude_desktop_config.example.json) into your config and fix the absolute paths.
- **Hook: [`.claude/hooks/guard_unattended.py`](.claude/hooks/guard_unattended.py).** A `PreToolUse` hook (stdlib only). With
  `UNATTENDED=1` it blocks (exit 2, reason on stderr) every tool except the three MCP read tools plus `Read`, `Grep` and `Glob`; with
  `UNATTENDED` unset it allows everything. Use it for headless runs, e.g. `UNATTENDED=1 claude -p "..."`, so an unattended agent
  cannot reach `Bash` or `Write` even if it wants to. Each decision is logged to `.claude/hooks/hook-audit.jsonl`.
  The MCP tool names are `mcp__pg-readonly__<tool>`, derived from the server name in `.mcp.json`; rename both together.
- **Skill: [`.claude/skills/db-report/SKILL.md`](.claude/skills/db-report/SKILL.md).** A 5-step checklist with a definition of done
  (list tables, describe, at most 3 queries, 5-line report with the exact SQL as citations, self-check that every number is cited or
  replaced by "not found"). It shows the "short numbered checklist + done criteria" pattern; the content is deliberately small.

## Tests

```bash
make test               # unit tests, no database needed
make demo-up && make test-integration
```

- `tests/test_guard.py`: pure unit tests of the guard. Accept and reject cases for each rule: multi-statement,
  every DML/DDL form, DML inside a CTE, `SELECT INTO`, row locks, dangerous functions, tables outside the allow-list in a join / subquery / CTE /
  union, CTE shadowing, system catalogs, unparseable SQL, the `LIMIT n+1` wrapper and the `truncated` logic, and the byte cap.
- `tests/test_config.py`, `tests/test_server_unit.py`, `tests/test_hook.py`: startup refusal, structured errors, secret scrubbing in the audit
  log, audit-write failures, the hook's allow/block matrix.
- `tests/test_integration.py` (runs only when `PG_TEST_DSN` is set): against the demo DB, allowed queries work, `secrets` is refused at the
  AST level, `DELETE` is refused, PostgreSQL itself rejects writes sent around the guard (`BEGIN READ ONLY` is in effect), the role cannot read
  `secrets` even with the guard bypassed, the statement timeout fires, the row and byte caps truncate correctly, and there is one audit line per call.

## Limitations

- **The checks are parser-based.** They can be bypassed only if `sqlglot` mis-parses something PostgreSQL would run differently. That is why the
  server executes SQL regenerated from the validated AST, why every statement also runs in a `READ ONLY` transaction, and why the DSN should be a
  `SELECT`-only role. Any one of the layers failing is not enough.
- The function deny-list is a deny-list: a new or extension-provided function that reads files or runs SQL from a string is not covered until
  added. Do not install `dblink`/`file_fdw`-style extensions in the database this role can reach; the role should not hold `EXECUTE`-style privileges on them.
- No row-level or column-level security: access is per table. Put sensitive columns in a view, or grant column-level `SELECT` to the role (the allow-list
  does not look inside `SELECT *`, the role's grants do).
- No query cost limits beyond the statement timeout and the row/byte caps. A query can still use a lot of CPU for up to the timeout.
- A new connection per call (no pooling), and `list_tables` row counts are planner estimates.
- The HTTP transport has no authentication. The audit log is a local file, not tamper-evident.
- Python SDK note: this uses `FastMCP` from `mcp>=1.9,<2`; the 2.x SDK renamed it.

## License

MIT, see [LICENSE](LICENSE).

TDQS

A3.9/5.0

Scored across 3 tools

Disambiguation5/5

Each tool occupies a distinct stage of the workflow: list_tables for discovery, describe_table for schema introspection, and query for execution. The descriptions make the boundaries explicit (query only runs against allow-listed tables), so an agent cannot easily misselect.

Naming Consistency4/5

list_tables and describe_table follow a clean verb_noun snake_case pattern, while 'query' is a bare verb that breaks the pattern slightly. It is still unambiguous and readable, so this is a minor deviation rather than an inconsistency.

Tool Count5/5

Three tools map exactly onto the minimal complete set for a read-only SQL server: discover, inspect, execute. Nothing is redundant and no obvious capability is missing from the surface.

Completeness4/5

The read-only lifecycle is well covered, with truncation and max_rows controls, but describe_table omits foreign-key relationships and there is no way to page past the row cap beyond lowering it. These are minor gaps an agent can work around.

Maintenance

ActivityMaintained
ResponsivenessNo recent community issues