From: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
---|---|
To: | Philip Scott <from_postgres(at)safetyphil(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Auditing via logical decoding |
Date: | 2018-07-27 13:36:31 |
Message-ID: | 1c55375f-7036-a9e1-a719-9be1cdcb21a5@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2018-07-27 13:44:54 | Re: PartitionDispatch's partdesc field |
Previous Message | Robert Haas | 2018-07-27 13:30:45 | Re: How can we submit code patches that implement our (pending) patents? |