Audit log file v14

You can generate the audit log file in CSV or XML format. The format is determined by the edb_audit configuration parameter.

The information in the audit log is based on the logging performed by PostgreSQL, as described in "Using CSV-Format Log Output” under “Error Reporting and Logging” in the PostgreSQL core documentation.

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.
  • XML element/attribute. For the XML format, name of the XML element and its attribute (if used), referencing the value.
  • Data type. Data type of the field as given by the PostgreSQL sample table definition.
  • Description. Description of the field.

The fields that don't have any values for logging appear as consecutive commas (,,) in the CSV format.

FieldXML element/attributeData typeDescription
log_timeevent/log_timetimestamp with time zoneLog date/time of the statement.
user_nameevent/usertextDatabase user who executed the statement.
database_nameevent/databasetextDatabase in which the statement was executed.
process_idevent/process_idintegerOperating system process ID in which the statement was executed.
connection_fromevent/remote_hosttextHost and port location from where the statement was executed.
session_idevent/session_idtextSession ID in which the statement was executed.
session_line_numevent/session_line_numbigintOrder of the statement within the session.
process_statusevent/process_statustextProcessing status.
session_start_timeevent/session_start_timetimestamp with time zoneDate/time when the session was started.
virtual_transaction_idevent/virtual_transaction_idtextVirtual transaction ID of the statement.
transaction_idevent/transaction_idbigintRegular transaction ID of the statement.
error_severityerror_severitytextStatement severity. Values are AUDIT for audited statements and ERROR for any resulting error messages.
sql_state_codeevent/sql_state_codetextSQL state code returned for the statement. The sql_state_code isn't logged when its value is 00000 for XML log format.
messagemessagetextThe SQL statement that was attempted for execution.
detaildetailtextError message detail.
hinthinttextHint for error.
internal_queryinternal_querytextInternal query that led to the error, if any.
internal_query_posinternal_query_posintegerCharacter count of the error position therein.
contextcontexttextError context.
queryquerytextUser query that led to the error. For errors only.
query_posquery_posintegerCharacter count of the error position therein. For errors only.
locationlocationtextLocation of the error in the source code. The location field is populated if log_error_verbosity is set to verbose.
application_nameevent/application_nametextName of the application from which the statement was executed, for example, psql.bin.
backend_typeevent/backend_typetextThe backend_type corresponds to what pg_stat_activity.backend_type shows and is added as a column to the csv log.
leader_pidSee note at bottom of the pageintegerProcess ID of leader for active parallel workers.
query_idSee note at bottom of the pagelongIdentifier of this backend's most recent query.
command_tagevent/command_tagtextSQL command of the statement.
audit_tagevent/audit_tagtextValue specified by the audit_tag parameter in the configuration file.
typeevent/typetextDetermines the audit event_type to identify messages in the log.

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

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

logging_collector = 'on'
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 following is the audited session:

$ psql edb enterprisedb
Password for user enterprisedb:
psql.bin (14.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 was split and displays across multiple lines. A blank line was inserted between the audit log entries for visual clarity.)

2022-12-14 12:19:01.035 UTC,"enterprisedb","edb",9290,"[local]",
6399bf35.244a,1,"authentication",2022-12-14 12:19:01 UTC,4/19,0,
AUDIT,00000,"connection authorized: user=enterprisedb database=edb",
,,,,,,,,"","client backend",,0,"","edbaudit","connect"

2022-12-14 12:19:12.599 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,1,"authentication",2022-12-14 12:19:12 UTC,5/1,0,
AUDIT,00000,"connection authorized: user=enterprisedb database=edb",
,,,,,,,,"","client backend",,0,"","edbaudit","connect"

2022-12-14 12:19:21.351 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,2,"idle",2022-12-14 12:19:12 UTC,5/3,0,AUDIT,00000,
"statement: CREATE SCHEMA edb;",,,,,,,,,"psql","client backend",,
0,"CREATE SCHEMA","edbaudit","create"

2022-12-14 12:19:27.817 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,3,"idle",2022-12-14 12:19:12 UTC,5/4,0,AUDIT,00000,
"statement: CREATE SCHEMA edb;",,,,,,,,,"psql","client backend",,
0,"CREATE SCHEMA","edbaudit","create"

2022-12-14 12:19:27.820 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,4,"CREATE SCHEMA",2022-12-14 12:19:12 UTC,5/4,0,ERROR,
42P06,"schema ""edb"" already exists",,,,,,"CREATE SCHEMA edb;",,,
"psql","client backend",,0,"CREATE SCHEMA","edbaudit","error"

2022-12-14 12:20:15.407 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,5,"idle",2022-12-14 12:19:12 UTC,5/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","client backend",,0,"CREATE TABLE","edbaudit",
 "create"

2022-12-14 12:20:24.433 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,6,"idle",2022-12-14 12:19:12 UTC,5/7,0,AUDIT,00000,
"statement: INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');",
,,,,,,,,"psql","client backend",,0,"INSERT","edbaudit","insert"

2022-12-14 12:20:34.393 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,7,"idle",2022-12-14 12:19:12 UTC,5/8,0,AUDIT,00000,
"statement: UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;",
,,,,,,,,"psql","client backend",,0,"UPDATE","edbaudit","update"

2022-12-14 12:20:34.394 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,8,"UPDATE",2022-12-14 12:19:12 UTC,5/8,0,ERROR,
42P01,"relation ""department"" does not exist",,,,,,
"UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;",8,,
"psql","client backend",,0,"UPDATE","edbaudit","error"

2022-12-14 12:20:43.721 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,9,"idle",2022-12-14 12:19:12 UTC,5/9,0,AUDIT,00000,
"statement: UPDATE dept SET loc = 'BOSTON' WHERE deptno = 10;",
,,,,,,,,"psql","client backend",,0,"UPDATE","edbaudit","update"

2022-12-14 12:20:51.231 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,10,"idle",2022-12-14 12:19:12 UTC,5/10,0,AUDIT,00000,
"statement: SELECT * FROM dept;",,,,,,,,,"psql","client backend",,0,
"SELECT","edbaudit","select"

2022-12-14 12:20:53.940 UTC,"enterprisedb","edb",9293,"[local]",
6399bf40.244d,11,"idle",2022-12-14 12:19:12 UTC,,0,AUDIT,00000,
"disconnection: session time: 0:01:41.344 user=enterprisedb database=edb 
host=[local]",,,,,,,,,"psql","client backend",,0,"","edbaudit","disconnect"

XML audit log file

The following is the XML format of the audit log file. (The output was formatted for visual clarity.)

<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="1" process_status=
       "authentication"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:11:17.806 IST"
       virtual_transaction_id="4/19" type="connect" audit_tag="edbaudit"
       backend_type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>connection authorized: user=enterprisedb database=edb</
          message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="2" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:11:32.558 IST"
       virtual_transaction_id="4/21" type="create" command_tag="CREATE SCHEMA
       " audit_tag="edbaudit" application_name="psql" backend_type="client
       backend">
          <error_severity>AUDIT</error_severity>
          <message>statement: CREATE SCHEMA edb;</message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="3" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:00.199 IST"
       virtual_transaction_id="4/23" type="create" command_tag="CREATE TABLE"
       audit_tag="edbaudit" application_name="psql" backend_type="client
       backend">
          <error_severity>AUDIT</error_severity>
          <message>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" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="4" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:10.992 IST"
       virtual_transaction_id="4/24" type="insert" command_tag="INSERT" audit_
       tag="edbaudit" application_name="psql" backend_type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>statement: INSERT INTO dept VALUES
                              (10,&apos;ACCOUNTING&apos;,&apos;NEW YORK&apos;
                              );
          </message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="5" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:21.764 IST"
       virtual_transaction_id="4/25" type="update" command_tag="UPDATE" audit_
       tag="edbaudit" application_name="psql" backend_type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>statement: UPDATE department SET loc = &apos;BOSTON&apos;
                              WHERE deptno = 10;
          </message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="6" process_status="UPDATE"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:21.765 IST"
       virtual_transaction_id="4/25" type="error" sql_state_code="42P01"
       command_tag="UPDATE" audit_tag="edbaudit" application_name="psql"
       backend_type="client backend">
          <error_severity>ERROR</error_severity>
          <message>relation &quot;department&quot; does not exist</message>
          <query>UPDATE department SET loc = &apos;BOSTON&apos; WHERE deptno =
          10;</query>
          <query_pos>8</query_pos>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="7" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:34.878 IST"
       virtual_transaction_id="4/26" type="update" command_tag="UPDATE" audit_
       tag="edbaudit" application_name="psql" backend_type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>statement: UPDATE dept SET loc = &apos;BOSTON&apos; WHERE
                              deptno = 10;
          </message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="8" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:45.471 IST"
       virtual_transaction_id="4/27" type="select" command_tag="SELECT" audit_
       tag="edbaudit" application_name="psql" backend_type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>statement: SELECT * FROM dept;</message>
</event>
<event user="enterprisedb" database="edb" process_id="5941" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="9" process_status="idle"
       session_start_time="2020-05-22 16:11:17 IST" log_time="2020-05-22
       16:12:53.048 IST"
       type="disconnect" audit_tag="edbaudit" application_name="psql" backend_
       type="client backend">
          <error_severity>AUDIT</error_severity>
          <message>disconnection: session time: 0:01:35.243 user=enterprisedb
          database=edb host=[local]</message>
</event>
Note

The XML audit log file currently doesn't capture the leader PID and query ID fields. The fields will be added to the XML file in a future minor release.