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

Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Oct 29, 2021, at 4:46 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> > But I don't think the concept of role ownership has zero potential
> > confusion, either. For instance, I could certainly imagine a user A
> > creating a role B (and therefore owning it), and then doing "GRANT A TO
> > B". Is there a reason to do that, or is the user confused about what
> > membership versus ownership mean?
>
> In general, I think that would be the result of the user being confused.  But it is hard to say that definitively,
becauseperhaps users A and C want to create a single user B with the union of both their roles, and have agreed to
perform:
>
> user_a%  CREATE ROLE B;
> user_a%  GRANT A TO B;
> user_c%  GRANT C TO B;
>
> The easiest way of thinking about role ownership is that a role's owner is superuser in so far as that role is
concerned. It can drop them, modify them, take their objects away from them, assign other objects to them, etc.
Anythinga superuser could do to impoverish them, their owner can do to impoverish them.  The difference is that an
actualsuperuser can enrich them with anything the superuser likes, whereas their owner can only enrich them with
objectsand privileges that the owner itself has rights to assign. 

I can generally get behind the idea that a user who has been allowed to
create other roles should be able to do various other things with that
role, but should also be limited by what rights they themselves have
(unlike how CREATEROLE works today).

That said, I have a hard time seeing why we're drawing this distinction
of 'ownership' as being ultimately different from simple 'admin' rights
on a role.  In other words, beyond the ability to actually create/drop
roles, having 'admin' rights on a role already conveys just about
everything 'ownership' would.  The things that are getting in the way
there are:

 - Ability to actually create/alter/drop roles, this needs to be
   addressed somehow but doesn't necessarily imply a need for
   'ownership' as a concept.

 - Restriction of a role from being able to implicitly have 'admin'
   rights on itself, as I started a discussion about elsewhere.

 - Some system for deciding who event triggers should fire for.  I don't
   think this should really be tied into the question of who has admin
   rights on whom except to the extent that maybe you can only cause
   event triggers to fire for roles you've got admin rights on (or maybe
   membership in).

One thing that comes to mind is that event triggers aren't the only
thing out there and I have to wonder if we should be thinking about
other things.  As a thought exercise- how is an event trigger really
different from a table-level trigger?  Anyone who has the ability to
create objects is able to create tables, create functions, create
operators, and a user logging in and running SQL can certainly end up
running those things with their privileges.  We've generally recognized
that that's not great and there's been work to get it so that the
'public' schema that everyone has in their search_path by default won't
be world-writable but that isn't exactly a cure-all for the general
problem.

One of the interesting bits is that there's two sides to this.  On the
one hand, as a user, maybe I don't want to run functions of people who I
don't trust.  As an admin/superuser/landlord, maybe I want to require
everyone who I have authority over to run these functions/event
triggers.  I'm not sure that we can find a solution to everything with
this but figure I'd share these thoughts.

Last thought I'll share is that I do believe we're going to want to
provide flexibility when it comes to defining who event triggers run
for, as a given admin may wish for that set to be different from the set
of roles that they ultimately have control over.  I dislike tying these
two things together at such a core level and therefore continue to feel
that CREATE EVENT TRIGGER should be extended in some fashion to allow
individuals who can create them to specify who they are to run for.
Open to different ideas as to how a user could express that, but it
feels to me like that should be a core part of the definition of a
user-defined event trigger (ie: could be "FOR ALL ROLES I OWN" or
whatever, and maybe that's the default, but having that be the only
option isn't appealing).

Thanks,

Stephen