This content originally appeared on DEV Community and was authored by Chitt Ranjan Mahto (Chirag)
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:
- Install PostgreSQL 16 with pgaudit Support
sudo apt update
sudo apt install postgresql-16 postgresql-16-pgaudit -y
- 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
- Create the pgaudit Extension
Login to PostgreSQL:
sudo -u postgres psql
Create Extension:
CREATE EXTENSION pgaudit;
- 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
- 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
- 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;,
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);",
- 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'
- 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.
- 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;,
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"
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
This content originally appeared on DEV Community and was authored by Chitt Ranjan Mahto (Chirag)
Chitt Ranjan Mahto (Chirag) | Sciencx (2025-07-12T02:12:27+00:00) Install PostgreSQL 16 with pgaudit Support on Ubuntu 24.04 LTS. Retrieved from https://www.scien.cx/2025/07/12/install-postgresql-16-with-pgaudit-support-on-ubuntu-24-04-lts/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.