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 ...

Get PostgreSQL 10 Administration Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.