Re: PostgreSQL Audit Extension

On 2/19/16 10:14 AM, Bruce Momjian wrote:
> On Fri, Feb 19, 2016 at 09:19:58AM -0500, David Steele wrote:
>>> I was suggesting we could track write events via logical replication and
>>> then we only have to figure out auditing of read events, which would be
>>> easier.
>>
>> I agree that DDL/DML audit logging requires a lot of the same
>> information as logical replication but I don't think reading the logical
>> WAL stream is a good way to do audit logging even for writes.
>>
>> For instance there are some "writes" that are not WAL logged such as SET
>> or ALTER SYSTEM. Determining attribution would be difficult or
>> impossible, such as which function called an update statement (or vice
>> versa). Tying together the read and write information would be tricky
>> as well since the WAL stream contains information on transactions but
>> not user sessions, if I understand it correctly.
>>
>> The end result is that it would be very difficult to record a coherent,
>> attributed, and sequential audit log and in fact represent a step
>> backward from pgaudit's current capabilities.
>
> Understood. My point is that there is a short list of read events, and
> many DDL events. We have already hesitated to record DDL changes for
> logical replication because of the code size, maintenance overhead, and
> testing required. If we could use the same facility for both logical
> replication and auditing, the cost overhead is less per feature. For
> example, we don't need to read the WAL to do the auditing, but the same
> facility could be used for both, e.g. output some JSON standard format
> that both logical replication and auditing could understand.

Agreed, and this was discussed up thread. In my mind putting a more
generic structure on top of logical replication and DDL auditing is a
promising solution but I have not looked at it closely enough to know if
it will work as expected or address maintenance concerns.

--
-David
david(at)pgmasters(dot)net