On Fri, Jul 27, 2018 at 5:41 AM Philip Scott <from_postg...@safetyphil.com> 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. > > A lot of that information is available already by listening to the > pgoutput decoding, and my first thought was that I could just write a > receiver for that. However, application name, username, client hostname > and current_query() are not available. This is understandable as they > aren’t useful for logical replication. > > I was about to give up, when I discovered pg_logical_emit_message. > > My current thoughts are to: > - Write this extra data into a logical message while the transaction > is still in progess > Either with a deferred trigger per table or, perhaps better > Find some global commit-time (or xid-assigment time) hook emit it > there > > - Then get the information out of the database: > Either modify the existing pgoutput plugin & protocol to forward > such messages in its stream, > Or write a dedicated ‘audit’ decoding plugin with its own protocol > > - Then get the information into the ‘auditing’ database: > Either with some standalone process that connects to both, consumes > the output created above, translates it to SQL to run in the auditing > DB. > Figure out how to create a proper postgres background process to do > it, in a similar fashion to the logical replication worker > > Any input you folks have would be very much appreciated. > > Kinds Regards, > > Philip > > PS: If there is someone out there who is willing & able to build this > for less than my company will have to pay me to do it, please drop me a > line ☺ All I can say is +1 this would be an awesome feature to have and I hope to see it someday.
