Re: Auditing extension for PostgreSQL (Take 2)

On Tue, Mar 24, 2015 at 3:17 AM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
> Sawada Masahiko wrote:
>
>> I tied to look into latest patch, but got following error.
>>
>> masahiko [pg_audit] $ LANG=C make
>> gcc -Wall -Wmissing-prototypes -Wpointer-arith
>> -Wdeclaration-after-statement -Wendif-labels
>> -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
>> -fwrapv -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o
>> pg_audit.o pg_audit.c
>> pg_audit.c: In function 'log_audit_event':
>> pg_audit.c:456: warning: ISO C90 forbids mixed declarations and code
>> pg_audit.c: In function 'pg_audit_ddl_command_end':
>> pg_audit.c:1436: error: 'pg_event_trigger_expand_command' undeclared
>> (first use in this function)
>
> You need to apply my deparsing patch first, last version of which I
> posted here:
> https://www.postgresql.org/message-id/20150316234406.GH3636@alvh.no-ip.org
>

Thank you for the info.
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.)

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.

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

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?

Regards,

-------
Sawada Masahiko