Skip to content

📚 PostgreSQL DB Audit

🔍 Prepare：

## Preparing the Master and Slave Server：

Environment name Version IP Folder
OS Rocky Linux 9.x
DB PostgreSQL 16.X 10.38.36.110 /pgdata/SID

1. Used log parameter to enable audit

  • 1.1 enable logging_collector to on
psql -p 5435

show logging_collector ;

alt text

  • 1.2 Enable log parameter for Role/User（Example audit postgres user）
psql -p 5435

ALTER ROLE postgres SET log_min_duration_statement TO '0';
ALTER ROLE postgres SET log_statement To 'all';
SELECT r.rolname AS role_name,d.datname AS database_name,s.setconfig FROM pg_db_role_setting AS s LEFT JOIN pg_roles AS r ON s.setrole = r.oid LEFT JOIN pg_database AS d ON s.setdatabase = d.oid;

alt text

  • 1.3 Make sure record to log（Example audit postgres user）
psql -p 5435

create table t;

alt text

alt text

2. Upload to log server

  • 2.1 Filter information from logs（Filter the logs of the previous hour at every hourly point）
su - postgres
crontab -l
############Postgres Filter Audit Log###############
0 * * * * /usr/bin/awk '/^[0-9]{4}-[0-9]{2}-[0-9]{2}/ {if (line!="") print line; line=$0; next} {line=line" "$0} END {print line}' /pgdata/Clustername/log/postgresql-`date -d "1 hour ago" "+\%a\%H"`.log |grep -E "user = postgres ,.*statement:" > /pgdata/Clustername/log/postgresql-`date -d "1 hour ago" "+\%a\%H"`_audit.log

alt text

alt text

alt text

  • 2.2 Install filebeat
rpm -ivh /tmp/filebeat-6.0.1-x86_64.rpm

alt text

  • 2.3 Modify parameter
vim /etc/filebeat/filebeat.yml

Change enabled to true

alt text

Add Log file PATH

alt text

Change include_lines

alt text

Remark Elasticsearch output

alt text

Change Logstash output

alt text

  • 2.4 Modify parameter
systemctl start filebeat
systemctl enable filebeat
systemctl status filebeat

3. Example on log server

alt text