Re: pgaudit - an auditing extension for PostgreSQL

* Abhijit Menon-Sen (ams(at)2ndquadrant(dot)com) wrote:
> At 2014-06-23 08:50:33 -0400, sfrost(at)snowman(dot)net wrote:
> > I'm not a huge fan of adding this as a contrib module
>
> I added it to the CF because we're interested in auditing functionality
> for 9.5, and as far as I can tell, there's nothing better available. At
> the moment, the contrib module has the advantage that it exists :-) and
> works with 9.[34] (and could be made to work with 9.2, though I didn't
> bother for the initial submission).

Sure, I understand this.

> > unless we can be
> > quite sure that there's a path forward from here to a rework of the
> > logging in core which would actually support the features pg_audit is
> > adding, without a lot of pain and upgrade issues.
>
> What sort of pain and upgrade issues did you have in mind?

I admit to having not looked in depth at it but my immediate concern
would be any objects which it creates in the database and worry about
the exact issues that hstore has. I also wouldn't want this to become
an excuse to not improve our current logging infrastructure, which is
how we got to the place we are wrt partitioning, imv.

> > I'd expect a catalog table or perhaps changes to pg_class (maybe other
> > things also..) to define what gets logged..
>
> Please explain?

...

> (I wish extensions were able to add reloptions. That would have made it
> relatively easy for us to implement per-object audit logging.)

Well, this, specifically. We can't control what gets audited through
the catalog and have to manage that outside of PG, right? Or are there
tables which are part of pg_audit to define this that then would have to
be addressed in an upgrade scenario..? As I recall from looking before,
it's the former.

> > I'd also like to see the ability to log based on the connecting user,
> > and we need to log under what privileges a command is executing
>
> I imagine it's not useful to point out that you can do the former with
> pgaudit (using ALTER ROLE … SET), and that we log the effective userid
> for the latter (though maybe you had something more in mind)…

Are both the connected user and the current role that the command is
running under logged? That's what I was getting at. As for ALTER ROLE-
if that can be done then that's an example of a potential upgrade
issue..

> > and, really, a whole host of other things..
>
> …but there's not a whole lot I can do with that, either.

It was more a comment on the goal of improving the logging
infrastructure in PG than a knock against pgaudit.

> Is the minimal set of auditing features that we would need in-core very
> different from what pgaudit already has?

It's funny because, in a way, I see this as having more-or-less the same
issues that the RLS patch has- more control is needed through the
catalog, through the grammar, and generally needs to be more integrated.
Perhaps pgaudit as a contrib module is able to avoid having everything
at the initial implementation which an in-core capability would have to
have, but for my 2c, I'd much rather have that in-core capability and I
worry that adding pgaudit as an external feature now would end up
preventing us from moving forward in this area for years to come..

That's not a particularly fair argument, but it's my current feeling on
the subject.

Thanks,

Stephen