Re: CREATEROLE and role ownership hierarchies

On 1/31/22 12:18, Mark Dilger wrote:
>
>> On Jan 31, 2022, at 12:43 AM, Michael Banck <michael(dot)banck(at)credativ(dot)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 relationship between the creator and the createe, and a lot of privileges were dependent on that ownership. Stephen objected that we were creating parallel tracks on which the privilege system was running; things like belonging to a role or having admin on a role were partially conflated with owning a role. He also objected that the pre-v8 patch sets allowed a creator role with the CREATEROLE privilege to give away any privilege the creator had, rather than needing to have GRANT 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 candidate solutions to some of Stephen's objections. In the long run, I want the solution to Stephen's objections to not create 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 thing being given. That includes attributes like BYPASSRLS, CREATEDB, LOGIN, etc., and also ADMIN on any role the createe is granted into.
>
> I tried to structure things for backwards compatibility, considering which things roles with CREATEROLE could give away historically. It turns out they can give away most everything, but not SUPERUSER, BYPASSRLS, or REPLICATION. So I structured the default privileges for CREATEROLE to match. But I'm uncertain that design is any good, and your comments below suggest 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 problem that 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 give that role LOGIN, nor CREATEDB, etc. You'd need to also have admin option on those things. To create a role that can give 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 remember to 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 mess around with arbitrary roles (rather than only roles that you created or have been transferred control over) then we need the concept of role ownership. This patch doesn't go that far, so for now, only superusers can do those things. Assuming some form of this patch is acceptable, the v9 series will resurrect some of the pre-v7 logic for role ownership and say 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