Logging & Audit Trails Explained

PostgreSQL logging and auditing look like one topic from the outside, but they are actually three distinct mechanisms that answer three distinct investigative questions.

This page builds the mental model that separates those mechanisms, server logs, statement-level auditing via pgaudit, and row-level history via audit triggers, so you can choose the right tool instead of reaching for whichever one is already configured.

Summary

• Core Idea: Logging and auditing are a layered set of mechanisms, each recording a different granularity of evidence for a different kind of question.
• Why It Matters: Choosing the wrong mechanism means discovering during an incident or audit that the evidence you need was never captured.
• Key Concepts: server log, log volume, statement-level audit, row-level audit, retention, forensic query.
• When to Use: New cluster provisioning, compliance program design (SOC2, HIPAA, PCI), and incident forensics when the question is "who did this."
• Limitations / Trade-offs: Every additional layer of capture adds I/O overhead, storage cost, and a new place sensitive data can leak.
• Related Topics: security hardening, monitoring metrics, role-based access control, compliance reporting.

Foundations

The server log is PostgreSQL's general-purpose flight recorder, capturing connections, disconnections, errors, checkpoints, and optionally slow queries.

It answers operational questions like "did this connection succeed" or "which query ran past the latency threshold," and it exists on every cluster by default in some minimal form.

pgaudit is a purpose-built extension that layers structured, statement-level access logging on top of the server log, answering "who ran what kind of SQL against which objects."

Audit triggers are a different mechanism entirely: application-level triggers that write before-and-after row values into dedicated audit tables, answering "what did this specific row look like before and after it changed."

The distinction that matters most is granularity: server logs and pgaudit describe statements and connections, while audit triggers describe row-level data changes with old and new values captured.

A useful analogy is a retail store: the server log is the door's entry counter, pgaudit is the security camera recording who walked down which aisle, and audit triggers are the itemized receipt showing exactly what changed hands.

None of the three replaces another, because a receipt tells you nothing about who was browsing without buying, and a door counter tells you nothing about what was in anyone's basket.

PostgreSQL 18.4 defaults to minimal logging, so almost every one of these capabilities requires deliberate configuration before it produces useful evidence.

Mechanics & Interactions

These mechanisms sit at different points in the request lifecycle, which explains both their strengths and their blind spots.

The server log is populated as a side effect of connection and query execution, controlled by settings like log_connections, log_statement, and log_min_duration_statement.

pgaudit hooks into the same execution path but classifies activity into logical categories (read, write, ddl, role), letting teams scope logging to exactly the classes a compliance program requires rather than an all-or-nothing switch.

Audit triggers execute inside the same transaction as the data change itself, firing on INSERT, UPDATE, or DELETE and writing a row into an audit table before the original transaction commits.

That transactional coupling is the key mechanical difference: an audit trigger's record either commits with the change it describes or rolls back with it, while a log line written to the server log is not transactional in that same sense.

-- One mechanism made concrete: a minimal audit trigger captures
-- old/new row state transactionally, tied to the change it describes.
CREATE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit.orders_history(order_id, old_row, new_row, changed_at)
  VALUES (COALESCE(NEW.id, OLD.id), to_jsonb(OLD), to_jsonb(NEW), now());
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Volume is the other mechanical concern that connects all three: logging every statement on a busy OLTP cluster can fill disk within hours, so both server logs and pgaudit are designed to be scoped narrowly (slow queries only, DDL only, specific roles) rather than run at maximum verbosity by default.

A frequent reasoning mistake is treating log volume as purely an infrastructure problem, when it is really an evidence-design problem: logging everything produces so much noise that finding the one relevant line during an incident becomes its own investigation.

Advanced Considerations & Applications

At scale, logging and auditing become a retention and pipeline problem as much as a capture problem.

Centralizing logs into a SIEM or log-shipping pipeline decouples retention from local disk, which matters because compliance programs often require 90 days to multiple years of retained evidence that a database host was never designed to store locally.

Structured formats like csvlog or PostgreSQL's JSON log output exist specifically to make that pipeline reliable, since parsing unstructured stderr output at scale is fragile compared to parsing well-defined fields.

Regulated data introduces a specific tension: audit evidence must be captured, but the same evidence (query text, row values) can itself contain the sensitive data being protected, so redaction and access control on the audit trail matter as much as on the source tables.

Audit triggers and pgaudit are frequently combined rather than chosen exclusively, with pgaudit providing the broad access trail required by frameworks like PCI DSS and triggers providing row-level history for the specific tables where "what was the old value" is a real regulatory question.

Tamper resistance is a design detail worth deliberate attention: audit tables should typically revoke UPDATE and DELETE from application roles so that only the trigger itself can append rows, preserving the trail's evidentiary value.

Common Misconceptions

• "Enabling the server log is the same as having an audit trail." - the server log records operational events, not a structured, queryable record of who changed which row.
• "pgaudit captures old and new row values." - pgaudit logs statement text and access classification, not before/after data; that granularity requires audit triggers.
• "Audit triggers and pgaudit are interchangeable, pick either." - they answer different questions (row history versus access trail) and are frequently used together, not as alternatives.
• "Logging everything is the safest default." - unbounded logging on a busy cluster can exhaust disk within hours and buries the one relevant line in noise.
• "Log data is inherently safe to store anywhere." - query text and row values in logs can contain the same sensitive data the logging exists to protect, so the trail itself needs access control.
• "Compliance retention is just a storage setting." - retention policy defines what evidence exists months later during an audit, making it a design decision, not an afterthought.

FAQs

Related

• Logging Basics - the recipe-level starting point for server log configuration.
• csvlog & JSON Logs - structured formats for centralized shipping.
• Audit Triggers vs pgaudit - a checklist for choosing between the two audit mechanisms.
• Forensic Queries - investigating access using the logs and triggers described here.
• The Security Hardening Blueprint - how audit fits into the broader layered security model.
• Monitoring & Metrics Key Points - the complementary real-time signal alongside audit history.

Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17).