Using Command Tags to Filter Audit Logs v13

Suggest edits

Each entry in the log file except for those displaying an error message contains a command tag. A command tag is the SQL command executed for that particular log entry. The command tag makes it possible to use subsequent tools to scan the log file to find entries related to a particular SQL command.

The following is an example in XML form. The example has been formatted for easier review. 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.

<event user="enterprisedb" database="edb" process_id="64234" remote_host=
"[local]"
       session_id="5ecbc7e6.faea" session_line_num="2" process_status="idle"
       session_start_time="2020-05-25 18:58:06 IST" log_time="2020-05-25
       18:58:21.147 IST"
       virtual_transaction_id="4/30" type="create" command_tag="CREATE ROLE"
       application_name="psql" backend_type="client backend">
           <error_severity>AUDIT</error_severity>
           <message>statement: CREATE ROLE newuser WITH LOGIN;</message>
</event>
<event user="enterprisedb" database="edb" process_id="64234" remote_host=
"[local]"
       session_id="5ecbc7e6.faea" session_line_num="3" process_status="idle"
       session_start_time="2020-05-25 18:58:06 IST" log_time="2020-05-25
       18:58:34.142 IST"
       virtual_transaction_id="4/31" type="error" sql_state_code="42601"
       application_name="psql" backend_type="client backend">
           <error_severity>ERROR</error_severity>
           <message>unrecognized role option &quot;super&quot;</message>
           <query>ALTER ROLE newuser WITH SUPER USER;</query>
           <query_pos>25</query_pos>
</event>
<event user="enterprisedb" database="edb" process_id="64234" remote_host=
"[local]"
       session_id="5ecbc7e6.faea" session_line_num="4" process_status="idle"
       session_start_time="2020-05-25 18:58:06 IST" log_time="2020-05-25
       18:58:44.680 IST"
       virtual_transaction_id="4/32" type="alter" command_tag="ALTER ROLE"
       application_name="psql" backend_type="client backend">
           <error_severity>AUDIT</error_severity>
           <message>statement: ALTER ROLE newuser WITH SUPERUSER;</message>
</event>
<event user="enterprisedb" database="edb" process_id="64234" remote_host=
"[local]"
       session_id="5ecbc7e6.faea" session_line_num="5" process_status="idle"
       session_start_time="2020-05-25 18:58:06 IST" log_time="2020-05-25
       18:58:58.173 IST"
       virtual_transaction_id="4/33" type="drop" command_tag="DROP ROLE"
       application_name="psql" backend_type="client backend">
           <error_severity>AUDIT</error_severity>
           <message>statement: DROP ROLE newuser;</message>
</event>

The following is the same example in CSV form. The command tag is the next to last column of each entry. In the listing, the column that appears empty (""), would be the value of the edb_audit_tag parameter if provided.

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.

2020-05-25 19:09:32.105 IST,"enterprisedb","edb",77212,"[local]",5ecbca7b.
12d9c,2,"idle",2020-05-25 19:09:07 IST,4/30,0,AUDIT,00000,"statement: CREATE
ROLE newuser WITH LOGIN;",,,,,,,,,"psql","client backend","CREATE ROLE","",
"create"

2020-05-25 19:09:50.975 IST,"enterprisedb","edb",77212,"[local]",5ecbca7b.
12d9c,3,"idle",2020-05-25 19:09:07 IST,4/31,0,ERROR,42601,"unrecognized role
option ""super""",,,,,,"ALTER ROLE newuser WITH SUPER USER;",25,,"psql",
"client backend","","","error"

2020-05-25 19:10:04.128 IST,"enterprisedb","edb",77212,"[local]",5ecbca7b.
12d9c,4,"idle",2020-05-25 19:09:07 IST,4/32,0,AUDIT,00000,"statement: ALTER
ROLE newuser WITH SUPERUSER;",,,,,,,,,"psql","client backend","ALTER ROLE","",
"alter"

2020-05-25 19:10:15.959 IST,"enterprisedb","edb",77212,"[local]",5ecbca7b.
12d9c,5,"idle",2020-05-25 19:09:07 IST,4/33,0,AUDIT,00000,"statement: DROP
ROLE newuser;",,,,,,,,,"psql","client backend","DROP ROLE","","drop"
← Prev

Using Error Codes to Filter Audit Logs

Next →

Redacting Passwords from Audit Logs