Postgres Auditing in 150 lines of SQL
supabase.com
supabase.com
After a while of screwing with this kind of approach, I realized that the number of different types of objects involved was going to cause an explosion in the number of related auditing and versioning tables. Keeping track of not only who changed something but also the exact facts they changed (such that you could revert as needed) requires a lot of additional boilerplate in each instance.
So, I made a pretty big gamble and went all-in on an event-sourced abstraction where everything is written to 1 gigantic append-only log of events. Turns out, this was worth it in our case. As long as everything has a timestamp and you have a way to refer to specific log entries (i.e. by offset), you basically have a complete solution. The hard parts are handled by a JSON serializer and gzip. We use in-memory indexes that are simply long/long dictionaries that map an object identity to a physical log offset.
The only downside with what I have proposed is that there is no garbage collection or cleanup possible. At this time, this is actually a highly-desirable property of the system. Disk space is cheap. Losing 3 weeks of work to an accidental button click is not.
If you are using Kafka, you can use log compaction[0] to achieve this. It's also possible to delete individual entries with tombstone records, assuming your objects have a "key" attribute.
[0]https://kafka.apache.org/20/documentation.html#compaction
There is also a concept of "purely functional data structures" used for example by Happstack. Those are data structures where you never mutate anything, but instead do updates by allocating new nodes and pointers, so an update takes O(log n) operations since you have to reallocate the path to the root of a b-tree like structure, instead of just overwriting a cell. In principle that should still not be too bad, if the amount of data isn't huge. For whatever reason, though, happstack's performance is apparently disappointing even when you take its design into account.
The main index is a trie, which indexes revision numbers. The leaf nodes of this trie are "RevisionRootPages". Under each RevisionRootPage another trie indexes the main data. Data is addressed through dense unique and stable 64bit int nodeKeys. Furthermore, the user-defined secondary indexes currently are also stored as further tries under a RevisionRootPage.
The last layer of inner pages in a trie adds references to a predefined maximum number of data page fragments. The copy-on-write architecture does not simply copy whole data pages, but it depends on the versioning algorithm. The default is a sliding snapshot algorithm, which copies changed/inserted/deleted nodes plus nodes, which fall out of a predefined window (usually the size is low, as the page fragments have to be read from random locations in parallel to reconstruct a full page). This reduces the amount of data to store for each new revision. The inner pages of the trie (as well as the data pages) are not page-aligned, thus they might be small. Furthermore, they are compressed before writing to persistent storage.
Currently, it offers a single read-write transaction on a resource plus read-only transactions without any locks.
What the parent is referring to is the actual database schema itself. It’s a giant versioned log essentially.
Presumably there is some sort of ‘key frame’ however, or you’d need to go all the way back however many years ago to start rebuilding the objects current state?
Correct. My criteria for the snapshot feature becoming a priority will be when log recovery takes longer than 5 minutes. I cannot see that occurring anytime soon based on current figures.
In our case we opted for three types of secondary user-defined indexes (besides the main document trie index to find unique, stable nodes by their 64bit ID), namely
1. field indexes
2. path indexes
3. content-and-structure indexes which index paths and their typed values
Furthermore a path summary keeps track of all distinct paths in the JSON resource.
One problem with the cdc approach is not having a way to monitor ddl operations. However, at least in any Postgres capable database with extensions support, it’s pretty easy to either: use pg_audit or write a rudimentary extension for ddl streaming.
Do you replicate that stream to avoid SPOF?
You do end up storing a lot of extra data, but it's very thorough - it's not going to miss anything, and figuring out what happened later on should be very easy.
> -- record = {"id": 1, "name": "bar"}, old_record = {"id": 1, "name": "foo"}
> select record - old_record where id=3;
name => bar
>https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2f406630f367b5...
Has anyone done something similar with SQL only?
```
SET LOCAL foo.who_id = 'some-uuid';
UPDATE table
SET ...```
-- function to make getting the setting easier
DROP FUNCTION IF EXISTS get_who_id (text);
CREATE OR REPLACE FUNCTION get_who_id (default_value text DEFAULT null::text) RETURNS text AS $get_who_id$
DECLARE
who_id text;
BEGIN
BEGIN
who_id := current_setting('foo.who_id');
EXCEPTION
WHEN SQLSTATE '42704' THEN
RETURN default_value;
END;
IF (length(who_id) = 0) THEN
RETURN default_value;
END IF;
return who_id;
END
$get_who_id$ LANGUAGE plpgsql VOLATILE;```
CREATE OR REPLACE FUNCTION some_table_audit () RETURNS TRIGGER AS $some_table_audit$
DECLARE
who_id text;
BEGIN
who_id := get_who_id(null::text);
...Identifiers changed, but hopefully will give you the idea.
If anyone is interested give me a shout out
Thank you very much!
https://postgrest.org/en/stable/auth.html
Supabase is built on top of PostgREST, but I can’t say for sure it uses the same means of authentication (I think it has its own Auth layer).
``` alter table audit.record_version add column user_id bigint default (coalesce(record, old_record) ->> 'user_id'); ```
or if you meant the SQL `current_user` you could extend the trigger to track it.
but if the user_id is only available in application space (not in the db) it will not be possible using straight SQL
happy to answer any questions
I've built audit systems in the past which track just the new values, with the idea being that you can replay the audit log for a particular record from the beginning to reconstruct its state. I'm not convinced that's a better approach than yours here though, just interested in your thinking.
That's def a valid concern if you're auditing a high frequency insert/update table, or have your entire database under audit. If you do have those needs a system that logs outside of postgres (like pgaudit) would be a better fit.
In my experience most startups and mid-size enterprises sprinkle in auditing around the sensitive parts of the DB, like `account` or `access_control` tables where writes load isn't much of a concern.
> I've built audit systems in the past which track just the new values
Yeah, that solution works great! The one place it breaks down is if you apply auditing to an existing table, and need to be able to recover the records from when auditing was enabled (initial state is not snapshotted)
We toyed with another approach to avoiding having to track old_record by first checking to see if the `record_id` exists in the audit table, and then inserting a row with the OLD values with a `SNAPSHOT` `operation` if it does not.
Even though that query/check was operating on an index, the performance overhead was higher than we were comfortable with.
Storage is pretty cheap these days so we opted to optimize for write throughput + reduced memory usage rather than any considerations wrt disk.
> Even though that query/check was operating on an index, the performance overhead was higher than we were comfortable with.
Was this done inside the trigger function? Couldn't you just insert the SNAPSHOT values first before applying the trigger? Seems wasteful to me to track both NEW and OLD for all subsequent operations just to ensure the initial value is covered.
[0] http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simp...
For sure! There is a conversion penalty that is paid incrementally (at write time). For paying that penalty, jsonb gets you reduced storage size and dramatically faster column value extraction with `->` or `->>` if you end up having to filter the audit table by something other than the `record_id`
It is a tradeoff though and depending on the specifics of your use-case JSON could be a better choice.
How well does this work when you audit multiple tables with foreign key relationships between them?
If the user references a customer, and you want to find out what customer the user belonged to at time X (but the customer might have been deleted in the mean time), you have to track through the history of multiple tables to find answers. More if you have n2m relationships...
It sounds doable, but not trivial to get right for more complicated models.
---
Update: to clarify, I want to be able to present a somewhat homan-readable history of an object without doing much table-specific logic.
If an author can publish many books, you typically have an n2m mapping table, like book_authors. If you want to add newly published books to the "author" history view, you have to determine than you have to include parts of the history of the book_authors table. I don't think you can do that through a join though? Trying to think my way through this...
Maybe this can be dealt with easier if you can assume each table has a non-composite primary key, and include that primary key in the history table, and then you can do joins more easily on the history?
For that scenario replicating audit data to a OLAP system may be a better fit
But how could you go about not storing the diff but still being able to select it?
If you have the HSTORE extension, you can also use it to get a diff of the old and new: `HSTORE(OLD) - HSTORE(NEW)` and vice versa, very nice to easily have the deltas.
Long story short: it is avoidable if you're okay with paying the penalty of querying the audit table by `record_id` before every write. We decided to use more disk and keep the higher write throughput
Over the course of a few months we extracted the system and built a standalone application that today has become [changebase.io](https://changebase.io/). We now use CDC, change data capture, to capture all events in the database and a metadata table to capture the context of why the change occurred and who made the change. The changes are then structured to be easily queryable.
Initially, we encoded the data as JSON, but over time have opted for encoding the data in the database’s native format. This creates a more complex query system, but at the end of the day we don’t have to worry about type conversions and querying is done in the same format the database that was replicated. Teams have benefited from the system by being able to recover data that was accidentally deleted, or deleted maliciously. Notifications have also been used for high value datasets where someone on a sales team wants to immediately know of a change in the system.
Which may be fine for your use case, don't change anything if it works for you.
In my use case, I also have a requirement that a user needs to be able to revert changes - an undo function, for edits and a 'deleted' state. I've been looking into Temporal Tables for that, where every table has a sibling 'history' table with a pair of timestamps - valid from and valid to. This allows you to query the database - including related rows in other tables - at a certain point in time, and to revert to a previous state without losing any intermediate steps (copy rows at timestamp X, insert as the 'current' version).
This is a built-in feature in a lot of modern enterprisey databases, but I'm constrained to sqlite. But, using triggers I've been able to make it work in a POC.
Whether it'll work in my 'real' application is yet to be determined, I've got a lot of work to do still - I need to get rid of my ORM and redo my data access to something more manual, unfortunately.
> select audit.enable_tracking('public.members');
So you can actually have functions that have side effects in a select statement? I guess nothing prevents it, so it is allowed. But somehow I have the impression that select statements don't change anything.
(DON'T ACTUALLY DO THIS on anything other than a personal test db as it will kill all the connections it has permission to kill)
Related, postgres has a number of different volatility options for functions so you can declare if there are side effects: https://www.postgresql.org/docs/14/xfunc-volatility.html These can become very important in some cases to let the optimizer have the freedom to shine.
I think you can use a standalone VALUES statement to avoid side effects? Those aren't well supported even though it's part of the SQL spec, but postgres does have them.
Datomic: https://www.datomic.com/
I'm working on a database system[1] in my spare time, which automatically retains all revisions and assignes revision timestamps during commits (single timestamp in the RevisionRootPage). Furthermore, it is tamper proof and the whole storage can be verified by comparing a single UberPage hash as in ZFS.
Basically it is a persistent trie-based revision index (plus document and secondary indexes) mapped to durable storage, a simple log-structured append-only file. A second file tracks revision offsets to provide binary search on an in-memory map of timestamps. As the root of the tree is atomically swapped it does not need a WAL, which basically is another data file and can be omitted in this case.
Besides versioning the data itself in a binary encoding similar to BSON it tracks changes and writes simple JSON diff files for each new revision.
The data pages are furthermore not simply copied on write, but a sliding snapshot algorithm makes sure, that only changed records mainly have to be written. Before the page fragments are written on durable storage they are furthermore compressed and in the future might be encrypted.
I haven't explored sirix before, but at first glance it looks like we have some similar ideas — thanks for sharing, I'm excited to learn more, especially about its application of ZFS.
[0] https://www.splitgraph.com/docs/working-with-data/tracking-c...
[1] https://www.splitgraph.com/docs/concepts/objects
[2] https://github.com/splitgraph/splitgraph/blob/master/splitgr...
In ZFS the UberPage is called UberBlock. We borrowed some of the concepts as to add checksums in the parent pages instead of the pages itself. In ZFS they are blocks :-)
Thanks for asking.
Of course we'd need more man-power as I'm more or less the only one working on the core in my spare time (since 2012).
Moshe mainly works on the clients and a frontend. A new frontend based on SolidJS is in the works showing the history and diffs as in
https://raw.githubusercontent.com/sirixdb/sirix/master/Scree...
However, we're of course looking forward to suggestions, bug reports, real world use cases and contributions :-)
Now days it's far better to look at tooling outside and around Postgres to give you that. Under the covers WAL is effectively this, so with tools like backrest you can maintain backups and WAL forever then have ability to restore and old point as you see fit. On managed service providers a lot of them give this out of the box though only for a limited period of time.
Only problem is that there's no way to delete historical data without DDL operations, which you need if a user demands to exercise their GDPR rights.
https://github.com/cmabastar/audit-trigger
It uses the old and tested http://wiki.postgresql.org/wiki/Audit_trigger_91plus
but with JSONB instead of HSTORE, and automatically creates partitioned tables by month (Requires PG11+).
Since there is no redo logs generated for data read, CDC could not help in this case. It will reply on the audit traces, the SQL capture and cache, etc. But it's costly.
This is not the case in most production for two major reasons:
1. Row-level security is expensive and hard to manage, so in most cases, users are granted the select privilege on table level. But indeed, it doesn't mean they should have access to all the rows in that table.
2. Power users should not have production accounts all the time. The production accounts should only be activated when power users have to perform the changes during the production change window.
So financial institutes usually generate two kinds of reports for audit/regulation purposes.
1. Report on the privileges and roles, which tells if a user should have those privileges and roles. This is a periodically static report.
2. Report on the actions a user has performed to see if these actions are part of the changes. This is a dynamic report in which we have to capture the select statements.