Greetings, On Tue, Oct 5, 2021 at 13:09 Robert Haas <robertmh...@gmail.com> wrote:
> 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. Yeah, ew. We should just fix this. 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. Agreed. Thanks, Stephen >
