pgaudit

pgaudit : provides auditing functionality

Overview

Packages

Source

Install

Make sure PGDG repo available:

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

Install this extension with pig:

pig install pgaudit;		# install via package name, for the active PG version

pig install pgaudit -v 18;   # install for PG 18
pig install pgaudit -v 17;   # install for PG 17
pig install pgaudit -v 16;   # install for PG 16
pig install pgaudit -v 15;   # install for PG 15
pig install pgaudit -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pgaudit';

Create this extension with:

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.