pg-readonly-mcp

A small MCP server that lets Claude query a PostgreSQL database read-only, with a table allow-list, a row limit, a statement timeout, and an audit log of every query. Written in Python, runs locally with Claude Desktop over stdio.

Ask Claude "Which country brought in the most paid revenue in March?" against the demo database and it calls list_tables, describe_table("orders"), then run_query with something like:

SELECT c.country, sum(o.amount) AS revenue
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid' AND o.created_at >= '2026-03-01' AND o.created_at < '2026-04-01'
GROUP BY 1 ORDER BY 2 DESC

which returns DE 4352.26, US 3500.06, FR 3089.25, IN 2564.44, GB 2529.24. Ask it for the api_keys table and the server refuses, tells Claude why, and logs the attempt.

What Claude gets

All four are marked readOnlyHint so MCP clients know they change nothing.

Related MCP server: postgres-mcp-lab

How it stays read-only

Three independent walls, so a mistake in one is caught by the next:

1. The guard (src/pg_readonly_mcp/guard.py) parses each query with sqlglot and refuses anything that is not exactly one plain read: no INSERT/UPDATE/DELETE/DDL, no SELECT ... INTO, no FOR UPDATE, no data-modifying CTEs, no semicolon batches. Every table reference is resolved by PostgreSQL itself (to_regclass in the same session and search_path), so pg_class is checked as pg_catalog.pg_class and Orders as public.orders, exactly as the database will read them, and then checked against the allow-list. Functions that read files, run SQL from a string (query_to_xml), reach other servers (dblink), sleep, change settings or touch sequences are refused, and unknown functions need to be allow-listed. A CTE may not reuse a table's name (that trick is how a query can hide a real table behind an alias). The statement that runs is regenerated from the checked parse tree, so a tokenising quirk in the original text cannot smuggle in anything the guard did not see.

2. The transaction. Every call runs in its own BEGIN READ ONLY transaction with statement_timeout and lock_timeout, then rolls back. Rows are read through a server-side cursor, so the row limit holds without PostgreSQL sending the whole result. The server refuses to run as a superuser unless you say so.

3. The role. Connect as a role that can only SELECT the tables you expose (SQL below). If the role can write to an allow-listed table, the server warns at list_tables.

The test suite checks each wall separately, including a test that skips the guard on purpose and confirms PostgreSQL still refuses the write.

Quick start (about 5 minutes)

You need Python 3.10 or newer and either Docker (for the demo database) or any PostgreSQL 13+.

git clone https://github.com/vinit1009/pg-readonly-mcp.git
cd pg-readonly-mcp
python3 -m venv .venv
.venv/bin/pip install -e .

# A demo shop database on localhost:54329, with a read-only role mcp_readonly / readonly
docker compose up -d --wait

Try it from the terminal before involving Claude. --check connects and prints what the server can see; --query runs one query through exactly the same guard:

export MCP_PG_DSN="postgresql://mcp_readonly:readonly@localhost:54329/shop"
export MCP_PG_ALLOWED_TABLES="public.customers, public.orders, public.order_totals, analytics.*"

.venv/bin/pg-readonly-mcp --check
.venv/bin/pg-readonly-mcp --query "SELECT status, count(*) FROM orders GROUP BY 1 ORDER BY 1"
.venv/bin/pg-readonly-mcp --query "SELECT * FROM api_keys"     # refused: not in the allow-list
.venv/bin/pg-readonly-mcp --query "DELETE FROM orders"         # refused: only SELECT

Connect Claude Desktop

1. Open Claude Desktop's config file (Settings → Developer → Edit Config), which lives at ~/Library/Application Support/Claude/claude_desktop_config.json on macOS and %APPDATA%\Claude\claude_desktop_config.json on Windows.

2. Add the server, using the absolute path to the script inside your .venv (examples/claude_desktop_config.json has the same block):

   {
     "mcpServers": {
       "shop-db": {
         "command": "/ABSOLUTE/PATH/TO/pg-readonly-mcp/.venv/bin/pg-readonly-mcp",
         "env": {
           "MCP_PG_DSN": "postgresql://mcp_readonly:readonly@localhost:54329/shop",
           "MCP_PG_ALLOWED_TABLES": "public.customers, public.orders, public.order_totals, analytics.*"
         }
       }
     }
   }

   On Windows the command is C:\\path\\to\\pg-readonly-mcp\\.venv\\Scripts\\pg-readonly-mcp.exe.

3. Quit and reopen Claude Desktop. The server and its four tools then show up in the chat's tools menu.

4. Ask something: "Which country brought in the most paid revenue in March?" or "Show me the api_keys table" (Claude will be told it is not allowed).

If the server does not appear, Claude Desktop's MCP log shows why: ~/Library/Logs/Claude/mcp-server-shop-db.log on macOS. A missing or bad setting is reported there in one line.

Configuration

Everything is an environment variable, so it fits in Claude Desktop's env block.

A read-only role for your own database

CREATE ROLE mcp_readonly LOGIN PASSWORD 'choose-a-password';
ALTER ROLE mcp_readonly SET default_transaction_read_only = on;
GRANT CONNECT ON DATABASE yourdb TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON public.customers, public.orders TO mcp_readonly;   -- only what Claude should see

To hide columns (emails, notes), create a view with the columns you want, grant SELECT on the view instead of the table, and allow-list the view. Row-level security policies apply as usual; the server warns if the role bypasses them.

The audit log

One JSON line per tool call, whatever the outcome: refused queries keep the reason, failed queries keep PostgreSQL's error, and successful ones keep the tables read, row count, whether the result was truncated, the time taken and the exact statement that ran.

{"at": "2026-11-09T04:00:27.835+00:00", "tool": "run_query", "sql": "SELECT c.country, sum(o.amount) AS revenue FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'paid' AND o.created_at >= '2026-03-01' AND o.created_at < '2026-04-01' GROUP BY 1 ORDER BY 2 DESC", "tables": ["public.customers", "public.orders"], "executed_sql": "SELECT c.country, SUM(o.amount) AS revenue FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.status = 'paid' AND o.created_at >= '2026-03-01' AND o.created_at < '2026-04-01' GROUP BY 1 ORDER BY 2 DESC", "status": "ok", "rows": 5, "truncated": false, "ms": 15}
{"at": "2026-11-09T04:00:28.167+00:00", "tool": "run_query", "sql": "SELECT * FROM api_keys", "status": "rejected", "reason": "public.api_keys is not in this server's allow-list. Call list_tables to see what you can query.", "ms": 12}

jq makes it easy to review: jq -c 'select(.status != "ok")' ~/.pg-readonly-mcp/audit.jsonl.

Tests

.venv/bin/pip install -e ".[test]"
.venv/bin/pytest

The tests start a throwaway PostgreSQL cluster with the local initdb and pg_ctl, or use TEST_PG_ADMIN_DSN (a superuser connection string) when it is set, as in CI. They load examples/seed.sql, then cover:

• queries that must work: joins, CTEs, window functions, FILTER, percentile_cont, DISTINCT ON, LATERAL, JSON operators, views, materialized views, schema.* entries, identifier case folding;

• 35 queries that must be refused, from plain DELETE to the less obvious ones: a CTE named after the hidden table, a backslash-quote string trick, unqualified pg_class, query_to_xml, pg_read_file, set_config, a DELETE inside a CTE, FOR UPDATE, SELECT INTO, semicolon batches;

• the row limit, the statement timeout (and that the session recovers), JSON-safe cell values and clipping;

• the database wall on its own: a superuser is refused, and a role that can write is warned about and still cannot write;

• the audit log for ok, refused and failed calls;

• a full MCP session over stdio, the transport Claude Desktop uses.

Limits

• One database per server process. For two databases, add two entries to the Claude Desktop config.

• The guard decides what is read; the database role decides what can be read. Keep the role narrow and treat the allow-list as the second lock, not the only one.

• Column-level rules are done with views (above), not in the server.

Licence

MIT