"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

```sql
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](/devops/ai-database-access) - 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](/devops/ai-database-access).
