Giving an AI Agent Production Data: Block Writes with Database Permissions, Not the Prompt

Short version: if you're going to show production data to an AI agent, make the database refuse writes, and keep even the reads away from the database your customers use. After this post you'll know how far a prompt, a session setting, a role and a replica each protect you, and which layer your service needs.

At work (a contract management SaaS on Rails, ECS Fargate and RDS PostgreSQL) an announcement went out: production would get an investigation read replica and a bastion server.

My first thought was: we can already exec into a production container and run rails console or SQL, so why build another road? Digging in, the old road assumed a developer using it carefully, and the user had changed. Customer support engineers would now ask an AI agent to investigate production data. Nothing had gone wrong yet. This was about stopping it before it did.

The old road: ECS Exec into a live container

ECS Exec opens a shell inside a running container. Once inside, you use the app's own database credentials. In other words you're on the writable primary with the app's permissions.

Taken apart, there were four problems.

Write protection relied on words. The query skill's doc said no update or destroy, and that was all. A person can be careful. For an AI, read only is a probabilistic promise. Miss the instruction once, or fall for one prompt injection (instructions hidden in the data), and an update_all goes out.

Reads aren't free either. Investigation queries change their conditions every time, so they rarely hit an index. A full scan of a table tens of GB in size runs on the same primary that serves customers, and exec runs inside a container that's taking web requests.

Exec sessions don't suit non-interactive use. When local stdin hit EOF, the session dropped after about 2 seconds. To have an AI send commands and read results, we needed a hack like sleep N | to hold stdin open.

The network was more open than expected. The existing database security group allowed 5432 from the entire VPC range. Put a bastion anywhere in the VPC and it could reach the primary. So we made a separate security group for the replica that only allows 5432 from the bastion. That's less about access control and more about preventing the accident of connecting to the primary by mistake.

What actually refuses a write? I tried it

Make a SELECT-only role. Open the session read-only. Replicas can't be written anyway. All three sound right, but they don't stop the same things. I ran a PostgreSQL 18.6 primary and a physical replica locally and checked with a 200,000-row contracts table.

-- Investigation role: SELECT only + default_transaction_read_only=on + statement_timeout=2s (primary)
UPDATE contracts ...                   ERROR 25006 cannot execute UPDATE in a read-only transaction
SET default_transaction_read_only = off;
UPDATE contracts ...                   ERROR 42501 permission denied for table contracts
SELECT pg_sleep(5)                     ERROR 57014 canceling statement due to statement timeout (2.1s)
SET statement_timeout = 0;
SELECT pg_sleep(3)                     OK (3.0s)

-- App role with only the read-only default (primary)
UPDATE contracts ...                   ERROR 25006
BEGIN READ WRITE; UPDATE ...; COMMIT;  OK

-- Replica, as superuser
UPDATE contracts ...                   ERROR 25006
BEGIN READ WRITE                       ERROR 0A000 cannot set transaction read-write mode during recovery
CREATE ROLE x                          ERROR 25006 cannot execute CREATE ROLE in a read-only transaction

Three things came out of this.

A read-only session is not a wall. default_transaction_read_only is a default, so the session steps past it with one BEGIN READ WRITE. Put only that on the app role and writes simply work. I honestly thought it would be enough at first.

Role privileges are a wall. Turn read-only off and a SELECT-only role still stops at permission denied. You want at least this.

A physical replica refuses even a superuser. The server is in recovery, so switching to read-write mode is itself rejected. That's also why you can't create the investigation role on the replica. Create it on the primary and it replicates over (it did in the test above).

Bonus: a statement_timeout set on the role is also just a default, and the session can set it to 0. The design at work uses 10 minutes, but that stops someone who forgot a heavy query running. It doesn't hold an AI back. That's one more reason for a separate replica: even if someone lifts the timeout, the customer-facing database doesn't slow down.

The replica catch: long reads get cancelled

Right after you build a replica, you hit this. When VACUUM (the cleanup that actually removes deleted rows) runs on the primary, it collides with queries on the replica that are still reading those rows. The replica waits a while, then cancels the query. In the same setup I started a 6-second query, deleted rows on the primary and ran VACUUM.

Replica setting                      6s query          Primary VACUUM
feedback off, wait 1s                cancelled at 2.3s removed deleted rows
feedback on,  wait 1s                finished          could not remove 66,667 rows
feedback off, wait 30s               finished          removed (replica stopped applying meanwhile)

Turn on hot_standby_feedback and the replica tells the primary it's still reading those rows, so the query survives. In exchange the primary's VACUUM can't remove them, and the longer the investigation queries, the more the big customer tables bloat. Raise max_standby_streaming_delay instead and the primary stays healthy while the replica's data falls behind by that much.

At work we chose the longer wait. The big table is over ten GB, and if it bloats the customers feel it. For investigation, data a few minutes old is fine. Given that the whole point of the replica is to leave the customer database alone, it's the natural choice.

How far to go: picking a layer

Protection	What it stops	Where it leaks	When
Read-only in the prompt or skill doc	Most mistakes	One missed instruction or one injection	Only with a human watching
default_transaction_read_only	A careless UPDATE	The session steps past with BEGIN READ WRITE	As an extra on top of role privileges
SELECT-only role	Every write	Load on the primary, reading sensitive data	The minimum
Physical replica + SELECT-only role	Writes and load on customers	Data can lag, instance cost	When an AI investigates routinely

Our setup is the last row. The bastion is a small EC2 with no public IP and no inbound rules, and SSM port forwarding brings the replica to local localhost:5432. The AI just runs psql as if it were any database. The app's database.yml has no replica config. The app never touches the replica; it exists only for investigation.

What I found interesting

Using this road requires admin rights to begin with. Anyone who has them could do anything they wanted. So this setup stops accidents, not malice. It's closer to making the default safe than to drawing a security boundary.

Replicas are usually built for read performance. Here the point is isolation. And since the role can SELECT every table, the AI can read contract bodies too. Technology doesn't solve that one. We got agreement inside the company before starting.

If you're designing a setup where an AI handles production data and aren't sure how much to lock down, you can ask negabaro. We can map your current access paths together and pick only the layers you need.