Install PostgreSQL 16 with pgaudit Support on Ubuntu 24.04 LTS

inchirags@gmail.com Chirag PostgreSQL DBA Tutorial https://www.chirags.in

*************************************************************************************

Install PostgreSQL 16 with pgaudit Support on Ubuntu 24.04 LTS

*************************************************************************************

pgaudit (PostgreSQL Audit Extension) provides detailed session and/or object-level logging for PostgreSQL. It’s especially useful for meeting compliance requirements like PCI-DSS, HIPAA, or SOX.

# What pgaudit Provides

Statement-level logging: SELECT, INSERT, UPDATE, DELETE, etc.
Object-level logging: Which tables, schemas, etc., were accessed.
Session logging: Logs for a session or user, including roles switching.
Helps with compliance auditing by capturing detailed actions.

Here is a step-by-step guide to enable and use pgaudit (PostgreSQL Audit extension) in PostgreSQL 16 on a Linux-based system Ubuntu 24.04 LTS:

1. Install PostgreSQL 16 with pgaudit Support

sudo apt update

sudo apt install postgresql-16 postgresql-16-pgaudit -y

2. Enable pgaudit in PostgreSQL Configuration

# A. Edit postgresql.conf

sudo nano /etc/postgresql/16/main/postgresql.conf

# B. Add or modify these lines:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all'       # or 'read, write' based on requirement
pgaudit.log_relation = on
pgaudit.log_parameter = on

# pgaudit.log values:

* READ (SELECT)

* WRITE (INSERT, UPDATE, DELETE)

* FUNCTION

* ROLE

* DDL

* MISC

* ALL

# C. Restart PostgreSQL

sudo systemctl restart postgresql

3. Create the pgaudit Extension

# Login to PostgreSQL:

sudo -u postgres psql

# Create Extension:

CREATE EXTENSION pgaudit;

4. Create Test User and Database (Optional)

CREATE DATABASE auditdb;
CREATE USER audituser WITH PASSWORD 'audit@123';
GRANT ALL PRIVILEGES ON DATABASE auditdb TO audituser;

\c auditdb

CREATE TABLE employee(id SERIAL PRIMARY KEY, name TEXT, salary NUMERIC);
GRANT ALL ON employee TO audituser;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE employee_id_seq TO audituser;

\q

5. Test Audit Logging

# As audituser:

psql -U audituser -d auditdb -h localhost -W

# Perform some operations:

SELECT * FROM employee;

INSERT INTO employee(name, salary) VALUES ('Chirag Mahto', 50000);

\q

6. Check Audit Logs

# Logs location (default on Debian/Ubuntu):

sudo tail -f /var/log/postgresql/postgresql-16-main.log

# Sample Audit Log:

AUDIT: SESSION,4,1,ROLE,GRANT,,,GRANT ALL PRIVILEGES ON DATABASE auditdb TO audituser;,<none>

AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,"CREATE TABLE employee(id SERIAL PRIMARY KEY, name TEXT, salary NUMERIC);",<none>

AUDIT: SESSION,2,1,ROLE,GRANT,,,GRANT ALL ON employee TO audituser;,<none>

AUDIT: SESSION,3,1,ROLE,GRANT,,,"GRANT USAGE, SELECT, UPDATE ON SEQUENCE employee_id_seq TO audituser;",<none>

AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.employee,SELECT * FROM employee;,<none>

AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.employee,"INSERT INTO employee(name, salary) VALUES ('Chirag Mahto', 50000);",<none>

7. Customize Logging (Optional)

You can fine-tune what gets logged by changing:

# pgaudit.log values:
* READ (SELECT)
* WRITE (INSERT, UPDATE, DELETE)
* FUNCTION
* ROLE
* DDL
* MISC
* ALL

Example:

pgaudit.log = 'read, write'

8. Security Best Practices

* Restrict postgresql.conf access:

sudo chmod 640 /etc/postgresql/16/main/postgresql.conf

* Rotate logs periodically:

Configure log_rotation_age or log_rotation_size in postgresql.conf.

9. Uninstall or Disable pgaudit

To disable without uninstalling:

shared_preload_libraries = ''  # or remove 'pgaudit'

To uninstall:

DROP EXTENSION pgaudit;

sudo apt remove postgresql-16-pgaudit

Example Use Case

If a user performs a DELETE, you will get a log like:

AUDIT: SESSION,1,3,WRITE,DELETE,TABLE,public.employee,DELETE FROM employee WHERE id=1;,<none>

You can forward this log to your SIEM or central logging solution for security auditing.

For any doubts and query, please write on YouTube video 📽️ comments section.

Note : Flow the Process shown in video 📽️.

😉Please Subscribe for more videos:

https://www.youtube.com/@chiragtutorial

💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

https://www.chirags.in

____________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.