Block DROP TABLE and ALTER TABLE for an AI agent's role with a Postgres event trigger

The short answer

Create a ddl_command_start event trigger that raises an error when current_user is the agent’s role and the command tag is one you want to stop, such as DROP TABLE or ALTER TABLE. Event triggers fire before DDL such as CREATE, ALTER, DROP and GRANT.¹ On PostgreSQL 18, ours refused ALTER TABLE and DROP TABLE from the agent’s role, even on a table that role owned, and the role couldn’t disable the trigger.

Two limits: only superusers can create event triggers², so on some managed services you can’t use this; and TRUNCATE isn’t DDL, so an event trigger doesn’t stop it. In our run, the same role emptied the table with TRUNCATE.

When you need this

The cleanest setup gives an agent a role that owns nothing and has only the grants it needs. Then DROP TABLE fails for lack of ownership, and no trigger is needed. Our post on what rds_superuser and neon_superuser carry shows how to build that role.

Real projects aren’t always clean:

• Migration tools often run as the role that owns the schema, and the agent runs the migration tool.
• A development database where the agent creates its own tables, but shouldn’t drop the shared ones.
• A legacy app whose single role owns everything, and the agent has to use it for now.

In those cases ownership already lets the role drop and alter tables, and grants can’t take that away. An event trigger can.

The setup

As a superuser, on a test database:

Code A database command in SQL. The text around it says what it does.

create role agent_rw login password '...';
grant create, usage on schema public to agent_rw;

create function block_agent_ddl() returns event_trigger language plpgsql as $$
begin
  if current_user = 'agent_rw' then
    raise exception 'DDL (%) is blocked for %; ask a person', tg_tag, current_user;
  end if;
end $$;

create event trigger no_agent_ddl on ddl_command_start
  when tag in ('DROP TABLE', 'ALTER TABLE', 'DROP SCHEMA')  -- add 'DROP TRIGGER' too; see below
  execute function block_agent_ddl();

create table orders(id int primary key, total int);
alter table orders owner to agent_rw;
insert into orders select g, g from generate_series(1,5) g;

orders is owned by the agent’s role on purpose: that’s the case grants can’t help with.

What happened when the agent tried

With set role agent_rw, we ran six statements. What psql printed:

The first two are what we wanted: the owner role can’t drop or alter the table. The last two show the trigger is about the command, not the table: the agent can create its own scratch table but not drop it, which may or may not be what you want. Widen or narrow the tag list and add a schema check for that.

Rows 3 and 4 are the gap. TRUNCATE went straight through and the table was empty.

Can the agent turn it off?

We tried both obvious routes as agent_rw:

Code What the screen or file shows, copied as is.

ERROR:  must be owner of event trigger no_agent_ddl
ERROR:  permission denied to set parameter "event_triggers"

ALTER EVENT TRIGGER ... DISABLE needs the trigger’s owner, a superuser here, and the event_triggers setting couldn’t be changed by the role. The docs note that event triggers are disabled in single-user mode and when event_triggers is set to false, which is the escape hatch for a broken trigger.² Neither is available to a normal role.

What an event trigger doesn’t see

From the docs and our run:

• TRUNCATE. Not in the DDL list event triggers fire for.¹ Revoke the TRUNCATE privilege instead, which is a table-level privilege³, though an owner keeps it. If the role must own the table, a BEFORE TRUNCATE trigger on the table can raise for that role. We tried it: as agent_rw, truncate orders then failed with ERROR: TRUNCATE is blocked for agent_rw. But the very next statement, drop trigger no_agent_truncate on orders, succeeded, because the role owns the table and DROP TRIGGER wasn’t in our event trigger’s tag list. Add DROP TRIGGER to the list (disabling a trigger is an ALTER TABLE, already covered) or the per-table trigger is a door the owner can open.
• DELETE and UPDATE. Data changes aren’t DDL. A DELETE FROM orders with no WHERE is as destructive as TRUNCATE and isn’t stopped here. Grants, row-level security and a blast radius small enough to recover from cover that.
• Shared objects. ddl_command_start doesn’t fire for DDL on databases, roles, tablespaces, parameter privileges or ALTER SYSTEM.¹ DROP DATABASE isn’t covered.
• Commands on event triggers themselves.¹ Protection there comes from ownership, as above.
• Superusers. A superuser can disable the trigger. Don’t give agents superuser credentials.

Operating it safely

• Test the error path. A bug in an event trigger function can block all DDL, including your migrations. The documented recovery is restarting with event_triggers off or using single-user mode.² Try your trigger on a copy first.
• Make the message useful. “Ask a person” in the error tells an agent what to do next; a bare permission error invites it to try another route.
• Keep migrations working. If your migration tool runs as the same role, give migrations their own role, or the trigger will block them too. That split is good practice anyway.
• Log refusals. A RAISE reaches the client and the server log. Repeated refusals for the agent’s role are worth a look; the ALTER TABLE over SSH report shows why an agent might try.

A checklist

• The agent’s role owns as little as possible; event triggers are for what’s left.
• An event trigger refuses DROP TABLE, ALTER TABLE, DROP SCHEMA and DROP TRIGGER for that role.
• TRUNCATE covered separately (privilege or a per-table trigger).
• DELETE and UPDATE limited by grants and row-level security.
• The trigger tested on a copy, including a deliberate failure.
• Migrations run as a different role.
• No superuser credentials anywhere the agent can read.

For the role and limits side, see safe Postgres access for agents. To grade the role your agent connects with, run npx deedglass scan --postgres-dsn-env DATABASE_URL, which lists the tables that role can write (how the grade works), or paste an MCP config into the check page; to try tool-call rules in your browser, open the playground.

Written with AI assistance and checked against our claims register.