The transaction was read-only. psycopg had opened it with BEGIN READ ONLY. I had checked.
Then this ran inside it:
SET TRANSACTION READ WRITE; DELETE FROM orders WHERE id = 7
And order 7 was gone.
I had done this on purpose. In part 1, a SQLite MCP server survived a disguised DELETE because of one setting: mode=ro. Postgres has no single switch like that. It has three separate things that can stop a bad query: the database role, the transaction, and the timeout. Before pointing Claude at a real company database, I wanted to know which of the three holds when the other two fail. So I broke them one at a time.
The read-only transaction was the first to fall. Here is how, the one-line fix, and the server that came out of it.
What you'll build: an MCP server (about 90 lines of Python) that lets Claude list tables, read columns and run SELECT queries on Postgres, a SQL script for a read-only role, and a test that attacks each lock with the other two removed.
Code: ops-ai/mcp-postgres-readonly on GitHub.
Tested with: Python 3.13, mcp 2.3.0 (official Python SDK), psycopg 3.3.8, PostgreSQL 16.15. All output below is real output from test_server.py.
Three locks, three different failures
The server uses the same tools as part 1: list_tables, describe_table, run_query, and a schema://tables resource. What changes is where the safety lives.
-
The role. The server logs in as
mcp_reader, which can onlySELECT, and only in the schemas you allow. This stops writes and stops reads of anything else. - The transaction. Every call runs inside a read-only transaction. This stops writes even if someone grants the role too much.
-
The timeout. Every statement is cancelled after a few seconds. This is the only lock that stops a perfectly legal
SELECTthat takes your database down.
Each one covers a gap in the others. The role does nothing about slow queries. The timeout does nothing about writes. The transaction does nothing about who can read what. That is why the test removes one lock at a time instead of testing the full stack and calling it done.
Lock 1: a role that can only read
CREATE ROLE mcp_reader LOGIN;
GRANT CONNECT ON DATABASE shop TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;
ALTER ROLE mcp_reader SET default_transaction_read_only = on;
ALTER ROLE mcp_reader SET statement_timeout = '5s';
The first five lines are the lock: connect, see the public schema, read its tables, and read tables created there later. No other schema gets a grant, so the sample internal.payroll table is invisible to the model in practice: permission denied for schema internal.
The password is not in the script. Set it with \password mcp_reader in psql, and pass it to the server through a DATABASE_URL environment variable.
The last two lines look like locks. They are not. They are defaults, and any session can change its own defaults. The test logs in as mcp_reader with a plain client and does exactly that:
role defaults are not locks: statement_timeout 5s -> 0
Keep them. They protect you when someone connects as mcp_reader from psql or a BI tool. But the server has to enforce its own transaction and timeout, which is where it went wrong first.
Lock 2: the read-only transaction, and how I unlocked it
psycopg 3 has a clean way to ask for read-only transactions: set conn.read_only = True, and every transaction it opens is read-only. My first version did that and ran the model's SQL with a plain conn.execute(sql).
The problem is a detail in psycopg's docs. With query parameters, psycopg uses Postgres's extended protocol, which accepts one statement. Without parameters, it accepts several, separated by semicolons. A model's free-form SQL has no parameters.
Postgres, for its part, lets you change a transaction from read-only to read-write as long as no query has run yet. So SET TRANSACTION READ WRITE as the first statement simply unlocks the transaction. To see this alone, the test grants DELETE to the role on purpose (lock 1 broken), then runs the query from the opening:
--- control: lock 1 broken, read_only=True, plain conn.execute()
orders after: 6 (one row deleted inside a 'read-only' transaction)
A read-only transaction that the query can switch off is a request, not a lock.
The fix is to run the model's SQL through a server-side cursor:
def connect() -> psycopg.Connection:
conn = psycopg.connect(
DATABASE_URL, autocommit=True, row_factory=dict_row, connect_timeout=5,
application_name="mcp-postgres-readonly",
options=f"-c statement_timeout={TIMEOUT_MS} -c search_path={','.join(SCHEMAS)}",
)
conn.read_only = True # every transaction starts as BEGIN READ ONLY
return conn
def fetch(sql: str, params=None, limit: int = MAX_ROWS) -> list[dict]:
try:
with connect() as conn, conn.transaction():
with conn.cursor(name="mcp_query") as cur: # server-side cursor
cur.execute(sql, params)
return [dict(r) for r in cur.fetchmany(limit)]
except psycopg.Error as e:
raise ToolError(f"Postgres error: {str(e).strip().splitlines()[0]}")
Passing name= makes psycopg send DECLARE mcp_query CURSOR FOR <your sql>. That changes three things at once:
-
One statement only. The
DECLAREgoes over the extended protocol, so a second statement is a syntax error. -
Postgres checks the shape, not my Python. A cursor can only be declared for a
SELECTorVALUESquery.DELETEis a syntax error. AWITHthat deletes is refused by name:DECLARE CURSOR must not contain data-modifying statements in WITH. -
The row cap is real.
fetchmany(200)on a normal cursor still pulls the full result into Python first. A server-side cursor sends only the rows you fetch.
Part 1 said a text filter is a courtesy. This is the version of that filter worth having, because the database's own parser runs it.
Two smaller choices sit in connect(). A fresh connection per call, not a pool: a query can run SELECT set_config('statement_timeout', '0', false), and I confirmed that setting survives the commit. In a pool, the next call would inherit it. And application_name, so every query from the model is easy to find in pg_stat_activity.
Lock 3: the timeout
A read-only role and a read-only transaction will happily run a five-table cross join on your production primary. Only a timeout stops that. The server sets it on every connection with -c statement_timeout, so it does not rely on the role default that a session can lift.
Can the query lift it from inside? I tried, in a single SELECT:
--- slow queries, timeout 2000 ms
BLOCKED (2.0s): SELECT pg_sleep(10)
-> Postgres error: canceling statement due to statement timeout
BLOCKED (2.0s): SELECT set_config('statement_timeout', '0', true), pg_sleep(10)
-> Postgres error: canceling statement due to statement timeout
The timer starts when the statement starts. Changing the setting halfway through does not reset it, and the cursor stops the model from running a SET first.
The test: break one lock at a time

Remove any one lock and the attack still stops at another. The control shows what happens when the query can unlock the transaction itself.
test_server.py loads the sample data, creates the role, starts the server over stdio and connects with the SDK's Client, the same way Claude does. Then it runs the same attacks with different locks removed.
Round 1: all locks in place. The disguised writes from part 1, plus the Postgres versions, all stop at the cursor. The interesting one is a write that is a valid SELECT. The sample database has a purge_order(id) helper function, the kind many app databases have, and Postgres grants EXECUTE on functions to everyone by default:
BLOCKED (0.0s): SELECT purge_order(7)
-> Postgres error: cannot execute DELETE in a read-only transaction
No text filter would catch this. It starts with SELECT, has no semicolon and names no table.
Round 2: lock 1 broken. The test runs GRANT ALL ON ALL TABLES IN SCHEMA public TO mcp_reader, the kind of rushed fix that ends up in real databases. The same call stops at the transaction: cannot execute DELETE in a read-only transaction.
Round 3: lock 2 absent. A plain client logs in as mcp_reader, with no cursor and no read-only transaction, and lifts the read-only default itself:
--- lock 2 absent (plain client as mcp_reader)
BLOCKED (0.0s): SET TRANSACTION READ WRITE; DELETE FROM orders
-> permission denied for table orders
BLOCKED (0.0s): SET TRANSACTION READ WRITE; SELECT purge_order(7)
-> permission denied for table orders
The function runs with the caller's rights, so the role's missing DELETE grant stops it.
Lock 3 alone. The slow queries above. Neither the role nor the transaction has anything to say about a legal SELECT, so the timeout is the only thing that stops them.
The test ends by counting rows as admin: orders still in database: 7, then ALL PASSED.
A lock you have only tested together with the others is an assumption, not a lock.
Connecting it to Claude
Setup is the same as in part 1, with one change: the connection string is a secret. In Claude Code, use the default local scope so it stays out of the repository's .mcp.json:
claude mcp add --transport stdio --scope local shopdb \
--env DATABASE_URL="postgresql://mcp_reader:...@replica-host:5432/shop" \
-- uv run --directory /full/path/to/mcp-postgres-readonly server.py
The repo includes a .env.example and the Claude Desktop config. Point it at a read replica if you have one.
When this is the wrong choice
- Customer-facing assistants. Do not give end users free-form SQL, however locked. Expose narrow tools with parameters.
-
Sensitive columns in allowed tables. Read-only is not "safe to read". Whatever
mcp_readercan select, the model can see. Grant views, not tables, or use row-level security. -
Hiding that a table exists.
pg_catalogis readable by every role. When I checked,mcp_readercould not readinternal.payrollbut could see its name. - High call volume. A connection per call costs milliseconds locally and more over a network. If you need a pool, reset the session on every return.
-
Side effects outside tables. A read-only transaction blocks table writes, not everything. Keep the role out of powerful built-in roles like
pg_read_server_files, and do not make it a member of your app's roles.
The question to ask about every lock
The fix here was one keyword argument, name="mcp_query". Without it, the server had a read-only role, a read-only transaction and a timeout, and it looked like defense in depth. It was three locks, and one of them opened from the inside.
At sea, a padlock you can open from the inside isn't a padlock. It's a sign. You only find out which one you have by taking the others away. So for every lock in your own setup, ask: if this were the only one left, what would get through? Then write the test that removes the others and find out.
The server, role script and lock-by-lock test are in ops-ai/mcp-postgres-readonly. If you find a query that gets past all three, open an issue. If it helped, a star on the repo helps other people find it.
References
- psycopg 3, Differences from psycopg2: multiple statements in the same query
- psycopg 3, Transaction management and transaction characteristics
- psycopg 3, Cursor types: server-side cursors
- PostgreSQL,
SET TRANSACTION - PostgreSQL,
DECLARE - PostgreSQL, Client connection defaults:
statement_timeout,default_transaction_read_only - PostgreSQL,
GRANT - MCP Python SDK, v2 migration guide
All code was run with Python 3.13, mcp 2.3.0, psycopg 3.3.8 and PostgreSQL 16.15. Results shown are actual output from test_server.py.
Top comments (0)