Re: Auditing extension for PostgreSQL (Take 2)

> On Wed, Mar 25, 2015 at 12:38 AM, David Steele <david@pgmasters.net> wrote:
>>> 2. OBJECT auditing does not work before adding acl info to pg_class.rel_acl.
>>> In following situation, pg_audit can not audit OBJECT log.
>>> $ cat postgresql.conf | grep audit
>>> shared_preload_libraries = 'pg_audit'
>>> pg_audit.role = 'hoge_user'
>>> pg_audit.log = 'read, write'
>>> $ psql -d postgres -U hoge_user
>>> =# create table hoge(col int);
>>> =# select * from hoge;
>>> LOG:  AUDIT: SESSION,3,1,READ,SELECT,,,select * from hoge;
>>>
>>> OBJECT audit log is not logged here since pg_class.rel_acl is empty
>>> yet. (Only logged SESSION log)
>>> So after creating another unconcerned role and grant any privilege to that user,
>>> OBJECT audit is logged successfully.
>>
>> Yes, object auditing does not work until some grants have been made to
>> the audit role.
>>
>>> =# create role bar_user;
>>> =# grant select on hoge to bar_user;
>>> =# select * from hoge;
>>> LOG:  AUDIT: SESSION,4,1,READ,SELECT,,,select * from hoge;
>>> LOG:  AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.hoge,select * from hoge;
>>>
>>> The both OBJCET and SESSION log are logged.
>>
>> Looks right to me.  If you don't want the session logging then disable
>> pg_audit.log.
>>
>> Session and object logging are completely independent from each other:
>> one or the other, or both, or neither can be enabled at any time.
>
> It means that OBJECT log is not logged just after creating table, even
> if that table is touched by its owner.
> To write OBJECT log, we need to grant privilege to role at least. right?

Exactly.  Privileges must be granted to the audit role in order for
object auditing to work.

--
- David Steele
david@pgmasters.net