Skip to content

How to stop an AI agent from dropping your production database

Last updated

View as Markdown

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.

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.

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 TABLE

Checking that the agent owns no tables is not enough. Check both:

SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
SELECT nspname AS schema, pg_get_userbyid(nspowner) AS schema_owner
FROM pg_namespace
WHERE 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 didresult
INSERT with the setting onERROR: cannot execute INSERT in a read-only transaction
SET default_transaction_read_only = off; then the same INSERTINSERT 0 1
inside BEGIN READ ONLY, the same SET, then INSERTstill refused
inside BEGIN READ ONLY, SET TRANSACTION READ WRITE; then INSERTINSERT 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 w
app=> SELECT public.record('written by the read-only role');
record
--------
app=> SELECT * FROM public.ledger;
ERROR: permission denied for table ledger

Connected 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.proacl
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');

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.

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_type
FROM information_schema.table_privileges
WHERE 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 trunc
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE 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_of
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
WHERE 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_create
FROM pg_namespace
WHERE 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_trigger
LANGUAGE 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 DATABASE

The 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 users
DETAIL: There is 1 other session using the database.
$ psql -U appowner -d postgres -c 'DROP DATABASE appdb WITH (FORCE);'
DROP DATABASE

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

DROP DATABASE, PostgreSQL 18 manual, read 2026-09-20

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 database

It does not protect itself. In the same session, with the trigger active:

postgres=# DROP EVENT TRIGGER no_drop;
DROP EVENT TRIGGER

This 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.

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.

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.