Re: Auditing extension for PostgreSQL (Take 2)

On Wed, Mar 25, 2015 at 12:23 PM, David Steele <david@pgmasters.net> wrote:
>> 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.
>

The table owner always can touch its table.
So does it lead that table owner can get its table information while
hiding OBJECT logging?

Also I looked into latest patch again.
Here are two review comment.

1.
> typedef struct
> {
>    int64 statementId;
>   int64 substatementId;
Both statementId and substatementId could be negative number.
I think these should be uint64 instead.

2.
I got ERROR when executing function uses cursor.

1) create empty table (hoge table)
2) create test function as follows.

create function test() returns int as $$
declare   cur1 cursor for select * from hoge;   tmp int;
begin   open cur1;   fetch cur1 into tmp;  return tmp;
end$$
language plpgsql ;

3) execute test function (got ERROR)
=# select test();
LOG:  AUDIT: SESSION,6,1,READ,SELECT,,,selecT test();
LOG:  AUDIT: SESSION,6,2,FUNCTION,EXECUTE,FUNCTION,public.test,selecT test();
LOG:  AUDIT: SESSION,6,3,READ,SELECT,,,select * from hoge
CONTEXT:  PL/pgSQL function test() line 6 at OPEN
ERROR:  pg_audit stack is already empty
STATEMENT:  selecT test();

It seems like that the item in stack is already freed by deleting
pg_audit memory context (in MemoryContextDelete()),
before calling stack_pop in dropping of top-level Portal.

Regards,

-------
Sawada Masahiko