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

[ Been away, catching up on email. ]

On Tue, Feb 22, 2022 at 10:54 AM Joshua Brindle
<joshua.brindle@crunchydata.com> wrote:
> Yes, absolutely. It is my understanding that generally a community
> consensus is attempted, I was throwing my (and Crunchy's) use case out
> there as a possible goal, and I have spent time reviewing and testing
> the patch, so I think that is fair. Obviously I am not in the position
> to stipulate hard requirements.

I agree with all of that -- and thanks for writing back.

> if 1A worked for admins, or members I think it may work (i.e., Bot is
> admin of employees but not a member of employees and therefore can
> manage employees but not become them or read their tables)
>
> For example, today this works (in master):
>
> postgres=# CREATE USER creator password 'a';
> CREATE ROLE
> postgres=# CREATE ROLE employees ADMIN creator NOLOGIN;
> CREATE ROLE
>
> as creator:
> postgres=> CREATE USER joshua IN ROLE employees PASSWORD 'a';
> ERROR:  permission denied to create role
>
> as superuser:
> postgres=# CREATE USER joshua LOGIN PASSWORD 'a';
> CREATE ROLE
>
> as creator:
> postgres=> GRANT employees TO joshua;
> GRANT ROLE
> postgres=> SET ROLE joshua;
> ERROR:  permission denied to set role "joshua"
> postgres=> SET ROLE employees;
> SET
>
> So ADMIN of a role can add membership, but not create, and
> unfortunately can SET ROLE to employees.
>
> Can ADMIN mean "can create and drop roles with membership of this role
> but not implicitly be a member of the role"?

I foresee big problems trying to go in this direction. According to
the documentation, "the ADMIN clause is like ROLE, but the named roles
are added to the new role WITH ADMIN OPTION, giving them the right to
grant membership in this role to others." And for me, the name "WITH
ADMIN OPTION" is a huge red flag. You grant membership in a role, and
you may grant that membership with the admin option, or without the
admin option, but either way you are granting membership. And to me
that is just built into the phraseology. You may be able to buy the
car that you want with or without the all-wheel drive option, and you
may even be able to upgrade a car purchased without that option to
have it later, but you can't buy all-wheel drive in the abstract
without an association to some particular car. That's what it means
for it to be an option.

Now, I think there is a good argument to be made that in this case the
fact that the administration privileges are an option associated with
membership is artificial. I expect we can all agree that it is
conceptually easy to understand the idea of being able to administer a
role and the idea of having that role's privileges as two separate
concepts, neither dependent upon the other, and certainly the SQL
syntax could be written in a way that makes that very natural. But as
it is, what is the equivalent of GRANT employees TO bot WITH ADMIN
OPTION when you want to convey only administration rights and not
membership? GRANT employees TO bot WITH ADMIN OPTION BUT WITHOUT THE
UNDERLYING MEMBERSHIP TO WHICH ADMIN IS AN OPTION? Maybe that sounds
sarcastic, but to me it seems like a genuinely serious problem. People
construct a mental model of how stuff works based to a significant
degree on the structure of the syntax, and I really don't see an
obvious way of extending the grammar in a way that is actually going
to make sense to people.

> The current (v8) patch conflates membership and admin:
>
> postgres=# CREATE USER user_creator CREATEROLE WITHOUT ADMIN OPTION
> PASSWORD 'a';
> CREATE ROLE
> postgres=# CREATE ROLE employees ADMIN user_creator NOLOGIN;
> CREATE ROLE
>
> (Note I never GRANTED employees to user_creator):

I think you did, because even right now without the patch "ADMIN
whatever" is documented to mean membership with admin option.

> > So that leads to these questions: (2A) Do you care about restricting
> > which roles the userbot can drop? (2B) If yes, do you endorse
> > restricting the ability of roles to revoke themselves from other
> > roles?
>
> 2A, yes
> 2B, yes, and IIUC this already exists:
> postgres=> select current_user;
>  current_user
> --------------
>  joshua
> (1 row)
>
> postgres=> REVOKE employees FROM joshua;
> ERROR:  must have admin option on role "employees"

No, because as Stephen correctly points out, you've got that REVOKE
command backwards.

> > I think that we don't have any great problems here, at least as far as
> > this very specific issue is concerned, if either the answer to (2A) is
> > no or the answer to (2B) is yes. However, if the answer to (2A) is yes
> > and the answer to (2B) is no, there are difficulties. Evidently in
> > that case we need some new kind of thing that behaves mostly likes a
> > group of roles but isn't actually a group of roles -- and that thing
> > needs to prohibit self-revocation. Given what I've written above, you
> > may be able to guess my preferred solution: let's call it a TENANT.
> > Then, my pseudo-super-user can have permission to (i) create roles in
> > that tenant, (ii) drop roles in that tenant, and (iii) assume the
> > privileges of roles in that tenant -- and your userbot can have
> > privileges to do (i) and (ii) but not (iii). All we need do is add a
> > roltenant column to pg_authid and find three bits someplace
> > corresponding to (i)-(iii), and we are home.
>
> I believe this works.

Cool.

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