Audit Log File v11

Suggest edits

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.

The information in the audit log is based on the logging performed by PostgreSQL as described in the section “Using CSV-Format Log Output” within Section “Error Reporting and Logging” in the PostgreSQL core documentation, available at:

https://www.postgresql.org/docs/11/static/runtime-config-logging.html

The following table lists the fields in the order they appear in the CSV audit log format. The table contains the following information:

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.

Field	XML Element/Attribute	Data Type	Description
`log_time`	`event/time`	`timestamp with time zone`	Log date/time of the statement.
`user_name`	`event/user`	`text`	Database user who executed the statement.
`database_name`	`event/database`	`text`	Database in which the statement was executed.
`process_id`	`event/process_id`	`integer`	Operating system process ID in which the statement was executed.
`connection_from`	`event/remote_host`	`text`	Host and port location from where the statement was executed.
`session_id`	`event/session_id`	`text`	Session ID in which the statement was executed.
`session_line_num`	`n/a`	`bigint`	Order of the statement within the session.
`process_status`	`n/a`	`text`	Processing status.
`session_start_time`	`n/a`	`timestamp with time zone`	Date/time when the session was started.
`virtual_transaction_id`	`n/a`	`text`	Virtual transaction ID of the statement.
`transaction_id`	`event/transaction_id`	`bigint`	Regular transaction ID of the statement.
`error_severity`	`message`	`text`	Statement severity. Values are `AUDIT` for audited statements and `ERROR` for any resulting error messages.
`sql_state_code`	`n/a`	`text`	SQL state code returned for the statement.
`message`	`message`	`text`	The SQL statement that was attempted for execution.
`detail`	`n/a`	`text`	Error message detail. (Not supported)
`hint`	`n/a`	`text`	Hint (Not supported)
`internal_query`	`n/a`	`text`	Internal query that led to the error, if any. (Not supported)
`internal_query_pos`	`n/a`	`integer`	Character count of the error position therein. (Not supported)
`context`	`n/a`	`text`	Error context. (Not supported)
`query`	`n/a`	`text`	User query that led to the error. (For errors only)
`query_pos`	`n/a`	`integer`	Character count of the error position therein. (For errors only)
`location`	`n/a`	`text`	Location of the error in the PostgreSQL source code. (Not supported)
`application_name`	`n/a`	`text`	Name of the application from which the statement was executed. (for example, `psql.bin`).
`command_tag`	`event/command_tag`	`text`	SQL command of the statement.
`audit_tag`	`event/audit_tag`	`text`	Value specified by the `audit_tag` parameter in the configuration file.

The following examples are generated in the CSV and XML formats.

The non-default audit settings in the postgresql.conf file are as follows:

edb_audit = 'csv'
edb_audit_connect = 'all'
edb_audit_disconnect = 'all'
edb_audit_statement = 'ddl, dml, select, error'
edb_audit_tag = 'edbaudit'

The edb_audit parameter is changed to xml when generating the XML format.

The audited session is the following:

$ psql edb enterprisedb
Password for user enterprisedb:
psql.bin (11.0.0)
Type "help" for help.

edb=# CREATE SCHEMA edb;
CREATE SCHEMA
edb=# SET search_path TO edb;
SET
edb=# CREATE TABLE dept (
edb(#     deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
edb(#     dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
edb(#     loc             VARCHAR2(13)
edb(# );
CREATE TABLE
edb=# INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT 0 1
edb=# UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;
ERROR:  relation "department" does not exist
LINE 1: UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;
            ^
edb=# UPDATE dept SET loc = 'BOSTON' WHERE deptno = 10;
UPDATE 1
edb=# SELECT * FROM dept;
 deptno |   dname    |  loc
--------+------------+--------
     10 | ACCOUNTING | BOSTON
(1 row)

edb=# \q

CSV Audit Log File

The following is the CSV format of the audit log file.

Each audit log entry has been split and displayed across multiple lines, and a blank line has been inserted between the audit log entries for more clarity in the appearance of the results.

2017-07-17 13:28:44.235 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,1,"authentication",2017-07-17 13:28:44 EDT,6/2,0,AUDIT,00000,
"connection authorized: user=enterprisedb database=edb",,,,,,,,,"","","edbaudit"

2017-07-17 13:28:44.235 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,2,"idle",2017-07-17 13:28:44 EDT,6/4,0,AUDIT,00000,
"statement: CREATE SCHEMA edb;",,,,,,,,,"psql.bin","CREATE SCHEMA","edbaudit"

2017-07-17 13:28:44.235 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,3,"idle",2017-07-17 13:28:44 EDT,6/6,0,AUDIT,00000,
"statement: CREATE TABLE dept (
    deptno        NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname         VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc           VARCHAR2(13)
);",,,,,,,,,"psql.bin","CREATE TABLE","edbaudit"

2017-07-17 13:28:44.235 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,4,"idle",2017-07-17 13:28:44 EDT,6/7,0,AUDIT,00000,
"statement: INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');",,,,,,,,,
"psql.bin","INSERT","edbaudit"

2017-07-17 13:28:44.235 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,5,"idle",2017-07-17 13:28:44 EDT,6/8,0,AUDIT,00000,
"statement: UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;",,,,,,,,,
"psql.bin","UPDATE","edbaudit"

2017-07-17 13:29:59.833 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,6,"UPDATE",2017-07-17 13:28:44 EDT,6/8,0,ERROR,42P01,
"relation ""department"" does not exist",,,,,,
"UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;",8,,"psql.bin","","edbaudit"

2017-07-17 13:29:59.833 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,7,"idle",2017-07-17 13:28:44 EDT,6/9,0,AUDIT,00000,
"statement: UPDATE dept SET loc = 'BOSTON' WHERE deptno = 10;",,,,,,,,,
"psql.bin","UPDATE","edbaudit"

2017-07-17 13:29:59.833 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,8,"idle",2017-07-17 13:28:44 EDT,6/10,0,AUDIT,00000,
"statement: SELECT * FROM dept;",,,,,,,,,"psql.bin","SELECT","edbaudit"

2017-07-17 13:29:59.833 EDT,"enterprisedb","edb",4068,"[local]",
596cf3cc.fe4,9,"idle",2017-07-17 13:28:44 EDT,,0,AUDIT,00000,
"disconnection: session time: 0:02:01.511 user=enterprisedb database=edb
host=[local]",,,,,,,,,"psql.bin","SELECT","edbaudit"

2017-07-17 13:30:53.617 EDT,,,3987,,596cf3b3.f93,1,,2017-07-17 13:28:19 EDT,,0,LOG,00000,
"database system is shut down",,,,,,,,,"","","edbaudit"

XML Audit Log File

The following is the XML format of the audit log file. The output has been formatted for more clarity in the appearance in the example.

<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:36:55 EDT"
       transaction_id="0" type="connect" audit_tag="edbaudit">
  <message>AUDIT: connection authorized: user=enterprisedb database=edb</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:02 EDT"
       transaction_id="0" type="create" command_tag="CREATE SCHEMA" audit_tag="edbaudit">
  <message>AUDIT: statement: CREATE SCHEMA edb;</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:19 EDT"
       transaction_id="0" type="create" command_tag="CREATE TABLE" audit_tag="edbaudit">
  <message>AUDIT: statement: CREATE TABLE dept (
              deptno         NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
              dname          VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
              loc            VARCHAR2(13));
  </message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:29 EDT"
       transaction_id="0" type="insert" command_tag="INSERT" audit_tag="edbaudit">
  <message>AUDIT: statement: INSERT INTO dept VALUES
                             (10,&apos;ACCOUNTING&apos;,&apos;NEW YORK&apos;);
  </message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:40 EDT"
       transaction_id="0" type="update" command_tag="UPDATE" audit_tag="edbaudit">
  <message>AUDIT: statement: UPDATE department SET
                             loc = &apos;BOSTON&apos; WHERE deptno = 10;
  </message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:40 EDT"
       transaction_id="0" type="error" audit_tag="edbaudit">
  <message>ERROR: relation &quot;department&quot; does not exist at character 8
  </message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:51 EDT"
       transaction_id="0" type="update" command_tag="UPDATE" audit_tag="edbaudit">
  <message>AUDIT: statement: UPDATE dept SET loc = &apos;BOSTON&apos; WHERE deptno = 10;
  </message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:37:59 EDT"
       transaction_id="0" type="select" command_tag="SELECT" audit_tag="edbaudit">
  <message>AUDIT: statement: SELECT * FROM dept;</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="596cf5b7.12a8" process_id="4776" time="2017-07-17 13:38:01 EDT"
       transaction_id="0" type="disconnect" command_tag="SELECT" audit_tag="edbaudit">
  <message>AUDIT: disconnection: session time: 0:01:05.814
                  user=enterprisedb database=edb host=[local]
  </message>
</event>
<event process_id="4696" time="2017-07-17 13:38:08 EDT"
       transaction_id="0" type="shutdown" audit_tag="edbaudit">
  <message>LOG: database system is shut down</message>
</event>

← Prev

Enabling Audit Logging

Using Error Codes to Filter Audit Logs

Audit Log File v11

← Prev

Next →