Auditing Access
Track who can see what.
Why Audit Access?
Knowing who accessed what data and when is a cornerstone of database security. Auditing creates a reliable trail of events so you can detect unauthorized access, investigate incidents, and satisfy compliance requirements such as GDPR, HIPAA, or SOC 2.
In this lesson you will learn how to design audit tables, capture access events automatically with triggers, use PostgreSQL's built-in logging features, and query the audit trail to answer the question: who can see what?
Designing an Audit Log Table
The first step is a dedicated table that records every notable event. A good audit log stores the table name, the type of operation, the old and new values, which user performed the action, and the exact timestamp.
The example below creates a general-purpose audit_log table using JSONB columns to store row snapshots — flexible enough to handle any table without schema changes.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);