APIO Core

Appearance

Postgres Audit Logging (optional) ​

This document outlines the procedure for configuring audit logging in PostgreSQL. Implementing this setup enables the detailed tracking of database activities, ensuring security compliance, monitoring user actions, and providing accountability for data access and modifications.

Overview ​

We use the pgaudit extension to record database activity. Setting it up involves three main parts:

  • The Extension: Installing pgaudit to generate the log entries.

  • The Rules: Choosing what to track.

  • The Storage: Managing where the logs.

Step 1: Install the pgaudit package ​

bash
sudo dnf install pgaudit_18

The 18 depends on the Postgresql version currently installed on the machine. To verify the current running version of Postgres, run

bash
SELECT version();
                                 version
----------------------------------------------------------------------
PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
(1 row)

It’s possible to search the available packages with the following command :

bash
dnf search pg audit
Last metadata expiration check: 1:23:07 ago on Mon 27 Jul 2026 10:33:20 AM CEST.
====================================================================================================== Name & Summary Matched: audit, pg ======================================================================================================
pgaudit.x86_64 : PostgreSQL Audit Extension
pgaudit.src : PostgreSQL Audit Extension
pgaudit16_14.x86_64 : PostgreSQL Audit Extension
pgaudit17_15.x86_64 : PostgreSQL Audit Extension
pgaudit_16.x86_64 : PostgreSQL Audit Extension
pgaudit_17.x86_64 : PostgreSQL Audit Extension
pgaudit_18.x86_64 : PostgreSQL Audit Extension

Step 2: Modify postgresql.conf ​

Add or modify to configure pgaudit

shared_preload_libraries = 'pgaudit'

ini
logging_collector = on
log_line_prefix = '%m [%p] %q%u@%d,host=%h '

log_directory = 'log'
log_filename = 'postgresql.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 0

pgaudit.log = 'ddl, role, read, write'
pgaudit.role = 'auditor'

Step 3: Setup logrotate ​

Create a new file with the following content so the logs get rotated, as pgaudit logs can get quite heavy fast. The argument “rotate” can be changed to a bigger number to keep more logs.

bash
vi /etc/logrotate.d/postgresql
/path/to/your/postgres/log/postgresql.log {
    daily                 # Rotate it daily
    rotate 7              # Keep exactly 7 old copies
    dateext               # Adds the -YYYYMMDD to the rotated files
    compress              # Compress old files with gzip
    copytruncate          # Safely clear the active log without stopping Postgres
}

Step 4: Restart the PostgreSQL Service ​

For the new settings to take effect

bash
sudo systemctl restart postgresql

Step 5: Connect to apio DB ​

bash
sudo -u postgres psql -d apio

Ensure that apio is the correct database that needs to be monitored. If unsure, run the following command:

It will print the list of databases currently present in Postgres

sh
su - postgres
psql
\l

Step 6: Enable pgaudit ​

Once connected to the right DB, add the pgaudit extension.

bash
CREATE EXTENSION IF NOT EXISTS pgaudit;

Step 7: Create dummy role ​

It will only be used to audit the database changes, no login is possible on this account

bash
CREATE ROLE auditor nologin;

Step 8: Give permissions to auditor role ​

8.1 Grant SELECT on all current tables to the pgAudit role ​

bash
GRANT SELECT ON ALL TABLES IN SCHEMA public TO auditor;

8.2 Ensure future tables are automatically granted to the auditor role ​

bash
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO auditor;

8.3 Remove access for the non-needed tables ​

bash
REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE alarms, simulations FROM auditor;

Conclusion ​

Once the setup is completed, the postgresql logs, located in the $DATA_DIR/log should be getting completed with pgaudit logs, here is an example of pgaudit logs

bash
2026-07-27 12:26:02.202 CEST [964861] apio_core@apio_core,host=172.18.0.2LOG:  AUDIT: OBJECT,373,1,READ,SELECT,TABLE,public.idp_sessions,"UPDATE users
 SET status = 'EXPIRED'
 FROM user_profiles
 WHERE users.profile_id = user_profiles.id
   AND status = 'ACTIVE'
   AND user_profiles.expire IS NOT NULL
   AND user_profiles.expire > 0
   AND users.last_password_change IS NOT NULL
   AND users.last_password_change + (user_profiles.expire * INTERVAL '1 day') < NOW()
   AND users.user_id not in (select user_id from idp_sessions where user_id = users.user_id) returning users.user_id",<none>

Which is an example of an user IDP session expiring.