[HN Gopher] Postgres audit tables saved us from taking down prod...
___________________________________________________________________
Postgres audit tables saved us from taking down production
Author : kmdupree
Score : 70 points
Date : 2021-10-26 17:32 UTC (5 hours ago)
(HTM) web link (heap.io)
(TXT) w3m dump (heap.io)
| crescentfresh wrote:
| Audit tables are awesome, this technique is great. We had one
| additional requirement that got us away from using a trigger for
| this: we wanted to know what user (in the application) caused the
| change. So we moved the logic of "insert into audit_table" into
| the application code itself using a CTE, roughly translated as:
| with affected_rows as ( insert/update/delete
| into/from ... returning * ) insert
| into my_audit_table select @op, current_timestamp,
| @userinfo, * from affected_rows
|
| where @op is bound to one of "insert", "update" or "delete" and
| @userinfo is the user(name|id|various|etc) of the user that
| caused the change.
| kmdupree wrote:
| RETURNING * is a clever way to do this. What is this * from
| affected_rows syntax you're using here? Any chance you have a
| link to the docs that describes this?
| kendru wrote:
| This is a useful technique. I have used it in at least one
| application before. One of my favorite things about it is that
| you can use it even when you don't own the application writing to
| the database!
| kmdupree wrote:
| Author here. :)
|
| >I have used it in at least one application before.
|
| Good to know that other folks are using this technique!
|
| >One of my favorite things about it is that you can use it even
| when you don't own the application writing to the database
|
| Great point! Hadn't thought of this.
| netcraft wrote:
| So we do something similar, using HSTORE to diff the old and new
| records, which then allows us to easily tell what was changed in
| the case of an update. Useful for many kinds of things, but
| especially forensics.
| kmdupree wrote:
| clever! hadn't thought of combining this with hstore.
| netcraft wrote:
| ``` , SUBSTRING(TG_OP, 1, 1) -- this gets you I,D,U, or T for
| truncate , HSTORE(oldData.*) -
| HSTORE(newData.*) , HSTORE(newData.*) -
| HSTORE(oldData.*)
|
| ``` this is basically what we do, getting the old and new of
| only what changed.
| PenguinCoder wrote:
| I have a usecase where I could use a diff of records changed,
| and what was changed. I don't see how HSTORE solves this for
| you. Could you explain more?
|
| In my example, say a user edits their document and saves it (to
| a row in the DB). I want to be able to tell what their previous
| row was, what the new one is, and basically git diff them.
| netcraft wrote:
| I replied to a sister comment with an example. HSTORE can
| take a whole row and turn it into an HSTORE value, plus
| HSTORE has an operator of `-` that will diff two HSTORE
| values. So combine that with the OLD and NEW that you get in
| the trigger and you can effectively diff the records.
| radicalbyte wrote:
| Around 7-8 years ago I built something very similar into the data
| subsystem of the Low-Code platform I was working on
| (triggre.com). All fully generated, and fairly performant.
|
| The nice thing there was that the entire data subsystem was
| designed to be lossless through the lifetime of system through
| normal use. So as you changed the datamodel, no data would
| actually be removed. For the target use-cases (small scale
| departmental systems) this is extremely powerful.
|
| Kind of miss working there, I still have a huge list of really
| cool awesome things that you could do which would pull our
| industry forward kicking and screaming.
| djbusby wrote:
| I'm interested in this list or even a part of it
| davidbanham wrote:
| I make heavy use of this technique. I use it to display an audit
| log to users that tells them what's changed on the entity and who
| did it. I set the application_name to the currently logged in
| user id so I can display it back.
|
| I find this lets me punt on a lot of fine-grained permissions for
| different roles within an organisation. With the audit log, it
| becomes reasonable to say "just ask them not to do that. If they
| do, you'll easily be able to see what happened and we can reverse
| it, then tell them to knock it off"
|
| I've also gotten pretty wild using it to reconstruct state in
| migrations. Like adding a "created_at" field to a table, then
| mining the audit log for creation events to set the value on
| existing rows. Or changing a table to a soft delete instead of an
| actual delete, then repopulating past entries from the audit log.
| zackbloom wrote:
| I have no idea how it's still online, but I have a more robust
| implementation borrowed from the Postgres wiki:
| https://eager.io/blog/audit-postgres/
|
| I now use it with every project and it has saved me many times.
| Sytten wrote:
| Audit tables are a good first step, but I am wondering if there
| was already work being done toward making foreign keys "version
| aware". Say I linked an order to a discount, it would be nice to
| be able to know how that linked discount looked like at the time
| of linking vs what it looks like now in case a merchant decided
| to change it (but still be able to do a group by id for the given
| discount).
| jdreaver wrote:
| This is solvable with vanilla table design. Add a version
| column to the discounts table, make your foreign key point to
| (discount_id, version) instead of just discount_id, keep old
| discounts around. Don't mutate discounts, just add a new row
| with a new version. You can still group by discount_id and
| exclude the latest version for each discount if you want.
| forinti wrote:
| For a moment I thought Postgres had a mechanism to simplify doing
| this.
|
| I see this same "shadow table"+trigger pattern with other
| databases too.
| sharadov wrote:
| You can use pgaudit, it's an extension that let's you audit
| DDL/DML statements. It's a great auditing mechanism. I use it
| on all our prod postgres instances, but have only "DDL"
| enabled, because of the potential performance overhead
|
| https://github.com/pgaudit/pgaudit
| AmericanChopper wrote:
| > but have only "DML" enabled, because of the potential
| performance overhead
|
| Surely the amount of DDL statements would be trivial compared
| to the DML ones?
| sharadov wrote:
| Good catch, I meant "DDL", corrected my comment.
| Scarbutt wrote:
| This extension simplifies things a bit:
| https://github.com/arkhipov/temporal_tables
| kmdupree wrote:
| Ah. Sorry to disappoint. The technique was just new to me, so I
| thought it was worth sharing. :)
| cfors wrote:
| This is essentially the idea behind CDC (Change Data Capture)
| [0].
|
| Martin Kleppmann has some great blogs about this as well [1].
|
| [0] https://en.wikipedia.org/wiki/Change_data_capture
|
| [1] https://www.confluent.io/blog/turning-the-database-inside-
| ou...
| kmdupree wrote:
| Thanks for sharing these links!
| gunnarmorling wrote:
| If you look for a ready-to-use open-source implementation of
| CDC for Postgres (and other databases), take a look at
| Debezium [1].
|
| On audit logs in particular, we have a post on our blog,
| which discusses how to use CDC for that, focusing in
| particular on enriching change events with additional
| metadata like business user performing a given change by
| means of stream processing [2].
|
| One advantage of log-based CDC over trigger-based approaches
| is that it doesn't impact latency of transactions, as it runs
| fully asynchronously from writing transactions, reading
| changes from the WAL of the database.
|
| Disclaimer: I work on Debezium
|
| [1] debezium.io [2]
| https://debezium.io/blog/2019/10/01/audit-logs-with-
| change-d...
| djbusby wrote:
| That is very cool.
| Legion wrote:
| > Unfortunately, the Node code we wrote to do this had a bug: it
| treated a string as if it was a number.
|
| I'm sure plenty of people here will have thoughts on this point.
___________________________________________________________________
(page generated 2021-10-26 23:00 UTC)