9 ms·

Postgres Auditing in 150 lines of SQL

313 points by oliverrice 5y ago

waratuman 5y ago
At 42Floors, we experienced a similar need for auditing. Initial we did a similar approach as described in the post. Over time the audit tables began to be incorporated into every aspect of the product, which over time caused the table to become quite large, and ended up causing a lot of downtime. Our initial implementation only audited what was inserted by our Rails application. If a change was made by another application or manually, it wouldn’t appear in the table. 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/ 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.
jph 5y ago
The post leads to a good Supabase Postgres auditing tool as a PG extension named supa_audit: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit
oliverrice 5y ago
author here (of the blog post and https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit) happy to answer any questions
- simonw 5y ago
  Are you at all worried about the size of the audit table? Keeping a JSON copy of both the old and the new data is going to add up pretty quickly for fast-changing tables. 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.
  - craigkerstiens 5y ago
    Building a bit off Simon's above question. I'm curious if any thought was given to using JSON over JSONB. The trade off of really fast ability to ingest them and save them seems like it could be better than JSONB which has to do some conversion and if there are any indexes on the JSONB columns could really dramatically slow down throughput.
    - oliverrice 5y ago
      > I'm curious if any thought was given to using JSON over JSONB 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.
  - oliverrice 5y ago
    > Are you at all worried about the size of the audit table? Keeping a JSON copy of both the old and the new data is going to add up pretty quickly for fast-changing tables. 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.
- simonw 5y ago
  I'm not completely clear on the reason for both record_id and old_record_id - is this so that you can capture instances where one of the columns that makes up the primary key for the row is itself modified for some reason?
  - oliverrice 5y ago
    Being able to see when the primary key changes using `record_id` and `old_record_id` is a perk, but we also wanted to make sure a snapshot of the existing data was available when auditing is enabled on a table that already has data in it. See comment ^ for more color 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
- netcraft 5y ago
  this is great! I've done something similar many times, but the ability to do it on arbitrary tables is really nice. 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.
- perlgeek 5y ago
  First of all, this looks super awesome, and at least an order of magnitude better than our home-grown solution (grown over 25+ years on a DB that is now mariadb). 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?
  - oliverrice 5y ago
    All the data would be available and joining it would be possible with some jsonb gymnastics but its optimized more for compliance style auditing than rapid lookups for complex queries. For that scenario replicating audit data to a OLAP system may be a better fit
- efxhoy 5y ago
  Really nice post! A possible addition could be showing the diff as its own field. This seems like a sane approach: https://stackoverflow.com/a/36043269 https://stackoverflow.com/a/36043269 But how could you go about not storing the diff but still being able to select it?
- oauea 5y ago
  Could you explain how this compares with https://wiki.postgresql.org/wiki/Audit_trigger_91plus https://wiki.postgresql.org/wiki/Audit_trigger_91plus / https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql https://github.com/2ndQuadrant/audit-trigger/blob/master/aud...?
simonw 5y ago
I've battled this problem many times over my career in many different ways - this approach looks very solid to me. I particularly like the way the schema design incorporates a record ID, old record ID, updated record and old record. 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.
craigkerstiens 5y ago
Love the detail and approach here, though (and it is mentioned in the bottom of the article) this shouldn't be used at any very large scale... the estimate of at about 1k transactions per second or less seems about right. For any larger scale you want to look at something like pg_audit which can scale much much further and be used with other extensions to ensure you have a full audit log that can't be modified (when dealing with compliance).
lichtenberger 5y ago
I'd argue that's still a lot of work to manually do. However, great work and detail, thanks a lot :-) 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. [1] https://sirix.io https://sirix.io | https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix
- jmalicki 5y ago
  Every google hit for "UberPage" seems to be your writing, is there standard ZFS terminology that would be found in ZFS documentation to what you're referring?
  - lichtenberger 5y ago
    It's in our case the tree root of the index, which is always written after all the descendant pages have been written as a new revision is committed (during a postorder traversal of the new pages). 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.
- lichtenberger 5y ago
  In order to provide fast audits of subtrees, the system stores optionally a merkle hash tree (a hash in each node) and updates the hashes for all ancestors automatically during updates.
raimille1 5y ago
This is a great technique, we used to have it setup very similarly at a previous startup and worked wonders. Thanks for sharing a generic way of doing it @oliverrice !
ithrow 5y ago
Debezium can be used for this in embedded mode if you are on the JVM.
- johndfsgdgdfg 5y ago
  Can you please elaborate? Last time I checked it needed Kafka to work.
  - ithrow 5y ago
    https://debezium.io/documentation/reference/1.9/development/engine.html https://debezium.io/documentation/reference/1.9/development/...
bob1029 5y ago
We have a new business system we are trying to build that has a lot of very granular auditing and versioning requirements throughout, and this is where I started looking. 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.
- lichtenberger 5y ago
  As posted in another comment that's basically what https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix supports along with easily reconstructing former revisions of a JSON document (or any node therein), sophisticated secondary (also versioned) indexes, querying with JSONiq and optimizations at query compile time as for joins and aggregates.... 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.
- infogulch 5y ago
  I've done this before on smaller scales. Writing a set of table-valued functions that expose the event data in relational form "as of date given by param X", and views on top of that where X is set to getdate(), you get a very ergonomic way to consume "current" & "historical" data. Materialized views can make querying these more natural relations very cheap if your engine supports them.
sususu 5y ago
I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I cannot see how to do that with a pure SQL solution. Has anyone done something similar with SQL only?
- oliverrice 5y ago
  if `user_id` is in the table you're auditing you could do ``` 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
- timwis 5y ago
  PostgREST has an interesting approach to authentication that might give you some ideas: https://postgrest.org/en/stable/auth.html 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).
  - oliverrice 5y ago
    Supabase passes all the JWT info to postgrest so it is still available in transaction local config
- netcraft 5y ago
  we do that by setting a local variable before the query and then reading that in the triggers: ``` 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.
scwoodal 5y ago
I didn't see this mentioned in the article but with JSONB you can leverage operators to know what data changed. > -- record = {"id": 1, "name": "bar"}, old_record = {"id": 1, "name": "foo"} > select record - old_record where id=3; name => bar >
- netcraft 5y ago
  I dont think this is built in, I think you must have an extension or custom operator providing that: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=273a440d043a82668825ba2dead68b99 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=273a440d043a82...
  - scwoodal 5y ago
    Ah yes, I was thinking HSTORE. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2f406630f367b52a8f4868bdac0cc931 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2f406630f367b5...
- efxhoy 5y ago
  This SO answer has a good jsonb diff function: https://stackoverflow.com/a/36043269 https://stackoverflow.com/a/36043269
SCLeo 5y ago
Ok, my SQL skills might be a bit lacking, but from the article: > 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.
- znep 5y ago
  For even more fun, try "SELECT pg_cancel_backend(pid) from pg_stat_activity". (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 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.
- giraffe_lady 5y ago
  Yep you can, though a lot of systems that should know better also assume they don't have side effects. For example postgrest GETs happen in readonly transactions which would cause a problem if you wanted to use a system like this to keep an audit logs of hits to a certain endpoint or whatever. 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.
deutschew 5y ago
Is there something like Datomic for Postgres? Having your team learn clojure, datalog, deploying Datomic was a huge pain for us. Not the road we recommend since all we really needed were just immutable append only log of all transactions.
- craigkerstiens 5y ago
  This used to exist in Postgres, Postgres held all history for all time and you could rewind etc. The reality is it was a huge pain to maintain and no one ever really used it so it got pulled out. 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.
- kot-behemoth 5y ago
  XTDB (https://xtdb.com https://xtdb.com) while is not directly for Postgres is at least not reliant on devs knowing Clojure. It's very close to Datomic in terms of approach. They expose SQL, Java, HTTP APIs.
dylanz 5y ago
I’d +1 the usage of pgAudit when higher throughput is needed. If you’re on AWS, RDS can automatically ship these logs to S3 where you can then run Athena queries against them.
pingsl 5y ago
I am afraid data audit is not just about data changes. The real challenge is how to audit the data read which people should not do. 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.
- cgio 5y ago
  That should be captured in security, right? I.e. if someone has access to data the assumption is they are able to read it. Security audit can be done without the grain of select statements but rather the grain of changes to provisioning. Fundamentally, you have an issue even if someone did not actually read what they should not but could. Select audit can be useful for performance tuning but I would not classify it as “audit”, rather monitoring.
  - pingsl 5y ago
    > if someone has access to data the assumption is they are able to read it. 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.
    - cgio 5y ago
      I can see the point around expensive row level security. Cannot see how it is harder to manage than to monitor though. Designing around risk acceptance of manual detective rather than preventative controls does not make this approach generally applicable or best practice, just a feasible one with conditions.
warmwaffles 5y ago
Is there something similar for sqlite?
graderjs 5y ago
This is a great article! Fantastic read.
drittich 5y ago
I did a similar thing with SQL Server using XML. I created views that would convert the XML value back to the correct SQL data type when querying the audit table. The view also Unions the audit data with the current data so that you could add the trigger at any time and the view would return the current data (the audit table only stores updates and deletes). One nice addition was saving the current user ID with all audit records, which I was able to get in the trigger by adding it to the db connection using CONTEXT_INFO().
- Pxtl 5y ago
  Ms SQL now has a very good built in feature for temporal tables such that you shouldn't have to use audit tables, it's an out-of-the-box feature. 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.
pgt 5y ago
Bitemporal Database: https://xtdb.com/ https://xtdb.com/ Datomic: https://www.datomic.com/ https://www.datomic.com/
qatanah 5y ago
I've been using my own audit trigger for 3 years now. https://github.com/cmabastar/audit-trigger https://github.com/cmabastar/audit-trigger It uses the old and tested http://wiki.postgresql.org/wiki/Audit_trigger_91plus http://wiki.postgresql.org/wiki/Audit_trigger_91plus but with JSONB instead of HSTORE, and automatically creates partitioned tables by month (Requires PG11+).
Cthulhu_ 5y ago
This looks like it works pretty well to just 'dump' data changes, and if needs be to visualize them somewhere, but the data is unstructured and long-term there may no longer be any relationship to older data. 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.

313 points by oliverrice 5y ago

At 42Floors, we experienced a similar need for auditing. Initial we did a similar approach as described in the post. Over time the audit tables began to be incorporated into every aspect of the product, which over time caused the table to become quite large, and ended up causing a lot of downtime. Our initial implementation only audited what was inserted by our Rails application. If a change was made by another application or manually, it wouldn’t appear in the table. 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/ 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.

jph 5y ago

The post leads to a good Supabase Postgres auditing tool as a PG extension named supa_audit: https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit

oliverrice 5y ago

author here (of the blog post and https://github.com/supabase/supa_audit https://github.com/supabase/supa_audit) happy to answer any questions

simonw 5y ago
Are you at all worried about the size of the audit table? Keeping a JSON copy of both the old and the new data is going to add up pretty quickly for fast-changing tables. 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.
- craigkerstiens 5y ago
  Building a bit off Simon's above question. I'm curious if any thought was given to using JSON over JSONB. The trade off of really fast ability to ingest them and save them seems like it could be better than JSONB which has to do some conversion and if there are any indexes on the JSONB columns could really dramatically slow down throughput.
  - oliverrice 5y ago
    > I'm curious if any thought was given to using JSON over JSONB 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.
- oliverrice 5y ago
  > Are you at all worried about the size of the audit table? Keeping a JSON copy of both the old and the new data is going to add up pretty quickly for fast-changing tables. 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.
simonw 5y ago
I'm not completely clear on the reason for both record_id and old_record_id - is this so that you can capture instances where one of the columns that makes up the primary key for the row is itself modified for some reason?
- oliverrice 5y ago
  Being able to see when the primary key changes using `record_id` and `old_record_id` is a perk, but we also wanted to make sure a snapshot of the existing data was available when auditing is enabled on a table that already has data in it. See comment ^ for more color 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
netcraft 5y ago
this is great! I've done something similar many times, but the ability to do it on arbitrary tables is really nice. 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.
perlgeek 5y ago
First of all, this looks super awesome, and at least an order of magnitude better than our home-grown solution (grown over 25+ years on a DB that is now mariadb). 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?
- oliverrice 5y ago
  All the data would be available and joining it would be possible with some jsonb gymnastics but its optimized more for compliance style auditing than rapid lookups for complex queries. For that scenario replicating audit data to a OLAP system may be a better fit
efxhoy 5y ago
Really nice post! A possible addition could be showing the diff as its own field. This seems like a sane approach: https://stackoverflow.com/a/36043269 https://stackoverflow.com/a/36043269 But how could you go about not storing the diff but still being able to select it?
oauea 5y ago
Could you explain how this compares with https://wiki.postgresql.org/wiki/Audit_trigger_91plus https://wiki.postgresql.org/wiki/Audit_trigger_91plus / https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql https://github.com/2ndQuadrant/audit-trigger/blob/master/aud...?

simonw 5y ago

I've battled this problem many times over my career in many different ways - this approach looks very solid to me. I particularly like the way the schema design incorporates a record ID, old record ID, updated record and old record. 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.

craigkerstiens 5y ago

Love the detail and approach here, though (and it is mentioned in the bottom of the article) this shouldn't be used at any very large scale... the estimate of at about 1k transactions per second or less seems about right. For any larger scale you want to look at something like pg_audit which can scale much much further and be used with other extensions to ensure you have a full audit log that can't be modified (when dealing with compliance).

lichtenberger 5y ago

I'd argue that's still a lot of work to manually do. However, great work and detail, thanks a lot :-) 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. [1] https://sirix.io https://sirix.io | https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix

jmalicki 5y ago
Every google hit for "UberPage" seems to be your writing, is there standard ZFS terminology that would be found in ZFS documentation to what you're referring?
- lichtenberger 5y ago
  It's in our case the tree root of the index, which is always written after all the descendant pages have been written as a new revision is committed (during a postorder traversal of the new pages). 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.
lichtenberger 5y ago
In order to provide fast audits of subtrees, the system stores optionally a merkle hash tree (a hash in each node) and updates the hashes for all ancestors automatically during updates.

raimille1 5y ago

This is a great technique, we used to have it setup very similarly at a previous startup and worked wonders. Thanks for sharing a generic way of doing it @oliverrice !

ithrow 5y ago

Debezium can be used for this in embedded mode if you are on the JVM.

johndfsgdgdfg 5y ago
Can you please elaborate? Last time I checked it needed Kafka to work.
- ithrow 5y ago
  https://debezium.io/documentation/reference/1.9/development/engine.html https://debezium.io/documentation/reference/1.9/development/...

bob1029 5y ago

We have a new business system we are trying to build that has a lot of very granular auditing and versioning requirements throughout, and this is where I started looking. 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.

lichtenberger 5y ago
As posted in another comment that's basically what https://github.com/sirixdb/sirix https://github.com/sirixdb/sirix supports along with easily reconstructing former revisions of a JSON document (or any node therein), sophisticated secondary (also versioned) indexes, querying with JSONiq and optimizations at query compile time as for joins and aggregates.... 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.
infogulch 5y ago
I've done this before on smaller scales. Writing a set of table-valued functions that expose the event data in relational form "as of date given by param X", and views on top of that where X is set to getdate(), you get a very ergonomic way to consume "current" & "historical" data. Materialized views can make querying these more natural relations very cheap if your engine supports them.

sususu 5y ago

I really like this approach, however I have a use case where the application user id that made the operation must be saved in the audit table, unfortunately, I cannot see how to do that with a pure SQL solution. Has anyone done something similar with SQL only?

oliverrice 5y ago
if `user_id` is in the table you're auditing you could do ``` 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
timwis 5y ago
PostgREST has an interesting approach to authentication that might give you some ideas: https://postgrest.org/en/stable/auth.html 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).
- oliverrice 5y ago
  Supabase passes all the JWT info to postgrest so it is still available in transaction local config
netcraft 5y ago
we do that by setting a local variable before the query and then reading that in the triggers: ``` 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.

scwoodal 5y ago

I didn't see this mentioned in the article but with JSONB you can leverage operators to know what data changed. > -- record = {"id": 1, "name": "bar"}, old_record = {"id": 1, "name": "foo"} > select record - old_record where id=3; name => bar >

netcraft 5y ago
I dont think this is built in, I think you must have an extension or custom operator providing that: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=273a440d043a82668825ba2dead68b99 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=273a440d043a82...
- scwoodal 5y ago
  Ah yes, I was thinking HSTORE. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2f406630f367b52a8f4868bdac0cc931 https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2f406630f367b5...
efxhoy 5y ago
This SO answer has a good jsonb diff function: https://stackoverflow.com/a/36043269 https://stackoverflow.com/a/36043269

SCLeo 5y ago

Ok, my SQL skills might be a bit lacking, but from the article: > 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.

znep 5y ago
For even more fun, try "SELECT pg_cancel_backend(pid) from pg_stat_activity". (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 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.
giraffe_lady 5y ago
Yep you can, though a lot of systems that should know better also assume they don't have side effects. For example postgrest GETs happen in readonly transactions which would cause a problem if you wanted to use a system like this to keep an audit logs of hits to a certain endpoint or whatever. 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.

deutschew 5y ago

Is there something like Datomic for Postgres? Having your team learn clojure, datalog, deploying Datomic was a huge pain for us. Not the road we recommend since all we really needed were just immutable append only log of all transactions.

craigkerstiens 5y ago
This used to exist in Postgres, Postgres held all history for all time and you could rewind etc. The reality is it was a huge pain to maintain and no one ever really used it so it got pulled out. 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.
kot-behemoth 5y ago
XTDB (https://xtdb.com https://xtdb.com) while is not directly for Postgres is at least not reliant on devs knowing Clojure. It's very close to Datomic in terms of approach. They expose SQL, Java, HTTP APIs.

dylanz 5y ago

I’d +1 the usage of pgAudit when higher throughput is needed. If you’re on AWS, RDS can automatically ship these logs to S3 where you can then run Athena queries against them.

pingsl 5y ago

I am afraid data audit is not just about data changes. The real challenge is how to audit the data read which people should not do. 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.

cgio 5y ago
That should be captured in security, right? I.e. if someone has access to data the assumption is they are able to read it. Security audit can be done without the grain of select statements but rather the grain of changes to provisioning. Fundamentally, you have an issue even if someone did not actually read what they should not but could. Select audit can be useful for performance tuning but I would not classify it as “audit”, rather monitoring.
- pingsl 5y ago
  > if someone has access to data the assumption is they are able to read it. 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.
  - cgio 5y ago
    I can see the point around expensive row level security. Cannot see how it is harder to manage than to monitor though. Designing around risk acceptance of manual detective rather than preventative controls does not make this approach generally applicable or best practice, just a feasible one with conditions.

warmwaffles 5y ago

Is there something similar for sqlite?

graderjs 5y ago

This is a great article! Fantastic read.

drittich 5y ago

I did a similar thing with SQL Server using XML. I created views that would convert the XML value back to the correct SQL data type when querying the audit table. The view also Unions the audit data with the current data so that you could add the trigger at any time and the view would return the current data (the audit table only stores updates and deletes). One nice addition was saving the current user ID with all audit records, which I was able to get in the trigger by adding it to the db connection using CONTEXT_INFO().

Pxtl 5y ago
Ms SQL now has a very good built in feature for temporal tables such that you shouldn't have to use audit tables, it's an out-of-the-box feature. 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.

pgt 5y ago

Bitemporal Database: https://xtdb.com/ https://xtdb.com/ Datomic: https://www.datomic.com/ https://www.datomic.com/

qatanah 5y ago

I've been using my own audit trigger for 3 years now. https://github.com/cmabastar/audit-trigger https://github.com/cmabastar/audit-trigger It uses the old and tested http://wiki.postgresql.org/wiki/Audit_trigger_91plus http://wiki.postgresql.org/wiki/Audit_trigger_91plus but with JSONB instead of HSTORE, and automatically creates partitioned tables by month (Requires PG11+).

Cthulhu_ 5y ago

This looks like it works pretty well to just 'dump' data changes, and if needs be to visualize them somewhere, but the data is unstructured and long-term there may no longer be any relationship to older data. 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.