READ ONLY Doesn't Stop What You Think It Stops in Postgres

There's a comforting belief in the AI database tooling world that goes "we set the connection to read-only, so we're safe." Half the AI database MCP servers I've read source for use this as their entire security story. Anthropic's archived reference Postgres MCP did exactly this — BEGIN TRANSACTION READ ONLY at the start of every session, rollback at the end.

It's better than nothing. It is also not a security boundary. I want to walk through what it actually stops, what it doesn't stop, and what you have to do on top of it if you're going to put an LLM in front of a real Postgres.

What READ ONLY actually does

Postgres has two related but distinct things:

The session-level default_transaction_read_only setting, and the transaction-level BEGIN TRANSACTION READ ONLY. They both prevent SQL commands that would alter the data — INSERT, UPDATE, DELETE, TRUNCATE, COPY into a table — within the affected scope.

That sounds like the whole job. It isn't.

What READ ONLY mode does not prevent:

• DDL on temporary tables (some versions, some configurations).
• Calls to volatile functions that have side effects of their own.
• SELECT statements that hold locks long enough to block production writes.
• SELECT statements that consume so much memory or CPU that they degrade the database.
• SELECT statements that exfiltrate data the agent should never have seen.
• Queries that pull millions of rows, blowing past your row-budget.

The first three are correctness/reliability problems. The last three are the security problems people actually care about. None are stopped by READ ONLY.

The exfiltration problem is the big one

The use case people are nervous about with AI database access is: an agent reads data it shouldn't, that data ends up in a model context window, and from there in a log, a screenshot, or a customer's chat output.

READ ONLY does literally nothing about this. The whole READ ONLY modifier is about whether the query changes state. Reading is exactly what's allowed.

So consider the table users(id, email, password_hash, ssn_last_4, salary, manager_id). A read-only connection can issue:

SELECT email, password_hash, ssn_last_4 FROM users WHERE id = 42;

No DML. No write. Postgres is happy. The hash and SSN are now in the model's context. If you set up your AI tool to summarize that response in a Slack thread, congratulations, you just exfiltrated PII to Slack. None of this triggered any safety check.

The fix isn't a transaction mode. It's a column-level allowlist enforced before the query runs. The agent should never see password_hash or ssn_last_4 as columns it can name in a SELECT. That's a parse-and-validate step, not a database-level setting.

The cost-of-query problem is the silent one

READ ONLY does not bound how expensive a read can be. A user with a perfectly read-only role can run:

SELECT * FROM events_archive_2024 ORDER BY created_at;

Against a 4-billion-row archive table with no useful index for that ordering, this:

• Holds an AccessShareLock on the table for the duration of the scan.
• Reads gigabytes of disk.
• Possibly forces the planner to spill sort data to disk.
• Takes 20 minutes.

Your prod app starts seeing replication lag. Page load times spike. Nobody knows why because the query was "just a SELECT."

I've watched this happen. The fix involves a few independent layers, all of which need to live in the gateway, not the database role:

• Statement timeout. Postgres has statement_timeout; set it on the connection, not just trust the user.
• Row limit. Parse the SQL, ensure a LIMIT is present (or wrap the query in one).
• EXPLAIN dry-run. For destructive cost queries, run EXPLAIN first, refuse if estimated cost > a threshold.
• Read replica routing. AI traffic should never touch the primary. A separate replica with its own resource budget keeps the blast radius contained.

READ ONLY doesn't help with any of these.

The CTE-and-procedure escape hatches

This one is the most embarrassing for "read-only is enough" advocates.

Postgres allows a writable CTE inside an outer SELECT:

WITH inserted AS (
  INSERT INTO audit_log(message) VALUES ('hi') RETURNING id
)
SELECT * FROM inserted;

Your BEGIN TRANSACTION READ ONLY blocks this. Good. But:

SELECT my_dangerous_function();

Where my_dangerous_function is a VOLATILE function defined with SECURITY DEFINER that, say, sends an HTTP request to an attacker-controlled URL with the contents of the users table — READ ONLY does not stop this. The function call is a SELECT. The fact that the function did something inside isn't visible at the transaction level.

You have to either deny pg_proc access, or, more practically, parse the SQL and refuse function calls outside an allowlist.

The lock-holding problem

A long-running SELECT holds an AccessShareLock on every table it touches. That's compatible with most other readers. It's not compatible with ACCESS EXCLUSIVE operations like ALTER TABLE, DROP INDEX, or some VACUUM FULL flavors.

If your AI agent is running a slow analytical query and your migration tool tries to add a column at the same time, the migration blocks waiting for the agent's lock. Your CI deploy hangs. You roll back, swearing.

Again — READ ONLY doesn't help. The fix is timeouts and routing.

What an actual security boundary looks like

The shape of a real boundary in front of an AI database tool, in order of importance:

1. A SQL parser before the query runs. Reject DDL. Reject volatile function calls outside an allowlist. Reject queries that reference tables not in the allowlist.
2. Column allowlist. The agent should never be able to SELECT columns marked as sensitive. The check is at the parser, not the role.
3. Row limit. Add or enforce a LIMIT on every query.
4. Statement timeout. Set on every connection, low enough that a misbehaving agent can't pin a worker.
5. Read replica. Production data, but not the production primary.
6. Audit log. Every query, every result size, every user.
7. Read-only role at the database level. Last line of defense, not first.

READ ONLY is item 7. It's load-bearing only when the first six failed.

Why MCP servers keep getting this wrong

The reason every reference Postgres MCP I've read does just BEGIN TRANSACTION READ ONLY is that the alternative is genuinely a lot of code. You need a SQL parser. You need a config layer. You need an allowlist representation. You need an audit pipe. You need a way to feed all of this into the model's context so it can write queries that won't be rejected.

That's not a 130-line file. That's a product. Most reference implementations are not products.

This is also why Anthropic's reference Postgres MCP got archived. Once teams started using it for real, the wall they hit was exactly the gap between "READ ONLY transaction" and "production security boundary." The right answer wasn't to extend the reference server. The right answer was for someone to ship the gateway as a real product.

That's the lane QueryBear is in. The Postgres MCP we expose to your agents is, on the wire, almost identical to Anthropic's. The work is in everything that happens before the query reaches Postgres. SQL parser. Allowlist. Column masking. Row limits. Audit log. Read-replica routing. The thing your agent talks to is a thin wrapper. The thing keeping you safe is a thick gateway.

The takeaway

If your "AI database security" plan is "the connection is read-only," your plan is two-thirds incomplete. READ ONLY blocks the obvious thing — writes. It doesn't block exfiltration, expensive queries, lock contention, or volatile-function escapes.

You need a layer above the database that decides what's allowed before Postgres sees it. Either build it, or use one. But please stop calling BEGIN TRANSACTION READ ONLY a security boundary. It's a guardrail at the bottom of a cliff, not a fence at the top.