Re: pg_has_role's handling of ADMIN OPTION is pretty weird

On Fri, Aug 26, 2022 at 11:55 AM Robert Haas <robertmhaas(at)gmail(dot)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