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

On Tue, 2021-10-19 at 18:52 -0400, Stephen Frost wrote:
> > The implementation in the patch set does this, but with finer
> > grained precision, because the universe of roles is divided into
> > more than just superuser vs. non-superuser.
>
> This last point is particularly important. Non-super users may be
> able to become superuser and those roles which are able to need to
> also be protected. Only protecting superuser roles themselves is
> *not* enough.

I'm trying to suggest an approach that is flexible enough (not limited
to superusers), but also addresses Noah's complaint that ordinary role
membership should not implicitly control event trigger firing.

The most direct way to address Noah's complaint is to make a new
explicit user option BYPASS_EVENT_TRIGGERS, which controls whether
event triggers fire or not.

To create an event trigger, a user would still need to be a member of
predefined role pg_create_event_trigger, which would still be a highly
privileged user that can essentially take over any user without
BYPASS_EVENT_TRIGGER. In practice, the members of
pg_create_event_trigger would be pseudo-superusers, or highly-
privileged roles that come along with a C extension that needs event
triggers.

Details:
* Event triggers created by a superuser would never be bypassed.
* Superusers would always bypass event triggers unless the event
trigger is owned by another superuser.
* If a role is highly privileged and/or can become superuser, it
should have BYPASS_EVENT_TRIGGERS specified so that members of
pg_create_event_trigger can't escalate to that role.
* Normal users should not have BYPASS_EVENT_TRIGGERS.

Another benefit of this is that it makes this event trigger change
independent of the Role Self-Administration discussion, so it could
potentially be committed sooner.

A downside is that with my suggestion, event triggers would still be
for the highly-privileged roles only. Allowing unprivileged users to
create event triggers that have limited scope might allow some really
interesting use cases. There might be some options here, like allowing
any user to create an event trigger that only affects that user.

Regards,
Jeff Davis

(Aside: I'm not really sure where the line is between adding a
predefined role and adding a user option. Are user options just the old
way, and predefined roles the new way?)