The following is a description of the configuration parameters that control database auditing. These parameters are found in the postgresql.conf file.
edb_audit
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.
edb_audit_directory
Specifies the directory where the log files will be created. The path of the directory can be relative or absolute to the data folder. This option can only be set at server start or in the postgresql.conf configuration file.
edb_audit_filename
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.
edb_audit_rotation_day
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. none is the default value. This option can only be set at server start or in the postgresql.conf configuration file.
edb_audit_rotation_size
Specifies a file size threshold in megabytes when file rotation will be forced to occur. The default value is 0 MB. If the parameter is commented out or set to 0, rotation of the file on a size basis will not occur. This option can only be set at server start or in the postgresql.conf configuration file.
edb_audit_rotation_seconds
Specifies the rotation time in seconds when a new log file should be created. To disable this feature, set this parameter to 0. This option can only be set at server start or in the postgresql.conf configuration file.
edb_audit_connect
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.
edb_audit_disconnect
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.
edb_audit_statement
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 etc., can be audited by setting edb_audit_statement to dml. 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.
Suppose we need to audit all connections, disconnections, DDL statements and statements resulting in an error. The audit file is to be rotated every Sunday.
● Enable auditing by the setting the edb_audit parameter to xml or csv.
● Set the file rotation day when the new file will be created by setting the parameter edb_audit_rotation_day to sun.
● To audit all connections, set the parameter, edb_audit_connect, to all.
● To audit all disconnections, set the parameter, edb_audit_disconnect, to all.
● To audit all DDL statements and error statements, set the parameter, edb_audit_statement, to dll, error.
Each audit line is preceded with a fixed prefix that cannot be changed. The prefix consists of user name, database name, remote host and port, process id, session id, transaction id, timestamp, and event type.
The following is the CVS and XML output when auditing is enabled:
CSV Audit Logfile
,,,1452,,,2008-03-13 12:40:02 ,startup,"AUDIT: database system is ready" enterprisedb,mgmtsvr,127.0.0.1(1266),1620,47d9595b.654,0,2008-03-13 12:42:03 ,connect,"AUDIT: connection authorized: user=enterprisedb database=mgmtsvr" enterprisedb,mgmtsvr,127.0.0.1(1266),1620,47d9595b.654,1588,2008-03-13 12:42:08 ,ddl,"AUDIT: statement: drop table HILOSEQUENCES " enterprisedb,mgmtsvr,127.0.0.1(1266),1620,47d9595b.654,1590,2008-03-13 12:42:09 ,ddl,"AUDIT: statement: create table HILOSEQUENCES ( SEQUENCENAME varchar(50) not null, HIGHVALUES integer not null, constraint hilo_pk primary key (SEQUENCENAME) ) " enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,0,2008-03-13 12:42:53 ,connect,"AUDIT: connection authorized: user=enterprisedb database=edb" enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,1618,2008-03-13 12:43:02 ,ddl,"AUDIT: statement: CREATE TABLE test (f1 INTEGER);" enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,1620,2008-03-13 12:43:02 ,sql statement,"AUDIT: statement: SELECT * FROM testx;" enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,1620,2008-03-13 12:43:02 ,error,"ERROR: relation "testx" does not exist" enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,1621,2008-03-13 12:43:04 ,ddl,"AUDIT: statement: DROP TABLE test;" enterprisedb,edb,127.0.0.1(1269),904,47d9598d.388,0,2008-03-13 12:43:20 ,disconnect,"AUDIT: disconnection: session time: 0:00:26.953 user=enterprisedb database=edb host=127.0.0.1 port=1269" enterprisedb,mgmtsvr,127.0.0.1(1266),1620,47d9595b.654,0,2008-03-13 12:43:29 ,disconnect,"AUDIT: disconnection: session time: 0:01:26.594 user=enterprisedb database=mgmtsvr host=127.0.0.1 port=1266" ,,,3148,,,2008-03-13 12:43:35 ,shutdown,"AUDIT: database system is shut down"
XML Audit Logfile
<event process_id="2516" time="2008-03-13 13:22:42 " type="startup"> <message>AUDIT: database system is ready</message> </event> <event user="enterprisedb" database="mgmtsvr" remote_host_and_port="127.0.0.1(1281)" process_id="352" session_id="47d96338.160" transaction="0" time="2008-03-13 13:24:08 " type="connect"> <message>AUDIT: connection authorized: user=enterprisedb database=mgmtsvr</message> </event> <event user="enterprisedb" database="mgmtsvr" remote_host_and_port="127.0.0.1(1281)" process_id="352" session_id="47d96338.160" transaction="1635" time="2008-03-13 13:24:10 " type="ddl"> <command>AUDIT: statement: drop table HILOSEQUENCES</command> </event> <event user="enterprisedb" database="mgmtsvr" remote_host_and_port="127.0.0.1(1281)" process_id="352" session_id="47d96338.160" transaction="1637" time="2008-03-13 13:24:10 " type="ddl"> <command>AUDIT: statement: create table HILOSEQUENCES ( SEQUENCENAME varchar(50) not null, HIGHVALUES integer not null, constraint hilo_pk primary key (SEQUENCENAME) )</command> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="0" time="2008-03-13 13:25:12 " type="connect"> <message>AUDIT: connection authorized: user=enterprisedb database=edb</message> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="1667" time="2008-03-13 13:25:17 " type="ddl"> <command>AUDIT: statement: CREATE TABLE test (f1 INTEGER);</command> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="1669" time="2008-03-13 13:25:17 " type="sql statement"> <command>AUDIT: statement: SELECT * FROM testx;</command> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="1669" time="2008-03-13 13:25:17 " type="error"> <message>ERROR: relation "testx" does not exist</message> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="1670" time="2008-03-13 13:25:18 " type="ddl"> <command>AUDIT: statement: DROP TABLE test;</command> </event> <event user="enterprisedb" database="edb" remote_host_and_port="127.0.0.1(1283)" process_id="3776" session_id="47d96378.ec0" transaction="0" time="2008-03-13 13:25:22 " type="disconnect"> <message>AUDIT: disconnection: session time: 0:00:10.094 user=enterprisedb database=edb host=127.0.0.1 port=1283</message> </event> <event user="enterprisedb" database="mgmtsvr" remote_host_and_port="127.0.0.1(1281)" process_id="352" session_id="47d96338.160" transaction="0" time="2008-03-13 13:25:31 " type="disconnect"> <message>AUDIT: disconnection: session time: 0:01:23.046 user=enterprisedb database=mgmtsvr host=127.0.0.1 port=1281</message> </event> <event process_id="2768" time="2008-03-13 13:25:36 " type="shutdown"> <message>AUDIT: database system is shut down</message> </event>
