Re: Auditing extension for PostgreSQL (Take 2)

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

> 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.

Regards,
--
- David Steele
david(at)pgmasters(dot)net