Thread: pg_has_role's handling of ADMIN OPTION is pretty weird
According to pg_has_role, it's possible to have USAGE WITH ADMIN OPTION on a role without having USAGE: template1=# create role foo; CREATE ROLE template1=# create role admin; CREATE ROLE template1=# grant foo to admin with inherit false, admin true; GRANT ROLE template1=# select p.priv, pg_has_role('admin', 'foo', p.priv) from (values ('USAGE'), ('MEMBER'),('USAGE WITH ADMIN OPTION'), ('MEMBER WITH ADMIN OPTION')) p(priv); priv | pg_has_role --------------------------+------------- USAGE | f MEMBER | t USAGE WITH ADMIN OPTION | t MEMBER WITH ADMIN OPTION | t (4 rows) To me it seems wrong to say that you can have "X WITH Y" without having X. If I order a hamburger with fries, I do not only get fries: I also get a hamburger. I think the problem here is that pg_has_role() is defined to work like has_table_privilege(), and for table privileges, each underlying privilege bit has a corresponding bit representing the right to grant that privilege, and you can't grant the right to set the privilege without first granting the privilege. For roles, you just get ADMIN OPTION on the role, and that entitles you to grant or revoke any privilege associated with the role. So the whole way this function is defined seems wrong to me. It seems like it would be more reasonable to have the third argument be, e.g. MEMBER, USAGE, or ADMIN and forget about this WITH ADMIN OPTION stuff. That would be a behavior change, though. If we don't do that, then I think things just get weirder if we add some more privileges around role memberships. Let's say that in addition to INHERIT OPTION and GRANT OPTION, we add some other things that one role could do to another, let's say FLUMMOX, PERTURB, and DISCOMBOBULATE, then we'll just end up with more and more synonyms for "does this role have admin option". That is: column1 | column2 ----------------------------------+--------------------------------------------- USAGE | Is this grant inheritable? MEMBER | Does a grant even exist in the first place? FLUMMOX | Can this grant flummox? PERTURB | Can this grant perturb? DISCOMBOBULATE | Can this grant discombobulate? USAGE WITH ADMIN OPTION | Does this grant have ADMIN OPTION? MEMBER WITH ADMIN OPTION | Does this grant have ADMIN OPTION? FLUMMOX WITH ADMIN OPTION | Does this grant have ADMIN OPTION? PERTURB WITH ADMIN OPTION | Does this grant have ADMIN OPTION? DISCOMBOBULATE WITH ADMIN OPTION | Does this grant have ADMIN OPTION? Maybe everybody else thinks that would be just fine? To me it seems fairly misleading. -- Robert Haas EDB: http://www.enterprisedb.com
On Fri, Aug 26, 2022 at 11:55 AM Robert Haas <robertmhaas@gmail.com> wrote: > According to pg_has_role, it's possible to have USAGE WITH ADMIN > OPTION on a role without having USAGE: One more thing about this. The documentation about how this function actually works seems never to have been very good, and I think it's actually worse starting in v13. In v12 and prior it wasn't terribly clear, but we said this: "pg_has_role checks whether a user can access a role in a particular way. Its argument possibilities are analogous to has_table_privilege, except that public is not allowed as a user name. The desired access privilege type must evaluate to some combination of MEMBER or USAGE. MEMBER denotes direct or indirect membership in the role (that is, the right to do SET ROLE), while USAGE denotes whether the privileges of the role are immediately available without doing SET ROLE." Now, has_table_privilege() allows you to specify multiple table options and to append WITH GRANT OPTION to any or all of them. That actually works for pg_has_role() too, and a particularly sharp user might suppose based on what we say elsewhere in the documentation that, in the case of roles, we normally write WITH ADMIN OPTION rather than WITH GRANT OPTION. So possibly someone could figure out what this function actually does without reading the source code, at least if they have a PhD degree in PostgreSQL-ology. Starting in v13, the only explicit mention of pg_has_role() is this table entry: "pg_has_role ( [ user name or oid, ] role text or oid, privilege text ) → boolean Does user have privilege for role? Allowable privilege types are MEMBER and USAGE. MEMBER denotes direct or indirect membership in the role (that is, the right to do SET ROLE), while USAGE denotes whether the privileges of the role are immediately available without doing SET ROLE. This function does not allow the special case of setting user to public, because the PUBLIC pseudo-role can never be a member of real roles." That gives no hint that you can specify multiple privileges, let alone append WITH ADMIN OPTION or WITH GRANT OPTION. Everything else in this table has the same problem. There is some text above the table which explains what's going on here and from which it might be possible to infer the behavior of pg_has_role(), but only if you actually read that text and understand that it actually acts as a modifier to everything as follows. None of the functions actually do what they say they do; they all do approximately that, but as modified to fit the scheme described in this paragraph. At the very least, these table entries should say that the last argument is called "privileges" not "privilege" so that someone might have a clue that more than one can be specified. And for the ones where you can add "WITH GRANT OPTION" or "WITH ADMIN OPTION" that should be mentioned in the table itself. -- Robert Haas EDB: http://www.enterprisedb.com