Re: pgaudit - an auditing extension for PostgreSQL

On Thu, Jun 26, 2014 at 3:50 AM, Abhijit Menon-Sen <ams@2ndquadrant.com> wrote:
> At 2014-06-25 10:36:07 -0400, sfrost@snowman.net wrote:
>>
>> To me, that's ridiculous on the face of it.  Other databases have had
>> this kind of capability as a matter of course for decades- we are far
>> behind in this area.
>
> OK, I've done a bit more thinking, but I'm not convinced that it's so
> cut-and-dried (as "ridiculous on the face of it").
>
> Ian looked into the auditing capabilities of various (SQL and otherwise)
> systems some time ago. Informix handles its auditing configuration
> entirely outside the database. DB2 uses a mixture of in-database and
> external configuration. Oracle stores everything in the database.
>
> We're certainly not going to invent a mechanism other than GUC settings
> or catalog tables for auditing information (à la Informix). What I think
> you're saying is that without storing stuff in the catalog tables, we're
> not going to be able to offer auditing capabilities worth having. I can
> appreciate that, but I'm not sure I agree.
>
> You're right, it isn't possible to sanely do something like "AUDIT
> SELECT ON TABLE t1 FOR role1" without storing configuration in the
> catalog tables. You're right, it would be nice to have that level
> of control.
>
> The part that I don't agree about is that using a GUC setting and
> a reloption won't give us anything useful. We can use that to do
> global, per-database, per-user, and per-object auditing with just
> mechanisms that are familiar to Postgres users already (ALTER … SET).
> Some other messages in the thread have suggested a similar mechanism,
> which implies that at least some people wouldn't be unhappy with it.
>
> No, we couldn't do combinations of TABLE/ROLE, but there's also no
> terrible conflict with doing that via some new "AUDIT foo ON bar"
> syntax later.
>
> As you point out, we're decades behind, and I seriously doubt if
> we're going to jump forward a few decades in time for 9.5.
>
> What do others think of the tradeoff?

I agree with Stephen's statement that the audit configuration
shouldn't be managed in flat files.  If somebody wants to do that in
an extension, fine, but I don't think we should commit anything into
our source tree that works that way.  The right way to manage that
kind of configuration is with GUCs, or reloptions, or (security)
labels, or some other kind of catalog structure.  Renaming an object
shouldn't be able to break auditing, but if the audit configuration is
a list of table names in a file somewhere, it will.

I disagree with Stephen's proposal that this should be in core, or
that it needs its own dedicated syntax.  I think contrib is a great
place for extensions like this.  That makes it a whole lot easier for
people to develop customized vesions that meet particular needs they
may have, and it avoids bloating core with a bunch of stuff that is
only of interest to a subset of users.  We spent years getting sepgsql
into a form that could be shipped in contrib without substantially
burdening core, and I don't see why this feature should be handed any
differently.

In fact, considering how much variation there is likely to be between
auditing requirements at different sites, I'm not sure this should
even be in contrib at this point.  It's clearly useful, but there is
no requirement that every useful extension must be bundled with the
core server, and in fact doing so severely limits our ability to make
further changes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company