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

Greetings,

On Wed, Oct 20, 2021 at 16:23 Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Oct 20, 2021 at 1:20 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > 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.
>
> I think that's basically giving up the important part of this idea,
> which is to allow meaningful administration without superuser
> privileges. "highly-privileged roles only" sounds like in practice it
> would amount to the superuser or someone who can become the superuser
> -- and thus probably wouldn't include the "master tenant" role in a
> service provider environment.

I’m in agreement with Robert on this point.

I don't really see what the problem is with Tom's proposal[1,2], or
> why the role self-administration thread is necessarily a blocker. It's
> true that if X creates an event trigger and it fires for Y because X
> can become Y, then Y might be able to revoke membership in Y from X
> and thus circumvent the event trigger firing. But that is a severable
> problem. We can fail to solve that problem and still be better off
> than today, because at least with the proposed change a cooperating
> group of users (or one whose ability to execute GRANT and REVOKE is
> restricted by some other means) can benefit from event triggers
> without any of them being superuser. If we make this change *and also*
> resolve the role self-administration problem, then it can also work in
> cases where a more privileged user needs to enforce event trigger
> firing against a less-privileged user.

I’m not thrilled with the idea of putting this out there without addressing
the risk that a role could circumvent it.

I did want to say that the idea put forward by Jeff of being able to
specify a set of users which an event trigger should fire for is an
interesting and potentially quite useful capability. Perhaps I don’t want a
given event trigger to fire for ALL of the roles which I have admin rights
(or ownership or whatever) over but rather some subset. Now, perhaps I
could create a role for that purpose, but also, maybe I haven’t been given
that right for $reasons. Being able to specify the roles for which an
event trigger fires would be useful in such cases- and now we are down to
just working out who is allowed to create event triggers to operate upon
whom. Admin rights on the role could certainly be one way of addressing
that, or we could have that be “ownership”, or it could potentially be an
explicitly GRANT’able ability on a role, similar to what “admin” is today.

I have to say that the last of those options strikes me as particularly
appealing and potentially something which could be extended (what about
setting GUCs for a particular role? Or maybe some subset of GUCs? Or
privilege to change/reset a password, or other things?).

I don’t think this information would be stored in the same manner as the
per-object GRANT system that we have today and by avoiding that perhaps we
can a more extensible method that hasn’t got us worrying about having
enough bits too.

Thanks,

Stephen