auditing in postgresql

Tracking any changes made at the database level is crucial for holding accountable any users with access to PostgreSQL. 

During my experience as a database administrator, I encountered two situations where applications reported data loss. Upon investigation, we discovered that a table had been truncated. Unfortunately, we couldn’t confirm the source of the issue because auditing was not enabled on the database. This highlights the importance of tracking all activities at the database level.

PostgreSQL supports auditing, and enabling it is relatively straightforward compared to other RDBMS systems.

Installing and configuring pgaudit

1. check if pgaudit is available in your repository 

sudo apt list | grep -i pgaudit

Since pgaudit is not installed on our server, we will proceed with the installation. As we are using PostgreSQL 15, I will install the appropriate version of pgaudit for PostgreSQL 15.postgresql-15-pgaudit

sudo apt install postgresql-15-pgaudit

2. Update shared library to include pgaudit
   open postgresql.conf located in etc/postgresql/15/main

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

update the parameter shared_preload_libraries to include pgaudit

3. Restart postgresql to get changes applied

sudo systemctl restart [email protected]

4. login to psql and Create the pgaudit extension

sudo -u postgres psql

create extension pgaudit; \dx

5. Verify the pgaudit parameters.

show pgaudit.log

the output command show that pgaudit.log  none meaning pgaudit is not tracking anything 

6. configure pgaudit to track activity as follow

• 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.
  for our case we will enable read,write,DDL

alter system set pgaudit.log to read,write,DDL;

7. Restart postgresql services 

sudo systemctl restart [email protected]

8. verify that configuration applied on pgaudti

show pgaudit.log;

9. Test auditing by performing DDL commands or select command 

check log in postgresql located in cat /var/log/postgresql/postgresql-15-main.log