Re: allowing for control over SET ROLE - Mailing list pgsql-hackers

On Sun, Oct 16, 2022 at 12:34 PM Stephen Frost <sfrost@snowman.net> wrote:
> As we work through splitting up the privileges and managing them in a
> more fine-grained way, it seems clear that we'll need to have a similar
> split for ADMIN rights on roles- that is, we'll need to be able to
> say "role X is allowed to GRANT INHERIT for role Y to other roles, but
> not SET".

I don't think this is clear at all, actually.  I see very little
advantage in splitting up ADMIN OPTION this way. I did think about
this, because it would be more consistent with what we do for table
privileges, but INHERIT and SET overlap enough from a permissions
point of view that there doesn't seem to be a lot of value in it. Now,
if we invent a bunch more per-grant options, things might look
different, but in my opinion that has dubious value. Right now, all
role privileges other than the ones that are controlled by ADMIN
OPTION, INHERIT, and what I'm proposing to make controlled by SET, are
gated by CREATEROLE or by SUPERUSER. The list looks something like
this: change the INHERIT flag on a role, change the CREATEROLE flag on
a role, change the CREATEDB flag on a role, change the connection
limit for a role, change the VALID UNTIL time for a role, change the
password for a role other than your own, drop the role.

And that's a pretty obscure list of things. I do think we need better
ways to control who can do those things, but I don't think making them
all role privileges and then on top of that giving them all separate
admin options is the right way to go. It's slicing things incredibly
finely to give alice the right to grant to some other user the right
to set only the VALID UNTIL time on role bob, but not the right to
modify role bob in any other way or the right to confer the ability to
set VALID UNTIL for any other user. I can't believe we want to go
there. It's not worth the permissions bits, and even if we had
infinite privilege bits available, it's not worth the complexity from
a user perspective. Maybe you have some less-obscure list of things
that you think should be grantable privileges on roles?

Another thing to consider is that, since ADMIN OPTION is, as I
understand it, part of the SQL specification, I think it would move us
further from the SQL specification. I think we will be better off
thinking of ADMIN OPTION on a role as roughly equivalent to being the
owner of that role, which is an indivisible privilege, rather than
thinking of it as equivalent to GRANT OPTION on each of N rights,
which could then be subdivided.

> I'm still half-tempted to say that predefined roles should just be dealt
> with as a special case.. but if we split ADMIN in the manner as
> described above then maybe we could get away with not having to, but it
> would depend a great deal of people actually reading the documentation
> and I'm concerned that's a bit too much to ask in this case.

I don't think any splitting of ADMIN would be required to solve the
predefined roles problem. Doesn't the patch I proposed do that?

-- 
Robert Haas
EDB: http://www.enterprisedb.com