Database audit logging is the practice of recording who connected to a database, what they ran, what data was affected, and when. A good audit log lets you answer, after the fact, questions such as “who exported the customer table last Tuesday?” and “which account changed this schema?” with evidence instead of guesses.
Audit logging is easy to confuse with ordinary operational logging, and the difference matters. A server log tells an engineer why the database is slow or why a connection failed. An audit log is a security and compliance record: it is meant to be complete, tamper-resistant, attributable to a person and kept for as long as your obligations require.
This page explains what an audit log should contain, the limits of native database logging, how to configure the basics, how long to keep records, what regulators and auditors expect, and where a dedicated query-level audit layer fits.
What a useful audit record contains
For each event, you need enough to reconstruct what happened without relying on memory.
| Field | Why it matters |
|---|---|
| Timestamp (with time zone) | Orders events and supports investigation windows |
| Who (the named person or service, not a shared role) | Attribution is the whole point of an audit log |
| Source (client address, application, tool) | Distinguishes a production application from a laptop |
| Session or connection identifier | Links statements that belong together |
| Statement (the query text or its normalized form) | Shows what was attempted |
| Target (database, schema, table, sometimes columns) | Shows which data was touched |
| Outcome (success, failure, error) | Failed attempts are often the first sign of misuse |
| Volume (rows returned or changed) | Separates a lookup from a bulk export |
| Why (a ticket or approval reference) | Ties the access to a reason of record |
If a record lacks the “who,” it is a log. If it has the “who,” the “what” and the “which data,” it is an audit trail.
Events worth capturing
- Connections and disconnections, including failed logins
- Privilege and role changes, such as grants, revokes and new users
- Schema changes (DDL), such as creating, altering or dropping objects
- Reads of sensitive data, such as queries against tables that hold personal or financial information
- Writes and deletes on important tables
- Administrative actions, such as configuration changes and extension installs
- Bulk operations, such as large exports or
COPY
Capturing every statement from every application can be expensive and noisy. A sensible approach is to log everything for human and administrative access, and to be selective for high-volume application traffic.
What native database logging gives you
Every major database engine can write some form of log. The detail varies.
- Connection and statement logging. PostgreSQL, for example, can log connections, disconnections and statements by setting parameters such as
log_connections,log_disconnectionsandlog_statement. See the PostgreSQL logging documentation. - Audit extensions. The pgAudit extension adds session and object-level audit logging for PostgreSQL. MySQL and SQL Server have their own audit features, and managed cloud databases expose audit options through the provider.
- Cloud-level logs. Managed services also log control-plane actions, such as who changed a database’s configuration. In AWS this goes to CloudTrail.
A starting configuration for PostgreSQL:
-- See current settings
SHOW log_connections;
SHOW log_disconnections;
SHOW log_statement;
-- Log connections, disconnections and schema-changing statements
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_statement = 'ddl';
SELECT pg_reload_conf();
Use 'mod' to include data-changing statements, or 'all' to log everything, bearing in mind the volume and the fact that statement text can contain sensitive values. See secret redaction in logs.
The limits of native logging
Native logs are a good floor, but they leave important gaps.
- Shared accounts hide the person. If five engineers connect as the same role, the log shows the role. It cannot say which engineer ran the query.
- Reads are often not logged by default. Turning on full statement logging helps, and it produces large volumes that are hard to search.
- They record that a query ran, not what came back. You may see the statement without knowing how many rows were returned or whether sensitive columns were included.
- They live on the database host. Someone who gains control of the server can alter or delete them. Logs must be shipped elsewhere quickly.
- They do not mask or block. A log tells you about a harmful query after it ran.
- Formats differ per engine. Each database has a different format, so a multi-database estate needs normalization.
- Statement text can leak secrets. A query with a literal password or card number writes that value into the log.
None of this means native logging is wrong. It means it is the foundation, not the whole answer.
Protecting the audit log itself
An audit log is only useful if it can be trusted.
- Ship it off the database host in near real time to a separate store.
- Make it append-only or write-once where possible, for example object storage with retention locks.
- Restrict who can read and who can delete. Administrators of the database should not be the only people able to erase its audit trail.
- Time-synchronize servers so events line up across systems.
- Monitor the logging itself. Alert if logging stops or the volume drops sharply.
- Protect sensitive content. Logs can hold personal data, so apply access controls and retention limits to them too.
How long to keep audit logs
Retention depends on the frameworks and contracts that apply to you. Common requirements include:
- PCI DSS requires audit trail history to be retained for at least a year, with a recent period immediately available for analysis.
- HIPAA requires documentation relevant to security compliance to be kept for six years. Organizations often keep audit records on a similar schedule.
- SOC 2 and ISO 27001 do not set a number, and expect you to define and follow a retention policy appropriate to your commitments.
Check the current text of each requirement and confirm it with your auditor, because interpretations differ. As a practical matter, keep recent data in searchable storage and move older data to cheaper, immutable storage.
What auditors typically ask
- Who has access to the production database, and who approved it?
- Can you show every access to this sensitive table in the last 90 days?
- Can you show that access was removed when someone left or changed roles?
- Who changed this schema or this permission, and when?
- How do you know the log is complete and unaltered?
The most common failure is not a missing log. It is an inability to connect a database action to an approved request and a named person. See closing the audit trail gap.
Query-level audit and database activity monitoring
Database activity monitoring (DAM) is the next step beyond native logs. A DAM layer sits in the path of each query and records it with the identity of the person, the statement, the outcome and often the volume, in one searchable place. It can also enforce rules before the query runs and mask sensitive values in the result. See database activity monitoring, DSPM vs DAM and our explainer on how DAM differs from native database logging.
A practical checklist
- Every human connection is tied to a named person, not a shared account
- Connections, privilege changes and schema changes are logged
- Reads of sensitive tables are captured
- Logs are shipped off the database host in near real time
- Logs are immutable or access-restricted, with monitoring for gaps
- Retention is defined and matches your obligations
- Statement text is checked for secrets and personal data
- You can link a query to an approved request or ticket
- You have tested a real question, such as “who read this table last month?”
How Cloudanix helps
Cloudanix addresses the gaps that native logging leaves: attribution, query-level detail and keeping the record in your own environment.
- Access that names the person. Database JIT gives people keyless, identity-stamped access to PostgreSQL, MySQL, MariaDB, SQL Server and CosmosDB, with no shared passwords. Each request, approval, session and revocation is logged with the real person’s identity to storage you own.
- Query-level audit. Database Access Management (DAM) is an add-on to Database JIT. It runs as a query proxy inside your own environment. For PostgreSQL, MySQL and MSSQL, it logs every query with full state and metadata to your storage, where it is queryable and searchable. It can also block risky operations through guardrails and mask sensitive values by role. DAM requires Database JIT and cannot be bought on its own.
- Evidence for audits. See query-level audit evidence for ISO 27001 and SOC 2 and query-level audit and PII masking for MySQL databases.
- Your data stays with you. The proxy and the logs live in your infrastructure. See data-sovereign DAM.
Be clear about scope: Cloudanix does not replace the audit settings in your database engine, and it does not supply retention policy. You define what must be kept and for how long.
Further reading
- PostgreSQL: Error Reporting and Logging
- pgAudit
- NIST SP 800-92: Guide to Computer Security Log Management
- OWASP Logging Cheat Sheet
Frequently asked questions
What is database audit logging?
It is the recording of who connected to a database, what they ran and what data was affected, kept in a form that supports security investigation and compliance.
What is the difference between audit logs and regular database logs?
Regular logs help engineers operate the database. Audit logs are a security record that is complete, attributable to a person, protected from tampering and retained to meet policy and regulation.
Does PostgreSQL have built-in audit logging?
It has configurable logging of connections and statements. The pgAudit extension adds more detailed session and object audit logging. Neither replaces attribution of shared accounts.
How do I know who ran a query if everyone shares one account?
You often cannot from native logs alone. Give each person their own identity when they connect, or use an access layer that stamps each session with the person behind it.
How long should database audit logs be kept?
It depends on your obligations. PCI DSS expects at least a year of audit trail history, and HIPAA expects six years for related documentation. Confirm with your auditor and set a written policy.