Skip to main content
Glama
pg-readonly-mcp

pg-readonly-mcp

by landaida

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.

Related MCP server: sqlgate-mcp

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

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; just open Claude Code in this directory and approve the pg-readonly server), or with Claude Desktop (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:

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

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 (committed here); .claude/settings.json pre-enables the pg-readonly server from it. For Claude Desktop copy examples/claude_desktop_config.example.json into your config and fix the absolute paths.

  • Hook: .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. 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

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.

Available Tools

3 tools
describe_tableA

Describe an allow-listed table: columns (name, type, nullable, default), primary key, indexes.

Inputs: table (required). Returns: object

list_tablesA

List the tables this server may read (schema.table), with a row-count estimate and comment.

Inputs: no named parameters. Returns: object

queryA

Run ONE read-only SELECT against allow-listed tables.

Returns {columns, rows, row_count, truncated, duration_ms}. max_rows can only lower the server cap.

Inputs: sql (required), max_rows. Returns: object

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    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

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      A
      quality
      D
      maintenance
      Enables AI agents to inspect and query PostgreSQL databases safely, with features like listing tables, retrieving schemas, and running read-only SQL queries.
      3
      -
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables AI agents to query Postgres, BigQuery and DuckDB read-only through policy-checked SQL, with AST-based guards that refuse writes and dangerous functions, column masking on the way out, and an audit log of every query. Also exposes schema browsing and query-planning tools so agents can see allowed tables, columns and costs before running anything.
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables agents to safely inspect a PostgreSQL database read-only: discovering schemas and tables, running guarded SELECT/EXPLAIN queries, and getting plan summaries with index suggestions. Access is constrained by schema allowlists, denied-column redaction, row caps, and a JSON-lines audit trail.
      MIT
    • A
      license
      Not graded
      quality
      B
      maintenance
      Gives AI agents read-only access to Postgres with parser-checked queries, hidden tables and columns, row limits and timeouts, caller-bound Row-Level Security, and a tamper-evident audit log.
      MIT