Archiving audit logs v16

Suggest edits

EDB audit log archiving enables database administrators to control the space consumed by the audit log directory and helps manage the audit log files. The Audit Archiver is responsible for the compression, execution, and expiration of log files with edb_audit_archiver_filename_prefix present in the audit directory. The edb_audit_archiver_timeout parameter triggers the compression or expiration of audit log files at an appropriate time. For more information about audit archiving configuration parameters, see Audit logging configuration parameters.

The audit archiver helps to:

  • Prepare a list of log files from the audit log directory for compression.
  • Determine the log files for compression based on the parameters edb_audit_archiver_compress_time_limit and edb_audit_archiver_compress_size_limit.
  • Perform compression of the log files by specifying the compression command based on the edb_audit_archiver_compress_command parameter.
  • Determine the log files to remove based on the edb_audit_archiver_expire_time_limit and edb_audit_archiver_expire_size_limit parameter.
  • Execute the expiration command specified in the edb_audit_archiver_expire_command parameter to remove the log files.

Rotating out older audit log files

To rotate out the older audit log files, you can set the log file rotation day when the new file is created. To do so, set the parameter edb_audit_rotation_day to the desired value. The audit log records are overwritten on a first-in, first-out basis if space isn't available for more audit log records.

Enabling compression and expiration of log files

To configure EDB Postgres Advanced Server to enable compression and expiration of the log files:

  1. Enable audit log archiving by setting the edb_audit_archiver parameter to on in the postgresql.conf file.
  2. To enable compression of log files, set the parameter edb_audit_archiver_compress_size_limit and edb_audit_archiver_compress_time_limit to the values you want.
  3. To enable expiration of log files, set the parameter edb_audit_archiver_expire_size_limit and edb_audit_archiver_expire_time_limit to the values you want.
  4. To enable both compression and expiration, set the parameters edb_audit_archiver_compress_size_limit, edb_audit_archiver_compress_time_limit, edb_audit_archiver_expire_size_limit, edb_audit_archiver_expire_time_limit, and edb_audit_archiver_expire_command to the values you want.

The following is an example of the configuration parameter settings in the postgresql.conf file.

Setting the edb_audit_archiver parameter in the configuration file affects the entire database cluster. The database cluster is established with edb_audit_archiver set to on, as shown in the postgresql.conf file. The audit log file is generated in CSV format based on the setting of the edb_audit configuration parameter.

logging_collector = 'on'
edb_audit = 'csv'
edb_audit_archiver = 'on'  # (Change requires restart)

Examples

In this example, edb_audit_archiver, edb_audit_archiver_compress_size_limit, and edb_audit_archiver_compress_time_limit are set to enable compression of the audit log files greater than 10MB.

edb_audit_archiver = 'on'
edb_audit_archiver_compress_size_limit = 10
edb_audit_archiver_compress_time_limit = 0

Before compression, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 2097276 Jun 4 11:42 audit-20200811_114237.csv   <== Shows non-compressed audit log files 
-rw-------. 1 enterprisedb edb 2097278 Jun 4 11:42 audit-20200811_114243.csv
-rw-------. 1 enterprisedb edb 2097289 Jun 4 11:42 audit-20200811_114245.csv
-rw-------. 1 enterprisedb edb 2097330 Jun 4 11:42 audit-20200811_114246.csv
-rw-------. 1 enterprisedb edb 2097343 Jun 4 11:42 audit-20200811_114248.csv
-rw-------. 1 enterprisedb edb 2097338 Jun 4 11:42 audit-20200811_114249.csv

The edb_audit_archiver parameter checks the log files, excluding the latest file. It retains at least 10MB of log files in the audit log directory and compresses the remaining log files. The .gz specifies the name of an already compressed log file. After compression, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 64119 Jun 4 11:42 audit-20200811_114237.csv.gz   <== Shows compressed audit log files 
-rw-------. 1 enterprisedb edb 64323 Jun 4 11:42 audit-20200811_114243.csv.gz
-rw-------. 1 enterprisedb edb 64091 Jun 4 11:42 audit-20200811_114245.csv.gz
-rw-------. 1 enterprisedb edb 64152 Jun 4 11:42 audit-20200811_114246.csv.gz      
-rw-------. 1 enterprisedb edb 2097343 Jun 4 11:42 audit-20200811_114248.csv    <== Shows non-compressed audit log files 
-rw-------. 1 enterprisedb edb 2097338 Jun 4 11:42 audit-20200811_114249.csv

In this example, edb_audit_archiver, edb_audit_archiver_expire_size_limit, and edb_audit_archiver_expire_time_limit are set to enable expiration of the audit log files older than one hour.

edb_audit_archiver = 'on'
edb_audit_archiver_expire_size_limit = 0
edb_audit_archiver_expire_time_limit = 3600

Before compression, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 2097367 Jun 4 13:23 audit-20200811_132352.csv   <== Shows non-compressed audit log files scheduled for expiration/removal
-rw-------. 1 enterprisedb edb 2097395 Jun 4 13:24 audit-20200811_132353.csv
-rw-------. 1 enterprisedb edb 2097328 Jun 4 14:35 audit-20200811_143438.csv
-rw-------. 1 enterprisedb edb 2097276 Jun 4 14:35 audit-20200811_143502.csv
-rw-------. 1 enterprisedb edb 2097211 Jun 4 14:35 audit-20200811_143503.csv
-rw-------. 1 enterprisedb edb 2097152 Jun 4 14:35 audit-20200811_143504.csv

The edb_audit_archiver parameter checks the log files, excluding the latest file. It removes the log files older than one hour. After expiration, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 2097328 Jun 4 14:35 audit-20200811_143438.csv
-rw-------. 1 enterprisedb edb 2097276 Jun 4 14:35 audit-20200811_143502.csv
-rw-------. 1 enterprisedb edb 2097211 Jun 4 14:35 audit-20200811_143503.csv
-rw-------. 1 enterprisedb edb 2097152 Jun 4 14:35 audit-20200811_143504.csv

In this example, edb_audit_archiver, edb_audit_archiver_compress_size_limit, edb_audit_archiver_compress_time_limit, edb_audit_archiver_expire_size_limit, edb_audit_archiver_expire_time_limit, and edb_audit_archiver_expire_command are set to enable both compression and expiration of the audit log files.

edb_audit_archiver = 'on'
edb_audit_archiver_compress_size_limit = 4
edb_audit_archiver_compress_time_limit = 0
edb_audit_archiver_expire_size_limit = 5
edb_audit_archiver_expire_time_limit = 0
edb_audit_archiver_expire_command = 'cp %p /home/edb_audit/backup-log/'

Before compression, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 2097522 Jun 4 13:02 audit-20200811_125816.csv   <== Shows non-compressed audit log files ready to be removed/expired
-rw-------. 1 enterprisedb edb 2097510 Jun 4 13:02 audit-20200811_130215.csv
-rw-------. 1 enterprisedb edb 2097336 Jun 4 13:10 audit-20200811_130947.csv
-rw-------. 1 enterprisedb edb 2097271 Jun 4 13:10 audit-20200811_131034.csv
-rw-------. 1 enterprisedb edb 2097246 Jun 4 13:10 audit-20200811_131035.csv
-rw-------. 1 enterprisedb edb 2097289 Jun 4 13:10 audit-20200811_131036.csv

The edb_audit_archiver parameter checks the log files, excluding the latest file. It retains at least 4MB of log files in the audit log directory and compresses the remaining log files. While checking the log files for expiration, edb_audit_archiver retains at least 5MB of log files in the audit log directory and removes the remaining log files. After compression and expiration, the audit log file appears as follows:

-rw-------. 1 enterprisedb edb 63854 Jun 4 13:10 audit-20200811_131034.csv.gz
-rw-------. 1 enterprisedb edb 63513 Jun 4 13:10 audit-20200811_131035.csv.gz
-rw-------. 1 enterprisedb edb 63738 Jun 4 13:10 audit-20200811_131036.csv.gz

The expiration command is specified as edb_audit_archiver_expire_command = 'cp %p /home/edb_audit/backup-log/' in the postgresql.conf file. The edb_audit_archiver executes this command and copies the log files to a backup log directory before deleting them from the audit log directory. After compression and expiration, the backup log directory appears as follows:

-rw-------. 1 enterprisedb edb 2097522 Jun 4 13:02 audit-20200811_125816.csv.gz
-rw-------. 1 enterprisedb edb 2097510 Jun 4 13:02 audit-20200811_130215.csv.gz
-rw-------. 1 enterprisedb edb 2097336 Jun 4 13:10 audit-20200811_130947.csv.gz
← Prev

Redacting passwords in audit logs

↑ Up

Using EDB audit logging

Next →

Auditing objects