Re: pgaudit - an auditing extension for PostgreSQL

* Abhijit Menon-Sen (ams@2ndquadrant.com) wrote:
> At 2014-05-04 08:52:42 -0400, sfrost@snowman.net wrote:
> > This also addresses things like anonymous DO blocks and functions
> > then..?  With enough information to be useful for forensics?
>
> For DML, it addresses anything that goes through InitPlan (which, via
> ExecCheckRTPerms, calls the ExecutorCheckPerms_hook). Yes, that does
> include table accesses within DO blocks:

Right, I figured, but wanted to clarify the usefullness of this goes
beyond just views.

> > We'd need to also consider permissions and how these are managed.
>
> Yes. For pgaudit, we kept it simple and made pgaudit.log
> superuser-only[1].

Yeah, for an initial version that makes sense, but I'm sure we'll need
more.

> I haven't given much thought to this area, because I didn't know what
> mechanism to use to set per-object auditing parameters. For a contrib
> module, extensible reloptions would have been convenient. But in-core
> auditing support could use a proper reloption, I suppose. It's a pity
> that extensions can't add reloptions.

Another reloption is one option, or an extension on the ACL system (for
that piece of it), or we could make a new catalog for it (ala
pg_seclabel), or perhaps add it on to one (pg_seclabel but rename it to
pg_security..?).

> Personally, I don't think it's important to support GRANT-ing the
> ability to set audit parameters. I think it would be reasonable even to
> say that only the superuser could do it (but I can imagine people being
> unhappy if the owner couldn't[2]).

I disagree.  Perhaps it could be a role-level permission instead of one
which is per-table, but I don't think this should be superuser-only.

> 1. I wish it were possible to prevent even the superuser from disabling
>    audit logging once it's enabled, so that if someone gained superuser
>    access without authorisation, their actions would still be logged.
>    But I don't think there's any way to do this.

Their actions should be logged up until they disable auditing and
hopefully those logs would be sent somewhere that they're unable to
destroy (eg: syslog).  Of course, we make that difficult by not
supporting log targets based on criteria (logging EVERYTHING to syslog
would suck).

I don't see a way to fix this, except to minimize the amount of things
requiring superuser to reduce the chances of it being compromised, which
is something I've been hoping to see happen for a long time.

> 2. On the other hand, I can also imagine a superuser being justifiably
>    annoyed if she were to carefully configure auditing, and random users
>    then enabled audit-everything for their newly-created tables and
>    filled the audit table with junk.

While I understand your concern, I'm not sure it's really what we should
be designing for.  One of the oft-commented on distinctions is to have
roles which are specifically auditors- and they have very limited access
beyond that.  To that point, a role-level attribute for 'can modify
auditing' seems key, but we wouldn't want such a role to also be an
owner of every relation they need to be able to modify auditing for, yet
it would be valuable to constrain the auditor to only being able to
modify auditing on certain sets of tables.

That seems to boil down to a GRANT'able option, since that gives the
per-table granularity.  That would also make the role-level attribute
unnecessary, which is appealing.  The downside of this is that the owner
ends up being untimately in control here- but then, the owner could
presumably drop and recreate the object anyway, and where would we be
then?  Perhaps having it be up to the owner isn't such a bad approach.

The implementation-level concern here comes from the way we actually
store and manage those permissions.  We're awful short of bits when we
start thinking about all the things we might want to make independently
grant'able.
Thanks,
    Stephen