RE: Auditing via logical decoding

>> 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 :)

Kind Regards,

Phil