Audit Logging Gaps

Scenario

After a data breach incident, the security team asks: “Who deleted 50,000 rows from the customers table last Tuesday?” The DBA checks PostgreSQL logs and finds only DDL statements were logged (log_statement = 'ddl'). The DELETE operation — a DML statement — generated no log entry. The pgaudit extension was never installed. The audit trail is completely missing.

How to Identify

Conditions:

• log_statement = 'ddl' — only CREATE/ALTER/DROP are logged, not INSERT/UPDATE/DELETE
• pgaudit extension not installed (SELECT * FROM pg_extension WHERE extname='pgaudit')
• PostgreSQL logs contain no <a class="sev1-termlink" href="https://thesev1database.com/glossary/tuple/">record of the DML that caused data loss
• log_min_duration_statement not set or too high (misses short DML queries)
• No application-level audit trail to fall back on

Analysis Steps

-- Check current logging configuration
SELECT name, setting
FROM pg_settings
WHERE name IN (
    'log_statement',
    'log_min_duration_statement',
    'log_connections',
    'log_disconnections',
    'log_duration',
    'log_error_verbosity',
    'log_line_prefix'
);
-- log_statement='ddl' = only DDL logged (misses all DML)

-- Check if pgaudit is installed
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';
-- No rows = pgaudit not installed

-- Check what was logged around the incident time (from OS):
-- grep "2024-01-15 14:2" $PGDATA/log/postgresql*.log
-- Likely shows only connection events, no DML

-- Simulate: check what log_statement='mod' would catch
-- log_statement options:
-- 'none'    = nothing logged
-- 'ddl'     = CREATE/ALTER/DROP only
-- 'mod'     = DDL + INSERT/UPDATE/DELETE/TRUNCATE
-- 'all'     = every statement (high volume, for debugging only)

Pitfalls

• log_statement = 'ddl' is the default in many installations and deliberately misses DML. This is fine for operational logs but wrong for security audit trails.
• log_statement = 'all' logs every statement including SELECT — creates enormous log volume and contains sensitive data. Not suitable as a long-term audit approach.
• pgaudit provides selective, structured auditing — audit specific objects, specific users, or specific statement types without logging everything.
• log_min_duration_statement = 0 logs all statements that run, but doesn’t log statements that failed authentication or were rejected by RLS.
• PostgreSQL’s built-in logging doesn’t capture which rows were modified — only the SQL text. For row-level audit trail (who changed what value), use application-level triggers or temporal tables.

Resolution Approach

Install pgaudit for structured, compliance-ready audit logging. Set pgaudit.log = 'write' to capture all DML on all tables, or pgaudit.log_relation = on to log per-table access. Use log_line_prefix to include timestamp, user, and database in every log line.