Why can't I just use pg_audit for compliance?

pg_audit logs SQL statements to PostgreSQL's log files, but these files can be modified or deleted by anyone with filesystem access. For compliance, you need a log that is provably unmodified. pgcomply's hash chain provides cryptographic evidence of integrity.

How does SHA-256 hash chaining work?

Each audit entry contains a SHA-256 hash computed from its own content plus the hash of the previous entry. This creates a chain where modifying any entry changes its hash, which no longer matches the next entry's expected value. It's the same principle used in blockchain technology, applied to audit logging.

Can a superuser tamper with pgcomply's audit trail?

pgcomply prevents casual tampering via UPDATE/DELETE triggers that raise exceptions. A determined superuser could disable triggers and modify data, but this would break the hash chain, which is detectable via verify_audit(). For maximum security, run verify_audit() from a separate monitoring system.

How much storage does the audit trail use?

Each audit entry is approximately 200-500 bytes depending on the detail JSON. At 1,000 events per day, that's about 150-350 KB/day or roughly 50-125 MB/year. pgcomply includes retention policies to manage audit log growth.

Building an Immutable Audit Trail in PostgreSQL

Why Standard Logging Isn't Enough

PostgreSQL's built-in logging writes to text files. pg_audit extends this with structured SQL statement logging. Both are useful for debugging, but neither satisfies compliance requirements because they're mutable. A privileged user can edit log files, delete entries, or disable logging entirely — and nobody can prove it happened.

Compliance frameworks require tamper-evident logging:

SOC 2 CC7.2: System operations are logged in a manner that enables later reconstruction
DORA Article 9: All ICT changes must be documented with audit trails
GDPR Article 5(2): The controller must demonstrate compliance (accountability principle)

How pgcomply's Audit Chain Works

Every compliance operation (forget, classify, health_check, consent changes, etc.) is automatically logged in pgcomply.audit_log:

SELECT id, event_type, created_at, executed_by, chain_hash
FROM pgcomply.audit_log
ORDER BY id DESC LIMIT 5;

Each entry contains:

event_type: What happened (forget, classify, health_check, etc.)
details: JSONB with operation-specific data
executed_by: The PostgreSQL role that performed the operation
request_id: Optional correlation ID for tracing
chain_hash: SHA-256 hash of this entry + previous entry's hash

The Hash Chain

Entry 1: hash = SHA256(content_1 || '0000...0000')
Entry 2: hash = SHA256(content_2 || hash_of_entry_1)
Entry 3: hash = SHA256(content_3 || hash_of_entry_2)

If someone modifies Entry 2, its hash changes, which means Entry 3's expected previous hash no longer matches. The chain is broken.

Verifying Integrity

SELECT pgcomply.verify_audit();

This walks the entire chain and returns:

 status | entries_checked | first_break | detail
--------+-----------------+-------------+--------
 valid  | 1247            |             | SHA-256 chain intact

If tampering is detected:

 status  | entries_checked | first_break | detail
---------+-----------------+-------------+---------------------------
 invalid | 1247            | 842         | Chain break at entry 842

Tamper Protection

pgcomply installs triggers on the audit table:

-- These triggers raise exceptions:
-- UPDATE on pgcomply.audit_log → ERROR: audit log is immutable
-- DELETE on pgcomply.audit_log → ERROR: audit log is immutable

This prevents accidental modification. For intentional attacks, the hash chain provides detection.

What Gets Logged

Every pgcomply function that changes state logs an event:

| Operation | Event Type | Details | |-----------|-----------|---------| | forget() | forget_complete | subject_id, tables affected, row counts | | register_pii() | pii_registered | table, column, pii_type | | health_check() | health_check | pass/warn/fail counts | | grant_consent() | consent_granted | subject_id, purpose, source | | report_breach() | breach_reported | breach_id, severity, subjects | | mask() | masking_configured | table, column, type |

Retention Management

Audit logs grow. Set retention policies:

SELECT pgcomply.retain('audit_log', 'created_at', '2 years', p_schema_name := 'pgcomply');

This keeps 2 years of audit history and purges older entries. For regulated industries, set longer retention periods (7+ years).

Summary

An immutable audit trail is the foundation of provable compliance. pgcomply's SHA-256 hash chain ensures that no audit entry can be modified without detection. Combined with trigger-based tamper protection, this gives you audit evidence that satisfies SOC 2, DORA, and GDPR requirements. Run verify_audit() regularly — ideally from a separate monitoring system — to maintain chain integrity.