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

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Mon, Oct 25, 2021 at 12:20 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > 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).
>
> I agree that Noah has a reasonably good point here. I don't think it's
> a total slam-dunk but it it's certainly not a stupid argument.

Ok.

> Conceding that point for the purposes of discussion, I don't
> understand how this kind of proposal gets us out from under the
> problem. Surely, it can't be the case that user X can cause event
> trigger E to run as user Y unless X can become Y, because to do so
> would allow X to usurp Y's privileges, except in the corner case where
> Y never does anything that can trigger an event trigger. But if X has
> to be able to become Y in order to force E to be run by Y, then I
> think we've made no progress in addressing Noah's complaint.

X having rights over Y is what would allow X to create an event trigger
which fires when Y does $something, but the act of GRANT'ing Y to X
wouldn't make it automatically start happening. The latter is what I
believed Noah's concern was around.

The downside there though is that GRANT'ing of roles to other roles is
how we build up sets of roles and you'd certainly wish to be able to
leverage that when deciding which roles a given event trigger should
fire for. If we made that work for event triggers then you'd still have
the case that *some* GRANT A to B would cause event triggers to suddenly
start happening for B without other actions being taken. Still, in that
case you could create specific such roles to manage that independently
of which roles happened to have admin rights over which other roles.

Examples might help here.

CREATE ROLE X;
CREATE ROLE Y;
CREATE ROLE Z;

GRANT Y to X;
GRANT Z to X;

SET ROLE X;
CREATE EVENT TRIGGER do_stuff();

Under one approach, that event trigger then fires for X, Y and Z. What
if you don't actually want it to though? What if some role Q is later
created and GRANT'd to X? Then the event trigger would also fire for
them.

Consider instead:

CREATE ROLE X;
CREATE ROLE Y;
CREATE ROLE Z;

GRANT Y to X;
GRANT Z to X;

SET ROLE X;
CREATE EVENT TRIGGER FOR Y do_stuff();

Now, X has explicitly said that they want the event trigger to fire for
role Y and if the event trigger fires or not is no longer based on
membership in the role creating the trigger but instead depends on being
the role which the event trigger was explicitly defined to fire on.

Does membership in role Y cause the event trigger to fire for that role?
I'd argue that the answer is probably 'yes', but at least it's no longer
tied back to membership in X (the owner of the trigger). That Noah
explicitly mentioned 'login' roles vs. 'non-login' roles makes me think
this is more in line with what the argument was about- the owner of the
trigger would almost certainly be a 'login' role. All that said, this
is definitely a complex area and there's certainly a lot of different
ways we could go.

Thanks,

Stephen