DEV Community

Cover image for Install PostgreSQL 17 with pgaudit Support on Ubuntu 24.04 LTS
Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag)

Posted on

Install PostgreSQL 17 with pgaudit Support on Ubuntu 24.04 LTS

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

Install PostgreSQL 17 with pgaudit Support on Ubuntu 24.04 LTS
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Here is a step-by-step guide to enable and use pgaudit (PostgreSQL Audit extension) in PostgreSQL 17 on a Linux-based system (e.g., Ubuntu 22.04/24.04):

  1. Install PostgreSQL 17 with pgaudit Support

A. Add PostgreSQL APT Repository (if not done)

sudo apt install wget gnupg lsb-release -y

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

B. Install PostgreSQL 17 and pgaudit

sudo apt update

sudo apt install postgresql-17 postgresql-17-pgaudit -y

  1. Enable pgaudit in PostgreSQL Configuration

A. Edit postgresql.conf

sudo nano /etc/postgresql/17/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

  1. Create the pgaudit Extension

Login to PostgreSQL:

sudo -u postgres psql

Create Extension:

CREATE EXTENSION pgaudit;

  1. 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

  1. 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

  1. Check Audit Logs

Logs location (default on Debian/Ubuntu):

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

Sample Audit Log:

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

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

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

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

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

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

  1. 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'

  1. Security Best Practices
  • Restrict postgresql.conf access:

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

  • Rotate logs periodically:

Configure log_rotation_age or log_rotation_size in postgresql.conf.

  1. Uninstall or Disable pgaudit

To disable without uninstalling:

shared_preload_libraries = '' # or remove 'pgaudit'
To uninstall:

DROP EXTENSION pgaudit;
sudo apt remove postgresql-17-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;,
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.

Top comments (0)