PostgreSQL audit logging
per user for GDPR

What PostgreSQL logs without pg_audit

Out of the box, PostgreSQL logs connection events and server errors. Individual queries are not logged unless log_statement is set. Even with log_statement = 'all', the default log line looks like this:

2024-01-15 14:23:47 UTC [18392] app_user@production LOG:  statement: SELECT id, email FROM users WHERE id = 4892

The role shown is app_user — a shared application credential used by all engineers connecting to production. There is no information about which human ran this query. Under GDPR, this log is non-compliant: it records what happened, but not who did it.

Installing and configuring pg_audit

pg_audit is a PostgreSQL extension maintained by the pgAudit project. It must be loaded at server start — you cannot enable it via SQL alone after the server is running.

Step 1 — Install the extension package

# Debian/Ubuntu
sudo apt install postgresql-16-pgaudit

# RHEL/Rocky Linux (PGDG repo)
sudo dnf install pgaudit_16

# macOS — no Homebrew formula; compile from source
git clone https://github.com/pgaudit/pgaudit.git && cd pgaudit
make install USE_PGXS=1 PG_CONFIG=$(pg_config --bindir)/pg_config

Step 2 — Add to shared_preload_libraries

Edit postgresql.conf or use ALTER SYSTEM. A server restart is required.

-- In psql (requires superuser)
ALTER SYSTEM SET shared_preload_libraries = 'pgaudit';

-- Then restart PostgreSQL, then create the extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

Step 3 — Configure audit parameters

-- Log reads and writes on all relations
ALTER SYSTEM SET pgaudit.log = 'read,write,ddl';

-- Log the specific relation (table/view) for each statement
ALTER SYSTEM SET pgaudit.log_relation = 'on';

-- Include bind parameters (required for GDPR data subject queries)
ALTER SYSTEM SET pgaudit.log_parameter = 'on';

SELECT pg_reload_conf();

-- Verify settings are active
SHOW pgaudit.log;  -- read,write,ddl

Per-role configuration (recommended)

If you have per-operator database roles, configure auditing at the role level to avoid logging high-volume application read traffic:

-- Create per-operator roles
CREATE ROLE engineer_alice LOGIN PASSWORD '...';

-- Enable auditing only for operator roles
ALTER ROLE engineer_alice SET pgaudit.log = 'read,write';
ALTER ROLE engineer_alice SET pgaudit.log_relation = 'on';

The pg_audit log format

A pg_audit log entry consists of the standard PostgreSQL log line prefix followed by the AUDIT: marker and nine comma-separated fields. Here is a real example:

2024-01-15 14:23:47 UTC [18392] app_user@production LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.users,"SELECT id, email, last_login FROM users WHERE id = 4892",<not logged>

The shared role problem

Most production applications connect via a single shared role — app_user, postgres, or a service account. When an engineer connects to production via a database GUI, psql, or an internal tool, they authenticate as that shared role. Every pg_audit log line shows the same role regardless of who ran the query.

Workaround: SET LOCAL session variable

The most common workaround is to inject the human identity as a PostgreSQL session variable at the start of each transaction:

-- Your application wraps every DB operation:
BEGIN;
SET LOCAL app.current_user = 'alice@company.com';
SELECT id, email, last_login FROM users WHERE id = $1;
COMMIT;

-- pg_audit logs the query under app_user.
-- Your application log must separately record
-- that alice@company.com initiated this session.

This approach has two compliance problems. First, the app.current_user value is set by application code — it is not verified by the database. Any code with database access can write any value. Second, the correlation between the session variable and the pg_audit log line lives in two separate log streams. A complete audit trail requires joining them, which introduces both technical complexity and gaps.

Approach comparison

All approaches measured against the three GDPR Article 32 requirements that matter for a supervisory authority inquiry.

Two structural gaps pg_audit cannot close

How Scalple closes both gaps

Scalple operates as a database access layer — engineers authenticate to Scalple with their individual identity (SSO, MFA), not to PostgreSQL directly. The database role is managed by Scalple, never exposed to the engineer. Every operation is logged in an INSERT-only audit table before the query executes. The audit schema revokes DELETE and UPDATE at the database level — no superuser can modify entries. The log shows the verified human identity, the exact query, and a tamper-evident timestamp. When your DPA asks for every query Alice ran on January 15, you export it in under 30 seconds.

-- Scalple audit log (INSERT-only, human-attributed)
SELECT
  audit_time,
  human_identity,  -- alice@company.com, not app_user
  command,
  object_schema,
  object_name,
  statement,
  row_count,
  duration_ms
FROM scalple_audit_log
WHERE human_identity = 'alice@company.com'
  AND audit_time::date = '2024-01-15'
ORDER BY audit_time;

PostgreSQL audit logging — frequently asked questions

Other databases