“PGAudit: How to Enabling Audit in PostgreSQL”

BySuyog Brid

In this comprehensive guide, we will walk you through the process of enabling pgAudit extension for PostgreSQL database audit logging. We’ll demonstrate the steps using PostgreSQL 15 on CentOS 7.

Installation of pgAudit

Step 1: Download pgAudit Extension for PostgreSQL 15

To begin, download the pgAudit extension compatible with PostgreSQL 15

wget https://yum.postgresql.org/15/redhat/rhel-7-x86_64/pgaudit17_15-1.7.0-1.rhel7.x86_64.rpm

Note:- pgAudit v1.7.X is intended to support PostgreSQL 15

Step 2: Install pgAudit Extension:

Install the downloaded pgAudit extension package:

rpm -ivh pgaudit17_15-1.7.0-1.rhel7.x86_64.rpm

Make necessary changes in the PostgreSQL configuration file

Step3: Add pgAudit into shared_preload_libraries:

shared_preload_libraries = 'pgaudit'

Step 4: Define Audit Logging Level:

Specify the classes of statements to be logged.

pgaudit.log = 'all'

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.

Note :- you can use multiple values together by adding “,” like DDL, MISC

Step 5: Define Log Filename:

Set the log filename format.

log_filename ='postgresql-%Y-%m-%d_%H%M%S.log'

Step 6: Set Log Rotation:

Configure log rotation parameters.

log_rotation_age = 1d
log_rotation_size = 10MB

Note:- Log rotation period set for 10MB and 1 day. Files rotate every 10MB and after 1 day. Set  log_truncate_on_rotation = off, so your log file not get truncate after rotation period.

Step 7: Set Log Line Prefix:

Define the log line prefix for better readability.

log_line_prefix = '%m [%p] user=%u host=%h database=%d '

Note:- This parameter allows you to specify a string that will be prefixed to each log entry

Step 8: Restart PostgreSQL Server:

Restart the PostgreSQL server to apply the changes.

/usr/pgsql-15/bin/pg_ctl -D /var/lib/pgsql/15/data/  restart

Step 9: Create pgAudit Extension:

Execute the following SQL command to create the pgAudit extension into database

CREATE EXTENSION pgaudit;

Step 10: Verify pgAudit Parameters:

Check the configured pgAudit parameters using.

show pgaudit.log;

Step 11: Verify Logs:

Check the generated logs to ensure proper functioning.

-bash-4.2$ cat postgresql-2024-03-25_130421.log 2024-03-25 13:04:21.842 IST [3281] user= host= database= LOG: starting PostgreSQL 15.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2024-03-25 13:04:21.845 IST [3281] user= host= database= LOG: listening on IPv4 address "0.0.0.0", port 5432 2024-03-25 13:04:21.847 IST [3281] user= host= database= LOG: listening on IPv6 address "::", port 5432 2024-03-25 13:04:21.857 IST [3281] user= host= database= LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-03-25 13:04:21.870 IST [3281] user= host= database= LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2024-03-25 13:04:21.958 IST [3285] user= host= database= LOG: database system was interrupted; last known up at 2024-03-24 11:22:35 IST 2024-03-25 13:04:31.290 IST [3285] user= host= database= LOG: database system was not properly shut down; automatic recovery in progress 2024-03-25 13:04:31.305 IST [3285] user= host= database= LOG: redo starts at 3/FAE36708 2024-03-25 13:04:31.305 IST [3285] user= host= database= LOG: invalid record length at 3/FAE367F0: wanted 24, got 0 2024-03-25 13:04:31.305 IST [3285] user= host= database= LOG: redo done at 3/FAE367B8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2024-03-25 13:04:31.321 IST [3283] user= host= database= LOG: checkpoint starting: end-of-recovery immediate wait 2024-03-25 13:04:31.348 IST [3283] user= host= database= LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.009 s, total=0.034 s; sync files=2, longest=0.007 s, average=0.005 s; distance=0 kB, estimate=0 kB 2024-03-25 13:04:31.358 IST [3281] user= host= database= LOG: database system is ready to accept connections 2024-03-25 13:05:30.106 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,1,1,DDL,CREATE SEQUENCE,SEQUENCE,public.customers_customer_id_seq,"CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) );",<not logged> 2024-03-25 13:05:30.106 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,1,1,DDL,CREATE TABLE,TABLE,public.customers,"CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) );",<not logged> 2024-03-25 13:05:30.106 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,1,1,DDL,CREATE INDEX,INDEX,public.customers_pkey,"CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) );",<not logged> 2024-03-25 13:05:30.106 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,1,1,DDL,ALTER SEQUENCE,SEQUENCE,public.customers_customer_id_seq,"CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) );",<not logged> 2024-03-25 13:05:30.130 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,2,1,WRITE,INSERT,,,"INSERT INTO customers (name, email, phone) VALUES ('John Doe', 'john@example.com', '123-456-7890'), ('Jane Smith', 'jane@example.com', '987-654-3210'), ('Alice Johnson', 'alice@example.com', '555-123-4567'), ('Bob Anderson', 'bob@example.com', '999-888-7777'), ('Emily Brown', 'emily@example.com', '444-555-6666');",<not logged> 2024-03-25 13:05:30.183 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,3,1,DDL,CREATE SEQUENCE,SEQUENCE,public.orders_order_id_seq,"CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE, total_amount NUMERIC(10, 2) );",<not logged> 2024-03-25 13:05:30.184 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,3,1,DDL,CREATE TABLE,TABLE,public.orders,"CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE, total_amount NUMERIC(10, 2) );",<not logged> 2024-03-25 13:05:30.184 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,3,1,DDL,CREATE INDEX,INDEX,public.orders_pkey,"CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE, total_amount NUMERIC(10, 2) );",<not logged> 2024-03-25 13:05:30.184 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,3,1,DDL,ALTER TABLE,TABLE,public.orders,"CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE, total_amount NUMERIC(10, 2) );",<not logged> 2024-03-25 13:05:30.184 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,3,1,DDL,ALTER SEQUENCE,SEQUENCE,public.orders_order_id_seq,"CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(customer_id), order_date DATE, total_amount NUMERIC(10, 2) );",<not logged> 2024-03-25 13:05:30.193 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,1,WRITE,INSERT,,,"INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, '2024-03-20', 150.25), (2, '2024-03-21', 200.50), (3, '2024-03-22', 75.00), (4, '2024-03-23', 300.75), (5, '2024-03-24', 100.00);",<not logged> 2024-03-25 13:05:30.224 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,2,READ,SELECT,,,"SELECT 1 FROM ONLY ""public"".""customers"" x WHERE ""customer_id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged> 2024-03-25 13:05:30.225 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,3,READ,SELECT,,,"SELECT 1 FROM ONLY ""public"".""customers"" x WHERE ""customer_id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged> 2024-03-25 13:05:30.227 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,4,READ,SELECT,,,"SELECT 1 FROM ONLY ""public"".""customers"" x WHERE ""customer_id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged> 2024-03-25 13:05:30.229 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,5,READ,SELECT,,,"SELECT 1 FROM ONLY ""public"".""customers"" x WHERE ""customer_id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged> 2024-03-25 13:05:30.230 IST [3299] user=postgres host=[local] database=postgres LOG: AUDIT: SESSION,4,6,READ,SELECT,,,"SELECT 1 FROM ONLY ""public"".""customers"" x WHERE ""customer_id"" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x",<not logged>

You have successfully enabled pgAudit for PostgreSQL database audit logging. Stay vigilant by regularly reviewing the logs to ensure the security and integrity of your database. This guide provides a comprehensive walkthrough of enabling pgAudit for PostgreSQL audit logging, ensuring your database’s security and compliance. Follow these steps meticulously to enhance your database’s audit capabilities and strengthen your overall security posture.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *