pg-readonly-mcp
Provides read-only access to allow-listed PostgreSQL tables, with tools to list tables, describe schemas, and run guarded SELECT queries under row limits, timeouts, and audit logging.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@pg-readonly-mcp Which countries have the most customers, and how many orders has each placed?"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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 |
|
|
|
2 | SELECT-only | Exactly one statement; root must be |
|
|
3 | Read-only at the database | Every statement runs in |
|
|
4 | Statement timeout |
|
|
|
5 | Row cap | The query is wrapped as |
|
|
6 | Result size cap | The JSON-serialized result is capped at |
|
|
7 | Audit log | One JSON line per tool call ( |
|
|
8 | Fail closed | Any unexpected exception becomes a structured |
|
|
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 DBRegister 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 |
| Allow-listed tables only: |
| Columns (name, type, nullable, default), primary key, indexes. Refuses tables not in the allow-list. |
|
|
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Configuration
Variable | Default | Meaning |
| required | libpq connection string or URL. Use a read-only role. Never logged. |
| required | Comma-separated |
|
|
|
|
| Row cap. A per-call |
|
| Cap on the JSON-serialized result. |
|
| JSONL audit file, appended to. |
|
| 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.jsonpre-enables thepg-readonlyserver from it. For Claude Desktop copyexamples/claude_desktop_config.example.jsoninto your config and fix the absolute paths.Hook:
.claude/hooks/guard_unattended.py. APreToolUsehook (stdlib only). WithUNATTENDED=1it blocks (exit 2, reason on stderr) every tool except the three MCP read tools plusRead,GrepandGlob; withUNATTENDEDunset it allows everything. Use it for headless runs, e.g.UNATTENDED=1 claude -p "...", so an unattended agent cannot reachBashorWriteeven if it wants to. Each decision is logged to.claude/hooks/hook-audit.jsonl. The MCP tool names aremcp__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-integrationtests/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, theLIMIT n+1wrapper and thetruncatedlogic, 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 whenPG_TEST_DSNis set): against the demo DB, allowed queries work,secretsis refused at the AST level,DELETEis refused, PostgreSQL itself rejects writes sent around the guard (BEGIN READ ONLYis in effect), the role cannot readsecretseven 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
sqlglotmis-parses something PostgreSQL would run differently. That is why the server executes SQL regenerated from the validated AST, why every statement also runs in aREAD ONLYtransaction, and why the DSN should be aSELECT-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 holdEXECUTE-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
SELECTto the role (the allow-list does not look insideSELECT *, 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_tablesrow 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
FastMCPfrommcp>=1.9,<2; the 2.x SDK renamed it.
License
MIT, see LICENSE.
Available Tools
3 toolsdescribe_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
Scored across 3 tools
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.
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.
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.
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
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Related MCP Servers
- FlicenseAqualityDmaintenanceEnables AI agents to inspect and query PostgreSQL databases safely, with features like listing tables, retrieving schemas, and running read-only SQL queries.3-
- AlicenseNot gradedqualityCmaintenanceEnables 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
- AlicenseNot gradedqualityCmaintenanceEnables 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
- AlicenseNot gradedqualityBmaintenanceGives 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