Session, Privilege & DDL Audit Logging (pgaudit & Enterprise Audit)

Who accessed, modified, or deleted which table, from which IP and at what time? Track all DDL, ROLE, WRITE, and sensitive READ operations with pgaudit.

Target Compliance Frameworks

✓ SOC 2 Type II✓ PCI-DSS v4.0 (Req 10)✓ HIPAA § 164.312✓ ISO 27001 (A.12.4)✓ KVKK / GDPR

Key Security & Compliance Objectives

Audit all schema changes (CREATE, ALTER, DROP) in real-time

Record commands executed by DBA and superuser roles in an immutable audit stream

Track granular SELECT access on regulated tables (e.g. accounts, users)

Feed audit streams into centralized SIEM systems for compliance alerting

Architecture & Operational Mechanism

pgaudit operates via execution hooks within PostgreSQL kernel. Unlike generic query loggers, it resolves object identities and role hierarchies before query execution, producing structured audit trails forwarded to SIEM platforms.

Reference Configuration File

# postgresql.conf - pgaudit Enterprise Setup
shared_preload_libraries = 'pgaudit'

# Denetlenecek eylemler: DDL, Role yetkileri, Yazma ve Fonksiyon çağrıları
pgaudit.log = 'ddl, role, write, function'
pgaudit.log_catalog = off
pgaudit.log_level = 'log'
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

# Syslog ile SIEM'e aktarım
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres_audit'

Step-by-Step Implementation Guide

Sequential execution procedures for verified production rollout.

Install pgaudit Extension

bash

Install the OS package matching your database major version.

sudo apt-get update && sudo apt-get install -y postgresql-17-pgaudit

Configure shared_preload_libraries & Restart

bash

Add pgaudit to shared_preload_libraries in postgresql.conf and restart PostgreSQL.

sudo systemctl restart postgresql

Create Extension in Target Database

sql

Connect as superuser and create extension in the target database.

CREATE EXTENSION IF NOT EXISTS pgaudit;

Fine-Grained Table-Level Audit Role

sql

Assign a dedicated audit role to audit specific tables without spamming global query logs.

CREATE ROLE rds_pgaudit_auditor;
ALTER SYSTEM SET pgaudit.role = 'rds_pgaudit_auditor';
SELECT pg_reload_conf();

-- Yalnızca hassas tablolarda okumaları logla:
GRANT SELECT ON public.customers TO rds_pgaudit_auditor;

Production Best Practices

✓Keep pgaudit.log_catalog = off to prevent log noise from internal catalog lookups.
✓Forward audit trails immediately to remote WORM or SIEM storage to prevent DBA tampering.
✓Ensure log signing and checksum verification for regulatory non-repudiation.

Common Pitfalls & Anti-Patterns

⚠Enabling pgaudit.log = 'all' indiscriminately, which exhausts storage within minutes under high OLTP traffic.
⚠Failing to mask cleartext secrets in command traces during password modification DDLs.

Audit List Critical CVE Feed →