How to stop an AI agent from dropping your production database
Quick answer: Instructions in a prompt do not stop anything. Give the agent its own login role that is not a superuser, owns no table and owns no schema, point it at a database that is not production, and prove it by running the destructive statement yourself and confirming that the server refuses it. Until you have tried it, you do not know whether the control works.
What is the problem?
Section titled “What is the problem?”The stories have the same shape. An agent is asked to fix a failing test, decides the schema is wrong, and resets it. Or it is asked to clean up test data and runs the cleanup against the connection string it happens to have.
None of this requires the model to misbehave. The credential it received only needs to permit the operation.
The useful questions are what this credential can do and whether you have checked.
The control that works: privileges
Section titled “The control that works: privileges”Give the agent its own role, separate from the application’s role and from yours.
CREATE ROLE ai_agent LOGIN PASSWORD 'use-a-generated-secret' NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5;Then grant it reading privileges and nothing else:
GRANT CONNECT ON DATABASE app TO ai_agent;GRANT USAGE ON SCHEMA public TO ai_agent;GRANT SELECT ON ALL TABLES IN SCHEMA public TO ai_agent;ON ALL TABLES means the tables that exist now. Tables created later receive no
grant to this role, which fails safe. Tables created later by a role with
configured default privileges may receive more access than intended. Set the
future case deliberately, once per creating role:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO ai_agent;DROP TABLE is not a privilege that you grant or revoke. The operation is
allowed for the table’s owner, the owner of the schema the table is in, and
any superuser. The schema owner is easy to miss. Measured on PostgreSQL 17.11,
with schema data owned by agent and table data.t owned by appowner, and
no grant of any kind to agent on that table:
app=> DROP TABLE data.t;DROP TABLEChecking that the agent owns no tables is not enough. Check both:
SELECT tablename, tableownerFROM pg_tablesWHERE schemaname NOT IN ('pg_catalog', 'information_schema')ORDER BY schemaname, tablename;
SELECT nspname AS schema, pg_get_userbyid(nspowner) AS schema_ownerFROM pg_namespaceWHERE nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema';The agent’s role in tableowner means it can drop that table. Its role in
schema_owner means it can drop every table in that schema, regardless of who
owns them. Check the database owner too. On PostgreSQL 15 and later, the
database owner is a member of pg_database_owner, which owns public:
SELECT datname, pg_get_userbyid(datdba) AS database_owner FROM pg_database;Two more settings belong on the role because a read-only agent can still cause harm by reading too much:
ALTER ROLE ai_agent SET statement_timeout = '30s';ALTER ROLE ai_agent SET idle_in_transaction_session_timeout = '60s';The control that does not work: a read-only session
Section titled “The control that does not work: a read-only session”This setting is widely recommended, but it is not a boundary.
ALTER ROLE ai_agent SET default_transaction_read_only = on;That setting is a default, and the client can change it. Measured on PostgreSQL 17.11 in a container, four times, because the cases behave differently:
| what the session did | result |
|---|---|
INSERT with the setting on | ERROR: cannot execute INSERT in a read-only transaction |
SET default_transaction_read_only = off; then the same INSERT | INSERT 0 1 |
inside BEGIN READ ONLY, the same SET, then INSERT | still refused |
inside BEGIN READ ONLY, SET TRANSACTION READ WRITE; then INSERT | INSERT 0 1, and it committed |
The third row is the case people rely on, and it does hold: once a transaction has started read-only, changing the default does not change that transaction. The fourth row is the escape, and it takes one statement.
With the table privileges revoked instead, the same test gave permission denied for table t in all four cases.
Set the setting anyway because it catches honest mistakes. Do not count it as the control.
The write path that survives having no write privileges
Section titled “The write path that survives having no write privileges”SELECT is not automatically safe because a function can write on the caller’s
behalf. SECURITY DEFINER runs the function body as its owner, and PostgreSQL
grants EXECUTE on new functions to PUBLIC by default. A function that no
one deliberately granted is therefore callable by every role in the database.
Measured on 17.11, role agent held SELECT on public.w and nothing at all
on public.ledger or on the function:
app=> INSERT INTO public.w VALUES (1);ERROR: permission denied for table wapp=> SELECT public.record('written by the read-only role'); record--------
app=> SELECT * FROM public.ledger;ERROR: permission denied for table ledgerConnected as postgres, the row was in ledger. The role could not read the
table it had just written to.
Audit functions as well as tables:
SELECT n.nspname, p.proname, pg_get_userbyid(p.proowner) AS owner, p.proaclFROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespaceWHERE p.prosecdef AND n.nspname NOT IN ('pg_catalog', 'information_schema');A proacl of null means default privileges, so PUBLIC can execute the
function. REVOKE EXECUTE ON FUNCTION ... FROM PUBLIC is the fix, applied per
function.
How do I confirm it?
Section titled “How do I confirm it?”Do not read the grants and conclude. Connect as the agent’s role and try the operation you believe is impossible.
SELECT current_user, session_user;Then attempt a write and expect the server to refuse it. Check what the role holds:
SELECT table_name, privilege_typeFROM information_schema.table_privilegesWHERE grantee = 'ai_agent'ORDER BY table_name, privilege_type;INSERT, UPDATE, DELETE, TRUNCATE or REFERENCES in that output for a
role you believed was read-only is a problem. It is common: GRANT ALL appears
in many setup scripts.
That query has a blind spot. It matches grantee literally, so it misses
privileges the role receives through a group and privileges granted to
PUBLIC, which every role holds. Ask the server what the role can do instead
of reading the grant list:
SELECT c.relname, has_table_privilege('ai_agent', c.oid, 'INSERT') AS ins, has_table_privilege('ai_agent', c.oid, 'UPDATE') AS upd, has_table_privilege('ai_agent', c.oid, 'DELETE') AS del, has_table_privilege('ai_agent', c.oid, 'TRUNCATE') AS truncFROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespaceWHERE c.relkind IN ('r', 'p') AND n.nspname NOT IN ('pg_catalog', 'information_schema')ORDER BY c.relname;has_table_privilege follows role membership and PUBLIC, so a true result is
true regardless of the path. List memberships too. A role inherits whatever its
groups hold, and the built-in pg_write_all_data grants access to every table:
SELECT r.rolname AS member_ofFROM pg_auth_members mJOIN pg_roles r ON r.oid = m.roleidWHERE m.member = 'ai_agent'::regrole;Then check where the role can create objects. Anything it creates it owns, and anything it owns it can drop:
SELECT nspname, has_schema_privilege('ai_agent', nspname, 'CREATE') AS can_createFROM pg_namespaceWHERE nspname NOT LIKE 'pg\_%' AND nspname <> 'information_schema';PostgreSQL 15 stopped granting CREATE on schema public to PUBLIC, so a
database created on 15 or later does not have the problem. A database upgraded
from an earlier version usually still does. Do not infer the setting from the
version: pg_upgrade preserves the old ACL, and somebody may have granted it
back. Read it:
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';On a fresh 17.11 cluster that returns
{pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}. The empty
grantee before the = is PUBLIC, and its privileges are U for USAGE
alone. A UC there is the one to remove:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;A backstop for the case where it does own things
Section titled “A backstop for the case where it does own things”In a development database, the agent may legitimately own objects while still having no business dropping them. An event trigger refuses the command itself:
CREATE OR REPLACE FUNCTION refuse_drop() RETURNS event_triggerLANGUAGE plpgsql AS $$BEGIN RAISE EXCEPTION 'DROP is disabled on this database';END;$$;
CREATE EVENT TRIGGER no_drop ON ddl_command_start WHEN TAG IN ('DROP TABLE', 'DROP SCHEMA') EXECUTE FUNCTION refuse_drop();DROP DATABASE cannot be in that list. Adding it produces no trigger because
the CREATE EVENT TRIGGER statement itself is rejected:
ERROR: event triggers are not supported for DROP DATABASEThe PostgreSQL manual’s event trigger firing matrix says the same thing in the
other direction: DROP DATABASE is a shared-object command and is not listed
as a supported tag. Nothing at the database level can intercept it. The manual
says the event trigger mechanism does not support shared objects, which are
databases, roles and tablespaces.
Ownership is the only control that stops it. Two controls that look useful are
not. Measured on 2026-09-20 on PostgreSQL 18.6 in a stock
postgres:18.6 container, a NOCREATEDB NOSUPERUSER role that owned
appdb dropped it while another of its own sessions was still connected:
$ psql -U appowner -d postgres -c 'DROP DATABASE appdb;'ERROR: database "appdb" is being accessed by other usersDETAIL: There is 1 other session using the database.$ psql -U appowner -d postgres -c 'DROP DATABASE appdb WITH (FORCE);'DROP DATABASEThe manual documents both the force option and the one control it leaves standing:
It can only be executed by the database owner.
FORCE: Attempt to terminate all existing connections to the target database. It doesn’t terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.
Connected sessions are not a barrier when the owner may terminate them, and the
role attribute is not one either: CREATEDB governs creating databases, not
dropping them. Ownership is the control that held. In that same container, the
same role tried to drop a database owned by postgres and got ERROR: must be owner of database appdb2, both before and after being granted CREATEDB.
Give the agent’s role a database it does not own.
With the tag removed, the trigger creates. Measured on 17.11, it refused
DROP TABLE and DROP SCHEMA from postgres itself:
postgres=# DROP TABLE public.t;ERROR: DROP is disabled on this databaseIt does not protect itself. In the same session, with the trigger active:
postgres=# DROP EVENT TRIGGER no_drop;DROP EVENT TRIGGERThis stops accidents rather than determined misuse: two statements get past it,
and the first is the kind of thing an agent reading an error message will try.
Only a superuser can create it, only a superuser can drop it, and only a
superuser or a role granted SET on the parameter can set
event_triggers = off to bypass it globally. A non-superuser trying that got
ERROR: permission denied to set parameter "event_triggers".
That is acceptable for a backstop as long as the agent’s role is not a
superuser, which is the first line of this page.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”Destructive commands are the correct answer to many questions, and the model cannot know which database it is connected to. “Reset the schema so the migration applies cleanly” has one obvious implementation. So does “clear out the test rows”. The generated statement is what you asked for; the connection string is what makes it a disaster.
The second pattern is environment confusion. DATABASE_URL in the shell is
whatever was exported last. An agent reading it has no signal that it points to
production because nothing in the string says so.
How do I stop it coming back?
Section titled “How do I stop it coming back?”One credential per purpose. The agent’s role is not the application’s role or a person’s role, so the audit trail identifies the actor and revoking the role affects one purpose.
Make production access deliberate. Default the agent to a development database. Reaching production should require an intentional step.
Test the guardrail on a schedule, not once. Grants drift. A quarterly run of the audit queries above, as the agent’s own role, is the only way to keep the answer current. Include the ownership queries, not only the privilege queries: the missed failure is a schema quietly created by the agent, which it then owns.
Keep backups you have restored. Every control above can be bypassed by a sufficiently determined mistake. A restore you have performed is the one that still works afterwards.
Can I just tell the agent not to drop tables?
No. An instruction in a prompt or an AGENTS.md file is a preference the model usually honours, not a control the database enforces. It survives no rewording, no context truncation and no prompt injection from a document the agent reads. The only thing that stops a DROP TABLE is the database refusing it, which means privileges.
What is the single most effective control?
A separate login role that is not a superuser, owns no tables, owns no schema, and holds no write privileges. All four matter. Measured on PostgreSQL 17.11, a role owning nothing but owning the schema dropped another role's table in that schema, so ownership of the container is enough.
Does setting the connection read-only protect me?
Not on its own. default_transaction_read_only is a session setting and the client can change it. Measured on PostgreSQL 17.11: with it on the INSERT was refused, after SET default_transaction_read_only = off it succeeded, and inside an already-open BEGIN READ ONLY the statement SET TRANSACTION READ WRITE let the insert through and commit. It is a default, not a boundary.
How do I stop an agent dropping a table it does own?
An event trigger on ddl_command_start can refuse DROP outright, and measured on PostgreSQL 17.11 it refused DROP TABLE from the superuser itself. Two cautions. DROP DATABASE cannot be one of its tags, because the CREATE EVENT TRIGGER statement is rejected outright with 'event triggers are not supported for DROP DATABASE'. And the trigger does not protect itself: DROP EVENT TRIGGER succeeded while it was active.
Should an AI agent connect to production at all?
Read-only, through a role built for it, with a statement timeout and a connection limit, yes: that is how it gets the plans and the row counts that make its answers correct. Write access to production for an agent is a decision to make deliberately, with the same review you would give a human getting the same credential.