PostgreSQL Audit Integration

Overview

This integration collects database authentication events, query audit logs, privilege changes, and DDL operations from PostgreSQL servers using the pgaudit extension. Database audit logging is a legal requirement under ISMS-P section 2.5.4.

Supported versions: PostgreSQL 14, 15, 16, 17

---

Prerequisites

• A KYRA MDR Collector installed and running (Installation Guide)
• PostgreSQL superuser access
• The pgaudit extension available (included in most distributions)
• Network connectivity from the database server to the collector on port 514

---

Configuration

Step 1: Install and Enable pgaudit

CREATE EXTENSION pgaudit;

Add to postgresql.conf:

pgaudit.log = 'read, write, ddl, role'
pgaudit.log_catalog = off
pgaudit.log_level = log
pgaudit.log_statement_once = on

Step 2: Configure Syslog Output

# postgresql.conf
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

Step 3: Enable Connection Logging

# postgresql.conf
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %u@%d from %h '

Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

---

Collected Log Types

Log Type	Security Use	Priority
Authentication failure	Brute force detection	High
Privilege escalation	ALTER ROLE ... SUPERUSER detection	Critical
DDL changes	DROP, ALTER, TRUNCATE monitoring	High
Read/write queries	Data access auditing (ISMS-P)	High
New IP connections	Unknown host access detection	Medium
Extension installation	Malicious extension detection	High
Connection events	Access pattern monitoring	Medium

---

Troubleshooting

No Audit Logs

1. Verify pgaudit is installed: SELECT * FROM pg_extension WHERE extname = 'pgaudit';
2. Confirm pgaudit.log is set in postgresql.conf
3. Check that log_destination is set to syslog and rsyslog is forwarding

High Log Volume

Reduce volume by narrowing the audit scope:

pgaudit.log = 'ddl, role'  # DDL and role changes only

For additional help, contact kyra@seekerslab.com.