When a perfect audit was too hard to use

There are tasks in the database world that engineers genuinely enjoy: query optimization, petabyte-scale sharding, or designing highly available clusters. And then there are tasks you do because you must — security auditing is one of them.

This article tells the story of how the PostgreSQL audit extension pg_proaudit was redesigned: from a technically brilliant but barely usable tool into something administrators actually want to use. The story is told by Mikhail Gribkov, senior developer at Postgres Professional.

If you’ve ever worked with security teams, this scenario will feel familiar. The chief of security department says: “We need to log everything”. An administrator enables log_statement = 'all', thirty minutes later the disk is full, the database is slow, and the logs are completely useless — buried under gigabytes of noise.

With pg_proaudit, things were different. From the very beginning, performance was solid. It didn’t slow down the database and didn’t flood disks. Technically, it worked great. But it fell into a classic engineering trap: it was too hard to use.

DBAs opened the documentation, realized how many rules they’d need to configure manually, sighed — and often chose a simpler, less precise solution.

“Yes, it’s powerful, but configuring it is a nightmare.” And this wasn’t just about time. Complex configuration is a breeding ground for human error. Forget a single rule — and suddenly there’s a blind spot attackers can exploit.

The most interesting part? Postgres Professional fixed it. They didn’t defend the original design or say “you’re using it wrong”. They rewrote the configuration model from scratch.

This is a story about listening to users — and rebuilding a product because of it.

When perfection becomes the enemy

In 2022, the development team took a deep look at pg_proaudit and noticed a strange paradox.

Technically, the extension was excellent:

• extremely granular — it could log almost any operation;
• fast — based on efficient hash tables with negligible performance impact;
• flexible — able to track actions of any user.

And yet, almost nobody was using it.

“When I joined the team, the extension was already mature, but there were barely any users. Bugs appeared once every few years, were fixed and tested — but it never really reached production usage at scale,” recalls Mikhail.

Few bugs can mean one of two things: the product is perfect — or nobody is touching it. In our case, it was the second.

Why vanilla PostgreSQL logging and pgAudit weren’t enough

Before blaming ourselves for complexity, let’s talk about why pg_proaudit existed at all. PostgreSQL already has its own logging system, and there’s also the popular open-source extension pgAudit.

The key difference is philosophical.

PostgreSQL logs describe the technical artefacts of the system: errors, warnings, checkpoints. Security teams don’t usually read raw database logs. They work with SIEM systems like ArcSight, QRadar, or Splunk — tools that aggregate signals from the entire infrastructure.

What they need is a clean, structured stream of security events, not a dump of everything that happened inside the database.

pgAudit, especially in compliance-focused setups, often produces too much data — for example, logging all read operations. That overwhelms both storage and analysts.

pg_proaudit was designed for precision: log only what matters. Monitor specific sensitive tables, roles, or operations — and ignore safe, routine activity.

pgAudit vs pg_proaudit:

• pgAudit writes through PostgreSQL’s standard logger and logs broad groups of events (READ, WRITE, DDL).
• pg_proaudit was built for targeted logging from day one and focuses on database-level security events. In version 2.0, combines fine-grained rules with event groups.

The interface that scared everyone away

The first version of pg_proaudit relied on low-level configuration functions like set_object and set_role. Users constantly had to guess which function applied in which case.

Example: you want to audit a finance team of 20 people. Version 1 required one rule per user, because role inheritance wasn’t supported.

Twenty rules is annoying — but manageable. Now imagine 100 tables that must be audited. Any schema change turns into a late-night DBA horror story.

So when users said: “Yes, it’s cool — but configuring it will kill us”, they were absolutely right.

The turning point: goodbye, hash tables

User feedback pointed to one clear requirement: generalized rules. Admins still wanted fine-grained control when needed — but for most cases, they wanted a few high-level rules that cover entire classes of operations.

Instead of hundreds of rules, they wanted this:

SELECT pg_proaudit_set_rule(
    current_database(),
    'ALL_DDL',   -- one rule for all DDL
    'TABLE',
    NULL,        -- all tables
    NULL
);

Simple. Obvious. Works.

But this created a serious architectural challenge. The original engine was built entirely on hash tables. Hashes are fast — but they don’t support wildcards, hierarchies, or partial matches. It’s either an exact key — or nothing.

“Switching from hash lookups to rule iteration sounds like saying goodbye to performance. We had to be sure that the number of rules would drop dramatically”, says Mikhail.

We chose usability over theoretical purity.

At the same time, we stopped identifying objects by internal IDs and switched to names. That alone solved a huge class of problems related to object recreation and migrations.

The refactoring results

• The number of rules was reduced from several hundred to just a dozen.
• Configuration complexity dropped 50–100×.

In practice, checking ten generalized rules is still extremely fast. Performance didn’t suffer.

And something magical happened. People started using pg_proaudit. Real users. Real bug reports. For a developer, that’s the best news possible.

Event classes and schema-level auditing

pg_proaudit 2.0 introduced a unified configuration function and a structured set of event classes. You can now work with high-level categories:

-- All DDL operations
SELECT pg_proaudit_set_rule(NULL, 'ALL_DDL', NULL, NULL, NULL, 'DDL operations');

-- All data modifications
SELECT pg_proaudit_set_rule(NULL, 'ALL_MOD', NULL, NULL, NULL, 'Data modification');

-- Role management
SELECT pg_proaudit_set_rule(NULL, 'ALL_ROLE', NULL, NULL, NULL, 'Role management');

The most important addition: schema-level rules. If the object is a schema, the rule applies to all tables inside it. No more 100 rules for 100 tables.

Role hierarchies that actually work

The big win for enterprises is proper role hierarchy support — and pg_proaudit 2.0 actually understands it.

SELECT pg_proaudit_set_rule(
    current_database(),
    'ALL_DML',
    'SCHEMA',
    'accounting',
    'accountant_group',
    'All accounting operations'
);

If user ivan belongs (directly or indirectly) to accountant_group, their actions will be logged — no matter how deep the role hierarchy goes.

Export formats for real-world security teams

At the request of users, pg_proaudit now supports CEF (Common Event Format) — widely used by SIEM platforms.

The extension can write to multiple destinations simultaneously:

• CSV — for archives or custom pipelines;
• CEF — native input for most SIEM systems;
• Syslog — including CEF over system channels.

This makes integration into existing security infrastructure straightforward.

How pg_proaudit Differs from pgAudit

Feature	pgAudit	pg_proaudit
Log storage	Writes to the main PostgreSQL server log (via the standard logger)	Separate log files or a dedicated syslog channel. Own background process
Rule configuration	Via configuration parameters (postgresql.conf)	SQL API. Rules are stored separately and can be changed on the fly
Granularity	Event groups only (READ, WRITE, DDL, etc.)	Flexible combination: event groups + specific objects + schemas + operation types
Operation classes	That’s all you get	Built-in classes plus support for fine-grained customization
Role-based logging	Limited	Full support for role hierarchies
Log rotation	Depends on PostgreSQL settings	Built-in rotation by time or file size

A real-world example: banking audit

Requirements:

• log all login attempts;
• log all DDL;
• audit changes to the accounts table;
• track all actions of accountants;
• log all disconnects.

Configuration: six rules instead of hundred.

-- 1. Authentication
SELECT pg_proaudit_set_rule('bank_core', 'AUTHENTICATE', null, null, null, 'Login attempts');

-- 2. Disconnects
SELECT pg_proaudit_set_rule('bank_core', 'DISCONNECT', null, null, null, 'Disconnections');

-- 3. Global DDL
SELECT pg_proaudit_set_rule(null, 'ALL_DDL', null, null, null, 'Global DDL audit');

-- 4. Sensitive table
SELECT pg_proaudit_set_rule('bank_core', 'ALL_MOD', 'TABLE', 'public.accounts', null, 'Accounts modification');

-- 5. Accountants
SELECT pg_proaudit_set_rule(
    'bank_core',
    'ALL_DML',
    'SCHEMA',
    'accounting',
    'accountant_group'
    'Accountants activity'
);

-- 6. Role management
SELECT pg_proaudit_set_rule(null, 'ALL_ROLE', null, null, null, 'Role management');

-- Save changes
SELECT pg_proaudit_save();

Engineering challenges worth mentioning

Logging disconnects. PostgreSQL doesn’t make this easy. A session can terminate before an extension gets a chance to log anything. Solving this required adding a low-level hook deep in the server lifecycle.

CEF quirks. CEF is an open standard — but SIEM vendors interpret it differently. Supporting real-world systems meant handling multiple “flavors” of the same standard.

Getting started

pg_proaudit is available out of the box in Postgres Pro Enterprise and Standard.

1. Add to postgresql.conf:
   

   shared_preload_libraries = 'pg_proaudit'
   

2. Restart PostgreSQL.

3. Create the extension:
   

   CREATE EXTENSION pg_proaudit;
   

4. Add your first rule and save:
   

   SELECT pg_proaudit_set_rule(current_database(), 'AUTHENTICATE', NULL, NULL, NULL);
   SELECT pg_proaudit_save();
   

Final thoughts

pg_proaudit is a reminder that even a great product can fail if it ignores usability.

We had a fast, powerful auditing engine — and almost no users. Once we stopped defending the original design and rebuilt the rule system around how people actually work, everything changed.

Today, pg_proaudit is a mature security tool that lets teams sleep better at night.

You’ll notice every DROP TABLE instantly. But if someone quietly tweaks account balances or grants themselves extra privileges — the audit log will notice too. And this time, it will be readable.

Comments

[Mads Hansen]

The redesign also shifts the main risk from “forgot one object rule” to “a broad rule silently stopped matching what we think it matches.” I’d pair generalized rules with a coverage test: create representative DDL/DML/role events in a disposable schema, verify the expected CEF/CSV records arrive, and assert known-safe events stay absent. Run that after extension upgrades, role-hierarchy changes, schema moves, and SIEM pipeline changes. A versioned export of the effective rule set plus a daily canary event makes drift visible; configuration syntax alone cannot prove end-to-end auditability. It’s also worth monitoring event volume by class and principal—both a sudden drop and a sudden surge can signal a broken boundary. Usable defaults win adoption, but executable coverage evidence is what keeps the simpler model trustworthy.