Re: Auditing extension for PostgreSQL (Take 2)

On Fri, Apr 24, 2015 at 3:23 AM, David Steele <david(at)pgmasters(dot)net> wrote:
> On 4/23/15 5:49 AM, Sawada Masahiko wrote:
>>
>> I'm concerned that behaviour of pg_audit has been changed at a few
>> times as far as I remember. Did we achieve consensus on this design?
>
> The original author Abhijit expressed support for the SESSION/OBJECT
> concept before I started working on the code and so has Stephen Frost.
> As far as I know all outstanding comments from the community have been
> addressed.
>
> Overall behavior has not changed very much since being submitted to the
> CF in February - mostly just tweaks and additional options.
>
>> And one question; OBJECT logging of all tuple deletion (i.g. DELETE
>> FROM hoge) seems like not work as follows.
>>
>>
>> =# grant all on bar TO masahiko;
>>
>> (1) Delete all tuple
>> =# insert into bar values(1);
>> =# delete from bar ;
>> NOTICE: AUDIT: SESSION,47,1,WRITE,DELETE,TABLE,public.bar,delete from bar ;
>> DELETE 1
>>
>> (2) Delete specified tuple (but same result as (1))
>> =# insert into bar values(1);
>> =# delete from bar where col = 1;
>> NOTICE: AUDIT: OBJECT,48,1,WRITE,DELETE,TABLE,public.bar,delete from
>> bar where col = 1;
>> NOTICE: AUDIT: SESSION,48,1,WRITE,DELETE,TABLE,public.bar,delete from
>> bar where col = 1;
>> DELETE 1
>
> Definitely a bug. Object logging works in the second case because the
> select privileges on the "col" column trigger logging. I have fixed
> this and added a regression test.
>
> I also found a way to get the stack memory context under the query
> memory context. Because of the order of execution it requires moving
> the memory context but I still think it's a much better solution. I was
> able to remove most of the stack pops (except function logging) and the
> output remained stable.
>
> I've also added some checking to make sure that if anything looks funny
> on the stack an error will be generated.
>
> Thanks for the feedback!
>

Thank you for updating the patch!
I ran the postgres regression test on database which is enabled
pg_audit, it works fine.
Looks good to me.

If someone don't have review comment or bug report, I will mark this
as "Ready for Committer".

Regards,

-------
Sawada Masahiko