How Do You Prove Your Database Audit Log Is Actually Complete?

August 24, 2026 · Ahsan — builds Unlogged & Bastion · 9 min read

"Logging is enabled" and "every access gets logged" are two very different claims. Almost everyone verifies the first and assumes the second. Here's how to actually test it — with a method you can run by hand this afternoon.

The assumption that fails quietly

Every compliance framework that touches a database — PCI DSS Requirement 10, SOX ITGC, HIPAA §164.312(b), GDPR Art. 32, SAMA CSF — demands that access to sensitive data is logged. So teams turn on pgaudit or SQL Server Audit, see events flowing, screenshot the config for the auditor, and move on.

The problem: a config screenshot describes intent, not outcome. It says what you asked the database to log. It says nothing about what the database actually failed to log. And databases fail to log far more than most teams realize — not through bugs in your app, but through the ordinary mechanics of how database auditing works.

The first time most teams test their audit coverage is the morning after an incident, when someone asks "who read those rows?" and the log has no answer. The 2026 wave of insider-driven breaches followed exactly this pattern: detection arrived after mass data access, because the paths used never produced a record.

Five ways a "complete" audit log isn't

1. Access paths your configuration doesn't cover

This is the biggest class, and the least visible. A few concrete, documented examples:

• Bulk export. In PostgreSQL, COPY (SELECT …) TO STDOUT historically bypassed pgaudit's object-level audit logging entirely (pgaudit #240). The single most exfiltration-shaped operation in the database — unlogged, while your config looked correct.
• Dynamic SQL inside functions. PostgreSQL's log_statement records top-level statements only. SQL executed via EXECUTE inside a PL/pgSQL function never appears as its own entry — a read wrapped in a function is a read your session log can't see.
• Reads through views and CTEs. Depending on engine and mode, auditing the base table doesn't always catch access routed through a view, and vice versa. SQL Server has well-documented evasion paths of this shape — an ex-Microsoft SQL security PM has catalogued them publicly.
• Identity switches. SET ROLE in PostgreSQL, EXECUTE AS in SQL Server: the access happens, but under an identity your filters weren't watching — or the switch itself goes unrecorded.
• Bound parameters. pgaudit did not log statement parameters for years (#130) — so the log said a query ran, but not against which customer.

2. The audit that silently dies

Auditing is a running process, and running processes fail. pgaudit had a bug where, after one internal error, new connections were never audited again — with no signal that anything was wrong (#226). The scariest audit log isn't a missing one; it's one that looks enabled and recorded nothing for six weeks.

3. Nobody logs the off switch

A superuser disabling pgaudit is not, itself, a logged event (#78). The person your audit trail most needs to watch is the person who can turn it off without a trace.

4. Disk pressure and failure modes you chose years ago

SQL Server Audit asks you at creation time what to do if writing the audit fails: ON_FAILURE = CONTINUE means the database keeps serving queries unaudited. Most installations choose it (nobody wants auditing to take down production) and forget it. On the PostgreSQL side, a full log volume or aggressive rotation quietly drops the exact windows you'll later need.

5. Pipeline decay

Even when the database logs correctly, the journey to your SIEM is fragile: binary .sqlaudit files a collector can't parse, a pgaudit→SIEM decoder that stops matching after an upgrade, filters that drop events. The log exists; the record you'd actually query in an investigation doesn't.

Why you can't prove completeness by reading the config

Completeness is a claim about outcomes: for every access that happened, a record exists. Configs, checklists, and vendor documentation can't establish that, for the same reason a fire-alarm spec sheet can't tell you whether the alarm in your building rings: the only way to know is to make a fire and listen.

So test it empirically. The method is old, simple, and borrowed from how auditors verify financial controls: inject known transactions, then check they appear in the record.

The probe-and-reconcile method

1. Enumerate the access paths that exist in your engine — not just "SELECT," but every distinct way data can be read: parameterized reads, prepared statements, reads through views, CTEs, set-returning functions, materialized views, COPY/bulk export, dynamic SQL inside functions, security-definer functions, DO blocks, reads after SET ROLE / EXECUTE AS. For PostgreSQL and SQL Server that's roughly twenty patterns.
2. Fire each one with a unique fingerprint embedded in the statement, against a non-production copy of the database:

   -- probe 07: read through a view
   SELECT /* ULG-run42-probe07 */ count(*) FROM v_customer_summary;
   
   -- probe 12: bulk export
   COPY (SELECT /* ULG-run42-probe12 */ * FROM customers LIMIT 5) TO STDOUT;

3. Export the audit log covering the run window — whatever your real pipeline produces (pgaudit stderr/csvlog, SQL Server Audit files, your SIEM export). Test the pipeline you actually rely on, not the prettiest one.
4. Reconcile. For each probe, search the export for its fingerprint:

   grep -c "ULG-run42-probe07" audit-export.log   # 1+ = logged
   grep -c "ULG-run42-probe12" audit-export.log   # 0  = blind spot

   Executed-but-absent is a blind spot — an access path an attacker or insider could use without leaving a trace.
5. Run two calibration controls, or the whole exercise can lie to you:
   • a positive control — a plain query that must appear. If it doesn't, your export or window is broken, and every "blind spot" you found is suspect;
   • a negative control — a fingerprint you never ran. If it appears, your matching is picking up noise.

The output is a plain set difference anyone can hand-verify:

That table is worth more to an auditor than any configuration screenshot, because it's evidence of outcome: these accesses happened, and here is exactly which ones your log caught. It also can't be quietly wrong — a skeptical reviewer re-runs the greps and gets the same answer.

What this doesn't prove — scope honestly

Probe-and-reconcile proves completeness for the paths you probed, on the day you probed them. It does not cover paths you didn't think to test, and it says nothing about tomorrow — the audit can die next Tuesday (failure modes 2–5 above). Two honest extensions:

• Re-run on a schedule. Completeness decays: engine upgrades, config drift, new extensions, a well-meaning DBA. A quarterly re-run catches the drift; before an audit window is the minimum.
• Monitor continuity, not just coverage. For the always-on version of this problem — proving the monitor itself never stopped — you need sequence numbers and a hash-chained record where a gap is itself a detectable, alertable event. That's a database activity monitoring concern, and it's the direction we've taken with Bastion.

Do it by hand, or in ten minutes

Everything above is deliberately DIY-able: a checklist of probes, a comment convention, and grep. If you'd rather not maintain the probe set and the reconciliation yourself, this method is exactly what we built Unlogged to automate: it fires ~20 fingerprinted access patterns at a disposable copy of a PostgreSQL or SQL Server database, reconciles them against your exported log, runs both calibration controls, and produces a sealed, tamper-evident LOGGED / NO-TRACE report you can hand to an auditor. It runs entirely inside your network — nothing leaves.

FAQ

Is enabling pgaudit enough to be "fully audited"?

No. pgaudit is the right tool, but its coverage depends on mode (session vs object), role configuration, and version — and it has had documented gaps (bulk COPY paths, unlogged parameters, error states that stop auditing new connections). Enable it, then verify it with the method above.

Does log_statement = 'all' capture everything?

It captures every top-level statement — at enormous log volume — and still misses SQL executed dynamically inside functions, plus anything lost to rotation or disk pressure. It's a debugging setting pressed into audit duty, not a completeness guarantee.

How often should completeness be re-verified?

At minimum before each audit window and after any engine upgrade, extension change, or audit-config change. Quarterly is a sane default; the test takes minutes once your probe set exists.