TL;DR: Why and what options do we have to do audit trails in Postgres
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.
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
We are ready to do real stuff here.
First, check the default logging options
postgres=# show log_statement;
Ok, it’s disabled by default. We set up it to log everything during the session.