PGAudit: Postgres Auditing

PGAudit is a PostgreSQL extension for logging session and object auditing over the standard PostgreSQL logging utility.

PGAudit grants fine grain control over which statements and objects are emitted to logs.

Enable the extension#

Settings#

The pgaudit.log setting controls which statements to log. Available values include:

• read: SELECT and COPY when the source is a relation or a query.

• write: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

• function: Function calls and DO blocks.

• role: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.

• ddl: All DDL that is not included in the ROLE class.

• misc: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM, SET.

• misc_set: Miscellaneous SET commands, e.g. SET ROLE.

• all: Include all of the above.

For a full list of available settings see settings docs. Be aware that the all setting will generate a very large volume of logs.

Example#

Given a pgaudit setting

set pgaudit.log = 'read, ddl';

The following create table, insert and select statements

create table account (
  id int primary key,
  name text,
  description text
);

insert into account (id, name, description)
values (1, 'Foo Barsworth', 'Customer account');

select * from account;

Results in the log output

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
  id int,
  name text,
  description text
);,<not logged>
AUDIT: SESSION,2,1,READ,SELECT,,,select * from account,,<not logged>

Note that the insert statement is not logged because we did not include the write option for pgaudit.log.

Resources#

• Official PGAudit documentation