pgAudit setup and PostgreSQL object audit logging

Why pgAudit records executed database objects

pgAudit is a PostgreSQL extension for organizations that need audit trails for government, financial, or ISO-related controls. PostgreSQL can record submitted SQL with `log_statement = all`, but the standard record shows what a user requested. It does not necessarily provide a convenient record of the object that PostgreSQL actually created or modified.

The README demonstrates the distinction with a `DO` block that assembles a table name through string concatenation. Standard statement logging captures the block as submitted, making `public.important_table` difficult to locate unless someone understands the dynamic string construction. pgAudit adds a second record containing the executed `CREATE TABLE`, its statement type, its object type, and the fully qualified table name.

That difference is central to pgAudit's purpose. An auditor looking for changes to a known object should not have to infer identifiers from arbitrary application code. The extension is aimed at compliance and forensic searches, while the standard statement log remains useful for monitoring submitted commands.

For `SELECT` and DML, pgAudit can produce a separate entry for each referenced relation. According to the README, this provides a direct way to find statements that touch a particular table without parsing every statement's text. Statement text is still present for deeper investigation, but relation-level records are the main search mechanism. This makes pgAudit different from a logger that merely copies client-submitted SQL into a text file.

How pgAudit's C extension enters PostgreSQL logging

The repository implements pgAudit primarily in C. Its main implementation file is `pgaudit.c`, while `pgaudit.control` supplies extension metadata and `pgaudit--19.0.sql` provides the SQL extension definition. The `Makefile` declares `MODULE_big = pgaudit` and reads `default_version` from `pgaudit.control` to derive `PGAUDIT_VERSION`. The Makefile explicitly identifies that control-file value as the source of truth.

pgAudit integrates with PostgreSQL through two required stages. The compiled library must be listed in `shared_preload_libraries`. The README states that failing to load it this way raises an error and means no audit logging occurs. Loading the library alone does not provide complete DDL object auditing, however.

Administrators must also run `CREATE EXTENSION pgaudit` before setting `pgaudit.log`. That SQL operation installs event triggers, which add object type and object name information to DDL audit entries. pgAudit can work without the SQL extension, but the README warns that DDL records will then lack those object fields. Such records may preserve basic audit activity, yet they do not deliver the normalized object identity that distinguishes pgAudit from ordinary statement logging.

Both stages feed PostgreSQL's normal logging facility. The README's DDL example uses an `AUDIT:` prefix and includes session information, record ordering, a substatement number, statement class, object class, fully qualified name, and executed SQL. That structure supplies both structured fields and statement text without requiring the separate collection service described elsewhere.

Building pgAudit with PGXS for PostgreSQL 19

The README documents installation on most Unix-like systems when PostgreSQL development packages and `PGXS` are available. Start by cloning the repository with the exact command below. The cloned `pgaudit` directory becomes the working directory for the remaining build steps.

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

After the clone completes, change into that directory. The README lists this as a separate operation:

cd pgaudit

Select the `REL_19_STABLE` branch for PostgreSQL 19. The README notes that a stable branch may not exist for an unreleased PostgreSQL major version, so this checkout example is specifically tied to PostgreSQL 19.

git checkout REL_19_STABLE

Build and install the extension through PGXS. The following command is copied from the PostgreSQL 19 example in the README:

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

The `/usr/pgsql-19/bin/pg_config` path is an example from the README, not a universal installation path. The binary must correspond to the PostgreSQL installation against which pgAudit is being built. The repository also points readers to `test` for testing and development instructions, but it does not present a complete first-use procedure there.

After installation, compilation alone is insufficient. The library must be configured through `shared_preload_libraries`, and the SQL extension must be created before `pgaudit.log` is enabled. The README does not document a server reload command for a particular platform, so this article does not substitute an invented command or configuration file path.

Creating the extension before enabling pgAudit settings

The first SQL activation operation stated in the README is `CREATE EXTENSION pgaudit`. It must run before `pgaudit.log` is configured because it installs the event triggers responsible for adding object type and object name details to DDL records.

Installing the shared library does not perform this action. Without `CREATE EXTENSION pgaudit`, the README says DDL records will not include the object type and object name information. An installation based only on `shared_preload_libraries` therefore falls short of the complete DDL behavior described by the project.

Only a PostgreSQL superuser may modify pgAudit settings. The README says settings can be applied globally through `postgresql.conf` or `ALTER SYSTEM ... SET`, to a database through `ALTER DATABASE ... SET`, or to a role through `ALTER ROLE ... SET`. The restriction exists because allowing ordinary users to change audit settings would undermine the purpose of the audit trail.

Settings do not follow ordinary role inheritance, and `SET ROLE` does not alter a user's pgAudit settings. The README attributes this boundary to PostgreSQL's roles system rather than presenting it as a pgAudit-specific feature. A deployment that changes effective roles must not assume that the new role automatically changes audit behavior.

If the extension must later be dropped and recreated, the README excerpt begins an instruction to unset `pgaudit.log` first but is truncated before the complete procedure. This article does not fill that gap with an unsupported SQL statement. Administrators should consult the current README for the complete removal and recreation sequence.

Object filtering, storage pressure, and latency

pgAudit's principal operational trade-off is audit-log volume. The README warns that some settings can generate an enormous amount of logging. Its bulk-loading example says that auditing inserts into a large fact table can produce a text log many times larger than the inserted data. Because PostgreSQL logs are normally stored by the operating system, this can exhaust the disk allocated to the log volume.

The problem is not limited to OLAP systems. The README also says that OLTP latency can be noticeably affected, even when insert volume is not especially high. Audit logging is therefore not a feature that can be evaluated only by checking whether records appear. Storage consumption and application latency need separate verification against representative workloads.

Object audit logging can reduce the number of relations included in `SELECT` and DML records. This is the project's documented approach when broad relation logging would generate too much output. It trades volume for explicit coverage management. When a new relation is created, administrators must add it to object audit logging if it should be included. New relations do not automatically enter that selected set under the process described by the README.

That limitation matters for frequently changing schemas. An operator must keep the selected relation list aligned with policy, while an exclusion-based approach may require programming to exclude designated tables and include everything else. The README mentions that programmatic approach as an option, but the repository files do not document a pgAudit configuration mechanism that implements it automatically. This makes object auditing more administratively explicit than unrestricted logging, with a clear boundary around what gets recorded.

pgAudit's PostgreSQL release branches and custom license

pgAudit supports PostgreSQL 14 or greater and maintains a separate branch for each supported server major version, currently PostgreSQL 14 through 19. The README maps pgAudit v19.X to PostgreSQL 19, v18.X to PostgreSQL 18, and v17.X to PostgreSQL 17. For older supported branches, v1.7.X corresponds to PostgreSQL 15 and v1.6.X corresponds to PostgreSQL 14.

This version coupling is the main upgrade constraint documented by the project. A PostgreSQL major upgrade requires selecting the matching pgAudit branch rather than assuming that a single extension release supports every server version. Stable branches receive bug fixes but no further development aside from that stated limit. New work is directed to the next unreleased PostgreSQL major version.

The release history follows the same mapping. pgAudit 19beta4 was released for PostgreSQL 19 on 2026-09-26, pgAudit 18.0 for PostgreSQL 18 on 2025-09-24, and pgAudit 17.1 for PostgreSQL 17 on 2025-02-19. The repository is not archived, and its last push was on 2026-09-26.

GitHub classifies the repository license as a custom license that it cannot categorize. The repository contains a `LICENSE` file, which is the controlling text for reviewing permissions and obligations. Public availability does not itself establish the applicable license terms, and this article does not offer legal advice. Teams intending to redistribute or embed pgAudit should examine that file first.

A standard statement logger is the clearest alternative when submitted SQL text is sufficient. pgAudit instead targets executed-object visibility, DDL metadata, and relation-level search records. The added detail costs storage and can affect latency, so the project is most appropriate when those records have a defined audit value rather than merely enabling every available event by default.

Editorial conclusion

Adopt pgAudit when PostgreSQL needs searchable DDL or relation-level audit records beyond `log_statement = all`. Do not enable broad DML auditing for bulk-loading or high-latency workloads without measuring storage use and latency on representative traffic. Verify the PostgreSQL major version, `shared_preload_libraries`, `CREATE EXTENSION pgaudit`, the selected audit scope, and log-volume capacity before relying on the resulting records.

Frequently asked questions

Official sources

1. Issues
2. pgaudit/pgaudit on GitHub
3. Project website
4. README
5. Releases