Unfortunately, PostgreSQL does not inherently provide the robust auditing capabilities, so we’ll use a PostgreSQL extension called “pgAudit” to handle this task.
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.
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.
Change to the pgAudit directory:
Checkout the REL_15_STABLE branch:
Build and install pgAudit:
Once installed, include pgAudit in the shared_preload_libraries configuration of PostgreSQL. Open the “postgresql.conf” file:
Enable the PgAudit Extension
To enable the pgAudit extension, run the following command:
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:
You can also use the set command to enable logging for ddl and dml commands as follows:
set pgaudit.log_relation = on;
Enable the session logging for all commands except MISC and raise the audit log messages:
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:
SELECT * FROM table_name;
You can also set the audit logging level per transaction or session:
SET LOCAL pgaudit.log = 'all';
SELECT * FROM table_name;
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.
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.