2.2 Controlling the Audit Logs

Table of Contents Previous Next


2 Database Administration : 2.2 Controlling the Audit Logs

You can use parameters specified in the postgresql.conf file to control the information included in the audit logs.
Use the following postgresql.conf configuration parameters to control database auditing:
Enables or disables database auditing. The values xml or csv will enable database auditing. These values represent the file format in which auditing information will be captured. none will disable database auditing and is also the default. This option can only be set at server start or in the postgresql.conf file.
Specifies the file name of the audit file where the auditing information will be stored. The default file name will be audit-%Y%m%d_%H%M%S. The escape sequences, %Y, %m etc., will be replaced by the appropriate current values according to the system date and time. This option can only be set at server start or in the postgresql.conf configuration file.
Specifies the day of the week on which to rotate the audit files. Valid values are sun, mon, tue, wed, thu, fri, sat, every, and none. To disable rotation, set the value to none. To rotate the file every day, set the edb_audit_rotation_day value to every. To rotate the file on a specific day of the week, set the value to the desired day of the week. every is the default value. This option can only be set at server start or in the postgresql.conf configuration file.
Enables auditing of database connection attempts by users. To disable auditing of all connection attempts, set edb_audit_connect to none. To audit all failed connection attempts, set the value to failed. To audit all connection attempts, set the value to all. This option can only be set at server start or in the postgresql.conf configuration file.
Enables auditing of database disconnections by connected users. To enable auditing of disconnections, set the value to all. To disable, set the value to none. This option can only be set at server start or in the postgresql.conf configuration file.
This configuration parameter is used to specify auditing of different categories of SQL statements. To audit statements resulting in error, set the parameter value to error. To audit DDL statements such as CREATE TABLE, ALTER TABLE, etc., set the parameter value to ddl. Modification statements such as INSERT, UPDATE, DELETE or TRUNCATE can be audited by setting edb_audit_statement to dml. To audit ROLLBACK statements, set the parameter value to rollback. Setting the value to all will audit every statement while none disables this feature. This option can only be set at server start or in the postgresql.conf configuration file.
1.
Enable auditing by the setting the edb_audit parameter to xml or csv.
3.
4.
<message>AUDIT: database system is ready</message>
<message>AUDIT: connection authorized: user=enterprisedb
<command>AUDIT: statement: drop table HILOSEQUENCES</command>
<command>AUDIT: statement: create table HILOSEQUENCES (
<message>AUDIT: connection authorized: user=enterprisedb database=edb</message>
<command>AUDIT: statement: CREATE TABLE test (f1 INTEGER);</command>
<command>AUDIT: statement: SELECT * FROM testx;</command>
<message>ERROR: relation &quot;testx&quot; does not exist</message>
<command>AUDIT: statement: DROP TABLE test;</command>
<message>AUDIT: disconnection: session time: 0:00:10.094 user=enterprisedb
<message>AUDIT: disconnection: session time: 0:01:23.046 user=enterprisedb
<message>AUDIT: database system is shut down</message>
Then, use the edb_filter_log.errcodes parameter to specify any error codes you wish to omit from the log files:
Where error_code specifies one or more error codes that you wish to omit from the log file. Provide multiple error codes in a comma-delimited list.
For example, if edb_filter_log is enabled, and edb_filter_log.errcode is set to '23505,23502,22012', any log entries that return one of the following SQLSTATE errors:
23505 (for violating a unique constraint)
23502 (for violating a not-null constraint)
22012 (for dividing by zero)

2 Database Administration : 2.2 Controlling the Audit Logs

Table of Contents Previous Next