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:
- PostgreSQL v17 was released shortly after the first blog entry in the series. The PostgreSQL instance has been upgraded to v17.
pgauditlogtofile
The first step to enable pgauditlogtofile is to configure the shared_preload_libraries
and other extension-specific parameters in postgresql.conf
.
Add the pgauditlogtofile
extension to the existing list, ensuring multiple entries are comma-separated. Additionally, configure the specific pgauditlogtofile parameters.
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.
Let’s revisit the commands from the first blog entry:
- Enable audit logging for the session.
- Create a table
t1
. - Insert some rows into table
t1
.
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.
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.