Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Learn more
Home

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

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "pgaudit" and 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


_10
set pgaudit.log = 'read, ddl';

The following create table, insert and select statements


_10
create table account (
_10
id int primary key,
_10
name text,
_10
description text
_10
);
_10
_10
insert into account (id, name, description)
_10
values (1, 'Foo Barsworth', 'Customer account');
_10
_10
select * from account;

Results in the log output


_10
AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(
_10
id int,
_10
name text,
_10
description text
_10
);,<not logged>
_10
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