Re: Auditing via logical decoding

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