Audit log files v17

Suggest edits

You can generate the audit log file in a CSV, XML, or JSON 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.

Overview of the CSV audit log format

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.

Field	XML element/attribute	Data type	Description
`log_time`	`event/log_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`	`event/session_line_num`	`bigint`	Order of the statement within the session.
`process_status`	`event/process_status`	`text`	Processing status.
`session_start_time`	`event/session_start_time`	`timestamp with time zone`	Date/time when the session was started.
`virtual_transaction_id`	`event/virtual_transaction_id`	`text`	Virtual transaction ID of the statement.
`transaction_id`	`event/transaction_id`	`bigint`	Regular transaction ID of the statement.
`error_severity`	`error_severity`	`text`	Statement severity. Values are `AUDIT` for audited statements and `ERROR` for any resulting error messages.
`sql_state_code`	`event/sql_state_code`	`text`	SQL state code returned for the statement. The `sql_state_code` isn't logged when its value is 00000 for XML log format.
`message`	`message`	`text`	The SQL statement that was attempted for execution.
`detail`	`detail`	`text`	Error message detail.
`hint`	`hint`	`text`	Hint for error.
`internal_query`	`internal_query`	`text`	Internal query that led to the error, if any.
`internal_query_pos`	`internal_query_pos`	`integer`	Character count of the error position therein.
`context`	`context`	`text`	Error context.
`query`	`query`	`text`	User query that led to the error. For errors only.
`query_pos`	`query_pos`	`integer`	Character count of the error position therein. For errors only.
`location`	`location`	`text`	Location of the error in the source code. The location field is populated if `log_error_verbosity` is set to verbose.
`application_name`	`event/application_name`	`text`	Name of the application from which the statement was executed, for example, `psql.bin`.
`backend_type`	`event/backend_type`	`text`	The `backend_type` corresponds to what `pg_stat_activity.backend_type` shows and is added as a column to the csv log.
`leader_pid`	`event/leader_pid`	`integer`	Process ID of leader for active parallel workers.
`query_id`	`event/query_id`	`long`	Identifier of this backend's most recent query.
`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.
`type`	`event/type`	`text`	Determines 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;

Output

 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="amul" database="edb" process_id="110405" 
       remote_host="[local]" session_id="63e1f4cf.1af45" 
       session_line_num="1" process_status="idle" 
       session_start_time="2023-02-07 12:20:56.920 IST" 
       log_time="2023-02-07 12:20:55 IST" virtual_transaction_id="4/22" 
       type="create" command_tag="CREATE ROLE" application_name="psql" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: create user edb superuser;</message>
</event>
<event user="edb" database="postgres" process_id="110424" 
       remote_host="[local]" session_id="63e1f4d4.1af58" 
       session_line_num="1" process_status="idle" 
       session_start_time="2023-02-07 12:21:00.561 IST" 
       log_time="2023-02-07 12:21:00 IST" virtual_transaction_id="4/25" 
       type="set" command_tag="SET" application_name="pg_regress" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: SET client_min_messages = warning</message>
</event>
<event user="edb" database="postgres" process_id="110424" 
       remote_host="[local]" session_id="63e1f4d4.1af58" 
       session_line_num="2" process_status="idle" 
       session_start_time="2023-02-07 12:21:00.561 IST" 
       log_time="2023-02-07 12:21:00 IST" virtual_transaction_id="4/26" 
       type="drop" command_tag="DROP DATABASE" application_name="pg_regress" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: DROP DATABASE IF EXISTS &quot;regression&quot;</message>
</event>
<event user="edb" database="postgres" process_id="110426" 
       remote_host="[local]" session_id="63e1f4d4.1af5a" 
       session_line_num="1" process_status="idle" 
       session_start_time="2023-02-07 12:21:00.568 IST" 
       log_time="2023-02-07 12:21:00 IST" virtual_transaction_id="4/29" 
       type="create" command_tag="CREATE DATABASE" application_name="pg_regress" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: CREATE DATABASE &quot;regression&quot; TEMPLATE=template0</message>
</event>
<event user="edb" database="postgres" process_id="110426" 
       remote_host="[local]" session_id="63e1f4d4.1af5a" 
       session_line_num="2" process_status="idle" 
       session_start_time="2023-02-07 12:21:00.983 IST" 
       log_time="2023-02-07 12:21:00 IST" virtual_transaction_id="4/30" 
       type="alter" command_tag="ALTER DATABASE" application_name="pg_regress" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: ALTER DATABASE &quot;regression&quot; SET lc_messages TO &apos;C&apos;;ALTER DATABASE &quot;regression&quot; SET lc_monetary TO &apos;C&apos;;ALTER DATABASE &quot;regression&quot; SET lc_numeric TO &apos;C&apos;;ALTER DATABASE &quot;regression&quot; SET lc_time TO &apos;C&apos;;ALTER DATABASE &quot;regression&quot; SET bytea_output TO &apos;hex&apos;;ALTER DATABASE &quot;regression&quot; SET timezone_abbreviations TO &apos;Default&apos;;</message>
</event>
<event user="edb" database="regression" process_id="110429" 
       remote_host="[local]" session_id="63e1f4d5.1af5d" 
       session_line_num="1" process_status="idle" 
       session_start_time="2023-02-07 12:21:01.152 IST" 
       log_time="2023-02-07 12:21:01 IST" virtual_transaction_id="4/33" 
       type="set" command_tag="SET" application_name="pg_regress/test_setup" 
       backend_type="client backend" query_id="0">
	<error_severity>AUDIT</error_severity>
	<message>statement: SET synchronous_commit = on;</message>
</event>
<event user="enterprisedb" database="edb" process_id="5942" remote_host=
"[local]"
       session_id="5ec7ac4d.1735" session_line_num="8" process_status="idle"
       session_start_time="2023-02-07 16:11:17 IST" log_time="2023-02-07
       16:12:45.471 IST" virtual_transaction_id="4/27" type="select" 
       command_tag="SELECT" audit_tag="edbaudit" application_name="psql" 
       backend_type="parallel worker" leader_pid="5940" query_id="0">
          <error_severity>AUDIT</error_severity>
          <message>statement: SELECT * FROM dept;</message>
</event>
<event process_id="112399" session_id="63e1f4e2.1b70f" session_line_num="1" 
       session_start_time="2023-02-07 12:21:14.150 IST" 
       log_time="2023-02-07 12:21:14 IST" virtual_transaction_id="5/2012" 
       type="error" sql_state_code="42601" command_tag="SELECT" 
       application_name="pg_regress/timestamptz" backend_type="parallel worker" 
       leader_pid="112317" query_id="0">
	<error_severity>ERROR</error_severity>
	<message>date format not recognized</message>
	<query>SELECT to_char(d1, E&apos;&quot;HH:MI:SS is&quot; 
   HH:MI:SS &quot;\\&quot;text between quote marks\\&quot;&quot;&apos;)
		   FROM TIMESTAMPTZ_TBL;</query>
</event>
<event process_id="112402" session_id="63e1f4e2.1b712" session_line_num="1" 
       session_start_time="2023-02-07 12:21:14.156 IST" log_time="2023-02-07 
       12:21:14 IST" virtual_transaction_id="5/2015" type="error" 
       sql_state_code="42601" command_tag="SELECT" 
       application_name="pg_regress/timestamptz" backend_type="parallel worker" 
       leader_pid="112317" query_id="0">
	<error_severity>ERROR</error_severity>
	<message>date format not recognized</message>
	<query>SELECT to_char(d1, &apos;HH24--text--MI--text--SS&apos;)
		   FROM TIMESTAMPTZ_TBL;</query>
</event>

Overview of the JSON audit log format

Including json as a value for the edb_audit parameter (see Summary of configuration parameters) allows you to import log files into different programs. This option generates log lines in the JSON format.

The following table shows JSON keys and their associated values. String fields with null values are excluded from output.

Key name	Type	Description
timestamp	string	Time stamp with milliseconds
user	string	User name
dbname	string	Database name
pid	number	Process ID
remote_host	string	Client host
remote_port	number	Client port
session_id	string	Session ID
line_num	number	Per-session line number
ps	string	Current ps display
session_start	string	Session start time
vxid	string	Virtual transaction ID
txid	string	Regular transaction ID
error_severity	string	Error severity
state_code	string	SQLSTATE code
message	string	Error message
detail	string	Error message detail
hint	string	Error message hint
internal_query	string	Internal query that led to the error
internal_position	number	Cursor index into internal query
context	string	Error context
statement	string	Client-supplied query string
cursor_position	number	Cursor index into query string
func_name	string	Error location function name
file_name	string	File name of error location
file_line_num	number	File line number of the error location
application_name	string	Client application name
backend_type	string	Type of backend
leader_pid	number	Process ID of leader for active parallel workers
query_id	number	Query ID
command_tag	string	SQL command of the statement
audit_tag	string	Value specified by the `audit_tag` parameter in the configuration file
event_type	string	Determines the audit `event_type` to identify messages in the log

The following is an example of JSON log output.

{"timestamp":"2024-04-17 13:37:47.914 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":1,"ps":"authentication","session_start":"2024-04-17 13:37:47 IST","vxid":"1/1","txid":0,"error_severity":"AUDIT","message":"connection authorized: user=edb database=edb","backend_type":"client backend","query_id":0,"command_tag":"","audit_tag":"edbaudit","event_type":"connect"}
{"timestamp":"2024-04-17 13:38:14.538 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":2,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/3","txid":0,"error_severity":"AUDIT","message":"statement: CREATE SCHEMA edb;","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"CREATE SCHEMA","audit_tag":"edbaudit","event_type":"create"}
{"timestamp":"2024-04-17 13:39:04.527 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":3,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/5","txid":0,"error_severity":"AUDIT","message":"statement: CREATE TABLE dept (\ndeptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,\ndname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,\nloc             VARCHAR2(13)\n);","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"CREATE TABLE","audit_tag":"edbaudit","event_type":"create"}
{"timestamp":"2024-04-17 13:39:13.805 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":4,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/6","txid":0,"error_severity":"AUDIT","message":"statement: INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"INSERT","audit_tag":"edbaudit","event_type":"insert"}
{"timestamp":"2024-04-17 13:39:26.632 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":5,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/7","txid":0,"error_severity":"AUDIT","message":"statement: UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"UPDATE","audit_tag":"edbaudit","event_type":"update"}
{"timestamp":"2024-04-17 13:39:26.632 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":6,"ps":"UPDATE","session_start":"2024-04-17 13:37:47 IST","vxid":"1/7","txid":0,"error_severity":"ERROR","state_code":"42P01","message":"relation \"department\" does not exist","statement":"UPDATE department SET loc = 'BOSTON' WHERE deptno = 10;","cursor_position":8,"application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"UPDATE","audit_tag":"edbaudit","event_type":"error"}
{"timestamp":"2024-04-17 13:39:32.521 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":7,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/8","txid":0,"error_severity":"AUDIT","message":"statement: UPDATE dept SET loc = 'BOSTON' WHERE deptno = 10;","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"UPDATE","audit_tag":"edbaudit","event_type":"update"}
{"timestamp":"2024-04-17 13:39:40.544 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":8,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","vxid":"1/9","txid":0,"error_severity":"AUDIT","message":"statement: SELECT * FROM dept;","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"SELECT","audit_tag":"edbaudit","event_type":"select"}
{"timestamp":"2024-04-17 13:39:46.439 IST","user":"edb","dbname":"edb","pid":105495,"remote_host":"[local]","session_id":"661f8353.19c17","line_num":9,"ps":"idle","session_start":"2024-04-17 13:37:47 IST","txid":0,"error_severity":"AUDIT","message":"disconnection: session time: 0:01:58.526 user=edb database=edb host=[local]","application_name":"psql","backend_type":"client backend","query_id":0,"command_tag":"","audit_tag":"edbaudit","event_type":"disconnect"}

Footer

← Prev

Summary of configuration parameters

↑ Up

Database administrator reference

EDB*Loader control file parameters