Many financial and insurance enterprises are required to comply with regulatory mandates such as the GDPR. These regulations often include auditing database access to track all activities, whether by specific individuals, such as DBAs, or general data access and manipulation. This blog series, consisting of three articles, will explore auditing in PostgreSQL.
The first article discusses the PGAudit extension and its capabilities for creating a custom, detailed audit trail.
The second article demonstrates how to separate the audit trail from the default PostgreSQL log into a dedicated audit log file using the pgauditlogtofile extension.
The final article showcases the set_user extension, which standardizes privilege escalation, improves logging, and enhances control.

PGAudit

PGAudit provides audit logging via PostgreSQL’s standard logging facility. Its configuration allows for customized, detailed audit logging by session and/or object. This extension is available under the PostgreSQL License.
PostgreSQL offers basic statement logging using the log_statement = all setting in postgresql.conf.
Copy to Clipboard
The corresponding log entries appear as follows:
Copy to Clipboard
This level of information is insufficient for a thorough audit. PGAudit focuses on detailed logging—specifically, who performed which action and when.
Let’s install the extension and observe the differences. PGAudit is available in the PostgreSQL Development Group (PGDG) repository and can be quickly installed using dnf.
Copy to Clipboard
Before configuring the extension, some considerations should be addressed. Audit logging can generate large volumes of log data, potentially exhausting disk space and causing PostgreSQL to fail. Therefore, it’s crucial to identify who and what should be monitored, and what can be safely ignored. A DBA is essential for auditing, but should an application user also be included?
The example below focuses on the superuser postgres. While PGAudit offers session and object auditing, this article will focus on session auditing.
The first step to enabling PGAudit is loading the module by configuring the shared_preload_libraries parameter in postgresql.conf.
Copy to Clipboard
Add the extension name pgaudit, ensuring multiple entries are comma-separated.
Copy to Clipboard

Additionally, the log_line_prefix parameter should be customized to add more valuable information.

Copy to Clipboard
Next, restart the PostgreSQL service to load the module.
Copy to Clipboard

Once the cluster restarts, the logs indicate that the PGAudit extension is successfully loaded. PGAudit is a cluster-wide extension and only needs to be installed once. The CREATE EXTENSION command activates it.

Copy to Clipboard

PGAudit settings can be configured at various levels within PostgreSQL’s object hierarchy:

  • Globally (postgresql.conf, ALTER SYSTEM ... SET)
  • Database level (ALTER DATABASE ... SET)
  • Role level (ALTER ROLE ... SET).

Note: PostgreSQL’s role system presents two limitations:

  1. Settings are not inherited through role inheritance.
  2. SET ROLE does not affect a user’s PGAudit settings.

The most important parameter is pgaudit.log, which enables audit logging based on the selected level of detail:

  • none: No audit logging
  • read: SELECT and COPY when the source is a relation or a query.
  • write: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
  • function: Function calls and DO blocks.
  • role: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
  • ddl: All DDL that is not included in the ROLE class.
  • misc: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
  • misc_set: Miscellaneous SET commands, e.g. SET ROLE.
  • all: Include all of the above.

These values can be combined as a comma-separated list, and certain subsets can be excluded by prefacing the level with a - sign.

In most scenarios, pgaudit.log is the primary setting to configure. Let’s examine an example using the postgres user.

Copy to Clipboard

Audit logging is configured to track all write and ddl SQL commands for the postgres user session. Then, a table t1 is created, and rows are inserted.

Copy to Clipboard

The log output provides essential details such as:

  • Command execution time
  • User who executed the command
  • Database in which the command was executed
  • Application name
  • Client connection (either Unix-domain socket or IP-based)
  • Session-based audit log entry
  • Command type
  • Full SQL statement

The audit log format is detailed here.

Copy to Clipboard

This simple example demonstrates the advantages of using the PGAudit extension to monitor user activities. I recommend using the ALTER ROLE command to explicitly set the pgaudit.log level for a role. Of course, many other logging options are available to obtain a comprehensive view of the activities within a cluster.

That’s all for now. Stay tuned for the second entry in this blog series.

Dirk Aumüller

Dirk Aumueller arbeitet als Associate Partner für die Proventa AG. Sein technologischer Schwerpunkt liegt bei Datenbankarchitekturen mit PostgreSQL sowie Data Management Lösungen mit Pentaho. Zusätzlich zu seinen Datenbanktransformations-Projekteinsätzen ist er regelmäßig als PostgreSQL Trainer unterwegs und betreut Studenten bei ihren Abschlussarbeiten. Seine fachlichen Erfahrungen erstrecken sich über die Branchen Telco und Financial Services.