5mins of Postgres E8: Postgres Auditing: Table triggers with supabase vs the pgAudit extension
Today, we'll take a look at Postgres auditing. We'll look at two different approaches:
- Using triggers in Postgres
- Using the pgAudit extension
Share this episode: Click here to post to twitter, or sign up for our newsletter and check out the newsletter archive and subscribe to our YouTube channel.
Transcript
Let's jump in!
Using triggers in Postgres for Postgres Auditing
Today’s episode was inspired by a blog post by Oliver Rice on the supabase blog, where he talks about Postgres auditing in 150 lines of SQL.
He’s describing how they want to audit changes to this table. They come to a solution that has a very simple API where you say audit.enable_tracking, and you tell it which table you want to track.
When you then do activity on the table, like an insert, update and delete, that gives you a record history in that audit.record_history table. You can go to Oliver’s article and you can see they did these changes and there was the old record and then there was the new record. That happens automatically once you enable a table for auditing.
They have this audit record version table and they use a BRIN index here. BRIN indexes are great if you have a particular ordering in your table, for example an audit table, most likely you're going to keep adding new records at the end assuming you don't delete old records.
BRIN indexes are great if you have a particular ordering in your table
So in their case, a BRIN index is very efficient because a BRIN index helps Postgres locate the relevant parts of a table when doing a search for a timestamp. They also added a B-tree index on the table_oid which helps them locate a particular table's history.
The way that they've implemented this, and this is really the key part of it, is that they've used a PL/pgSQL function that is defined as a trigger on the table. What it does is, when a record changes in a particular table that you are auditing, then it does an insert into this audit.record_version table. Pretty simple approach, nothing magical going on here.
If you've never seen TG_OP and TG_RELID and such, these are magic constants that are defined when you define a trigger in PL/pgSQL. As Oliver showed earlier in his post, they have this audit.enable_tracking API and it attaches the trigger to particular tables.
Before insert or update or delete, this trigger activates and calls the insert_update_delete_trigger function, which then ends up inserting a record into the audit table.
This approach is great and you can use their supa_audit extension, which they released, which packages this approach using triggers in Postgres.
The one important caveat here is that triggers on the table have their own overhead. If you're doing high concurrent writes then you will notice that the trigger overhead is a problem.
Using the pgAudit extension for Postgres Auditing
Oliver recommends pgAudit in his article. PgAudit takes a very different approach.
pgAudit is an extension – it's been around for a while – certainly something I would use in production if I need auditing. The supabase extension is about writing things to the database itself, versus the pgAudit extension is about logging to your Postgres log files, and then you can retain these log files however long you want. The pgAudit extension doesn't do anything beyond outputting things to the Postgres log.
Oliver gives this example where pgAudit is doing a DDL, but it's doing it in a non-obvious way. If you just used log_statement, then you might miss the fact that this statement was actually doing DDL, it was creating a table!
The pgAudit extension has a specific method to track DDL changes to your database. Then you get this in your Postgres logs, which you can then analyze and say: oh, somebody created a table or somebody executed DDL.
Really important to know with pgAudit is that pgAudit can generate an enormous volume of logging. It's even worse, in a sense, than what the supa_audit extension tries to do. If you just turn on pgAudit and you use the simple settings and just turn everything on, then you will have so much activity, it's going to slow down your database, it’s going to run you out of disk. It's a really bad idea to just turn on everything in terms of auditing here!
That's why it's really important to configure how much you're logging. pgAudit does have a few mechanisms to control that. If you look at the supa_audit extension, it works on a per table basis.
I didn't realize that for a long time, but pgAudit can also work on a per table basis, but the way it does it is not obvious. There are a few things in pgAudit that are controlled using the Postgres configuration system. You tell it what to log and you can tell it: log all SELECT statements or log all DELETE statements and it lets you control what type of statements to log, but there is no setting in pgAudit that says "log this table".
Instead, what pgAudit does is it looks at which tables it has access to. If the statement that's being executed is on a table that it has access to, using the Postgres role system, then it logs the audit log for a table, but if it doesn't have access, then it doesn't log that.
Generally, I would say, the trigger based approach that supabase described is easier to manage. At a small scale it is a bit more convenient. I would say pgAudit is the more reliable solution, but it does require a little bit of fine tuning with the Postgres settings as well as the Postgres grant system to make full use of it.
Thank you so much for listening. This was 5mins of Postgres. Subscribe to our YouTube channel to hear about next episodes and talk to you next week.