Using command tags to filter audit logs v16

Suggest edits

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

This example is in XML form, 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.

<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 (""), is the value of the edb_audit_tag parameter, if provided.

(Each audit log entry was split and displays across multiple lines. A blank line was inserted between the audit log entries for visual clarity.)

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

↑ Up

Using EDB audit logging

Next →

Redacting passwords in audit logs