Re: pgaudit - an auditing extension for PostgreSQL

Abhijit,

* Abhijit Menon-Sen (ams(at)2ndquadrant(dot)com) wrote:
> 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'll certainly still have some external configuration, but not things
which are referring to specific roles or tables. We will also need a
way to manage that configuration such that an individual could have only
access to modify the audit parameters without also having lots of other
rights to the system. Having a separate auditing role is called out as
a requirement for quite a few different industries.

> 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.

This is going a bit father than I intended. I was trying to outline
where we want to be going with this and pointing out that if we put
pgaudit out there as "this is how you do auditing in PG" that we'll be
stuck with it more-or-less forever- and that it won't satisfy critical
use-cases and would minimize PG's ability to be used in those
industries. Further, if we go with this approach now, we will end up
creating a huge upgrade headache (which could end up being used as a
reason to not have the in-core capabilities).

A catalog-based approach which had only the specific features that
pgaudit has today, but is implemented in such a way that we'll be able
to add those other features in the future would be great, imv. What
this looks like, however, is a solution which only gets us half-way and
then makes it quite difficult to go farther.

> 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.

Great, glad we agree on that. :)

> 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).

I've clearly miscommunicated and my apologies for that- it's not that
the feature set isn't useful or that using GUCs and reloptions wouldn't
be useful, it's much more than I don't want that to be our end-state and
I don't think we'll have an easy time moving from GUCs and reloptions to
the end-state that I'm looking at. What I was balking at was the notion
that GUCs and reloptions would satisfy the auditing requirements for all
(or even most) of our users. It would be sufficient and useful for
some, but certainly not all.

> Some other messages in the thread have suggested a similar mechanism,
> which implies that at least some people wouldn't be unhappy with it.

Sure.

> 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.

We also wouldn't be able to provide fine-grained control over who is
allowed to define the auditing requirements and I don't see how you'd
support per-column auditing definitions or integrate auditing with RLS.

> 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.

Agreed- but as has been pointed out to me on the RLS thread, we don't
want to put something into 9.5 which will mean we can't ever catch up
because of backwards compatibility or pg_upgrade issues.

Thanks,

Stephen