Audit Changes with pgAudit

You’ve built tables, written queries, and maybe even tuned indexes. But when a critical row disappears or a column value changes unexpectedly, do you know who did it and when? Default PostgreSQL logs don’t tell you that. Without a clear audit trail, debugging data loss or meeting compliance requirements becomes a painful guessing game. In this lesson, you’ll learn to audit changes with pgAudit — the gold‑standard extension that records every INSERT, UPDATE, and DELETE with precision — so you can answer those questions with confidence.

The problem this lesson solves

PostgreSQL’s built‑in logging can capture queries, but it’s coarse: it logs statements without distinguishing who ran them, and it doesn’t reliably track changes to specific tables. When you need to answer questions like “Who deleted this row?” or “What changes occurred between 2 PM and 3 PM?”, standard logs fall short. Think of a compliance audit or a forensic investigation: you need a precise, tamper‑resistant record of every change. That’s exactly what pgAudit provides.

Why it matters now — Regulations like GDPR, HIPAA, and SOC 2 often require audit trails. Even without regulatory pressure, a consistent change log helps you detect anomalies, revert mistakes, and understand user behavior. Starting with the right tool now saves you from building fragile home‑grown logging later.

Core concept / mental model

Think of pgAudit as a black‑box flight recorder for your database. While regular PostgreSQL logs are like general radio chatter, pgAudit focuses on changes: every INSERT, UPDATE, DELETE, and TRUNCATE. It records the who (session user), what (SQL statement), when (timestamp), and where (database, schema, table).

pgAudit works as an extension that sits between your SQL and the write path. When enabled, it generates audit log entries that you can send to PostgreSQL’s standard logging system or to a dedicated log collector. The key is that it captures changes even if they occur inside a transaction — every statement gets logged individually, not just the transaction commit.

Key definitions

• Audit trail — a chronological, tamper‑evident record of database changes.
• pgAudit — a PostgreSQL extension that provides detailed session and object‑level audit logging.
• Session audit — logs all statements by a user or in a database, regardless of which tables are touched.
• Object audit — logs statements that affect specific objects (like a table or schema), giving finer control.

How it works step by step

Let’s trace how pgAudit does its job:

1. Install the extension — pgAudit is available as a package (e.g., postgresql-16-pgaudit on Debian/Ubuntu) or via CREATE EXTENSION pgaudit; if the files are in place.
2. Configure PostgreSQL — you must add pgaudit to shared_preload_libraries in postgresql.conf and restart the server. Also set pgaudit.log = 'write, ddl' (or similar) to define which events to record.
3. Enable the extension — run CREATE EXTENSION pgaudit; in the target database(s).
4. Define audit scope — use session‑level settings (e.g., ALTER ROLE app_user SET pgaudit.log = 'write';) or object‑level rules (via pgaudit.log_relation).
5. Execute and observe — when a change occurs, pgAudit writes a LOG message to the PostgreSQL log, including the statement and session context.

How to read the log output

Each audit entry looks like this:

LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,"public","orders","INSERT INTO orders (id, total) VALUES (1, 99.50)",<not logged>

Breaking it down: - AUDIT: SESSION — this is a session‑level audit. - 2,1 — statement sequence and substatement numbers (useful for nested statements). - WRITE — the command class (INSERT, UPDATE, DELETE, etc.). - INSERT — actual command. - "public","orders" — schema and table. - The full SQL — the exact statement as parsed.

This structured output makes it trivial to grep for actions on a specific table or by a specific time.

Hands‑on walkthrough

Let’s put pgAudit into action. We’ll assume a PostgreSQL 16 server on Ubuntu, but the steps are similar for other platforms.

Step 1: Install and enable pgAudit

First, install the package:

sudo apt update
sudo apt install postgresql-16-pgaudit

Then edit postgresql.conf to add:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl'   # log all writes and DDL

Restart PostgreSQL:

sudo systemctl restart postgresql

Now connect and create the extension:

CREATE EXTENSION pgaudit;

Step 2: Set up a demo table and enable audit

-- Create a sample table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    total NUMERIC(10,2),
    status TEXT DEFAULT 'pending'
);

-- Enable audit for current session (or set per role later)
SET pgaudit.log = 'write, ddl';
SET pgaudit.log_relation = TRUE;

Step 3: Perform changes and check the audit log

Run some DML and DDL:

INSERT INTO orders (total) VALUES (100.00);
UPDATE orders SET status = 'shipped' WHERE id = 1;
DELETE FROM orders WHERE id = 1;
ALTER TABLE orders ADD COLUMN note TEXT;

Now check the PostgreSQL log (typically /var/log/postgresql/postgresql-16-main.log):

sudo tail -f /var/log/postgresql/postgresql-16-main.log | grep AUDIT

You should see entries for each statement, like:

LOG:  AUDIT: SESSION,7,1,WRITE,INSERT,"public","orders","INSERT INTO orders (total) VALUES (100.00)",<not logged>
LOG:  AUDIT: SESSION,8,1,WRITE,UPDATE,"public","orders","UPDATE orders SET status = 'shipped' WHERE id = 1",<not logged>
LOG:  AUDIT: SESSION,9,1,WRITE,DELETE,"public","orders","DELETE FROM orders WHERE id = 1",<not logged>
LOG:  AUDIT: SESSION,10,1,DDL,ALTER TABLE,"public","orders","ALTER TABLE orders ADD COLUMN note TEXT",<not logged>

Pro tip: To see the actual role that made the change, you must configure pgaudit.log_client or use CSV log format with log_line_prefix including %u. By default, the session user isn’t shown, so make it explicit!

Step 4: Audit only specific roles or tables

You can scope auditing to avoid noise:

-- Audit all writes by a specific role
ALTER ROLE app_user SET pgaudit.log = 'write';

-- Audit only changes to a particular table (object-level)
SELECT pgaudit.set_audit_object('public', 'orders', 'table', 'write');

The second approach logs only statements touching orders, which reduces log volume and focuses on critical tables.

Compare options / when to choose what

When to choose what: - Use pgAudit session for a comprehensive audit trail with minimal coding. - Use pgAudit object when you only care about a few tables (e.g., orders, users) and want to save disk space. - Use triggers + history table if you need to capture old and new values (pgAudit logs the full statement, but not the row diff). For insert/update/delete diffs, you’ll need a trigger‑based solution. - Use built‑in log_statement only as a temporary debugging tool, not for compliance.

Trade‑off alert: pgAudit logs the full SQL, which can include sensitive data (passwords in INSERTs, if you’re not using parameterized queries). Weigh that against your data protection policy.

Troubleshooting & edge cases

• pgAudit doesn’t load after CREATE EXTENSION: Check that shared_preload_libraries contains pgaudit and that server restarted. Run SHOW shared_preload_libraries; to verify.
• No audit logs for writes: Ensure pgaudit.log is set before the transaction. Use ALTER ROLE or SET at session start. Also confirm pgaudit.log_relation if you’re using object‑level audit.
• Audit logs are too verbose: Use pgaudit.log = 'write' (exclude ddl) or switch to object‑level for specific tables. Filter logs with pgaudit.log_parameter = off to omit parameters.
• Logs show <not logged> for parameters: Set pgaudit.log_parameter = on to include parameters in the log — but be careful with sensitive data.
• Extension not found: On many distributions, you must install the matching postgresql-XX-pgaudit package. Check your PostgreSQL version with SELECT version(); and install accordingly.
• Audit inside transactions: pgAudit logs each statement as it happens, so even rolled‑back statements appear in the log. This is often desirable for a complete trail.

What you learned & what's next

You now understand how to audit changes with pgAudit, from installation to configuration to reading the logs. You can distinguish session‑level from object‑level auditing, and you know when to use pgAudit versus triggers or built‑in logging. You’ve also practiced setting up a demo audit trail and troubleshooting common issues.

With this foundation, you’re ready to integrate audit logs into your monitoring stack or build visual dashboards. In the next lesson, you’ll explore role‑based security and row‑level security to control who can make changes — a perfect complement to knowing who did.

Now take the knowledge you’ve gained and consider adding a retention policy for your audit logs, so they don’t grow unbounded. Happy auditing!