Re: pgaudit - an auditing extension for PostgreSQL

On 1/21/15 6:50 PM, Stephen Frost wrote:
>> 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.
> Break in which way..? If you're saying "it'll be easy for a user to
> misconfigure" then I might agree with you- but documentation and
> examples can help to address that.

I'm worried about user misconfiguration. Setting up a good system of roles (as in, distinguishing between application accounts, users, account(s) used to deploy code changes, DBAs, etc) is already tricky because of all the different use cases to consider. I fear that adding auditing to that matrix is just going to make it worse.

I do like Robert's idea of role:action:object triplets more, though I'm not sure it's enough. For example, what happens if you

CREATE ROLE su SUPERUSER NOINHERIT NOLOGIN;
CREATE ROLE su_role IN ROLE su NOLOGIN;
GRANT su_role TO bob;

and have

su_role:*:*

Does bob get audited all the time then? Only when he does SET ROLE su? For that matter, how does SET ROLE affect auditing?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com