pgAudit: PostgreSQL Audit Logging for SOC2, PCI-DSS, and HIPAA Compliance

Compliance audits fail not because systems were insecure, but because there was no proof they were secure. For PostgreSQL databases handling financial records, healthcare data, or cardholder information, that proof lives in a structured, tamper-evident audit log — and the tool purpose-built to generate it is pgAudit. Unlike PostgreSQL's built-in log_statement, which produces free-form text unsuitable for automated parsing, pgAudit emits structured records that SIEM tools, log aggregators, and compliance auditors can actually consume. If your PostgreSQL deployment is heading toward a SOC 2 Type II, PCI-DSS, or HIPAA audit, pgAudit is not optional.

What is pgAudit?

pgAudit is a PostgreSQL extension that provides detailed session and object audit logging through PostgreSQL's standard logging infrastructure. It was created to fill a critical gap: PostgreSQL's native log_statement = 'all' setting produces unstructured log output that is difficult to parse, filter, or ship to compliance tooling. pgAudit, by contrast, produces records with a defined format — each line tagged with AUDIT: and containing discrete fields for statement class, command type, object name, the full statement text, and bound parameters.

pgAudit is available for all major PostgreSQL versions. pgAudit v17 supports PostgreSQL 17. It integrates with the existing PostgreSQL logging pipeline, meaning there is no separate daemon, no additional network port, and no schema changes required. The extension hooks into the executor and processes each statement through its audit logic before the results are ever returned to the client.

The comparison with log_statement = 'all' is worth spelling out. Both write to the same log file. But log_statement output looks like this:

2026-03-05 10:42:17 UTC [12345] appuser@myapp LOG:  statement: SELECT * FROM credit_cards WHERE user_id = 42

pgAudit output looks like this:

2026-03-05 10:42:17 UTC [12345]: user=appuser,db=myapp AUDIT: SESSION,1,1,READ,SELECT,TABLE,credit_cards,"SELECT * FROM credit_cards WHERE user_id = $1",42

The pgAudit format is machine-readable. You can extract the object_name field with a regex. You can filter to READ class events on credit_cards in a pipeline. You can build dashboards, alerts, and compliance reports from it. You cannot do that reliably with free-form log_statement output at scale.

How pgAudit Works

pgAudit operates by installing itself as an executor hook in PostgreSQL's internal processing pipeline. When a statement executes, pgAudit inspects it, determines whether it matches the configured audit criteria, and emits a structured log record if it does. The actual log write goes through PostgreSQL's standard logging system — the same destination as all other PostgreSQL log output.

Session Auditing vs. Object Auditing

pgAudit supports two distinct auditing modes that can be used independently or together.

Session auditing is configured globally (or per-role) via pgaudit.log. It logs all statements matching the specified classes for every session on the database. The available log classes are:

• READ — SELECT and COPY FROM statements
• WRITE — INSERT, UPDATE, DELETE, TRUNCATE, COPY TO
• FUNCTION — Function calls and DO blocks
• ROLE — GRANT, REVOKE, CREATE/ALTER/DROP ROLE
• DDL — All DDL not covered by ROLE
• MISC — DISCARD, FETCH, CHECKPOINT, VACUUM
• MISC_SET — SET commands
• ALL — All of the above

Object auditing is configured via a dedicated PostgreSQL role assigned to pgaudit.role. Rather than logging all statements of a class, pgAudit inspects the objects accessed by each statement. If any object has been granted the relevant privilege to the audit role, the statement is logged. This allows precise targeting — audit only the credit_cards table rather than every SELECT on every table in the database.

Object auditing is the right approach for most compliance scenarios. It produces dramatically less log volume while capturing exactly the access events that matter to auditors.

Audit Log Format

Every pgAudit log entry contains a consistent set of fields after the AUDIT: tag:

2026-03-05 10:42:17 UTC [12345]: user=appuser,db=myapp AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,"CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT, amount DECIMAL(10,2))",

The fields in order are:

1. AUDIT_TYPE — SESSION or OBJECT
2. statement_id — unique ID for the top-level statement
3. substatement_id — for statements within functions or DO blocks
4. class — DDL, READ, WRITE, ROLE, etc.
5. command — CREATE TABLE, SELECT, INSERT, etc.
6. object_type — TABLE, INDEX, SEQUENCE, etc. (blank for DDL)
7. object_name — schema-qualified object name
8. statement — the full SQL text
9. parameter — bound parameters if pgaudit.log_parameter = on

This structure is what makes pgAudit output parseable by Filebeat, Fluentd, Logstash, or any tool with regex support.

Installing and Configuring pgAudit

Step-by-Step Installation and Configuration

Step 1: Install the package

# Ubuntu/Debian (for PostgreSQL 17)
sudo apt-get install postgresql-17-pgaudit

# RHEL/CentOS
sudo yum install pgaudit17_17

# Verify the shared library is present
ls /usr/lib/postgresql/17/lib/pgaudit.so

Step 2: Load the extension via shared_preload_libraries

pgAudit must be loaded at server start. It cannot be loaded with a simple CREATE EXTENSION at runtime.

# postgresql.conf

# Load pgAudit at startup (required)
shared_preload_libraries = 'pgaudit'

Restart PostgreSQL after editing postgresql.conf.

Step 3: Create the extension in your database

-- Run in the target database
CREATE EXTENSION pgaudit;

Step 4: Configure audit settings

# Session-level audit (logs all DDL, DML, role changes)
pgaudit.log = 'ddl, role, connection, misc_set'

# For stricter compliance (adds all reads and writes)
# pgaudit.log = 'all'

# Log catalog objects (pg_* system tables) — disable to reduce ORM noise
pgaudit.log_catalog = off

# Include bound parameters in audit log
pgaudit.log_parameter = on

# Log relation name with each statement
pgaudit.log_relation = on

# Log level — use 'log' to write to the standard log file
pgaudit.log_level = log

Step 5: Reload configuration

SELECT pg_reload_conf();

At this point, pgAudit is active. All DDL statements, role changes, connections, and SET commands will be logged with structured AUDIT records.

Object-Level Auditing

Targeting Sensitive Tables with Role-Based Auditing

Object auditing is the most operationally sound approach for production databases. Instead of logging every read and write across all tables, you grant privileges on sensitive tables to a dedicated audit role. pgAudit then logs only statements that touch those tables.

-- Create the dedicated audit role (no login, no privileges of its own)
CREATE ROLE auditor;

-- Grant the operations you want to audit on sensitive tables
-- pgAudit watches for these grants to determine what to log
GRANT SELECT ON credit_cards TO auditor;
GRANT INSERT, UPDATE, DELETE ON credit_cards TO auditor;

GRANT SELECT, INSERT, UPDATE, DELETE ON patient_records TO auditor;
GRANT SELECT ON medical_history TO auditor;

-- Assign the audit role to pgAudit
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();

-- Verify the setting
SHOW pgaudit.role;

Now, any statement by any user that accesses credit_cards, patient_records, or medical_history will generate an OBJECT-type audit record — regardless of whether the executing user is appuser, readonly_user, or a DBA connecting via psql. The audit role itself never executes queries; it exists solely as a marker for pgAudit.

-- Exclude monitoring user from session audit entirely
ALTER ROLE monitoring SET pgaudit.log = 'none';

-- Limit a read-only user to logging only reads
ALTER ROLE readonly_user SET pgaudit.log = 'read';

Compliance Patterns

SOC 2 Type II

SOC 2 Type II audits focus on the Trust Services Criteria: Security, Availability, Processing Integrity, Confidentiality, and Privacy. For databases, auditors want evidence that schema changes are tracked, access control changes are logged, and connections are recorded.

pgaudit.log = 'ddl, role, connection'  # Track schema changes and access
pgaudit.log_parameter = on             # Log query parameters
pgaudit.log_catalog = off              # Reduce ORM noise
log_connections = on
log_disconnections = on

PCI-DSS

PCI-DSS Requirement 10 mandates logging all access to system components, including cardholder data. Requirement 10.3 specifically requires capturing the user, type of event, date/time, indication of success/failure, origination, and identity of affected data.

pgaudit.log = 'all'           # All reads and writes on cardholder data
pgaudit.log_relation = on     # Log each relation accessed
log_min_duration_statement = 0  # Log all query durations
log_line_prefix = '%m [%p] %u@%d %r'

HIPAA

HIPAA's Security Rule requires audit controls under 45 CFR §164.312(b). The standard requires recording and examining activity on systems that contain or use electronic Protected Health Information (ePHI). The key is demonstrating that all access to PHI tables is logged.

# Use role-based object auditing to target PHI tables precisely
pgaudit.role = 'hipaa_auditor'
pgaudit.log_parameter = on
# Also enable session auditing for DDL and role changes
pgaudit.log = 'ddl, role, connection'

-- Grant audit coverage on PHI tables
CREATE ROLE hipaa_auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON patient_records TO hipaa_auditor;
GRANT SELECT, INSERT, UPDATE, DELETE ON medical_history TO hipaa_auditor;
GRANT SELECT ON prescriptions TO hipaa_auditor;

ALTER SYSTEM SET pgaudit.role = 'hipaa_auditor';
SELECT pg_reload_conf();

Managing Audit Log Volume

Log volume is the primary operational challenge with pgAudit. On a database serving 10,000 queries per second, enabling full session auditing means logging 10,000 records per second — approximately 1 GB every few minutes, and potentially 500 GB per day.

Strategy 1: Use object auditing for read/write classes

As described above, grant audit permissions only on tables that actually contain sensitive data. A typical database might have 200 tables but only 5-10 contain cardholder data or PHI. Object auditing reduces log volume by 95%+ compared to session-level READ, WRITE auditing.

Strategy 2: Per-role overrides for service accounts

-- Exclude monitoring user from session audit
ALTER ROLE monitoring SET pgaudit.log = 'none';

-- Override session audit for read-only users
ALTER ROLE readonly_user SET pgaudit.log = 'read';

Strategy 3: Disable catalog auditing

pgaudit.log_catalog = off

ORMs like SQLAlchemy and ActiveRecord issue dozens of pg_* catalog queries on every connection. Disabling catalog auditing can reduce log volume by 30-50% on ORM-heavy workloads with no loss of compliance-relevant data.

Strategy 4: Ship logs off the database server immediately

Audit logs must not live on the database server. They can be modified or deleted by anyone with OS-level access to that host. Ship them to an immutable destination — S3 with Object Lock, Elasticsearch, Splunk, or a managed SIEM.

# Ship logs to Elasticsearch via Filebeat
# /etc/filebeat/filebeat.yml
filebeat.inputs:
- type: log
  paths:
    - /var/log/postgresql/postgresql-*.log
  include_lines: ['AUDIT:']
  multiline.pattern: '^\d{4}-\d{2}-\d{2}'
  multiline.negate: true
  multiline.match: after

The include_lines: ['AUDIT:'] filter means Filebeat only ships pgAudit records, not all PostgreSQL log output. This dramatically reduces the volume sent to the SIEM and keeps indexing costs in check.

Key Takeaways

Working with JusDB on PostgreSQL Compliance

Configuring pgAudit correctly is the first step. Operationalizing it — managing log volume, building retention pipelines, maintaining audit coverage through schema changes, and producing the evidence packages that auditors actually want — is where most teams run into trouble. Compliance frameworks evolve, databases grow, and an audit configuration that was adequate at 500 QPS becomes a liability at 5,000 QPS.

At JusDB, we work with engineering and security teams to design and maintain PostgreSQL audit configurations that satisfy SOC 2, PCI-DSS, and HIPAA requirements without compromising database performance. That includes pgAudit configuration tuned to your specific query patterns, log shipping pipelines to your SIEM or cloud storage, and documentation structured to answer auditor questions directly.

If your PostgreSQL deployment is approaching a compliance audit or you need to harden an existing pgAudit configuration, reach out to the JusDB team. We can review your current setup and identify gaps before your auditors do.

Related Reading

• PostgreSQL Database Hardening — OS-level and network-level controls to complement pgAudit
• PostgreSQL Privilege Management — Role design and least-privilege patterns that reduce audit scope
• PostgreSQL Row-Level Security — Enforce data access boundaries at the database level for multi-tenant systems