# 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?

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

Give the agent its own role, separate from the application's role and from
yours.

```sql
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:

```sql
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:

```sql
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:

```sql
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`:

```sql
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:

```sql
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

This setting is widely recommended, but it is not a boundary.

```sql
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

`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:

```sql
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.

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

```sql
SELECT current_user, session_user;
```

Then attempt a write and expect the server to refuse it. Check what the role
holds:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
```

## 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:

<!-- sql-check: skip creating an event trigger needs superuser, and the harness runs as a role that would make the check prove less than it appears to -->
```sql
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](https://www.postgresql.org/docs/18/sql-dropdatabase.html)

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.

## 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?

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

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent what it needs to
answer database questions without a write credential: live plans through
`EXPLAIN`, per-table statistics, the schema, and the query workload attributed
to the database user that ran it. The last one is the useful part here, because
it makes an agent's activity separable from the application's. It surfaces and
explains; it does not write to your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [How to set up a read-only Postgres role for an AI agent](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
- [Is it safe to let an AI agent access my production database?](https://www.dbgorilla.com/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)
- [Is a read-only MCP server actually read-only?](https://www.dbgorilla.com/learn/postgres/is-a-read-only-mcp-server-actually-read-only/)