Re: PostgreSQL Audit Extension

On 2/17/16 10:25 PM, Bruce Momjian wrote:
> On Wed, Feb 17, 2016 at 01:59:09PM +0530, Robert Haas wrote:
>> On Wed, Feb 17, 2016 at 5:20 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>> On Fri, Feb 5, 2016 at 01:16:25PM -0500, Stephen Frost wrote:
>>>> Looking at pgaudit and the other approaches to auditing which have been
>>>> developed (eg: applications which sit in front of PG and essentially
>>>> have to reimplement large bits of PG to then audit the commands sent
>>>> before passing them to PG, or hacks which try to make sense out of log
>>>> files full of SQL statements) make it quite clear, in my view, that
>>>> attempts to bolt-on auditing to PG result in a poorer solution, from a
>>>> technical perspective, than what this project is known for and capable
>>>> of. To make true progress towards that, however, we need to get past
>>>> the thinking that auditing doesn't need to be in-core or that it should
>>>> be a second-class citizen feature or that we don't need it in PG.
>>>
>>> Coming in late here, but the discussion around how to maintain the
>>> auditing code seems very similar to how to handle the logical
>>> replication of DDL commands. First, have we looked into hooking
>>> auditing into scanning logical replication contents, and second, how are
>>> we handling the logical replication of DDL and could we use the same
>>> approach for auditing?
>>
>> Auditing needs to trace read-only events, which aren't reflected in
>> logical replication in any way. I think it's a good idea to try to
>> drive auditing off of existing machinery instead of inventing
>> something new - I suggested plan invalidation items upthread. But I
>> doubt that logical replication is the thing to attach it to.
>
> 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.

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