pgaudit

Overview

pg15=pgaudit17, pg14=pgaudit16

Version

Install

You can install pgaudit directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pgaudit;          # Install for current active PG version

pig ext install -y pgaudit -v 18  # PG 18
pig ext install -y pgaudit -v 17  # PG 17
pig ext install -y pgaudit -v 16  # PG 16
pig ext install -y pgaudit -v 15  # PG 15
pig ext install -y pgaudit -v 14  # PG 14

dnf install -y pgaudit_18       # PG 18
dnf install -y pgaudit_17       # PG 17
dnf install -y pgaudit_16       # PG 16
dnf install -y pgaudit_15       # PG 15
dnf install -y pgaudit_14       # PG 14

apt install -y postgresql-18-pgaudit   # PG 18
apt install -y postgresql-17-pgaudit   # PG 17
apt install -y postgresql-16-pgaudit   # PG 16
apt install -y postgresql-15-pgaudit   # PG 15
apt install -y postgresql-14-pgaudit   # PG 14

Preload:

shared_preload_libraries = 'pgaudit';

Create Extension:

CREATE EXTENSION pgaudit;

Usage

pgaudit: Open Source PostgreSQL Audit Logging

pgAudit provides detailed session and/or object audit logging via the standard PostgreSQL logging facility, producing audit trails required for government, financial, or ISO certifications.

CREATE EXTENSION pgaudit;

Configuration Parameters

Session Audit Logging

Log all DML and DDL with per-relation detail:

SET pgaudit.log = 'write, ddl';
SET pgaudit.log_relation = on;

Log everything except miscellaneous commands:

SET pgaudit.log = 'all, -misc';

Example output:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.account,create table account(...)
AUDIT: SESSION,2,1,READ,SELECT,,,select * from account

Object Audit Logging

Grant permissions to an audit role to control which relations are logged:

SET pgaudit.role = 'auditor';

GRANT SELECT, DELETE
   ON public.account
   TO auditor;

Now any SELECT or DELETE on the account table will be audit logged.

Log Format

Entries are CSV with fields: AUDIT_TYPE, STATEMENT_ID, SUBSTATEMENT_ID, CLASS, COMMAND, OBJECT_TYPE, OBJECT_NAME, STATEMENT, PARAMETER.