After an outage, "who deleted the branch?" and "who changed that role?" are questions your database should be able to answer. You can build an audit trail of administrative actions with standard PostgreSQL features: an audit table, a trigger that records every insert, update, and delete on sensitive tables, and a pair of queries that filter by action and export the results to CSV. This walkthrough builds exactly that, and it runs on any Postgres 14+ server, including a Neki shard, because it uses nothing beyond plain SQL and psql.
What You'll Build
You will build a small audit subsystem for a roles table, the classic "who changed access?" problem:
- An audit table that records the action (
INSERT,UPDATE,DELETE), the table, the affected row key, the full row as JSON, the database user, and the timestamp. - A trigger function that populates the audit table automatically, so no application code change is required.
- A query that filters the log by action, for example "show me every role change."
- A CSV export of the filtered log you can hand to whoever runs the post-incident review.
Everything is namespaced with a run-unique suffix so you can run the walkthrough repeatedly without colliding with objects left behind by earlier runs.
Prerequisites
- A PostgreSQL 14 or newer server you can connect to, with credentials that allow creating tables and functions in your database.
- The
psqlcommand-line client. If it is not installed, install it first. On Debian or Ubuntu:
sudo apt-get update && sudo apt-get install -y postgresql-client
- A connection string in an environment variable, for example:
export DATABASE_URL="postgres://user:password@host:5432/dbname"
No extensions are required. The audit log uses only core SQL, PL/pgSQL, and psql meta-commands.
Implementation
Step 1: Create the table under audit and the audit table
Use a unique suffix so repeated runs never collide:
export AUDIT_SUFFIX="audit_$(date +%s)"
echo "$AUDIT_SUFFIX"
Then create the demo roles table and the audit table:
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
CREATE TABLE app_roles_$AUDIT_SUFFIX (
role_name text PRIMARY KEY,
access_level text NOT NULL
);
CREATE TABLE admin_audit_log_$AUDIT_SUFFIX (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
action text NOT NULL,
table_name text NOT NULL,
row_key text,
details jsonb,
performed_by text NOT NULL DEFAULT current_user,
performed_at timestamptz NOT NULL DEFAULT now()
);
SQL
Step 2: Create the trigger function and attach it
The function writes one audit row per affected row and returns the correct record for each operation type:
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
CREATE OR REPLACE FUNCTION audit_app_roles_$AUDIT_SUFFIX() RETURNS trigger AS \$\$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO admin_audit_log_$AUDIT_SUFFIX (action, table_name, row_key, details)
VALUES (TG_OP, TG_TABLE_NAME, OLD.role_name, to_jsonb(OLD));
RETURN OLD;
ELSE
INSERT INTO admin_audit_log_$AUDIT_SUFFIX (action, table_name, row_key, details)
VALUES (TG_OP, TG_TABLE_NAME, NEW.role_name, to_jsonb(NEW));
RETURN NEW;
END IF;
END;
\$\$ LANGUAGE plpgsql;
CREATE TRIGGER app_roles_audit_$AUDIT_SUFFIX
AFTER INSERT OR UPDATE OR DELETE ON app_roles_$AUDIT_SUFFIX
FOR EACH ROW EXECUTE FUNCTION audit_app_roles_$AUDIT_SUFFIX();
SQL
Step 3: Seed activity and filter by action
Seed two roles, change one, and delete the other. These statements are what produce the audit rows you observe next:
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
INSERT INTO app_roles_$AUDIT_SUFFIX VALUES ('billing', 'write');
INSERT INTO app_roles_$AUDIT_SUFFIX VALUES ('reporting', 'read');
UPDATE app_roles_$AUDIT_SUFFIX SET access_level = 'admin' WHERE role_name = 'billing';
DELETE FROM app_roles_$AUDIT_SUFFIX WHERE role_name = 'reporting';
SELECT action, row_key, performed_by, performed_at
FROM admin_audit_log_$AUDIT_SUFFIX
WHERE action = 'UPDATE';
SQL
Step 4: Export the filtered log to CSV
\copy writes the result of the filtered query to a local CSV file:
psql "$DATABASE_URL" <<SQL
\copy (SELECT action, row_key, performed_by, performed_at FROM admin_audit_log_$AUDIT_SUFFIX WHERE action IN ('UPDATE','DELETE') ORDER BY performed_at) TO 'admin_changes_$AUDIT_SUFFIX.csv' WITH CSV HEADER
SQL
cat "admin_changes_$AUDIT_SUFFIX.csv"
Complete Example
Save this as verify_audit.sh and run it with bash verify_audit.sh. It creates fresh uniquely named objects, seeds them, filters by action, exports, prints the CSV, and tears everything down in one copy-paste run:
#!/usr/bin/env bash
set -euo pipefail
export DATABASE_URL="${DATABASE_URL:?Set DATABASE_URL first}"
SUFFIX="audit_$(date +%s)"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
CREATE TABLE app_roles_$SUFFIX (role_name text PRIMARY KEY, access_level text NOT NULL);
CREATE TABLE admin_audit_log_$SUFFIX (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
action text NOT NULL,
table_name text NOT NULL,
row_key text,
details jsonb,
performed_by text NOT NULL DEFAULT current_user,
performed_at timestamptz NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION audit_app_roles_$SUFFIX() RETURNS trigger AS \$\$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO admin_audit_log_$SUFFIX (action, table_name, row_key, details)
VALUES (TG_OP, TG_TABLE_NAME, OLD.role_name, to_jsonb(OLD));
RETURN OLD;
ELSE
INSERT INTO admin_audit_log_$SUFFIX (action, table_name, row_key, details)
VALUES (TG_OP, TG_TABLE_NAME, NEW.role_name, to_jsonb(NEW));
RETURN NEW;
END IF;
END;
\$\$ LANGUAGE plpgsql;
CREATE TRIGGER app_roles_audit_$SUFFIX
AFTER INSERT OR UPDATE OR DELETE ON app_roles_$SUFFIX
FOR EACH ROW EXECUTE FUNCTION audit_app_roles_$SUFFIX();
INSERT INTO app_roles_$SUFFIX VALUES ('billing', 'write');
INSERT INTO app_roles_$SUFFIX VALUES ('reporting', 'read');
UPDATE app_roles_$SUFFIX SET access_level = 'admin' WHERE role_name = 'billing';
DELETE FROM app_roles_$SUFFIX WHERE role_name = 'reporting';
SELECT action, row_key FROM admin_audit_log_$SUFFIX WHERE action = 'UPDATE';
SQL
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
\copy (SELECT action, row_key FROM admin_audit_log_$SUFFIX WHERE action = 'UPDATE') TO 'admin_changes_$SUFFIX.csv' WITH CSV HEADER
SQL
echo "--- exported CSV ---"
cat "admin_changes_$SUFFIX.csv"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<SQL
DROP TABLE app_roles_$SUFFIX;
DROP TABLE admin_audit_log_$SUFFIX;
DROP FUNCTION audit_app_roles_$SUFFIX();
SQL
echo "--- teardown complete ---"
Observed output:
action | row_key
--------+---------
UPDATE | billing
(1 row)
--- exported CSV ---
action,row_key
UPDATE,billing
--- teardown complete ---
How It Works
The trigger, not the application, is the source of truth. Because the audit insert happens in an AFTER ... FOR EACH ROW trigger, every path that touches the table is logged, including manual psql sessions, migration tools, and admin scripts. That is exactly the property you need after an incident: the log cannot be bypassed by someone connecting directly to the database.
TG_OP, to_jsonb, and current_user carry the evidence. TG_OP gives you the action string to filter on, to_jsonb(NEW) or to_jsonb(OLD) snapshots the full row so you can see what a role's access level was before and after, and current_user records which database principal made the change. Note that current_user is the database role, not an application user; if your app connects with a shared role, pass the acting human identity explicitly in an extra column.
Filtering is a plain WHERE clause. To answer "who changed a role," filter WHERE action = 'UPDATE'. To answer "who deleted anything," filter WHERE action = 'DELETE'. The details JSONB column supports deeper questions, for example WHERE details ->> 'access_level' = 'admin'.
Export uses \copy, not COPY. COPY ... TO writes on the server's filesystem, which you often cannot read. \copy runs on the client, so the CSV lands next to you, ready to attach to an incident report.
Edge cases worth knowing. Truncate operations do not fire row-level triggers; if you need to catch TRUNCATE, add a statement-level trigger. Very hot tables make one audit row per row change, so index action and performed_at and archive old rows on a schedule. And the audit table itself is writable by anyone with write access to the schema; for stricter environments, restrict privileges on it with REVOKE.
Conclusion
You now have a working audit trail for administrative database actions: a trigger-maintained log table, a one-line filter for "who changed what," and a CSV export for incident reviews. The complete example is self-contained, uniquely named per run, and cleans up after itself, so you can adapt it to your own sensitive tables by swapping the demo roles table for the ones you actually care about.
verification:
status: unverified
tested_at: null
product_version: null
command: null
expected_result: null