How to save pgaudit output to separate file.

Products

• VMware Tanzu Greenplum

Issue/Introduction

When using the pgaudit extension for VMWare Postgres, by default it will save the output to the postgres log file. 

 EST [30673] LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,,,insert into test3 values (3);,<not logged>
 EST [30656] LOG:  received fast shutdown request

 

Environment

Product Version: 15.1

Resolution

If it's required to save the audit message to separate files, install package "pgauditlogtofile".
1. Download the package installation file from : https://github.com/fmbiete/pgauditlogtofile
2. Download the vmware postgres devel package from vmware product page.
3. Make sure perl and openssl-devel package installed first.
4. install the vmware postgres devel package (yum install )
5. install the pgauditlogtofile package (yum install)
6. update the postgresql.conf file (set  shared_preload_libraries = 'pgaudit,pgauditlogtofile')
7. restart the postgres database.
8. psql , then create the extension: "CREATE EXTENSION pgauditlogtofile;"
9. pg_ctl to reload the parameter.
10. check if the audit message has been saved to the $PGDATA/log/ directory. (default setting).

Please note, the vmware postgres devel package only works well after 14.2, 13.6, 12.10, 11.15, and 10.20 release.

For more pgauditlogtofile extension setting, refer to below part:

pgaudit.log_directory
Name of the directory where the audit file will be created.
Scope: System
Default: 'log'
Empty or NULL will disable the extension and the audit logging will be done to PostgreSQL server logger.

pgaudit.log_filename
Name of the file where the audit will be written. Writing to an existing file will append the new entries.
This variable can contain time patterns up to minute to allow automatic rotation.
Scope: System
Default: 'audit-%Y%m%d_%H%M.log'
Empty or NULL will disable the extension and the audit logging will be done to PostgreSQL server logger.

pgaudit.log_rotation_age
Number of minutes after which the audit file will be rotated.
Scope: System
Default: 1440 minutes (1 day)
0 will disable the rotation

pgaudit.log_connections
Intercepts server log messages emited when log_connections is on
Scope: System
Default: off
Requires: log_connections = on

pgaudit.log_disconnections
Intercepts server log messages emited when log_disconnections is on
Scope: System
Default: off
Requires: log_disconnections = on