You know from my blogs that I am a PostgreSQL addict. If there is one thing that made my DBA days easier, was the mighty PGAudit extension. With pgaudit we can have detailed session and/or object audit logging.
Could expand more on why audit logs are essential under certain circumstances like government, financial, or ISO certification audits but my focus would be more on operations.
Imagine wanting to perform certain database migrations. You can switch traffic to a read replica which you shall promote, but this has the drawback of failed writes. How could you identify which time of day statistically has mainly reads and little or no writes? This is where the aggregation of the PGAudit logs would give you the answer.
Another example is cpu spikes building up during the day and you want to check the query patterns and see any correlation.
Furtermore you want to have a list of read queries that could be cached. By having PGAudit enabled you can identify the frequency of those queries and then decide how you can maximize the caching impact by picking the right queries.
Could go on and on. Overall PGAudit can do wonders.
So let’s get started.
We need to have a PostgreSQL installation with the extension enabled.
Debian already has a package for PGAudit available. In other cases you need to build the package on your own. Instructions can be found on the official guide.
We use a debian based docker image thus apt-get will do the work
FROM postgres:17 USER root RUN apt-get update; apt-get install postgresql-17-pgaudit -y USER postgres
Since PGAudit is installed we can create a custom postgresql configuration enabling it.
listen_addresses = '*' port = 5432 max_connections = 20 shared_buffers = 128MB temp_buffers = 8MB work_mem = 4MB max_wal_senders = 3 shared_preload_libraries = 'pgaudit' pgaudit.log = 'all' pgaudit.log_catalog = 'off' pgaudit.log_parameter = 'off' pgaudit.log_statement_once = 'on'
Let’s put them all together into a docker compose file
version: '3.1'
services:
postgres:
build: ./
restart: always
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
volumes:
- ./postgresql.conf:/etc/postgresql/postgresql.conf
command:
- "-c"
- "config_file=/etc/postgresql/postgresql.conf"
ports:
- 5432:5432
To run issue
docker compose up
Docker Compose V2 is out there with many good features, you can find more about it on the book I authored:
A Developer’s Essential Guide to Docker Compose.
No we can execute some queries and see how they are logged.
$ docker compose exec -it postgres bash
# psql
postgres=# SELECT 1;
?column?
----------
1
(1 row)
If we check the container logs, we shall see an audit trail of the queries we executed.
postgres-1 | 2025-09-18 23:33:05.166 GMT [271] LOG: AUDIT: SESSION,1,1,READ,SELECT,,,SELECT 1,<not logged>
Now this looks amazing however there are certain things to take into consideration.
Costs
Audit logs are expensive because of their volume. Before enabling them on production you need to make sure they are stored in a cost effective form of storage.
Resources
They do consume resources from your PostgreSQL instance. Size up your resources carefully.
Caution on Parameters
Do not log the parameters and if you do, understand that you log the actual data contained in the database. Logging parameters and storing audit logs in a widely access storage likely results in a data leak. Avoid logging the parameters. If you do so have a good reason and also a proper place to store them that satisfies the security needs.
Prevent Data Leaks
If your application is written badly and the queries do not use parameterized statements the audit logs will lead to dataleaks. All queries should be parameterized and should avoid any hardcoded string inside.
We have this table
CREATE TABLE table_name (
id SERIAL PRIMARY KEY,
sensitive_field TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
This is a bad query
SELECT *FROM table_name WHERE sensitive_field='data-leak';
Leads to leaking the sensitive field value to the terminal
postgres-1 | 2025-09-18 23:44:53.953 GMT [271] STATEMENT: SELECT *FROM table_name WHERE sensitive_field='data-leak';
Instead you can use a parameterized query, just like the following jdbc example
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM table_name WHERE sensitive_field = ?")) {
// Parameterized query - safe from SQL injection
stmt.setString(1, "data-leak");
In the logs the information is not leaked.
postgres-1 | 2025-09-19 07:20:39.531 GMT [63] LOG: AUDIT: SESSION,3,1,READ,SELECT,,,SELECT * FROM table_name WHERE sensitive_field = $1,<not logged>
That’s it. Let’s enjoy PostgreSQL that amazing piece of technology and handle with care.
On a previous blog we 
Emmanouil Gkatziouras 