Postgres Audit Log

This tutorial teaches us how to work with the PostgreSQL Log Audit. We will also discuss how to enable and understand how to use it in the database.

Unfortunately, PostgreSQL does not inherently provide the robust auditing capabilities, so we’ll use a PostgreSQL extension called “pgAudit” to handle this task.

PostgreSQL PgAudit

The pgAudit is a PostgreSQL extension that provides detailed audit logging capabilities for database activities.

The pgAudit extension enhances the native logging functionality of PostgreSQL by capturing specific events and actions within the database which allow for comprehensive auditing and monitoring of database activities.

It offers a fine-grained control over which events are logged which enables the administrators to track and analyze the critical operations.

Compile and Install the PgAudit in PostgreSQL

Before we can use the pgAudit extension, we need to compile and use it to configure our server to load it.

Prerequisites:

Before proceeding, ensure that you have the following:

Installed PostgreSQL
Superuser access or equivalent rights
Network connectivity
Build tools and compiler dependencies

With the given requirements met, let us proceed to the following steps.

Start by cloning the pgAudit GitHub repository.

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

Change to the pgAudit directory:

cd pgaudit

Checkout the REL_15_STABLE branch:

git checkout REL_15_STABLE

Build and install pgAudit:

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

Once installed, include pgAudit in the shared_preload_libraries configuration of PostgreSQL. Open the “postgresql.conf” file:

shared_preload_libraries = 'pgaudit'

Enable the PgAudit Extension

To enable the pgAudit extension, run the following command:

CREATE EXTENSION pgaudit;

Configure the PgAudit

PgAudit is configured by setting the “pgaudit.log” configuration directive in “postgresql.conf” or on a per-session basis using SET.

The “pgaudit.log” configuration can contain a comma-separated list of values. These include:

READ – It logs all SELECT and COPY when the source is a relation or a query.
WRITE – It logs all DDL and MOD.
FUNCTION – It logs all function calls and DO blocks.
ROLE – It logs all role-related DDL.
MISC – It logs the miscellaneous commands.
MISC_SET – It logs all miscellaneous SET commands.

For example, log all read and write operations and set the configuration as follows:

pgaudit.log = 'READ,WRITE'

You can also use the set command to enable logging for ddl and dml commands as follows:

set pgaudit.log = 'write, ddl';

set pgaudit.log_relation = on;

Enable the session logging for all commands except MISC and raise the audit log messages:

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

set pgaudit.log_level = notice;

PostgreSQL Audit Logging

The actual audit logging is performed via the pgAudit’s functions. For example, we audit the log for the select statement in a given table as follows:

SET pgaudit.log = 'all';

SELECT * FROM table_name;

You can also set the audit logging level per transaction or session:

BEGIN;

SET LOCAL pgaudit.log = 'all';

SELECT * FROM table_name;

COMMIT;

View the Audit Logs

All the logged audit events are available in the PostgreSQL log files which are defined by the log_directory and log_filename settings in the “postgresql.conf” file.

The audit logs contain a wealth of information. Each entry has an associated AUDIT event type such as ROLE, DDL, FUNCTION, etc. as described in the configuration section.

Conclusion

This tutorial explored how to enable and configure the log auditing in PostgreSQL. We also explored the basic examples of log auditing in Postgres. You can learn more about the pgAudit functionality and configuration in this resource: https://github.com/pgaudit/pgaudit.