Auditing SQL

There are two main ways to log SQL

• Using the PostgreSQL log_statement parameter
• Using the pgaudit extension’s pgaudit.log parameter

The log_statement parameter can be set to one of the following options:

• ALL: logs all SQL statements executed at top-level
• MOD: logs all SQL statements for INSERT, UPDATE, DELETE, and TRUNCATE
• ddl: logs all SQL statements for DDL commands
• NONE: no statements logged

For example,to log all DDL commands, edit your postgresql.conf file to set the following

log_statement  =  'ddl'

The log_statement SQL statements explicitly given in top-level commands. It is still possible to perform SQL without it being logged by this setting if you use any of the PL languages, either through DO statements or by calling a function that includes SQL statements.

Was the change committed? It is possible to have some statements recorded in the log file but not visible in the database structure. Most DDL commands in PostgreSQL can be rolled back, so what is in the log is just a list of commands executed by PostgreSQL-not what was actually committed. The log file is not transactional, and it keeps commands that were rolled back. It is possible to display the transaction identifier on each log line by including %x in the log_line_prefix setting, though that has some difficulties in usage.

Who made the changes? To be able to know which database user who made the DDL  changes, you have to make sure that this information is logged as well. In order to do so, you may have to change the log_line_prefix parameter to include the %u format string.

A recommended minimal log_line_prefix format string for auditing DDL is %t  %u  %d, which tells PostgreSQL to log the timestamp, database user, and database name at the start of every log line.

The pgaudit extension provides two levels of audit logging: session and object levels. Session level has been designed to solve some of the problems of log_statement. pgaudit will log all access, even if it is not executed as a top-level statement and it will log all dynamic SQL. pgaudit.log can be set to include zero or more of the following settings:

• READ: SELECT and COPY
• WRITE: INSERT, UPDATE, DELETE, TRUNCATE and COPY
• FUNCTION: Function calls and DO blocks
• ROLE: GRANT, REVOKE, CREATE/ALTER/DROP ROLE
• DDL: All DDL not already included in the ROLE category
• MISC: Miscelaneous – DISCARD, FETCH, CHECKPOINT, VACUUM, and so on

For example, to log all DDL commands, edit your postgresql.conf file to set the following:

pgaudit.log = 'role, ddl'

You should set these parameters to reduce the overhead of logging:

pgaudit.log_catalog = off
pgaudit.log_relation = off
pgaudit.log_statement_once = on

The pgaudit extension was originally written by Simon Riggs and Abhijit Menon-Sen of 2ndQuadrant as part of the AXLE project for the EU. The next version was designed by Simon Riggs and David Steele to provide object level logging. The original version has been deprecated and is no longer available. The new version is fully supported and has been adopted by the US DoD as the tool of choice for PostgreSQL audit logging.

pgaudit is available in binary form via postgresql.org repositories.