Using Command Tags to Filter Audit Logs v11

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" remote_host="[local]"
       session_id="595e8537.10f1" process_id="4337" time="2017-07-06 14:45:18 EDT"
       transaction_id="0" type="create"
       command_tag="CREATE ROLE">
  <message>AUDIT: statement: CREATE ROLE newuser WITH LOGIN;</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="595e8537.10f1" process_id="4337" time="2017-07-06 14:45:31 EDT"
       transaction_id="0" type="error">
  <message>ERROR: unrecognized role option &quot;super&quot; at character 25
           STATEMENT: ALTER ROLE newuser WITH SUPER USER;</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="595e8537.10f1" process_id="4337" time="2017-07-06 14:45:38 EDT"
       transaction_id="0" type="alter" command_tag="ALTER ROLE">
  <message>AUDIT: statement: ALTER ROLE newuser WITH SUPERUSER;</message>
</event>
<event user="enterprisedb" database="edb" remote_host="[local]"
       session_id="595e8537.10f1" process_id="4337" time="2017-07-06 14:45:46 EDT"
       transaction_id="0" type="drop" command_tag="DROP ROLE">
  <message>AUDIT: 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. (The last column appears empty as "", which would be the value provided by the edb_audit_tag parameter.)

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-06 14:47:22.294 EDT,"enterprisedb","edb",4720,"[local]",
595e85b2.1270,1,"idle",2017-07-06 14:47:14 EDT,6/4,0,AUDIT,00000,
"statement: CREATE ROLE newuser WITH LOGIN;",,,,,,,,,"psql.bin","CREATE ROLE",""

2017-07-06 14:47:29.694 EDT,"enterprisedb","edb",4720,"[local]",
595e85b2.1270,2,"idle",2017-07-06 14:47:14 EDT,6/5,0,ERROR,42601,
"unrecognized role option ""super""",,,,,,"ALTER ROLE newuser WITH SUPER USER;",25,,
"psql.bin","",""

2017-07-06 14:47:29.694 EDT,"enterprisedb","edb",4720,"[local]",
595e85b2.1270,3,"idle",2017-07-06 14:47:14 EDT,6/6,0,AUDIT,00000,
"statement: ALTER ROLE newuser WITH SUPERUSER;",,,,,,,,,"psql.bin","ALTER ROLE",""

2017-07-06 14:47:29.694 EDT,"enterprisedb","edb",4720,"[local]",
595e85b2.1270,4,"idle",2017-07-06 14:47:14 EDT,6/7,0,AUDIT,00000,
"statement: DROP ROLE newuser;",,,,,,,,,"psql.bin","DROP ROLE",""

← Prev

Using Error Codes to Filter Audit Logs

Redacting Passwords from Audit Logs

Using Command Tags to Filter Audit Logs v11

← Prev

Next →