Table of Contents Previous Next


3 Database Administration : 3.5 EDB Audit Logging

Advanced Server allows database and security administrators, auditors, and operators to track and analyze database activities using the EDB Audit Logging functionality. EDB Audit Logging generates audit log files, which contains all of the relevant information. The audit logs can be configured to record information such as:
The parameters specified in the configuration files, postgresql.conf or postgresql.auto.conf, control the information included in the audit logs.
Use the following configuration parameters to control database auditing. See Section 3.1.2 to determine if a change to the configuration parameter takes effect immediately, or if the configuration needs to be reloaded, or if the database server needs to be restarted.
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.
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.
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.
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, which is the default. To audit all connection attempts, set the value to all.
This configuration parameter is used to specify auditing of different categories of SQL statements. Various combinations of the following values may be specified: none, dml, insert, update, delete, truncate, select, error, rollback, ddl, create, drop, alter, grant, revoke, and all. The default is ddl and error. See Section 3.5.2 for information regarding the setting of this parameter.
Bulk processing logs the resulting statements into both the Advanced Server log file and the EDB Audit log file. However, logging each and every statement in bulk processing is costly. This can be controlled by the edb_log_every_bulk_value configuration parameter. When set to true, each and every statement in bulk processing is logged. When set to false, a log message is recorded once per bulk processing. In addition, the duration is emitted once per bulk processing. Default is false.
Specifies whether the audit log information is to be recorded in the directory as given by the edb_audit_directory parameter or to the directory and file managed by the syslog process. Set to file to use the directory specified by edb_audit_directory, which is the default setting. Set to syslog to use the syslog process and its location as configured in the /etc/syslog.conf file. Note: In recent Linux versions, syslog has been replaced by rsyslog and the configuration file is in /etc/rsyslog.conf.
The edb_audit_statement permits inclusion of one or more, comma-separated values to control which SQL statements are to be audited. The following is the general format:
edb_audit_statement = 'value_1[, value_2]...'
all – Results in the auditing and logging of every statement including any error messages on statements.
none – Disables all auditing and logging. A value of none overrides any other value included in the list.
ddl – Results in the auditing of all data definition language (DDL) statements (CREATE, ALTER, and DROP) as well as GRANT and REVOKE data control language (DCL) statements.
dml – Results in the auditing of all data manipulation language (DML) statements (INSERT, UPDATE, DELETE, and TRUNCATE).
select – Results in the auditing of SELECT statements.
rollback – Results in the auditing of ROLLBACK statements.
error – Results in the logging of all error messages that occur. Unless the error value is included, no error messages are logged regarding any errors that occur on SQL statements related to any of the other preceding parameter values except when all is used.
Section 3.5.2.1 describes additional parameter values for selecting particular DDL or DCL statements for auditing.
Section 3.5.2.2 describes additional parameter values for selecting particular DML statements for auditing.
If an unsupported value is included in the edb_audit_statement parameter, then an error occurs when applying the setting to the database server. See the database server log file for the error such as in the following example where create materialized vw results in the error. (The correct value is create materialized view.)
This section describes values that can be included in the edb_audit_statement parameter to audit DDL and DCL statements.
If the edb_audit_statement parameter includes either ddl or all, then all DDL statements are audited. In addition, the DCL statements GRANT and REVOKE are audited as well.
If the edb_audit_statement is set to none, then no DDL nor DCL statements are audited.
Use the following syntax to specify an edb_audit_statement parameter value for DDL statements:
object_type is any of the following:
If object_type is omitted from the parameter value, then all of the specified command statements (either create, alter, or drop) are audited.
Use the following syntax to specify an edb_audit_statement parameter value for DCL statements:
The following is an example where edb_audit_connect and edb_audit_statement are set with the following non-default values:
Thus, only SQL statements invoked by the CREATE and ALTER commands are audited. Error messages are also included in the audit log.
The CREATE and ALTER statements for the adminuser role and auditdb database are audited. The error for the ALTER ROLE adminuser statement is also logged since error is included in the edb_audit_statement parameter.
Similarly, the CREATE statements for schema edb and tables department and dept are audited.
Note that the DROP TABLE department statement is not in the audit log since there is no edb_audit_statement setting that would result in the auditing of successfully processed DROP statements such as ddl, all, or drop.
The following is an example where edb_audit_connect and edb_audit_statement are set with the following non-default values:
Thus, only SQL statements invoked by the CREATE VIEW , CREATE MATERIALIZED VIEW, CREATE SEQUENCE and GRANT commands are audited.
The CREATE VIEW and CREATE MATERIALIZED VIEW statements are audited. Note that the prior CREATE TABLE emp statement is not audited since none of the values create, create table, ddl, nor all are included in the edb_audit_statement parameter.
The CREATE SEQUENCE and GRANT statements are audited since those values are included in the edb_audit_statement parameter.
This section describes the values that can be included in the edb_audit_statement parameter to audit DML statements.
If the edb_audit_statement parameter includes either dml or all, then all DML statements are audited.
If the edb_audit_statement is set to none, then no DML statements are audited.
Use the following syntax to specify an edb_audit_statement parameter value for SQL INSERT, UPDATE, DELETE, or TRUNCATE statements:
The following is an example where edb_audit_connect and edb_audit_statement are set with the following non-default values:
Thus, only SQL statements invoked by the UPDATE and DELETE commands are audited. All errors are also included in the audit log (even errors not related to the UPDATE and DELETE commands).
The UPDATE dept and DELETE FROM emp statements are audited. Note that all of the prior INSERT statements are not audited since none of the values insert, dml, nor all are included in the edb_audit_statement parameter.
The SELECT * FROM dept statement is not audited as well since neither select nor all is included in the edb_audit_statement parameter.
Since error is specified in the edb_audit_statement parameter, but not the truncate value, the error on the TRUNCATE employee statement is logged in the audit file, but not the successful TRUNCATE emp statement.
1.
Enable auditing by the setting the edb_audit parameter to xml or csv.
3.
4.
5.
To audit DDL, DCL, DML and other statements, set the parameter, edb_audit_statement according to the instructions in Section 3.5.2.
The setting of the edb_audit_statement parameter in the configuration file affects the entire database cluster.
The type of statements that are audited as controlled by the edb_audit_statement parameter can be further refined according to the database in use as well as the database role running the session:
The edb_audit_statement parameter can be set as an attribute of a specified database with the ALTER DATABASE dbname SET edb_audit_statement command. This setting overrides the edb_audit_statement parameter in the configuration file for statements executed when connected to database dbname.
The edb_audit_statement parameter can be set as an attribute of a specified role with the ALTER ROLE rolename SET edb_audit_statement command. This setting overrides the edb_audit_statement parameter in the configuration file as well as any setting assigned to the database by the previously described ALTER DATABASE command when the specified role is running the current session.
The edb_audit_statement parameter can be set as an attribute of a specified role when using a specified database with the ALTER ROLE rolename IN DATABASE dbname SET edb_audit_statement command. This setting overrides the edb_audit_statement parameter in the configuration file as well as any setting assigned to the database by the previously described ALTER DATABASE command as well as any setting assigned to the role with the ALTER ROLE command without the IN DATABASE clause as previously described.
The database cluster is established with edb_audit_statement set to all as shown in its postgresql.conf file:
Database auditdb with ddl, insert, update, and delete
Role admin with select and truncate
Role admin in database auditdb with create table, insert, and update
Case 1: Changes made in database auditdb by role enterprisedb. Only ddl, insert, update, and delete statements are audited:
The following audit log file shows entries only for the CREATE TABLE, INSERT INTO audit_tbl, and UPDATE audit_tbl statements. The SELECT * FROM audit_tbl and TRUNCATE audit_tbl statements were not audited.
Case 2: Changes made in database edb by role admin. Only select and truncate statements are audited:
Continuation of the audit log file now appears as follows. The last two entries representing the second case show only the SELECT * FROM edb_tbl and TRUNCATE edb_tbl statements. The CREATE TABLE edb_tbl and INSERT INTO edb_tbl statements were not audited.
Case 3: Changes made in database auditdb by role admin. Only create table, insert, and update statements are audited:
Continuation of the audit log file now appears as follows. The next to last two entries representing the third case show only CREATE TABLE audit_tbl_2 and INSERT INTO audit_tbl_2 statements. The SELECT * FROM audit_tbl_2 and TRUNCATE audit_tbl_2 statements were not audited.
The audit log file can be generated in either CSV or XML format depending upon the setting of the edb_audit configuration parameter. The XML format contains less information than the CSV format.
Field. Name of the field as shown in the sample table definition in the PostgreSQL documentation as previously referenced.
XML Element/Attribute. For the XML format, name of the XML element and its attribute (if used), referencing the value. Note: n/a indicates that there is no XML representation for this field.
Data Type. Data type of the field as given by the PostgreSQL sample table definition.
Description. Description of the field. For certain fields, no output is generated in the audit log as those fields are not supported by auditing. Those fields are indicated by “Not supported”.
The fields with the Description of “Not supported” appear as consecutive commas (,,) in the CSV format.
Statement severity. Values are AUDIT for audited statements and ERROR for any resulting error messages.
Value specified by the audit_tag parameter in the configuration file.
The non-default audit settings in the postgresql.conf file are as follows:
The edb_audit parameter is changed to xml when generating the XML format.
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)
Each entry in the log file except for those displaying an error message contains a command tag, which is the SQL command executed for that particular log entry.
The command tag is displayed as the command_tag attribute of the event element with values CREATE ROLE, ALTER ROLE, and DROP ROLE in the example.

3 Database Administration : 3.5 EDB Audit Logging

Table of Contents Previous Next