Re: pgaudit - an auditing extension for PostgreSQL

At 2014-06-25 10:36:07 -0400, sfrost(at)snowman(dot)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?

-- Abhijit