Re: pgaudit - an auditing extension for PostgreSQL - Mailing list pgsql-hackers

From Fabrízio de Royes Mello
Subject Re: pgaudit - an auditing extension for PostgreSQL
Date
Msg-id CAFcNs+rWhOCXbPzZNKs=DZ1E7Fbe8f95R9iTzZ-vn4_L-0NySQ@mail.gmail.com
Whole thread Raw
In response to Re: pgaudit - an auditing extension for PostgreSQL  (Simon Riggs <simon@2ndquadrant.com>)
Responses Re: pgaudit - an auditing extension for PostgreSQL
List pgsql-hackers


On Tue, Oct 7, 2014 at 1:24 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> On 31 July 2014 22:34, Stephen Frost <sfrost@snowman.net> wrote:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Stephen Frost <sfrost@snowman.net> writes:
> >> > * Bruce Momjian (bruce@momjian.us) wrote:
> >> >> Actually, thinking more, Stephen Frost mentioned that the auditing
> >> >> system has to modify database _state_, and dumping/restoring the state
> >> >> of an extension might be tricky.
> >>
> >> > This is really true of any extension which wants to attach information
> >> > or track things associated with roles or other database objects.  What
> >> > I'd like to avoid is having an extension which does so through an extra
> >> > table or through reloptions or one of the other approaches which exists
> >> > in contrib and which implements a capability we're looking at adding to
> >> > core
> >>
> >> We have core code that uses reloptions --- autovacuum for instance ---
> >> so I'm not exactly clear on why that's so unacceptable for this.
> >
> > There was a pretty good thread regarding reloptions and making it so
> > extensions could use them which seemed to end up with a proposal to turn
> > 'security labels' into a more generic metadata capability.  Using that
> > kind of a mechanism would at least address one of my concerns about
> > using reloptions (specifically that they're specific to relations and
> > don't account for the other objects in the system).  Unfortunately, the
> > flexibility desired for auditing is more than just "all actions of this
> > role" or "all actions on this table" but also "actions of this role on
> > this table", which doesn't fit as well.
>
> Yes, there is a requirement, in some cases, for per role/relation
> metadata. Grant and ACLs are a good example.
>
> I spoke with Robert about a year ago that the patch he was most proud
> of was the reloptions abstraction. Whatever we do in the future,
> keeping metadata in a slightly more abstract form is very useful.
>

When we discussed about the rejected patch "store-custom-reloptions" I pointed my thoughts about it in http://www.postgresql.org/message-id/CAFcNs+p+2OA2fg7o-8KWmckazjAYWue6mVNnUdpuRpT0PZ8D_g@mail.gmail.com

We can think in a mechanism to create "properties / options" and assign it to objects (table, index, column, schema, ...) like COMMENT does.

A quickly thought:

CREATE OPTION [ IF NOT EXISTS ] name
    VALIDATOR valfunction
    [ DEFAULT value ];

ALTER TABLE name
    SET OPTION optname { TO | = } { value | 'value' | DEFAULT };

It's just a simple thought of course. We must think better about the syntax and purposes.


> I hope we can get pgAudit in as a module for 9.5. I also hope that it
> will stimulate the requirements/funding of further work in this area,
> rather than squash it. My feeling is we have more examples of feature
> sets that grow over time (replication, view handling, hstore/JSONB
> etc) than we have examples of things languishing in need of attention
> (partitioning).
>

+1

Regards.

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: pgaudit - an auditing extension for PostgreSQL
Next
From: Jim Nasby
Date:
Subject: Re: Proposal for better support of time-varying timezone abbreviations