Read-only Postgres for an AI agent: only the GRANT holds
"Can I point Claude at the production database?" is one of the questions people now ask most about agents, and the usual answer is "yes, read-only". The trouble is that read-only means three different things in Postgres,
"Can I point Claude at the production database?" is one of the questions people now ask most about agents, and the usual answer is "yes, read-only". The trouble is that read-only means three different things in Postgres, and two of them are settings the session itself is allowed to change.
This is not hypothetical. In August 2025 Datadog Security Labs showed that the reference Postgres MCP server, @modelcontextprotocol/server-postgres, could be made to drop every table in a database it was supposed to only read (their write-up). The server wrapped each query in BEGIN TRANSACTION READ ONLY, and the payload simply started with COMMIT;. The package was deprecated in July 2025; Datadog counted about 21,000 weekly npm downloads of it anyway.
So we took the three layers people usually reach for, ran each one against a payload an agent (or a prompt injection riding inside one) could send, and counted rows. PostgreSQL 16.15, a throwaway cluster, a table called orders with three rows in it.
The short answer
| what you rely on | payload | result |
|---|---|---|
the client wraps every query in BEGIN TRANSACTION READ ONLY
|
SELECT 1; COMMIT; DELETE FROM orders WHERE id = 3 |
row deleted (3 → 2) |
ALTER ROLE … SET default_transaction_read_only = on, role has write grants |
SET default_transaction_read_only = off; COMMIT; DELETE … |
row deleted (2 → 1) |
| same role | COMMIT; BEGIN READ WRITE; DELETE …; COMMIT |
row deleted (1 → 0) |
role with only GRANT SELECT
|
both payloads above |
ERROR: permission denied for table orders, rows unchanged |
Read-only transactions and read-only defaults are session state. Anything that can send SQL can change session state. Privileges are the only one of the three the session cannot talk its way out of.
Layer 1: the read-only transaction wrapper
This is what the old MCP server did, and what plenty of home-made tools still do: open a read-only transaction, run whatever text came in, roll back.
BEGIN TRANSACTION READ ONLY;
-- the "query" the agent sent:
SELECT 1; COMMIT; DELETE FROM orders WHERE id = 3;
-- the wrapper's cleanup:
ROLLBACK;
The COMMIT closes the read-only transaction. The DELETE then runs in a fresh, ordinary transaction and commits on its own. The wrapper's ROLLBACK comes too late and Postgres says so: WARNING: there is no transaction in progress. In our run the row was gone.
The wrapper only works if the client refuses to send more than one statement at a time. Datadog's suggested patch does exactly that (prepared statements, which accept a single statement, and a fresh connection for every call). If your tool passes a string to a simple-protocol query, assume it doesn't.
Layer 2: default_transaction_read_only on the role
Several guides recommend this as the database-side fix:
ALTER ROLE agent SET default_transaction_read_only = on;
It is a default. The role can override it for its own session, and nothing stops it:
SET default_transaction_read_only = off; COMMIT; DELETE FROM orders WHERE id = 2;
or, without touching the setting at all:
COMMIT; BEGIN READ WRITE; DELETE FROM orders WHERE id = 1; COMMIT;
Both deleted their row in our test. If the role holds INSERT, UPDATE or DELETE on a table, those privileges are what decide, and the default is a polite suggestion.
Layer 3: a role that can only SELECT
CREATE ROLE agent LOGIN PASSWORD '…';
GRANT CONNECT ON DATABASE app TO agent;
GRANT USAGE ON SCHEMA public TO agent;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO agent;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO agent;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
Same two payloads: ERROR: permission denied for table orders, three rows before and three after. The agent can close transactions and flip settings all day; the table still refuses the write.
That is the floor. But it has two holes worth knowing about before you call it done.
Hole 1: functions run with their owner's rights
Postgres grants EXECUTE on new functions to PUBLIC by default. If your schema has a SECURITY DEFINER function that writes, your SELECT-only role can call it:
-- created earlier by the table owner:
CREATE FUNCTION purge_order(i int) RETURNS void
LANGUAGE sql SECURITY DEFINER AS 'DELETE FROM orders WHERE id = i';
-- as the SELECT-only agent role:
SELECT purge_order(3); -- works; the row is gone
We ran exactly that and lost the row. The fix is to take EXECUTE back, and to stop handing it out by default:
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM PUBLIC;
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
-- then GRANT EXECUTE on the read-only functions the agent actually needs
After both, the same call answered ERROR: permission denied for function purge_order, for the old function and for one created afterwards. Check what else is exposed before you trust the role:
SELECT p.oid::regprocedure
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND has_function_privilege('agent', p.oid, 'EXECUTE');
Extensions and their functions count too.
Hole 2: limits set on the role are defaults as well
statement_timeout is the usual guard against an agent running a query that scans everything. Set on the role, it behaves just like the read-only default:
ALTER ROLE agent SET statement_timeout = '2s';
SELECT pg_sleep(3); -- cancelled, as intended
SET statement_timeout = 0; SELECT pg_sleep(3); -- runs to completion
So a role can't be relied on to limit its own load. Datadog describes the nastier side of the same thing: on a pooled connection, a payload like COMMIT; SET statement_timeout TO 1; leaves the setting behind for whoever gets that connection next. If load matters, point the agent at a replica, cap its connections (ALTER ROLE agent CONNECTION LIMIT 2), and make the client send one statement per call on a connection it doesn't share.
One more thing a SELECT-only role can still do: create temporary tables, because TEMP on the database is granted to PUBLIC by default. That is harmless to your data. Revoke it (REVOKE TEMP ON DATABASE app FROM PUBLIC) if you want the role to write nothing at all.
What a client can add, on top of the role
The role answers "what can this connection do". It doesn't answer "should the agent do this right now", "what did it look at", or "did it just read a column of customer emails into a model's context". That is the client's job, and it only works with the role underneath it.
This is the part we build. Tabellum is a database client for PostgreSQL, MySQL and MariaDB, SQL Server, BigQuery, SQLite and MongoDB, and its agent, together with any agent you connect over MCP, works under these rules:
- The app classifies the SQL, not the model. Its own parser classifies every statement, and a label the model puts on a statement never overrides it.
-
Anything that isn't a read waits for a click. Writes, DDL and admin statements show up in an approval dialog with the exact SQL, and run in a transaction where the database supports one. Over MCP they go through
propose_write, which returnsdeclined: timeoutif nobody approves within ten minutes. - Rows are opt-in per connection. The agent sees row values only where the connection shares them. Elsewhere it gets tables, columns and counts.
-
The MCP server listens on
127.0.0.1only, with a per-install token.
Those are checks in the client. They stop the agent from writing behind your back through Tabellum. They do nothing about a password that leaks, a second tool on the same credentials, or a bug of ours. Give Tabellum the SELECT-only role as well. The GRANT is the floor in any client, ours included, and everything above it is a second line.
The details are in the MCP setup README, and there's a browser demo if you want to see the approval dialog before installing anything.
Checklist
- A dedicated login role for the agent. Never the application's role.
-
GRANT SELECTon what it should read, nothing more. Default privileges for new tables. -
REVOKE CREATE ON SCHEMA public FROM PUBLIC. -
REVOKE EXECUTE … FROM PUBLICon functions, plusALTER DEFAULT PRIVILEGES; then list theSECURITY DEFINERfunctions the role can still run. - Optionally
REVOKE TEMP ON DATABASE … FROM PUBLIC. - Don't count on
default_transaction_read_onlyor a role-levelstatement_timeoutas limits. They are defaults the session can change. - A replica and a connection limit if load matters.
- A client that sends one statement per call, and asks before anything that writes.
Run it yourself
Everything above takes two minutes on a scratch cluster. This script needs a local PostgreSQL 16 with the Debian/Ubuntu paths and root (it runs initdb as postgres on port 5499 and removes the cluster at the end). Never run it near a real database.
#!/usr/bin/env bash
set -u
D=/var/tmp/pgt; B=/usr/lib/postgresql/16/bin
rm -rf $D; mkdir -p $D; chown postgres $D
su postgres -c "$B/initdb -D $D/data -A trust >/dev/null && $B/pg_ctl -D $D/data -o '-p 5499 -k $D' -l $D/log start >/dev/null"; sleep 2
P="psql -h $D -p 5499 -U postgres -X -q"; n(){ $P -tAc "select count(*) from orders" postgres; }
$P -c "create table orders(id int); insert into orders values (1),(2),(3);
create role ro_default login; grant select,insert,delete on orders to ro_default;
alter role ro_default set default_transaction_read_only=on;
create role ro_grant login; grant select on orders to ro_grant; revoke create on schema public from public;" postgres
echo "rows=$(n)"
echo "A: read-only wrapper, payload 'SELECT 1; COMMIT; DELETE'"
$P -c "BEGIN TRANSACTION READ ONLY" -c "SELECT 1; COMMIT; DELETE FROM orders WHERE id=3" -c "ROLLBACK" postgres >/dev/null 2>&1; echo " rows=$(n)"
echo "B: default_transaction_read_only=on, payload 'SET ... = off; COMMIT; DELETE'"
$P -U ro_default -c "SET default_transaction_read_only = off; COMMIT; DELETE FROM orders WHERE id=2" postgres >/dev/null 2>&1; echo " rows=$(n)"
echo "B2: same role, payload 'COMMIT; BEGIN READ WRITE; DELETE; COMMIT'"
$P -U ro_default -c "COMMIT; BEGIN READ WRITE; DELETE FROM orders WHERE id=1; COMMIT" postgres >/dev/null 2>&1; echo " rows=$(n)"
$P -c "insert into orders values (1),(2),(3)" postgres
echo "C: SELECT-only role, both payloads"
$P -U ro_grant -c "SET default_transaction_read_only = off; COMMIT; DELETE FROM orders" postgres 2>&1 | grep -i error
$P -U ro_grant -c "COMMIT; BEGIN READ WRITE; DELETE FROM orders; COMMIT" postgres 2>&1 | grep -i error; echo " rows=$(n)"
echo "D: SELECT-only role calls a SECURITY DEFINER function"
$P -c "create function purge_order(i int) returns void language sql security definer as 'delete from orders where id = i'" postgres
$P -U ro_grant -c "SELECT purge_order(3)" postgres >/dev/null 2>&1; echo " rows=$(n)"
$P -c "revoke execute on function purge_order(int) from public" postgres
$P -U ro_grant -c "SELECT purge_order(2)" postgres 2>&1 | grep -i error; echo " rows=$(n)"
su postgres -c "$B/pg_ctl -D $D/data stop" >/dev/null; rm -rf $D
Our output, PostgreSQL 16.15:
rows=3
A: read-only wrapper, payload 'SELECT 1; COMMIT; DELETE'
rows=2
B: default_transaction_read_only=on, payload 'SET ... = off; COMMIT; DELETE'
rows=1
B2: same role, payload 'COMMIT; BEGIN READ WRITE; DELETE; COMMIT'
rows=0
C: SELECT-only role, both payloads
ERROR: permission denied for table orders
ERROR: permission denied for table orders
rows=3
D: SELECT-only role calls a SECURITY DEFINER function
rows=2
ERROR: permission denied for function purge_order
rows=2
Originally published at https://tabellum.dudko.dev/blog/ai-agent-read-only-postgres-only-the-grant-holds.
Originally published by Dev.to Security. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.