Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

> On Nov 1, 2021, at 1:13 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>
>> Having Batman *own* all residents in Gotham city would work, if we can agree on a role ownership system. It has the downside that only a role's (direct or indirect) owner can do the auditing, though. That's more flexible than what we have today, where only superuser can do it, but maybe some people would want to argue for a different solution with even more flexibility? A grantable privilege perhaps? But whatever it is, the reasoning about who gets audited and who does not must be clear enough that Batman can pass a compliance audit.
>
> What about roles which Batman owns but which he *doesn't* want the event
> trigger to fire for?

I think Batman just has the event trigger exit early for that. There is nothing we can hardcode for filtering users into and out of the trigger that will be as flexible as the logic that Batman can implement in the trigger itself. We only need to worry about Batman over stepping his authority. It's not our job to filter further than that.

> Note that event triggers are not strictly limited to the auditing case.
> Viewing them through that lense masks other quite common use-cases which
> are also importnat to consider (like preventing many users, but not all,
> from being able to DROP objects as a clear example).

Nothing in my proposal limits what superusers can do with event triggers they create. The issue under discussion is entirely to do with what non-superusers are allowed to do with event triggers. I see no reason why some ordinary role "joe" should be allowed to thwart DROP commands issued on a table that "joe" doesn't own by roles that "joe" doesn't own. Maybe "own" here should be "have ADMIN on", but it has to be something.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company