Re: Auditing via logical decoding

2018-07-27 16:43 GMT+02:00 <from_postgres(at)safetyphil(dot)com>:

> >> 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 is very simple to show application name or any other info

Few years ago I customized pgAudit and it was not hard work - almost all
time

Regards

Pavel

> 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 :)
>
> Kind Regards,
>
> Phil
>
>
>