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

Greetings,

* Noah Misch (noah(at)leadboat(dot)com) wrote:
> On Wed, Oct 20, 2021 at 11:27:11AM -0700, Jeff Davis wrote:
> > On Wed, 2021-10-20 at 10:32 -0700, Mark Dilger wrote:
> > > I'd like to have a much clearer understanding of Noah's complaint
> > > first. There are multiple things to consider: (1) the role which
> > > owns the trigger, (2) the role which is performing an action which
> > > would cause the trigger to fire, (3) the set of roles role #1 belongs
> > > to, (4) the set of roles role #1 has ADMIN privilege on, (5) the set
> > > of roles that role #2 belongs to, and (6) the set of roles that role
> > > #2 has ADMIN privilege on. Maybe more?
>
> > > I'd like to know precisely which combinations of these six things are
> > > objectionable, and why. There may be a way around the objections
> > > without needing to create new user options or new privileged roles.
> >
> > I can't speak for Noah, but my interpretation is that it would be
> > surprising if GRANT/REVOKE or membership in an ordinary role had
> > effects other than "permission denied" errors. It might make sense for
> > event trigger firing in all the cases we can think of, but it would
> > certainly be strange if we started accumulating a collection of
> > behaviors that implicitly change when you move in or out of a role.
> >
> > That's pretty general, so to answer your question: it seems like a
> > problem to use #3-6 in the calculation about whether to fire an event
> > trigger.
>
> Exactly. That's the main point. Also, it's currently a best practice for
> only non-LOGIN roles to have members. The proposed approach invites folks to
> abandon that best practice. I take the two smells as a sign that we'll regret
> adopting the proposal, despite not knowing how it will go seriously wrong.

This seems like a pretty good point, which leads me to again think that
we should explicitly add a way for an individual who can create event
triggers to be able to specify for whom the event trigger should fire,
and only allow them to specify roles other than their own provided they
have been given that authority (either explicitly somehow or implicitly
based on some defined access that they have to that other role).

Thanks,

Stephen