Levirge · blog

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.

"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_data is 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_only makes every new transaction read-only unless the session says otherwise.
  • statement_timeout cancels 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 transaction

Good. Then:

SET default_transaction_read_only = off;
SET
SHOW default_transaction_read_only;
 off

The 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 orders

Still 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 timeout

The layers, in order of strength

  1. Privileges. A role that was only ever granted reads cannot write, whatever it sets in its session. This is the lock. Grant SELECT on what the agent needs, or pg_read_all_data if it genuinely needs everything. On MySQL the equivalent is a user with only SELECT granted.
  2. 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.
  3. Read-only transactions. default_transaction_read_only on PostgreSQL, SET SESSION TRANSACTION READ ONLY on MySQL. Useful as a second check and a clear error message - but, as above, a session can change its own default.
  4. 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.
  5. 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 $out writes 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.
  6. 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 = on and a statement_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.

Get new posts by email

One email when a new post goes up. No pitch, no list swapping, unsubscribe in one click.

We use your address for post notifications only. See the privacy notice.

Check your inbox

Confirm the subscription from the email we just sent and you're on the list.