Re: pgaudit - an auditing extension for PostgreSQL

On 1/21/15 5:38 PM, Stephen Frost wrote:
> * Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)com) wrote:
>> On 1/20/15 9:01 PM, Stephen Frost wrote:
>>> * Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)com) wrote:
>>>>> +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. The only good option I could see to provide this kind of flexibility would be allowing the user to provide a function that accepts role, object, etc and make return a boolean. The performance of that would presumably suck with anything but a C function, but we could provide some C functions to handle simple cases.
>>> Superusers will be able to bypass, trivially, anything that's done in
>>> the process space of PG. The only possible exception to that being an
>>> SELinux or similar solution, but I don't think that's what you were
>>> getting at.
>>
>> Not if the GUC was startup-only. That would allow someone with OS access to the server to prevent a Postgres superuser from disabling it.
>
> That is not accurate.
>
> Being startup-only won't help if the user is a superuser.

Crap, I thought postgresql.auto.conf was handled as an #include and therefore you could still preempt it via postgresql.conf

>>> I certainly don't think having the user provide a C function to specify
>>> what should be audited as making any sense- if they can do that, they
>>> can use the same hooks pgaudit is using and skip the middle-man. As for
>>> the performance concern you raise, I actually don't buy into it at all.
>>> It's not like we worry about the performance of checking permissions on
>>> objects in general and, for my part, I like to think that's because it's
>>> pretty darn quick already.
>>
>> I was only mentioning C because of performance concerns. If SQL or plpgsql is fast enough then there's no need.
>
> If this is being done for every execution of a query then I agree- SQL
> or plpgsql probably wouldn't be fast enough. That doesn't mean it makes
> sense to have pgaudit support calling a C function, it simply means that
> we need to find another way to configure auditing (which is what I think
> I've done...).

I'm still nervous about overloading this onto the roles system; I think it will end up being very easy to accidentally break. But if others think it'll work then I guess I'm just being paranoid.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com