Auditing: pg_audit, Log Analysis & Incident Reporting

Why Database Auditing Matters

A database audit trail is a chronological record of who did what to which data, and when. Without it, a security incident cannot be properly investigated, and compliance with frameworks such as HIPAA, PCI-DSS, and SOX cannot be demonstrated. PostgreSQL does not capture fine-grained object-level events by default, so the pgaudit extension was created to fill that gap. Enabling pgaudit is one of the first hardening steps a DBA should take on any production instance that holds sensitive data.

How pgaudit Works

pgaudit hooks into PostgreSQL's standard logging infrastructure. It adds two layers of auditing:

• Session auditing – records every SQL statement executed in a session according to the log categories you configure.
• Object auditing – records statements that touch specific tables, views, sequences, or functions based on role-level privileges granted to a special audit role.

Configuration happens in `postgresql.conf` (or `ALTER SYSTEM`) using the `pgaudit.log` parameter. Common category values include `READ`, `WRITE`, `DDL`, `ROLE`, `FUNCTION`, and `MISC`. You may combine them as a comma-separated string, for example:

```

shared_preload_libraries = 'pgaudit'

pgaudit.log = 'role, ddl, write'

```

After changing `shared_preload_libraries` you must restart the PostgreSQL service. Changes to `pgaudit.log` alone can be reloaded with `SELECT pg_reload_conf();`.

Audit records appear in the standard PostgreSQL log file alongside other server messages. Each audit line is prefixed with `AUDIT:` and includes the session ID, audit type (SESSION or OBJECT), statement class, command tag, and the SQL text. This means your log management pipeline (rotation, shipping to a SIEM) automatically captures audit events without a separate storage mechanism.

Installing and Enabling pgaudit

On most Linux distributions, pgaudit is available through the PostgreSQL PGDG package repository. Install the package that matches your PostgreSQL major version (e.g., `pgaudit16` for PostgreSQL 16), then add it to `shared_preload_libraries` and restart. Verify the extension is active by running:

```sql

CREATE EXTENSION IF NOT EXISTS pgaudit;

SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

```

For object auditing, create a dedicated role with no login privilege and grant it SELECT or other privileges on the objects you want monitored. Then set `pgaudit.role = 'your_audit_role'` in the server configuration.

Reading and Querying Audit Logs

PostgreSQL writes logs as text files by default. Location is controlled by `log_directory` and `log_filename` in `postgresql.conf`. When `log_destination` includes `csvlog`, each line is parseable with standard tools. You can also load CSV logs into a table using `COPY` for SQL-based analysis:

```sql

CREATE TABLE pg_log_import (

log_time timestamptz, user_name text, database_name text,

process_id int, connection_from text, session_id text,

session_line_num bigint, command_tag text, session_start_time timestamptz,

virtual_transaction_id text, transaction_id bigint, error_severity text,

sql_state_code text, message text, detail text, hint text,

internal_query text, internal_query_pos int, context text,

query text, query_pos int, location text, application_name text

);

COPY pg_log_import FROM '/var/log/postgresql/postgresql.csv' CSV;

```

Once imported, filter for audit records:

```sql

SELECT log_time, user_name, message

FROM pg_log_import

WHERE message LIKE 'AUDIT:%'

ORDER BY log_time;

```

Recognizing Attack Patterns with STRIDE

When analyzing a log file for a security incident, organize your findings using the STRIDE threat model:

• Spoofing – authentication attempts using another user's identity (repeated failed logins).
• Tampering – unauthorized `UPDATE`, `DELETE`, or DDL on protected tables.
• Repudiation – absence of audit records that should exist (gaps in the log).
• Information Disclosure – unexpected `SELECT` on sensitive tables, especially outside business hours.
• Denial of Service – large queries or lock contention that degrade availability.
• Elevation of Privilege – `GRANT`, role membership changes, or `SET ROLE` to a more powerful role.

Classify each suspicious event by STRIDE category, then assign a severity (Critical, High, Medium, Low) based on the sensitivity of the affected object and the likelihood of harm.

Writing a Structured Incident Report

An incident report converts raw log evidence into a document that managers, auditors, and legal counsel can act on. A minimal one-page report includes:

1. Date and time of the detected event (use the database server's timezone and note it).

2. Affected objects – schema, table, or function names.

3. Account involved – database role and, if known, the OS or application account behind it.

4. Evidence from the log – paste exact log lines; do not paraphrase.

5. Recommended remediation – specific, actionable steps such as revoking a privilege, resetting credentials, or enabling row-level security.

Always cite the exact log line (including session ID and timestamp) so findings can be verified independently. Vague statements like "suspicious activity was observed" are not sufficient.

Safety and Professional Responsibility

Audit logs may contain personally identifiable information (PII) and confidential business data. Handle log excerpts with the same care as the production data itself: store them on encrypted media, share them only with authorized personnel, and destroy copies securely when the investigation is closed. Never share raw logs in a public forum or upload them to untrusted analysis tools. When working in a lab environment, use only synthetic or anonymized data so no real user information is exposed during exercises.