Postgres audit trails

TL;DR: Why and what options do we have to do audit trails in Postgres

Auditing is coming!

Motivation

In a very high-level view, audit trails respond to the questions of who, what and when access to the database.

Probably it’s a duty if your business has to pass an audit or it has compliance issues.

This is very relevant in sectors such as fintech and healthcare.

Besides this, I found very relevant the use of audit trails when you have no-code/low-code platforms in your organization. In this kind of tool, users are not so much tech experts who access directly internal APIs and/or databases.

Here it’s important to have a trace of the changes that they apply.

State of the Art

In most of the DBMS, there is no audit trail options out-of-box.

Some modern DBaaS like Snowflake have a good audit trails solution from the standard edition. We can consider it a low-level system and you can build on it as your solution.

In the case of the best open-source DBMS (in my humble opinion), Postgres, we have several extensions that can do the work.

It’s important to highlight that in Postgres we can enable audit logs at a very basic level.

With just a simple change in the configuration, we can audit everything. Just set the statement log_statement=all and every query will be recorded.

Testing log_statement

We start testing the default Postgres options. Just create a new container using the vanilla version

$ docker run -d --name postgres-audit-all -e POSTGRES_PASSWORD=postgres postgres

Now we are going to log in a perform the log_statement settings in the session.

$ docker exec -it postgres-audit-all bash
% su postgres
% psql

We are ready to do real stuff here.

First, check the default logging options

postgres=# show log_statement;
 log_statement  
--------------- 
 none 
(1 row)

Ok, it’s disabled by default. We set up it to log everything during the session.