Re: Auditing via logical decoding

Greetings,

* from_postgres(at)safetyphil(dot)com (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.

I've not been following this very closely but when it comes to pgAudit,
the log lines will include whatever is in log_line_prefix, where you can
include information like the application_name, login user, etc, which is
why you don't explicitly see options for those in pgAudit itself.

This is because pgAudit logs messages the same way PG itself does (which
also means it goes to the same place, such as to a CSV log that can then
be imported into a PG database).

Thanks!

Stephen