What is database auditing, and what should you log?
Quick Answer
Database auditing is the practice of recording who did what, when, to which data — a durable trail used for security investigation, compliance, and detecting misuse. At minimum, log authentication attempts (success/failure), schema changes (DDL), privilege changes, and access to particularly sensitive tables/columns; for regulated data, often every read/write to specific tables. Audit logs must themselves be protected from tampering (ideally written to storage the application/admin accounts being audited can't modify or delete).
Detailed Answer
Why auditing matters beyond "logging in general"
Application logs typically capture business events ("order placed," "user logged in"); database auditing specifically answers "who touched this data, and what did they do to it" — critical for investigating a suspected breach, satisfying regulatory compliance (SOC 2, HIPAA, PCI-DSS, GDPR all have audit-trail requirements in some form), and detecting insider misuse (an employee with legitimate access browsing records they have no business reason to view).
What's commonly worth auditing
- Authentication events — successful and (especially) failed login attempts, which can reveal brute-force attempts or credential-stuffing attacks in progress.
- DDL/schema changes —
CREATE,ALTER,DROPstatements, since these can indicate either legitimate migrations or an attacker attempting to cover tracks/establish persistence (e.g., creating a backdoor account or table). - Privilege/permission changes —
GRANT/REVOKEstatements, since privilege escalation is a common step in a real attack chain. - Access to specifically sensitive tables/columns — for regulated data (health records, financial data, PII), logging every
SELECT/UPDATEagainst those specific tables, even by legitimately authorized users, so that unusual access patterns (a support engineer querying far more customer records than their role would normally require) can be detected. - Administrative/superuser actions — anything performed with elevated privileges deserves closer logging scrutiny than routine application-role activity, since it represents higher potential impact if misused.
Implementation approaches
-- PostgreSQL: pgAudit extension provides fine-grained, configurable audit logging
-- (session-level and object-level auditing of DDL, role changes, specific tables, etc.)
Most major engines offer a dedicated auditing feature/extension (SQL Server Audit, Oracle's Unified Auditing, MySQL Enterprise Audit, PostgreSQL's pgAudit) rather than expecting you to hand-roll auditing via ad-hoc triggers — these are generally more complete, more performant, and harder to accidentally bypass than a custom trigger-based approach (see the triggers question for why trigger-based logic can itself be fragile/incomplete).
Protecting the audit trail itself
An audit log that the very users being audited can modify or delete isn't a trustworthy record — best practice is to ship audit logs to a separate, append-only destination (a dedicated logging service, write-once storage, or a system the application's own database credentials have no delete/modify access to) so that even a fully compromised application account can't retroactively erase evidence of what it did.
The cost side of auditing
Fine-grained auditing (logging every single row read, for instance) has real performance and storage costs, and can itself become a source of sensitive-data sprawl (the audit log might contain the very sensitive values it's auditing access to, requiring its own access controls and retention policy). Scope auditing deliberately — broad enough to satisfy real compliance/security needs, not maximal by default — and treat the audit log's own storage and access control with the same rigor as the primary data it's protecting.