Re: CREATEROLE and role ownership hierarchies - Mailing list pgsql-hackers

On 1/31/22 12:18, Mark Dilger wrote:
>
>> On Jan 31, 2022, at 12:43 AM, Michael Banck <michael.banck@credativ.de> wrote:
>> Ok, sure. I think this topic is hugely important and as I read the
>> patch anyway, I added some comments, but yeah, we need to figure out
>> the fundamentals first.
> Right.
>
> Perhaps some background on this patch series will help.  The patch versions before v8 were creating an owner-owned
relationshipbetween the creator and the createe, and a lot of privileges were dependent on that ownership.  Stephen
objectedthat we were creating parallel tracks on which the privilege system was running; things like belonging to a
roleor having admin on a role were partially conflated with owning a role.  He also objected that the pre-v8 patch sets
alloweda creator role with the CREATEROLE privilege to give away any privilege the creator had, rather than needing to
haveGRANT or ADMIN option on the privilege being given.
 
>
> The v8-WIP patch is not a complete replacement for the pre-v8 patches.  It's just a balloon I'm floating to try out
candidatesolutions to some of Stephen's objections.  In the long run, I want the solution to Stephen's objections to
notcreate problems for anybody who liked the way the pre-v8 patches worked (Robert, Andrew, and to some extent me.)
 
>
> In this WIP patch, for a creator to give *anything* away to a createe, the creator must have GRANT or ADMIN on the
thingbeing given.  That includes attributes like BYPASSRLS, CREATEDB, LOGIN, etc., and also ADMIN on any role the
createeis granted into.
 
>
> I tried to structure things for backwards compatibility, considering which things roles with CREATEROLE could give
awayhistorically.  It turns out they can give away most everything, but not SUPERUSER, BYPASSRLS, or REPLICATION.  So I
structuredthe default privileges for CREATEROLE to match.  But I'm uncertain that design is any good, and your comments
belowsuggest that you find it pretty hard to use.
 
>
> Part of the problem with trying to be backwards compatible is that we must break compatibility anyway, to address the
problemthat historically having CREATEROLE meant you effectively had ADMIN on all non-superuser roles.  That's got to
change. So in part I'm asking pgsql-hackers if partial backwards compatibility is worth the bother.
 
>
> If we don't go with backwards compatibility, then CREATEROLE would only allow you to create a new role, but not to
givethat role LOGIN, nor CREATEDB, etc.  You'd need to also have admin option on those things.  To create a role that
cangive those things away, you'd need to run something like:
 
>
> CREATE ROLE michael
>     CREATEROLE WITH ADMIN OPTION    -- can further give away "createrole"
>     CREATEDB WITH ADMIN OPTION    -- can further give away "createdb"
>     LOGIN WITH ADMIN OPTION    -- can further give away "login"
>     NOREPLICATION WITHOUT ADMIN OPTION    -- this would be implied anyway
>     NOBYPASSRLS WITHOUT ADMIN OPTION    -- this would be implied anyway
>     CONNECTION LIMIT WITH ADMIN OPTION    -- can specify connection limits
>     PASSWORD WITH ADMIN OPTION    -- can specify passwords
>     VALID UNTIL WITH ADMIN OPTION    -- can specify expiration
>
> (I'm on the fence about the phrase "WITH ADMIN OPTION" vs. the phrase "WITH GRANT OPTION".)
>
> Even then, when "michael" creates new roles, if he wants to be able to further administer those roles, he needs to
rememberto give himself ADMIN membership in that role at creation time.  After the role is created, if he doesn't have
ADMIN,he can't give it to himself.  So, at create time, he needs to remember to do this:
 
>
> SET ROLE michael;
> CREATE ROLE mark ADMIN michael;
>
> But that's still a bit strange, because "ADMIN michael" means that michael can grant other roles membership in
"mark",not that michael can, for example, change mark's password.  If we don't want CREATEROLE to imply that you can
messaround with arbitrary roles (rather than only roles that you created or have been transferred control over) then we
needthe concept of role ownership.  This patch doesn't go that far, so for now, only superusers can do those things.
Assumingsome form of this patch is acceptable, the v9 series will resurrect some of the pre-v7 logic for role ownership
andsay that the owner can do those things.
 
>

This seems complicated. Maybe the previous proposal was too simple, but
simplicity has some virtues. It seemed to me that more complex rules
could possibly have been implemented for those who really needed them by
using SECURITY DEFINER functions. The whole 'NOFOO WITH ADMIN OPTION'
thing seems to me a bit like a POLA violation. Nevertheless I can
probably live with it as long as it's *really* well documented. Even so
I suspect it would be too complex for many, and they will just continue
to use superusers to create and manage roles if possible.


cheers


andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com