Building on the first blog entry in the series, Auditing in PostgreSQL, it is time to extend the audit features used. This blog entry introduces the extension pgauditlogtofile, which separates the audit log trail from the default PostgreSQL log into a dedicated file. It is common practice in many companies to separate the audit log from the default log, often due to different departments handling monitoring and security, depending on whether the focus is on performance management or on security and compliance.

Note: PostgreSQL’s role system presents two limitations:

  1. PostgreSQL v17 was released shortly after the first blog entry in the series. The PostgreSQL instance has been upgraded to v17.

pgauditlogtofile

pgauditlogtofile is an add-on to PGAudit that redirects audit log entries to a separate file, rather than the default PostgreSQL server log. It supports log rotation based on time intervals, which helps manage large audit log files.
Copy to Clipboard
Next, create a dedicated directory to store the audit logs. Ensure the postgres user has write permissions for this directory.
Copy to Clipboard

The first step to enable pgauditlogtofile is to configure the shared_preload_libraries and other extension-specific parameters in postgresql.conf.

Copy to Clipboard

Add the pgauditlogtofile extension to the existing list, ensuring multiple entries are comma-separated. Additionally, configure the specific pgauditlogtofile parameters.

Copy to Clipboard
Now, restart the PostgreSQL service to load the new module.
Copy to Clipboard

Once the cluster restarts, the logs confirm that the pgauditlogtofile extension has been successfully loaded. pgauditlogtofile is a cluster-wide extension, so it only needs to be installed once. Use the CREATE EXTENSION command to activate it.

Copy to Clipboard

Let’s revisit the commands from the first blog entry:

  1. Enable audit logging for the session.
  2. Create a table t1.
  3. Insert some rows into table t1.
Copy to Clipboard
Copy to Clipboard

The table creation and the insertion of sample rows are now logged in the separate audit logfile. Refer to the record format in the GitHub repository for more details.

Copy to Clipboard

In conclusion, the pgauditlogtofile extension offers a significant improvement for organizations looking to separate audit logs from general PostgreSQL logs, ensuring better log management and increased security. By directing audit logs to a dedicated file, it facilitates compliance and monitoring without cluttering the main logs with security events. The easy installation and configuration, along with support for log rotation, make it a valuable tool for maintaining a clean and manageable audit trail. As demonstrated, configuring and using the extension alongside PGAudit provides a comprehensive solution for audit logging in PostgreSQL, particularly for environments where security and compliance are top priorities.

Stay tuned for the third 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.