Audit Logs

An audit log answers who did what, when, from where. It is required by most compliance regimes and is the only source of truth after a suspected compromise.

What to record

Always:

• Authentication successes and failures.
• Privilege changes: grants, revokes, role creation, password changes.
• DDL: create, alter, drop.
• Administrative connections and superuser activity.
• Configuration changes.

Usually worth it:

• Access to tables containing personal or financial data.
• Bulk exports — a SELECT returning millions of rows deserves a record.
• Deletes and truncates.

Per engine

PostgreSQL — the pgaudit extension:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'
pgaudit.log_catalog = off
pgaudit.log_parameter = off        # keep literal values out of the audit log

-- Object-level auditing for one sensitive table.
CREATE ROLE auditor;
GRANT SELECT ON customers TO auditor;
-- pgaudit.role = 'auditor'  → reads of that table are audited

MySQL / MariaDB — the audit plugin (Enterprise, Percona or MariaDB's own):

plugin_load_add = server_audit
server_audit_logging = ON
server_audit_events = CONNECT,QUERY_DDL,QUERY_DCL
server_audit_output_type = SYSLOG

MongoDB — auditing in Enterprise and Atlas:

auditLog:
  destination: syslog
  filter: '{ atype: { $in: ["authenticate", "createUser", "dropCollection"] } }'

Elasticsearch / OpenSearch — security audit logging with configurable event categories.

Storage

Requirements for the destination:

• Append-only or write-once retention.
• Different access control from the database itself.
• Retention matching the compliance obligation, which is often years.
• Monitoring that alerts if audit records stop arriving.

Making them usable

An audit log nobody reads is a compliance artefact rather than a control. Add alerting for the events that should never happen quietly:

• Authentication failures above a baseline rate, or from an unexpected source.
• Any use of a superuser account outside a change window.
• Privilege grants outside the migration process.
• DROP or TRUNCATE on a production table.
• A bulk export from a table containing personal data.

Personal data in the audit log

Auditing can capture the very data it is meant to protect — a WHERE email = '…' clause records the email. Disable parameter logging where possible, redact at the collector, and apply the same access controls to the audit store as to the database.