Re: Auditing extension for PostgreSQL (Take 2)

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.

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

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

If that sounds paranoid, well, auditing is all about paranoia!

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