Re: pgaudit - an auditing extension for PostgreSQL

On Wed, Jul 30, 2014 at 02:49:25PM -0400, Alvaro Herrera wrote:
> Stephen Frost wrote:
> > * Bruce Momjian (bruce(at)momjian(dot)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 as we'd then have to figure out how to make pg_upgrade deal with
> > moving such a configuration from the extension's table or from
> > reloptions into SQL commands which work against the version of PG which
> > implements the capability in core.
>
> I think you're making too much of this upgrade issue. Consider
> autovacuum's history: in its initial form, we made it configurable per
> table by asking users to insret rows in the pg_autovacuum catalog. It
> was a pretty horrible user interface, it was very error prone, it didn't
> survive dump/restore cycles (And autovacuum was a core feature, not an
> extension). We were able to iron out a lot of issues during those
> initial two releases with the accumulated experience. We eventually
> replaced the interface with reloptions (ALTER TABLE SET), which is what
> we have today. We didn't add pg_upgrade code to migrate settings from
> the old way to the new one; users who wished to do this were responsible
> for handling it for themselves.
>
> Sure, we're a more mature project now and our standards are higher. So
> I think we should provide documented procedures to upgrade from pgaudit
> to integrated feature; but that should suffice.

Agreed. It is one thing to have gigabytes of data in hstore, and
another to have per-object audit settings.

pg_upgrade --check could detect pg_audit and suggest the user run the
Perl script before upgrading, and run the SQL commands through psql
after.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +