Re: pgaudit - an auditing extension for PostgreSQL

On 2/17/15 4:40 AM, Yeb Havinga wrote:
> 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.

Agreed. This logic could be applied to any database object: why have
tables when a superuser can so easily drop them and cause data loss?

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

Exactly. This patch (and my rework) are focused entirely on auditing
the actions of normal users in the database. While auditing can be
enabled for superusers, there's no guarantee that it's reliable since it
would be easy for a superuser to disable. Normal users can be
configured to not have that capability, so auditing them is reliable.

--
- David Steele
david(at)pgmasters(dot)net