Re: role self-revocation

On Thu, Mar 10, 2022 at 12:45 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

>
> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > On Thu, Mar 10, 2022 at 11:05 AM Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
>
Why not just look at the admin_option field of pg_auth_members...? I
> don't get why that isn't an even more minimal fix than this idea you
> have of adding a column to pg_authid and then propagating around "this
> user could become a superuser" or writing code that has to go check "is
> there some way for this role to become a superuser, either directly or
> through some subset of pg_* roles?"
>

Indeed, maybe I am wrong on the scope of the patch. But at least for the
explicit attribute it should be no more difficult than changing:

if (grouprole_is_superuser and current_role_is_not_superuser) then error:
to be
if ((grouoprole_is_superuser OR !groupuser_has_adminattr) AND
current_role_is_not_superuser) then error;

I have to imagine that given how fundamental inheritance is to our
permissions system than doing a similar check up the tree wouldn't be
difficult, but I truly don't know with a strong degree of certainty.

Assuming we don't actually rip out CREATEROLE when this change goes in...do
you propose to prohibit a CREATEROLE user from altering the membership
roster of any group which itself is not a member of and also those which it
is a member of but where admin_option is false?

I don't personally have a problem with the current state where CREATEROLE
is an admin for, but not a member of, every non-superuser(-related) role in
the system. If the consensus is to change that then I suppose this becomes
the minimally invasive fix that accomplishes that goal as well. It seems
incomplete though, since you still need superuser to create a group and add
the initial WITH ADMIN member to it. So this seems to work in the "avoid
using superuser" sense if you've also added something that has what
CREATEROLE provides today - admin without membership - but that would have
the benefit of not carrying around all the baggage that CREATEROLE has.

> > I made the observation that being able to manage the membership of a
> group
> > without having the ability to create new users seems like a half a loaf
> of
> > a feature. That's it. I would presume that any redesign of the
> > permissions system here would address this adequately.
>
> If the new design ideas that are being thrown around don't address what
> you're thinking they should, it'd be great to point that out.
>

I mean, you need a Create Role permission in some form, even if it's
deprecating the attribute and making it a predefined role. I picked this
thread up because it seemed like a limited scope that I could get my head
around with the time I have, with the main goal to try to understand this
aspect of the system better. I haven't gone and looked into the main
thread yet.

David J.