Implementing User-Level Audit Logging in PostgreSQL

Problem

The client needed to implement audit logging for their PostgreSQL 15 databases. Specifically, they wanted to track user actions such as:

• Configuration changes
• Creation, deletion, or modification of objects (documents, users, settings)
• Attempts to access forbidden resources
• Privilege escalation attempts

Additionally, they requested that auditing be limited only to administrative users, not applied globally across all users.

Process

Step 1 – Initial Analysis

The expert reviewed the audit requirements and confirmed that PostgreSQL does not natively provide fine-grained auditing out of the box but can achieve this using the open-source pgaudit extension.

Step 2 – Enabling Audit Capabilities

To enable audit logging, the expert recommended installing pgaudit, a free and widely used extension. Then, the PostgreSQL configuration needed to be modified to preload this extension:

shared_preload_libraries = 'pgaudit'

Restart PostgreSQL to apply changes.

Step 3 – Activating the Extension

Once PostgreSQL was restarted, the extension was installed using the following command in psql:

CREATE EXTENSION pgaudit;

Step 4 – Configuring User-Level Auditing

To limit auditing to specific users, the expert configured logging at the user level:

Enable full auditing for the admin account (postgres in this case):

ALTER USER postgres SET pgaudit.log = 'ALL';

Disable auditing for all other users:

ALTER USER <username> SET pgaudit.log = 'NONE';

This ensured that only actions by administrative users were audited, as required.

Solution

The pgaudit extension was installed and configured to provide detailed audit logs for administrative activity. This setup allowed logging of:

• DDL operations (e.g., CREATE, DROP)
• Configuration changes
• Unauthorized access attempts
• Any privilege escalations performed by designated admin accounts

Non-admin users were excluded from the audit to reduce noise and preserve performance.

Conclusion

With minimal configuration changes and the use of a free extension, the client successfully implemented a focused audit logging solution. This setup met all compliance needs while maintaining efficiency by targeting only administrative users.

If future needs expand to include more granular audit control or enterprise features, transitioning to EDB’s commercial edbaudit solution was recommended.