Re: pgaudit - an auditing extension for PostgreSQL

Hi list,

On 20/01/15 23:03, Jim Nasby wrote:> On 1/20/15 2:20 PM, Robert Haas wrote:
>> On Tue, Jan 20, 2015 at 1:05 AM, Abhijit
>> Menon-Sen<ams(at)2ndquadrant(dot)com> wrote:
>>> >So when I'm trying to decide what to audit, I have to:
>>> >
>>> > (a) check if the current user is mentioned in .roles; if so,
>>> audit.
>>> >
>>> > (b) check if the current user is a descendant of one of the roles
>>> > mentioned in .roles; if not, no audit.
>>> >
>>> > (c) check for permissions granted to the "root" role and see if
>>> that
>>> > tells us to audit.
>>> >
>>> >Is that right? If so, it would work fine. I don't look forward to
>>> trying
>>> >to explain it to people, but yes, it would work (for anything you could
>>> >grant permissions for).
>> I think this points to fundamental weakness in the idea of doing this
>> through the GRANT system. Some people are going want to audit
>> everything a particular user does, some people are going to want to
>> audit all access to particular objects, and some people will have more
>> complicated requirements. Some people will want to audit even
>> super-users, others especially super-users, others only non
>> super-users. None of this necessarily matches up to the usual
>> permissions framework.
>
> +1. In particular I'm very concerned with the idea of doing this via
> roles, because that would make it trivial for any superuser to disable
> auditing.

Rejecting the audit administration through the GRANT system, on the
grounds that it easy for the superuser to disable it, seems unreasonable
to me, since superusers are different from non-superusers in a
fundamental way.

The superuser operates on the administration level of the database, in
contrast with users that need access to the actual information in the
data to perform their job. An organization that wants to implement an
auditing strategy needs to think in different terms to audit
user/application level actions, and administrator/superuser actions.
Consequently it should be no surprise when PostgreSQL mechanisms for
auditing behave different for superusers vs non superusers.

The patch as it is, is targeted at auditing user/application level
access to the database, and as such it matches the use case of auditing
user actions.

Auditing superuser access means auditing beyond the running database.
The superuser can dump a table, and pipe this data everywhere outside of
the auditing domain. I cannot begin to imagine the kind of security
restrictions you'd need to audit what happens with data after libpq has
produced the results. My best guess would be to incorporate some kind of
separation of duty mechanism; only allow certain superuser operations
with two people involved.

regards,
Yeb Havinga