Re: Auditing via logical decoding

On 07/27/2018 04:43 PM, from_postgres(at)safetyphil(dot)com wrote:
>>> 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.
>
>> 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.
>
> Actually no, I hadn't come across this before, thanks for the heads
> up. It is important for us to be able to get the audit data back into
> a different database, but it looks like I could scrape the logs and
> do that. At the very least it shows that it is possible to hook into
> postgres in the right places to emit extra logical messages if that
> turns out to be the better way to do it.
>
> It doesn't appear to support application_name, nor the connection
> details of the user doing the update - but perhaps that would be just
> as sensible to add it here than in a logical output plugin.
>
> It's a bit tricky coming to a big codebase like postgres and trying
> to decide the best route of doing something; I don't have much of a
> mental model about how complicated the various systems are :)
>

It may not support everything, but in general when a feature can be done
in an extension, it's generally faster to deliver that way. One of the
reasons is the ad hoc release cycle (compared to the 1-per-year cycle of
PostgreSQL). So I'd suggest investigating this option first.

It may not meet all your requirements immediately (or at all), but well,
no solution probably will.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services