SQL 126 🛢️ Database Auditing, Compliance Logging, and Event Tracking
Database auditing is not a single mechanism but a spectrum of approaches that differ in what they capture, where the record is stored, and whether the record can be tampered with by the same principals being audited. The choice of approach is determined by the compliance framework and the threat model, not by convenience. A native query log that captures every statement is easy to enable but produces unstructured text that a privileged user can delete. A trigger-based audit table captures before-and-after values but runs inside the audited transaction, which means a rollback erases the audit record as well . pgAudit provides structured, compliance-grade logging, but it still writes to the same log files that the database administrator controls . The question that matters is not “are we logging?” but “can the person who made the change also delete the log of the change?”
This chapter covers the four auditing mechanisms—native logging, pgAudit, trigger-based row auditing, and change data capture to external storage—and the compliance framework requirements that determine which combination is appropriate. It examines the SELECT auditing gap that makes triggers unreliable for access tracking, the retention periods imposed by PCI DSS, HIPAA, and SOX, and the integrity requirement that pushes audit records out of the audited database.
Key point: Audit logs that reside inside the audited database are not tamper-proof. A principal with sufficient privileges can modify or delete them. Compliance frameworks that require log integrity—PCI DSS, HIPAA, SOX—require the audit record to be stored in a repository that the audited environment cannot reach .
Why the choice of auditing mechanism matters
The rollback erasure problem. Trigger-based auditing for SELECT statements is fundamentally unreliable. A trigger fires when a query executes, but if the transaction is rolled back, the trigger’s side effect is also rolled back. An attacker can query sensitive data, roll back the transaction, and leave no trace in the audit table. The PostgreSQL community has documented this limitation explicitly: “I just covered all my tracks quite effectively, because the ROLLBACK canceled any and all side effects of my transaction” . This is why SELECT auditing must rely on logging outside the transaction scope, not on triggers.
The structured versus unstructured problem. PostgreSQL’s native log_statement = 'all' writes every SQL statement to the log file as unstructured text. It is simple to enable and useful for performance analysis, but it is difficult to parse automatically, prone to volume explosion, and may include sensitive query parameters in plaintext . pgAudit exists because compliance auditors need structured records with session IDs, user identities, object names, and statement types, not free-text log lines.
The compliance framework problem. Different regulations impose different retention and integrity requirements. PCI DSS requires one year of retention with the most recent three months immediately available. HIPAA requires six years. SOX commonly requires seven years for financial records. GDPR does not specify a retention period but requires that retention be justified and proportionate . A single organization-wide retention default does not satisfy any of these frameworks. The retention policy must be derived from the most stringent requirement applicable to each database’s data classification.
The insider threat problem. The principal who is most likely to abuse access is also the principal with the most ability to erase the evidence. A database administrator can disable logging, delete audit rows, and truncate the log file. The audit record must be outside the administrator’s reach for the audit to be meaningful. This is the reasoning behind forwarding audit events to a SIEM, a Kafka topic with long retention, or an S3 bucket with object lock .
a. Native Logging and pgAudit
PostgreSQL’s built-in logging captures statements, connections, disconnections, and durations. The configuration is in postgresql.conf and takes effect after a reload or restart. The output is text, and tools like pgBadger can parse it into reports.
-- Enable comprehensive native logging
ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
SELECT pg_reload_conf();The native log is a starting point, not a compliance solution. It captures everything but structures nothing. pgAudit adds the structure. It extends the native log with session-level and object-level audit records that include the user, database, object name, and statement type in a parseable format.
-- Enable pgAudit
CREATE EXTENSION IF NOT EXISTS pgaudit;
-- Log write operations and DDL
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
ALTER SYSTEM SET pgaudit.log_parameter = off;
SELECT pg_reload_conf();The pgaudit.log setting accepts categories: read, write, function, role, ddl, misc, and all. The write category captures INSERT, UPDATE, DELETE, TRUNCATE, and COPY. The ddl category captures schema changes. The pgaudit.log_parameter setting controls whether query parameters are included; turning it off reduces log volume and prevents sensitive values from appearing in the log.
pgAudit can be scoped by session, by user, or by object. A session-level setting applies to the current connection. A user-level setting applies to a specific role. An object-level setting applies only to specific tables and requires a dedicated audit role with limited permissions. The object-level scope is the most precise and the least noisy, but it requires explicit configuration for each table that needs auditing.
-- Scope auditing to a specific sensitive role
ALTER ROLE finance_app SET pgaudit.log = 'all';b. Trigger-Based Row Auditing
Triggers capture before-and-after values for row-level changes. The audit table stores the operation type, the old row, the new row, the user, and the timestamp. This is the mechanism that provides the field-level modification history that SOX auditors require for financial reporting tables.
-- Audit table for row-level changes
CREATE TABLE orders_audit (
audit_id bigserial PRIMARY KEY,
operation char(1) NOT NULL,
row_id bigint NOT NULL,
old_data jsonb,
new_data jsonb,
changed_by text NOT NULL,
changed_at timestamptz DEFAULT now()
);
-- Trigger function
CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
INSERT INTO orders_audit (operation, row_id, old_data, changed_by)
VALUES ('D', OLD.id, to_jsonb(OLD), current_user);
RETURN OLD;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO orders_audit (operation, row_id, old_data, new_data, changed_by)
VALUES ('U', NEW.id, to_jsonb(OLD), to_jsonb(NEW), current_user);
RETURN NEW;
ELSE
INSERT INTO orders_audit (operation, row_id, new_data, changed_by)
VALUES ('I', NEW.id, to_jsonb(NEW), current_user);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();The trigger approach has two structural limitations. It adds a write to every transaction, which increases latency and creates contention on the audit table under concurrent load. And it cannot reliably audit SELECT statements because a rollback erases the trigger’s side effect. For row-level change history where the transaction commits, it is the correct mechanism. For access auditing, it is not.
c. CDC to External Storage and the Integrity Requirement
Logical replication reads the Write-Ahead Log and produces a structured stream of every committed change without querying the source tables. Debezium consumes this stream and writes it to Kafka, where it can be forwarded to an external audit store. The key advantage for compliance is that the audit record is not stored in the database being audited. A DBA who deletes rows from the source table does not delete the corresponding Kafka messages.
-- postgresql.conf: enable logical decoding
-- wal_level = logical
-- Define the captured tables
CREATE PUBLICATION audit_pub FOR TABLE financial_transactions;The setup requires wal_level = logical, a replication slot, and a publication that defines the captured tables. The CDC stream captures INSERT, UPDATE, and DELETE operations, but not SELECT. For SELECT auditing, the native log or pgAudit remains necessary.
The limitation of CDC for compliance is the replication slot. If the consumer falls behind, the slot retains WAL segments and the database’s disk usage grows. Monitoring slot lag is part of the operational discipline that CDC requires.
The integrity model is what makes CDC a compliance mechanism rather than just another audit source. The record crosses a boundary that the audited principal does not control. Once in Kafka, the messages can be written to an S3 bucket with object lock, which prevents deletion even by the bucket owner until the retention period expires. This is the architecture that satisfies the “cannot be tampered with” requirement that native logs and trigger tables cannot.
Complete Example Session
-- ============================================
-- PART 1: NATIVE LOGGING BASELINE
-- ============================================
ALTER SYSTEM SET log_statement = 'ddl';
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d ';
SELECT pg_reload_conf();
-- ============================================
-- PART 2: PGAUDIT FOR STRUCTURED COMPLIANCE LOGGING
-- ============================================
CREATE EXTENSION IF NOT EXISTS pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl';
ALTER SYSTEM SET pgaudit.log_parameter = off;
SELECT pg_reload_conf();
-- Verify the setting
SHOW pgaudit.log;
-- ============================================
-- PART 3: SCOPED AUDITING FOR A SPECIFIC ROLE
-- ============================================
-- Log all actions by a sensitive service account
ALTER ROLE finance_app SET pgaudit.log = 'all';
-- ============================================
-- PART 4: TRIGGER-BASED ROW AUDIT
-- ============================================
CREATE TABLE financial_transactions_audit (
audit_id bigserial PRIMARY KEY,
operation char(1),
row_id bigint,
old_data jsonb,
new_data jsonb,
changed_by text,
changed_at timestamptz DEFAULT now()
);
CREATE OR REPLACE FUNCTION audit_financial_transactions() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'UPDATE' THEN
INSERT INTO financial_transactions_audit
(operation, row_id, old_data, new_data, changed_by)
VALUES ('U', NEW.id, to_jsonb(OLD), to_jsonb(NEW), current_user);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO financial_transactions_audit
(operation, row_id, old_data, changed_by)
VALUES ('D', OLD.id, to_jsonb(OLD), current_user);
RETURN OLD;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER financial_transactions_audit_trigger
AFTER UPDATE OR DELETE ON financial_transactions
FOR EACH ROW EXECUTE FUNCTION audit_financial_transactions();
-- ============================================
-- PART 5: EXTERNAL AUDIT STORAGE VIA CDC
-- ============================================
-- postgresql.conf
-- wal_level = logical
CREATE PUBLICATION audit_pub FOR TABLE financial_transactions;
-- Debezium connector consumes this publication
-- and writes changes to Kafka
-- The audit record is not in the PostgreSQL database
-- ============================================
-- PART 6: VERIFY AUDIT RECORDS
-- ============================================
-- Check pgAudit output in the PostgreSQL log
-- Check trigger audit table
SELECT * FROM financial_transactions_audit ORDER BY audit_id DESC LIMIT 10;
-- ============================================
-- PART 7: RETENTION POLICY BY FRAMEWORK
-- ============================================
-- PCI DSS: 1 year, 3 months immediately available
-- HIPAA: 6 years
-- SOX: 7 years (common practice)
-- GDPR: justified and proportionate
-- ============================================
-- PART 8: LOG INTEGRITY
-- ============================================
-- Native logs and trigger tables are inside the database
-- A DBA can modify or delete them
-- For integrity, forward to an external system
-- (Kafka, S3 with object lock, SIEM)
-- ============================================
-- PART 9: SELECT AUDITING LIMITATION
-- ============================================
-- Triggers cannot reliably audit SELECT because ROLLBACK erases the audit
-- Use pgAudit or native log_statement for SELECT auditing
-- The log is outside the transaction scope
-- ============================================
-- PART 10: COMPLIANCE CHECKLIST
-- ============================================
-- [ ] pgAudit enabled for DDL and write operations
-- [ ] Trigger audit for tables requiring before/after values
-- [ ] CDC stream for external immutable audit storage
-- [ ] Retention policy aligned with applicable framework
-- [ ] Log integrity verified (external repository)
-- [ ] SELECT auditing via log, not triggerThe ten parts trace the spectrum from native logging through pgAudit, trigger-based row auditing, and CDC-based external storage. The critical distinctions are in Part 8 (integrity requires external storage) and Part 9 (SELECT auditing cannot rely on triggers).
Quick Reference
Auditing Mechanisms Compared
| Mechanism | Captures | Location | Tamper-Resistant |
|---|---|---|---|
| Native log | All statements | Log file | No |
| pgAudit | Structured DDL/DML | Log file | No |
| Triggers | Row changes | Audit table | No (same DB) |
| CDC | Committed changes | External (Kafka) | Yes (if external) |
pgAudit Categories
| Category | Captures |
|---|---|
read | SELECT, COPY |
write | INSERT, UPDATE, DELETE, TRUNCATE |
function | Function and procedure calls |
role | User and privilege changes |
ddl | Schema changes |
all | Everything |
Compliance Retention
| Framework | Minimum Retention | Notes |
|---|---|---|
| PCI DSS | 1 year | 3 months immediately available |
| HIPAA | 6 years | Security documentation |
| SOX | 7 years | Common practice |
| GDPR | Not specified | Must be justified |
Trigger Auditing Limitations
| Operation | Trigger Audit Reliable |
|---|---|
| INSERT | Yes (if transaction commits) |
| UPDATE | Yes (if transaction commits) |
| DELETE | Yes (if transaction commits) |
| SELECT | No (rollback erases) |
Best Practices
✅ Do This:
-- Use pgAudit for structured compliance logging
ALTER SYSTEM SET pgaudit.log = 'write, ddl'; -- ✅-- Use triggers for row-level before/after values on financial tables
CREATE TRIGGER audit_trigger AFTER UPDATE ON financial_transactions; -- ✅# Forward audit records to an external repository the DBA cannot modify
# Kafka, S3 with object lock, or a SIEM -- ✅-- Scope pgAudit by role for sensitive service accounts
ALTER ROLE finance_app SET pgaudit.log = 'all'; -- ✅❌ Don’t Do This:
-- Rely on triggers for SELECT auditing
-- ROLLBACK erases the audit record -- ❌-- Store audit records only in the same database
-- A DBA can delete them -- ❌-- Enable log_statement = 'all' without retention and rotation
-- Disk fills, logs are deleted, audit trail is lost -- ❌-- Assume one retention period fits all databases
-- PCI, HIPAA, and SOX have different requirements -- ❌Real-World Examples
1. Enable pgAudit
CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, write';
SELECT pg_reload_conf();2. Scope to a Role
ALTER ROLE sensitive_app SET pgaudit.log = 'all';3. Trigger for Row Audit
CREATE TRIGGER audit_trigger AFTER UPDATE OR DELETE ON financial_transactions
FOR EACH ROW EXECUTE FUNCTION audit_financial_transactions();4. Native Log for DDL
ALTER SYSTEM SET log_statement = 'ddl';5. CDC Publication
CREATE PUBLICATION audit_pub FOR TABLE financial_transactions;6. Check pgAudit Output
grep "AUDIT:" /var/log/postgresql/postgresql.log7. External Audit via Debezium
{"connector.class": "io.debezium.connector.postgresql.PostgresConnector"}8. Monitor Slot Lag
SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
FROM pg_replication_slots;9. Role-Based Audit Scope
ALTER ROLE postgres SET pgaudit.log = 'none';10. Verify Audit Table
SELECT * FROM financial_transactions_audit ORDER BY audit_id DESC;Visual
The Auditing Spectrum
┌──────────────────────────────────────────────────────────────┐
│ MECHANISM CAPTURES INTEGRITY │
│ │
│ Native log → All SQL Low (DBA can delete) │
│ │
│ pgAudit → Structured Low (same log files) │
│ DDL/DML │
│ │
│ Triggers → Row changes Low (same DB) │
│ (committed) │
│ │
│ CDC → Committed High (external) │
│ changes │
│ │
│ For compliance, the record must leave the database. │
│ A DBA who can change the data can also change the log. │
│ │
└──────────────────────────────────────────────────────────────┘The SELECT Auditing Gap
┌──────────────────────────────────────────────────────────────┐
│ ATTACKER QUERIES SENSITIVE DATA │
│ │
│ BEGIN; │
│ SELECT * FROM salaries WHERE employee_id = 1000; │
│ -- Attacker sees the data │
│ ROLLBACK; │
│ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ TRIGGER FIRES, WRITES TO AUDIT TABLE ││
│ │ ││
│ │ ROLLBACK REVERSES THE TRIGGER'S INSERT ││
│ │ ││
│ │ Audit table: no record ││
│ │ Attacker: has the data ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ FIX: Use pgAudit or native log_statement for SELECT │
│ The log is written outside the transaction scope. │
│ │
└──────────────────────────────────────────────────────────────┘Compliance Framework Mapping
┌──────────────────────────────────────────────────────────────┐
│ FRAMEWORK FOCUS RETENTION │
│ │
│ PCI DSS Cardholder data 1 year (3 mo live) │
│ │
│ HIPAA ePHI access 6 years │
│ │
│ SOX Financial reports 7 years (common) │
│ Before/after values │
│ │
│ GDPR Personal data Justified/proportionate │
│ │
│ One retention period does not fit all. │
│ Derive the policy from the data classification. │
│ │
└──────────────────────────────────────────────────────────────┘External Audit Architecture
┌──────────────────────────────────────────────────────────────┐
│ POSTGRESQL │
│ │
│ ┌─────────────────┐ │
│ │ WAL │ Every committed change │
│ └────────┬────────┘ │
│ │ logical decoding │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Debezium │ Reads WAL, produces events │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Kafka │ Durable, replayable │
│ └────────┬────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ SIEM / S3 │ Immutable audit store │
│ │ (object lock) │ DBA cannot modify │
│ └─────────────────┘ │
│ │
│ The audit record is not in the database. │
│ The DBA cannot delete it. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Native logging | log_statement = 'all'; unstructured text |
| pgAudit | Structured compliance logging; categories |
| Trigger auditing | Before/after values; committed changes only |
| Trigger SELECT limitation | Rollback erases audit record |
| CDC | WAL-based; external storage; no table queries |
| Log integrity | Requires external repository |
| PCI DSS retention | 1 year |
| HIPAA retention | 6 years |
| SOX retention | 7 years |
Key takeaways:
- The location of the audit record determines its integrity. A native log file, a trigger audit table, and a pgAudit output all reside inside the database being audited. A principal with sufficient privileges can modify or delete them. Compliance frameworks that require log integrity require the record to be stored externally .
- Triggers cannot audit SELECT. A trigger’s side effect is part of the transaction. A rollback erases it. An attacker can query sensitive data and roll back, leaving no trace in the audit table . SELECT auditing must use pgAudit or native logging, which write outside the transaction scope.
- pgAudit provides the structure that auditors require. Native logging produces unstructured text. pgAudit produces session IDs, user identities, object names, and statement types in a parseable format . The categories allow scoping:
writefor DML,ddlfor schema changes,allfor comprehensive coverage. - Trigger-based row auditing is for before/after values, not access tracking. It provides the field-level modification history that SOX requires for financial reporting tables . It is the correct tool for capturing what a row looked like before and after an UPDATE, not for capturing who read it.
- Retention is framework-specific. PCI DSS, HIPAA, and SOX impose different minimum retention periods . A single organization-wide default does not satisfy any of them. The retention policy must be derived from the data classification and the applicable framework.
Remember: Database auditing is a spectrum from convenience to compliance. Native logging is convenient and insufficient. pgAudit is structured and still local. Triggers capture row history but cannot capture access. CDC moves the record outside the database, which is what integrity requires. The choice is not between “auditing” and “not auditing”; it is between mechanisms that answer different questions. A compliance audit asks who changed what, when, and whether the record can be trusted. The mechanism that answers all three is the one that stores the record where the person being audited cannot reach it.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!