Postgres Audit Logging Guide

If you need to know who changed what in your PostgreSQL database, you have more than one way to find out. You might be chasing down an anomaly, building a paper trail for an upcoming audit, or just trying to answer the question every team eventually asks: who ran that query? PostgreSQL records activity at several levels of detail, and the right setup depends on how much you need to capture and what you can afford to pay for it in performance.

This guide walks through the five approaches I see most often, from PostgreSQL's built-in logging up to pgAudit and Bytebase, with the pros and cons of each so you can pick the one that fits your situation.

1. Native PostgreSQL Logging#

PostgreSQL ships with a logging subsystem out of the box, and it's usually where an audit trail starts.

Key settings

You configure logging in your postgresql.conf file:

logging_collector = on
log_statement = 'all'          # Options: none, ddl, mod, all
log_line_prefix = '%m [%p] %u@%d '   # Timestamp, process ID, user, database
log_duration = on
log_destination = 'csvlog'

This tells PostgreSQL to record every executed SQL statement, who ran it and when, and how long each query took.

Pros

• Simple to enable, built-in, no extension needed.
• Useful for performance analysis and basic audit visibility.

Cons

• Unstructured text logs that are hard to parse automatically.
• Can grow large quickly.
• May include sensitive query parameters.

If you want to make sense of these logs, pgBadger parses them and generates visual reports of who ran what and when.

2. Trigger-Based Auditing#

When you care about row-level changes, the before and after values on an UPDATE, for example, triggers are the way to capture them.

Example

First, create a table to store the change history:

CREATE TABLE audit_log (
  id serial PRIMARY KEY,
  table_name text,
  action text,
  changed_by text,
  changed_at timestamptz DEFAULT now(),
  old_data jsonb,
  new_data jsonb
);

Then define a trigger:

CREATE OR REPLACE FUNCTION audit_trigger() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, changed_by, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP, current_user, row_to_json(OLD), row_to_json(NEW));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_user_table
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

This captures every DML operation (INSERT, UPDATE, DELETE) on the users table, recording what changed and who changed it.

Pros

• Captures before/after data.
• Fully customizable schema.

Cons

• Must be defined per table.
• Can hurt performance on write-heavy workloads.

If you also want real-time change notifications, the tcn module (Triggered Change Notification) sends NOTIFY events to listening clients when data changes.

3. Logical Replication Based Auditing#

PostgreSQL's logical replication decodes changes from the Write-Ahead Log (WAL) and streams them in a structured format. The appeal here is that you capture all data changes without adding triggers or touching application code.

How It Works

Logical replication uses replication slots and output plugins (like wal2json or pgoutput) to turn WAL entries into JSON or other formats. Tools like Debezium consume those changes and forward them to audit storage systems like Kafka, Elasticsearch, or S3.

Example Setup

Enable logical replication in postgresql.conf:

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

Create a replication slot:

SELECT * FROM pg_create_logical_replication_slot('audit_slot', 'wal2json');

Read changes:

SELECT * FROM pg_logical_slot_get_changes('audit_slot', NULL, NULL);

You'll get JSON output like this:

{
  "change": [
    {
      "kind": "update",
      "schema": "public",
      "table": "users",
      "columnnames": ["id", "email", "updated_at"],
      "columnvalues": [42, "user@example.com", "2025-11-10 10:30:00"],
      "oldkeys": { "keynames": ["id"], "keyvalues": [42] }
    }
  ]
}

Pros

• Captures all data changes automatically, with no per-table setup.
• Minimal performance impact, since it reads from existing WAL infrastructure.
• Works with existing tools like Debezium for streaming to external systems.
• No application code changes required.

Cons

• Does not capture SELECT queries, only data modifications.
• WAL retention can increase storage if the replication slot falls behind.

This is the approach to reach for when you need near-real-time change data capture (CDC) for auditing, analytics, or event-driven architectures.

4. pgAudit Extension#

For structured, compliance-grade audit logs, the pgAudit extension is the standard choice. It extends native logging with more context and granularity, particularly around read and write operations.

Installation

Enable the extension:

CREATE EXTENSION pgaudit;

Update the configuration:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'READ, WRITE'
pgaudit.log_catalog = off

After restarting PostgreSQL, you'll start seeing audit logs like:

AUDIT: SESSION,1,READ,SELECT,,,,"SELECT * FROM customers WHERE id=42;",<none>

Benefits

• Records who executed which statement, in which session.
• Captures both DDL and DML activity.
• Integrates with PostgreSQL's standard log collector, so there's no new storage model.

Considerations

• Logs can be verbose. Set pgaudit.log_parameter = off to reduce noise.
• Requires proper log rotation and an analysis strategy.

5. Bytebase#

Bytebase is a Database DevSecOps platform that provides a centralized audit trail across your PostgreSQL environments. It records who did what, when, and why, linking each SQL action to its context (issues, approvals, and deployments) while keeping sensitive data secure.

What Bytebase Audits

• Query access: logs who queried which data and when across SQL Editor, Admin Query, and Data Export.
• Schema and data changes: tracks who made which changes, when they were approved, and through which workflow or Git commit.
• Governance controls: built-in SQL review rules, approval flow, and role-based access help prevent unauthorized actions.
• Actual end users, not just database users: This is where Bytebase solves the shared database user problem. In most applications, every query goes through the same database connection user (like app_user), which makes it impossible to trace an action back to an individual using traditional database auditing. Because users operate through Bytebase, every action is attributed to the real end user, not a generic database account.

Why It Matters

• Complete visibility across read and write operations.
• Privacy-safe auditing with no sensitive data exposure.
• Compliance-ready logs aligned with SOC 2, ISO 27001, and GDPR.

You can also call the API to ship the audit logs to a centralized log sink. For the full record format, the three gaps Bytebase closes versus engine-native logs, and the per-column masking detail, see How Bytebase Handles Audit Logging.

Conclusion#

PostgreSQL gives you several layers of auditing, from basic text logs up to a full governance solution.

Comparison Table

• Use native logging for baseline activity tracking (captures the database user only).

• Use triggers for fine-grained row-level auditing on critical tables (captures the database user only).

• Use logical replication for near-real-time change data capture without application changes.

• Use pgAudit for structured, compliance-grade statement logging (captures the database user only).

• Add Bytebase for centralized auditing that tracks actual end users rather than just database users, linking every action to the person who performed it along with the full context of approvals and workflows.

These layers stack. Combine them and you get both the visibility and the control you need for secure, compliant, well-governed database operations.

Related reading:

• Database Audit Logging: Two Layers, One Trail: the hub: infrastructure-vs-workflow split, framework-by-framework and engine-by-engine guides
• How Bytebase Handles Audit Logging: the workflow-layer record format, three gaps, masking metadata, export paths
• SOC 2 Audit Log Requirements: what auditors actually want, four-field framework, six categories of admin activity