Re: Auditing extension for PostgreSQL (Take 2)

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!

--
- David Steele
david@pgmasters.net