Обсуждение: 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