Re: Role Self-Administration

On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
<mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> Additionally, role "alice" might not exist anymore, which would leave the privilege irrevocable.

I thought that surely this couldn't be right, but apparently we have
absolutely no problem with leaving the "grantor" column in pg_authid
as a dangling reference to a pg_authid role that no longer exists:

rhaas=# select * from pg_auth_members where grantor not in (select oid
from pg_authid);
roleid | member | grantor | admin_option
--------+--------+---------+--------------
3373 | 16412 | 16410 | f
(1 row)

Yikes. We'd certainly have to do something about that if we want to
use the grantor field for anything security-sensitive, since otherwise
hilarity would ensue if that OID got recycled for a new role at any
later point in time.

This seems weirdly inconsistent with what we do in other cases:

rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# grant select on table foo to alice with grant option;
GRANT
rhaas=# \c rhaas alice
You are now connected to database "rhaas" as user "alice".
rhaas=> grant select on table foo to bob;
GRANT
rhaas=> \c - rhaas
You are now connected to database "rhaas" as user "rhaas".
rhaas=# drop role alice;
ERROR: role "alice" cannot be dropped because some objects depend on it
DETAIL: privileges for table foo
rhaas=#

Here, because the ACL on table foo records alice as a grantor, alice
cannot be dropped. But when alice is the grantor of a role, the same
rule doesn't apply. I think the behavior shown in this example, where
alice can't be dropped, is the right behavior, and the behavior for
roles is just plain broken.

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