On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger <mark.dil...@enterprisedb.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
