Re: Auditing extension for PostgreSQL (Take 2)

On Mon, Apr 20, 2015 at 10:17 PM, David Steele <david(at)pgmasters(dot)net> wrote:
> On 4/20/15 4:40 AM, Sawada Masahiko wrote:
>>
>> Thank you for updating the patch.
>>
>> One question about regarding since v7 (or later) patch is;
>> What is the different between OBJECT logging and SESSION logging?
>
> In brief, session logging can log anything that happens in a user
> session while object logging only targets DML and SELECT on selected
> relations.
>
> The pg_audit.log_relation setting is intended to mimic the prior
> behavior of pg_audit before object logging was added.
>
> In general, I would not expect pg_audit.log = 'read, write' to be used
> with pg_audit.role. In other words, session logging of DML and SELECT
> should probably not be turned on at the same time as object logging is
> in use. Object logging is intended to be a fine-grained version of
> pg_audit.log = 'read, write' (one or both).

Thank you for your explanation!
I understood about how to use two logging style.

>> I used v9 patch with "pg_audit.log_relation = on", and got quite
>> similar but different log as follows.
>>
>> =# select * from hoge, bar where hoge.col = bar.col;
>> NOTICE: AUDIT: OBJECT,8,1,READ,SELECT,TABLE,public.hoge,"select *
>> from hoge, bar where hoge.col = bar.col;"
>> NOTICE: AUDIT: SESSION,8,1,READ,SELECT,TABLE,public.hoge,"select *
>> from hoge, bar where hoge.col = bar.col;"
>> NOTICE: AUDIT: OBJECT,8,1,READ,SELECT,TABLE,public.bar,"select * from
>> hoge, bar where hoge.col = bar.col;"
>> NOTICE: AUDIT: SESSION,8,1,READ,SELECT,TABLE,public.bar,"select *
>> from hoge, bar where hoge.col = bar.col;"
>>
>> The behaviour of SESSION log is similar to OBJECT log now, and SESSION
>> log per session (i.g, pg_audit.log_relation = off) is also similar to
>> 'log_statement = all'. (enhancing log_statement might be enough)
>> So I couldn't understand needs of SESSION log.
>
> Session logging is quite different from 'log_statement = all'. See:
>
> http://www.postgresql.org/message-id/552323B2.8060708@pgmasters.net
>
> and/or the "Why pg_audit?" section of the pg_audit documentation.
>
> I agree that it may make sense in the future to merge session logging
> into log_statements, but for now it does provide important additional
> functionality for creating audit logs.
>

I'm concerned that behaviour of pg_audit has been changed at a few
times as far as I remember. Did we achieve consensus on this design?

And one question; OBJECT logging of all tuple deletion (i.g. DELETE
FROM hoge) seems like not work as follows.

=# grant all on bar TO masahiko;

(1) Delete all tuple
=# insert into bar values(1);
=# delete from bar ;
NOTICE: AUDIT: SESSION,47,1,WRITE,DELETE,TABLE,public.bar,delete from bar ;
DELETE 1

(2) Delete specified tuple (but same result as (1))
=# insert into bar values(1);
=# delete from bar where col = 1;
NOTICE: AUDIT: OBJECT,48,1,WRITE,DELETE,TABLE,public.bar,delete from
bar where col = 1;
NOTICE: AUDIT: SESSION,48,1,WRITE,DELETE,TABLE,public.bar,delete from
bar where col = 1;
DELETE 1

Regards,

-------
Sawada Masahiko