How to audit query history in PostgreSQL

Knowing what queries have run against your database — and who ran them — is important for security, debugging, and compliance. PostgreSQL provides several tools for this, each with different trade-offs.

Option 1: pg_stat_statements

pg_stat_statements is the most commonly used approach. It tracks aggregate statistics for every distinct query that has been executed, including how many times each query ran, total execution time, and rows returned.

Enable it

Add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Then restart PostgreSQL and run:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query the stats

SELECT
  query,
  calls,
  total_exec_time,
  rows,
  mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This shows your most expensive queries by total cumulative time.

Limitations

pg_stat_statements is an aggregate view — it normalises query parameters and combines identical query patterns. It does not tell you:

Which user ran a specific query at a specific time
The exact parameter values used
Whether a query read a specific row

For that, you need logging or pgaudit.

Option 2: log_statement

PostgreSQL can write every query to its log file. This is the simplest audit trail.

Configure it

In postgresql.conf:

log_statement = 'all'          -- log every query
log_min_duration_statement = 0 -- also log execution time
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

%t — timestamp
%u — user name
%d — database name
%p — process ID

Reload:

SELECT pg_reload_conf();

What it produces

Your PostgreSQL log will contain entries like:

2024-01-15 14:32:01 UTC [1234]: user=byesql_reader,db=mydb LOG:
  statement: SELECT count(*) FROM orders WHERE status = 'pending'

Limitations

Log files grow fast. On a busy database, log_statement = 'all' can produce gigabytes of logs per day. Consider:

log_statement = 'mod' — log only INSERT, UPDATE, DELETE, DDL (not SELECT)
log_min_duration_statement = 1000 — log only queries slower than 1 second

Neither is appropriate for full SELECT auditing — use pgaudit for that.

Option 3: pgaudit

pgaudit is a PostgreSQL extension specifically designed for audit logging. It gives you fine-grained, per-object logging that is compliant with standards like SOC 2 and ISO 27001.

Install

On Ubuntu/Debian:

sudo apt-get install postgresql-16-pgaudit

On RHEL/CentOS:

sudo yum install pgaudit16_16

Configure

In postgresql.conf:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read, write'  -- audit SELECT and DML
pgaudit.log_catalog = off    -- reduce noise from catalog queries
pgaudit.log_relation = on    -- log which table each query touches

After reloading:

CREATE EXTENSION IF NOT EXISTS pgaudit;

Per-user auditing

pgaudit can be scoped to a specific role:

ALTER ROLE byesql_reader SET pgaudit.log = 'read';

Now every SELECT run by byesql_reader will be logged with the object name, operation, and timestamp.

Sample audit log entry

AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.orders,
SELECT * FROM orders WHERE customer_id = 42,<none>

Comparing the options

	pg_stat_statements	log_statement	pgaudit
Per-query timestamp	No	Yes	Yes
Which user	No	Yes	Yes
Which table	No	No	Yes
Parameter values	No	Yes	Yes
Performance impact	Low	Medium–High	Medium
Setup complexity	Low	Low	Medium
Compliance use	No	Partial	Yes

Practical recommendation

For most teams:

Enable pg_stat_statements always — it’s low overhead and useful for performance debugging
Set log_statement = 'mod' to capture writes without overwhelming your log storage
If you have compliance requirements (SOC 2, PCI, HIPAA), add pgaudit

If you only want to audit queries coming from a specific tool or user, apply pgaudit or log_statement at the role level rather than globally.