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