Re: role self-revocation - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: role self-revocation
Date
Msg-id CAKFQuwaQ1AgyE2zQdXeA3e2Q0jMwWEgnAohLj5MjpZyoJv_U8g@mail.gmail.com
Whole thread Raw
In response to Re: role self-revocation  (Stephen Frost <sfrost@snowman.net>)
List pgsql-hackers
On Thu, Mar 10, 2022 at 12:45 PM Stephen Frost <sfrost@snowman.net> wrote:

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Thu, Mar 10, 2022 at 11:05 AM Stephen Frost <sfrost@snowman.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.

pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: pg_walinspect - a new extension to get raw WAL data and WAL stats
Next
From: Robert Haas
Date:
Subject: Re: role self-revocation