Re: Auditing extension for PostgreSQL (Take 2)

From: David Steele <david(at)pgmasters(dot)net>
To: Sawada Masahiko <sawada(dot)mshk(at)gmail(dot)com>
Cc: Tatsuo Ishii <ishii(at)postgresql(dot)org>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Abhijit Menon-Sen <ams(at)2ndquadrant(dot)com>, Fujii Masao <masao(dot)fujii(at)gmail(dot)com>
Subject: Re: Auditing extension for PostgreSQL (Take 2)
Date: 2015-04-23 18:23:29
Message-ID: 553938A1.7030707@pgmasters.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)pgmasters(dot)net

Attachment Content-Type Size
pg_audit-v10.patch text/plain 132.2 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2015-04-23 19:05:42 Moving ExecInsertIndexTuples and friends to new file
Previous Message Alvaro Herrera 2015-04-23 18:10:41 Re: Reducing tuple overhead