pgAudit
Open Source PostgreSQL Audit Logging

Introduction

The PostgreSQL Audit Extension (pgAudit) provides detailed session and/or object audit logging via the standard PostgreSQL logging facility.

The goal of pgAudit is to provide PostgreSQL users with capability to produce audit logs often required to comply with government, financial, or ISO certifications.

An audit is an official inspection of an individual's or organization's accounts, typically by an independent body. The information gathered by pgAudit is properly called an audit trail or audit log. The term audit log is used in this documentation.

Why pgAudit?

Basic statement logging can be provided by the standard logging facility with log_statement = all. This is acceptable for monitoring and other usages but does not provide the level of detail generally required for an audit. It is not enough to have a list of all the operations performed against the database. It must also be possible to find particular statements that are of interest to an auditor. The standard logging facility shows what the user requested, while pgAudit focuses on the details of what happened while the database was satisfying the request.

For example, an auditor may want to verify that a particular table was created inside a documented maintenance window. This might seem like a simple job for grep, but what if you are presented with something like this (intentionally obfuscated) example:

DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

Standard logging will give you this:

LOG:  statement: DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;

It appears that finding the table of interest may require some knowledge of the code in cases where tables are created dynamically. This is not ideal since it would be preferable to just search on the table name. This is where pgAudit comes in. For the same input, it will produce this output in the log:

AUDIT: SESSION,33,1,FUNCTION,DO,,,"DO $$
BEGIN
    EXECUTE 'CREATE TABLE import' || 'ant_table (id INT)';
END $$;"
AUDIT: SESSION,33,2,DDL,CREATE TABLE,TABLE,public.important_table,CREATE TABLE important_table (id INT)

Not only is the DO block logged, but substatement 2 contains the full text of the CREATE TABLE with the statement type, object type, and full-qualified name to make searches easy.

When logging SELECT and DML statements, pgAudit can be configured to log a separate entry for each relation referenced in a statement. No parsing is required to find all statements that touch a particular table. In fact, the goal is that the statement text is provided primarily for deep forensics and should not be required for an audit.

Usage Considerations

Depending on settings, it is possible for pgAudit to generate an enormous volume of logging. Be careful to determine exactly what needs to be audit logged in your environment to avoid logging too much.

For example, when working in an OLAP environment it would probably not be wise to audit log inserts into a large fact table. The size of the log file will likely be many times the actual data size of the inserts because the log file is expressed as text. Since logs are generally stored with the OS this may lead to disk space being exhausted very quickly. In cases where it is not possible to limit audit logging to certain tables, be sure to assess the performance impact while testing and allocate plenty of space on the log volume. This may also be true for OLTP environments. Even if the insert volume is not as high, the performance impact of audit logging may still noticeably affect latency.

To limit the number of relations audit logged for SELECT and DML statements, consider using object audit logging (see Object Auditing). Object audit logging allows selection of the relations to be logged allowing for reduction of the overall log volume. However, when new relations are added they must be explicitly added to object audit logging. A programmatic solution where specified tables are excluded from logging and all others are included may be a good option in this case.

PostgreSQL Version Compatibility

pgAudit supports PostgreSQL 14 or greater.

In order to support new functionality introduced in each PostgreSQL release, pgAudit maintains a separate branch for each PostgreSQL major version (currently PostgreSQL 14 - 19) which will be maintained in a manner similar to the PostgreSQL project.

Aside from bug fixes, no further development is allowed for stable branches. New development, if any, will be strictly for the next unreleased major version of PostgreSQL.

pgAudit versions relate to PostgreSQL major versions as follows:

• pgAudit v19.X is intended to support PostgreSQL 19.

• pgAudit v18.X is intended to support PostgreSQL 18.

• pgAudit v17.X is intended to support PostgreSQL 17.

• pgAudit v16.X is intended to support PostgreSQL 16.

• pgAudit v1.7.X is intended to support PostgreSQL 15.

• pgAudit v1.6.X is intended to support PostgreSQL 14.

Compile and Install

pgAudit can be compiled against an installed copy of PostgreSQL with development packages using PGXS. The following instructions should work on most Unix-like operating systems.

Clone the pgAudit extension:

git clone https://github.com/pgaudit/pgaudit.git

Change to pgAudit directory:

cd pgaudit

Checkout REL_19_STABLE branch (note that the stable branch may not exist for unreleased versions of PostgreSQL):

git checkout REL_19_STABLE

Build and install pgAudit:

make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-19/bin/pg_config

Instructions for testing and development may be found in test.

Settings

Settings may be modified only by a superuser. Allowing normal users to change their settings would defeat the point of an audit log.

Settings can be specified globally (in postgresql.conf or using ALTER SYSTEM ... SET), at the database level (using ALTER DATABASE ... SET), or at the role level (using ALTER ROLE ... SET). Note that settings are not inherited through normal role inheritance and SET ROLE will not alter a user's pgAudit settings. This is a limitation of the roles system and not inherent to pgAudit.

The pgAudit extension must be loaded in shared_preload_libraries. Otherwise, an error will be raised at load time and no audit logging will occur.

In addition, CREATE EXTENSION pgaudit must be called before pgaudit.log is set to ensure proper pgaudit functionality. The extension installs event triggers which add additional auditing for DDL. pgAudit will work without the extension installed but DDL statements will not have information about the object type and name.

If the pgaudit extension is dropped and needs to be recreated then pgaudit.log must be unset first otherwise an error will be raised.

pgaudit.log

Specifies which classes of statements will be logged by session audit logging. Possible values are:

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

Multiple classes can be provided using a comma-separated list and classes can be subtracted by prefacing the class with a - sign (see Session Audit Logging).

The default is none.

pgaudit.log_catalog