On 07/27/2018 12:44 PM, Philip Scott wrote:
Hi Postgres Hackers,
We have been using our own trigger-based audit system at my firm
successfully for some years, but the performance penalty is starting to
grate a bit and so I have been tasked with seeing if we can make use of
the new logical decoding functions to achieve the same thing. I thought
that someone must already have written something that would satisfy our
use-case but my internet searches have come up short so far so I am
considering writing a logical decoding plugin to do what we want.
I thought I would run the idea past you all here just in case my plan is
crazy; I’ve browsed around the postgres source code a bit before but
I’ve never really gotten my hands dirty and am a little bit nervous
about putting my own C code into the heart of our DBMS so if this comes
to anything I would like to offer my code up for review and/or possible
inclusion as a contributed module.
A quick summary of requirements:
We want to log (to a separate, remote database)
- One row for every transaction that changes the state of the database.
We call this table ‘audit_entry’ and contains the xid, transaction
timestamp, username, client hostname, and application name of the
session that caused the change.
- One row for each change made by each transaction which records the
state of the tuple before the change.
We call this table ‘audit_detail’ and contains xid, statement
timestamp, table name & schema, event_type, primary_key (hstore),
old_row (hstore), and the text of the query that was responsible for the
change.
Have you checked pgaudit [1]? I haven't checked if it matches all your
requirements, but considering it's an extension aimed at auditing use
cases it might. And it's already available, of course.
[1] https://www.pgaudit.org/
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
