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

On Mon, 2021-09-27 at 11:15 -0700, Mark Dilger wrote:
> Allow non-superusers to create event triggers. The logic already
> existed and is unchanged to handle event triggers owned by non-
> superusers and conditioning those triggers firing on the (trigger-
> owner, role-performing-event) pair. The v7 patch set didn't go quite
> so far as allowing non-superusers to create event triggers, but that
> undercuts much of the benefit of the changes for no obvious purpose.

The thread on role self-administration seems like a dependency here.
And it doesn't look like there's consensus that we should be
conditioning event trigger firing on role membership:

https://postgr.es/m/20211005043438.GB314685@rfd.leadboat.com

Instead, how about:

* make a predefined role pg_event_trigger that allows creating event
triggers
* make it an error for a superuser to fire an event trigger created by
a non-superuser

It doesn't solve the problem hierarchically, but we don't solve other
predefined role privileges hierarchically, either (and for many of them
it makes no sense).

A downside is that the privileged event trigger creator could
accidentally make life annoying for a superuser that's trying to issue
DDL: the superuser would need to disable the event trigger, perform the
action, then re-enable it. But that shouldn't be a practical problem in
sane setups -- superusers shouldn't be performing a lot of DDL, and if
they are, it's good to be explicit that they are bypassing something
configured by their pseudo-admin.

Regards,
Jeff Davis