Read-only isn't one switch: giving an AI agent your database
We set up a read-only PostgreSQL role for an agent and tried to break it. The setting most people reach for, the agent could turn off itself. The grant held.
· Published
"Give the agent read-only access" sounds like one decision. In practice it is several layers, and they are not equally strong. PostgreSQL's own docs describe a read-only transaction as "a high-level notion of read-only that does not prevent all writes to disk."
So we set one up and tried to get round it. Everything below was run on PostgreSQL 17.11 on 7 November 2026, in a throwaway container.
The setup most guides suggest
CREATE ROLE agent_ro LOGIN PASSWORD '…';
GRANT pg_read_all_data TO agent_ro;
ALTER ROLE agent_ro SET default_transaction_read_only = on;
ALTER ROLE agent_ro SET statement_timeout = '2s';Three different mechanisms in four lines:
pg_read_all_datais a predefined role. In the docs' words it "allows reading all data (tables, views, sequences), as if having SELECT rights on those objects". It grants reads and nothing else. It "does not bypass row-level security (RLS) policies".default_transaction_read_onlymakes every new transaction read-only unless the session says otherwise.statement_timeoutcancels any statement that runs too long.
What happened when the agent role tried to write
Connected as agent_ro:
UPDATE orders SET status = 'x';
ERROR: cannot execute UPDATE in a read-only transactionGood. Then:
SET default_transaction_read_only = off;
SET
SHOW default_transaction_read_only;
offThe role turned its own read-only default off. Nothing stopped it. One more try at the write:
UPDATE orders SET status = 'x';
ERROR: permission denied for table ordersStill refused - but by the grant, not the setting. The read-only default was a seatbelt. The privileges were the lock.
And the timeout did its job:
SELECT pg_sleep(3);
ERROR: canceling statement due to statement timeoutThe layers, in order of strength
- Privileges. A role that was only ever granted reads cannot write,
whatever it sets in its session. This is the lock. Grant
SELECTon what the agent needs, orpg_read_all_dataif it genuinely needs everything. On MySQL the equivalent is a user with onlySELECTgranted. - A replica. Point the agent at a read replica and writes have nowhere to go. It also keeps the agent's exploratory queries off your primary.
- Read-only transactions.
default_transaction_read_onlyon PostgreSQL,SET SESSION TRANSACTION READ ONLYon MySQL. Useful as a second check and a clear error message - but, as above, a session can change its own default. - Limits. A statement timeout, plus row limits and paging in whatever sits in front of the database. These protect the database from a well-meaning agent running a full scan on your largest table.
- The tool in front. A gateway or MCP server can refuse writes for
roles that shouldn't make them, flag any statement that changed data, and
treat disguised writes as writes. In MongoDB, for example, an aggregation
that ends in
$outwrites its results to a collection - and, per MongoDB's docs, "atomically replaces the existing collection" if one is already there. A careful tool treats that as a write. - An audit trail with the agent's name on it. Not your account. When someone asks who ran that query, the answer should be "the agent".
Layers 3 to 6 are worth having. None of them replaces layer 1.
A setup we'd use
- A dedicated role per agent, granted reads only - on a replica if you have one.
default_transaction_read_only = onand astatement_timeout, as seatbelts.- A tool in front that pages results, refuses writes for read roles, and records every call against the agent's identity.
- A test, like the one above, that tries to write and fails. Run it again whenever the role or the tool changes.
That last tool layer is what we are building with Levirge DevOps - read that as disclosure. Its role and connection checks sit in front of the database's own; they are not a substitute for a read-only role.
More: Let AI agents query your production database, read-only.
Check your inbox
Confirm the subscription from the email we just sent and you're on the list.