Monitoring violations¶

Every refusal is written to the PostgreSQL log. Normal work writes nothing, so anything appearing is worth looking at.

What a record looks like¶

One line, fixed prefix, key=value pairs:

pg_vault_tables_violation: ts=2026-08-17 09:14:22.104+10 operation=update
  reason=not_permitted database=prod schema=public table=audit_log
  permissions=insert retention_days= current_user=alice session_user=bob
  client_addr=10.1.2.3 application_name=psql pid=8210

Written at LOG level, so it appears wherever your ordinary server log goes.

The fields¶

Field	Meaning
`ts`	When it happened
`operation`	What was attempted — `insert`, `update`, `delete`, `truncate`, `drop`, `alter`
`reason`	Why it was refused (see below)
`database`, `schema`, `table`	Which table
`permissions`	What the table actually grants
`retention_days`	Its retention period, blank if none
`current_user`	The effective role at the time
`session_user`	The role that logged in
`client_addr`	Where the connection came from, or `local`
`application_name`	Whatever the client set
`pid`	Backend process ID

current_user and session_user are both recorded on purpose. They differ when somebody has used SET ROLE, and knowing that alice was acting while logged in as bob is often the most useful thing on the line.

The reasons¶

`reason`	What happened
`not_permitted`	The operation is not in the table's permissions
`alter_never_permitted`	An `ALTER TABLE`, which no permission grants
`retention_not_expired`	A delete on a row still inside its retention period
`insertonce_consumed`	A second insert into an `insertonce` table
`no_permission_set`	The table's permissions could not be read — investigate
`purge_ts_column_missing`	Retention is set but the deadline column is gone — investigate

The last two should never occur. If they do, something is wrong with the table's catalogue entry and it is worth a proper look.

Finding them¶

In a log file:

grep pg_vault_tables_violation /var/log/postgresql/postgresql-18-main.log

Just today's, by table:

grep pg_vault_tables_violation postgresql.log \
  | grep "$(date +%Y-%m-%d)" \
  | grep -o 'table=[^ ]*' | sort | uniq -c | sort -rn

Shipping them somewhere¶

The line is deliberately in one piece with a fixed prefix, so it works with ordinary log shipping. A minimal approach with most tools is to match on the prefix and split the remainder on spaces and =.

Worth alerting on, because they should be rare:

Any violation on a production audit table.
reason=alter_never_permitted, which means somebody tried to change a table's structure.
Repeated violations from one session_user, which looks like probing rather than a mistake.
no_permission_set or purge_ts_column_missing, which suggest a problem rather than an attempt.

What does not produce a record¶

Any permitted operation.
A purge removing eligible rows.
Ordinary SELECT — this extension has nothing to do with reading.

That is what makes the signal useful. If normal traffic generated records, nobody would read them.

Records survive the failed transaction¶

Worth knowing: the refusal rolls back the transaction that attempted it, but the log line is still written. Log writes are not transactional, so an attempt cannot erase its own record by failing.