pgAudit, triggers or logical decoding: auditing what an AI agent wrote to Postgres

When an AI agent writes to Postgres, the question after something goes wrong is rarely “which statement did it send?” It’s “which rows changed, and was that the agent?” Postgres gives you four common ways to keep a record, and they answer different questions. We put the same agent write through all four and kept the output.

The test

Postgres 18.6 in Docker with pgAudit 18.0 from the PGDG packages, started with wal_level=logical, log_statement=mod and pgaudit.log=write. The agent connects as agent_bot with application_name=support-agent and may update two columns of public.orders (500 rows). The table also carries the kind of trigger old schemas collect:

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

CREATE FUNCTION legacy.ship_open_batch() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $fn$
BEGIN
  UPDATE public.orders SET status = 'shipped', updated_at = now()
  WHERE id IN (SELECT id FROM public.orders
               WHERE status <> 'shipped' AND id <> NEW.id
               ORDER BY id LIMIT 300);
  RETURN NULL;
END $fn$;

CREATE TRIGGER orders_ship_open_batch
  AFTER UPDATE OF status ON public.orders FOR EACH ROW
  WHEN (NEW.status = 'shipped' AND OLD.status IS DISTINCT FROM 'shipped'
        AND pg_trigger_depth() < 1)
  EXECUTE FUNCTION legacy.ship_open_batch();

SECURITY DEFINER is common on triggers like this: it lets the trigger write rows the caller can’t. It matters below.

The agent refunds order 45, then ships order 44:

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

UPDATE 1
 shipped_before
----------------
            150

UPDATE 1
 shipped_after
---------------
           451

Two UPDATE 1 tags. 301 more rows shipped. The agent will report one row, because that is what Postgres told it. (The 1 row, 301 rows post walks through why.)

log_statement

log_statement=mod logs DDL and data-changing statements to the server log, with whatever log_line_prefix adds:

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

09:38:52.909 UTC [243] agent_bot@postgres app=support-agent LOG:  statement: UPDATE public.orders SET status = 'refunded' WHERE id = 45;
09:38:52.910 UTC [243] agent_bot@postgres app=support-agent LOG:  statement: UPDATE public.orders SET status = 'shipped' WHERE id = 44;

It tells you who connected and what they typed. Nothing about the trigger, no row count, no values. Two more gaps from the same run:

• SELECT public.touch_two(), a function that updates two rows, logged nothing: the top-level statement is a SELECT, which mod doesn’t cover. A DO block that ran an UPDATE logged nothing either.
• SET session_replication_role = replica logged nothing, because SET isn’t a modifying statement.

It did log ALTER TABLE and TRUNCATE, which some of the others miss.

pgAudit

pgAudit logs session and object access through Postgres’s standard logging, and its WRITE class covers INSERT, UPDATE, DELETE, TRUNCATE and COPY into a relation.¹ With pgaudit.log_relation and pgaudit.log_rows on, the same two statements gave:

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

AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,UPDATE public.orders SET status = 'refunded' WHERE id = 45,<not logged>,1
AUDIT: SESSION,2,1,WRITE,UPDATE,TABLE,public.orders,UPDATE public.orders SET status = 'shipped' WHERE id = 44,<not logged>,1

The last field is the row count: 1. The trigger’s 300-row UPDATE isn’t there, and neither is the audit trigger’s INSERT from the next section. pgAudit’s source says why: it skips logging when the session user isn’t a member of the current role, a check whose comment says it exists to keep the contents of security definer functions out of the log. Both triggers are SECURITY DEFINER.

We switched the legacy function to SECURITY INVOKER and ran the write again:

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

AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,"UPDATE public.orders SET status = 'shipped', updated_at = now()
  WHERE id IN (SELECT id FROM public.orders ...)",<not logged>,300
AUDIT: SESSION,1,2,WRITE,UPDATE,TABLE,public.orders,UPDATE public.orders SET status = 'shipped' WHERE id = 44,<not logged>,1

Now the substatement appears with its 300 rows. pgAudit also caught the inner UPDATE of SELECT public.touch_two() with rows 2, which log_statement missed. That’s its strength: statement-level detail per role, including statements inside invoker functions.

What it doesn’t give you: row values or keys, so “which 300 orders?” has no answer in the log. TRUNCATE logged with rows 0 although 20 rows went. With write only, ALTER TABLE wasn’t logged (add the ddl class). And its README is plain that audit logging is best effort and not transactional: a statement is logged when it executes, even if its transaction later rolls back, and superusers can’t be reliably audited.

An audit trigger

A row-level trigger that copies OLD and NEW into an audit table is the oldest answer, and it saw everything in this run:

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

SELECT old_row->>'status' AS was, count(*)
FROM audit.row_changes WHERE depth = 2 GROUP BY 1;

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

    was    | count
-----------+-------
 cancelled |    43
 refunded  |     1
 paid      |   256

depth is pg_trigger_depth() at the time of the write: 1 for the row the agent named, 2 for rows the legacy trigger changed. Forty-three cancelled orders were marked shipped, and so was order 45, which the agent had refunded a moment earlier. Every row has its before and after, in the same transaction as the write, so a rolled-back write leaves no audit row.

Two things went wrong in our own setup, and both are common:

• current_user lies inside the audit function. Ours is SECURITY DEFINER, so current_user recorded postgres for all 302 rows. session_user recorded agent_bot. Store both.
• It sees only what fires it. TRUNCATE on a table with the trigger wrote zero audit rows (row triggers don’t fire on it). After SET session_replication_role = replica, an UPDATE wrote zero audit rows too. Anyone who owns the table can also ALTER TABLE … DISABLE TRIGGER, and anyone who can write the audit table can edit history.

It also doubles the writes: the 300-row statement became 600 row writes plus WAL for both.

Logical decoding

Logical decoding reads the write-ahead log back as committed row changes. We created a slot with the test_decoding plugin and set REPLICA IDENTITY FULL on orders so old values come through:

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

SELECT * FROM pg_create_logical_replication_slot('audit_slot', 'test_decoding');
SELECT xid, data FROM pg_logical_slot_peek_changes('audit_slot', NULL, NULL, 'skip-empty-xacts', '1');

Transaction 819 held 301 UPDATEs on public.orders, each with its old and new row:

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

819 | BEGIN 819
819 | table public.orders: UPDATE: old-key: id[integer]:44 ... status[text]:'paid' ... new-tuple: id[integer]:44 ... status[text]:'shipped' ...
819 | table public.orders: UPDATE: old-key: id[integer]:28 ... status[text]:'paid' ... new-tuple: id[integer]:28 ... status[text]:'shipped' ...
...
819 | COMMIT 819

It doesn’t care how the rows changed: the trigger, the SECURITY DEFINER function, the replica-mode UPDATE the audit trigger missed. All in the log, all committed. The TRUNCATE showed up as table public.order_notes: TRUNCATE: (no-flags), with no rows.

What it can’t tell you is who. There is no role, no application name and no statement text in a change. One way to tie a transaction to an agent action is a transactional message written inside it:

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

BEGIN;
SELECT pg_logical_emit_message(true, 'agent-action', 'act_demo_0001 support-agent refund order 46');
UPDATE public.orders SET status = 'refunded' WHERE id = 46;
COMMIT;

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

822 | message: transactional: 1 prefix: agent-action, sz: 43 content:act_demo_0001 support-agent refund order 46
822 | table public.orders: UPDATE: old-key: id[integer]:46 ...

Treat that tag as a claim, not identity. agent_bot could call the function because its ACL was the default, which grants execute to PUBLIC, so any session can write any tag.

The other costs: DDL isn’t decoded (our ALTER TABLE … ADD COLUMN produced nothing), a slot holds WAL until someone reads it (max_slot_wal_keep_size was -1, unlimited), and you need wal_level=logical, which some managed providers make a setting you have to turn on. The glossary entry has the short version.

Side by side

The short answer: log statements for “who sent what”, and read committed rows for “what changed”. For agent audits you want both and the link between them, because an agent’s tool reports the command tag and the command tag counts only the rows the statement named.

If you do one thing today, scope pgAudit to the agent’s role and find the triggers it can’t see into:

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

ALTER ROLE agent_bot SET pgaudit.log = 'write, ddl';
ALTER ROLE agent_bot SET pgaudit.log_relation = on;
ALTER ROLE agent_bot SET pgaudit.log_rows = on;

SELECT tgrelid::regclass AS on_table, tgname, p.oid::regprocedure AS function
FROM pg_trigger t JOIN pg_proc p ON p.oid = t.tgfoid
WHERE p.prosecdef AND NOT t.tgisinternal;

Every row the second query returns is a place where the agent’s write can fan out with nothing in the pgAudit log to show for it. Settings attached with ALTER ROLE apply from the role’s next login.

How Deedglass does it

Deedglass takes the last column and adds the link. Its rules decide each tool call before it runs, and its check reads Postgres’s change log and reports Matches or Doesn’t match against what the statement named.² In our demo, the agent said 1 row and Postgres changed 301.² Postgres changes that didn’t come through its rules show up as unattributed in the change-log reader.²

If you already have a gate or a pgAudit setup, observe mode works beside it: it reads only Postgres’s change log, through the deedglass_witness role and the replication slot deedglass connector postgres init creates.³ It records and offers undo; it doesn’t decide or block anything.

You can see the trigger case in your browser at /play. To check what your agent’s database role can change today, run npx deedglass scan; the score page explains the grade.

Written with AI assistance and checked against our claims register.