Re: Auditing extension for PostgreSQL (Take 2)

Hi David,

Thank you for your answer!

On Wed, Mar 25, 2015 at 12:38 AM, David Steele <david(at)pgmasters(dot)net> wrote:
> Hi Sawada,
>
> Thank you for taking the time to look at the patch.
>
> On 3/24/15 10:28 AM, Sawada Masahiko wrote:
>> I've applied these patchese successfully.
>>
>> I looked into this module, and had a few comments as follows.
>> 1. pg_audit audits only one role currently.
>> In currently code, we can not multiple user as auditing user. Why?
>> (Sorry if this topic already has been discussed.)
>
> There is only one master audit role in a bid for simplicity. However,
> there are two ways you can practically have multiple audit roles (both
> are mentioned in the docs):
>
> 1) The audit role honors inheritance so you can grant all your audit
> roles to the "master" role set in pg_audit.role and all the roles will
> be audited.
>
> 2) Since pg_audit.role is a GUC, you can set a different audit role per
> database by using ALTER DATABASE ... SET. You can set the GUC per logon
> role as well though that would probably make things very complicated.
> The GUC is SUSET so normal users cannot tamper with it.

I understood.

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

>> 3. pg_audit logged OBJECT log even EXPLAIN command.
>> EXPLAIN command does not touch the table actually, but pg_audit writes
>> audit OBJECT log.
>> I'm not sure we need to log it. Is it intentional?
>
> This is intentional. They are treated as queries since in production
> they should be relatively rare (that is, not part of a normal function
> or process) and it's good information to have because EXPLAIN can be
> used to determine the number of rows in a table, and could also be used
> to figure out when data is added or removed from a table. In essence,
> it is a query even if it does not return row data.

Okay, I understood.

Regards,

-------
Sawada Masahiko