We encountered an upgrade failure due to error:
"ERROR: grant options cannot be granted back to your own grantor"
The failure scenario is reproducible in Postgres v16/v17 (may be before
that as well). The root cause of this failure is that revoking a role
membership does not consequently revoke associated cascade privileges.
Steps to Reproduce:
/* Create two users: "user1" and "user2" */
/* Login "user1" */
create database db1;
GRANT ALL ON DATABASE db1 TO user2 WITH GRANT OPTION;
/* Login "user2", gets error due to obvious reason */
GRANT ALL ON DATABASE db1 TO user2 WITH GRANT OPTION;
ERROR: grant options cannot be granted back to your own grantor
/* Login "user1" */
grant user1 to user2;
/* Login "user2" */
GRANT ALL ON DATABASE db1 TO user2 WITH GRANT OPTION;
/* Login "user2" */
select datacl from pg_database where datname='db1';
datacl
-------------------------------------------------------------------
{=Tc/user1,user1=CTc/user1,user2=C*T*c*/user1,user2=C*T*c*/user2}
/* Now revoke role membership but it does not update ACL in pg_database */
revoke user2 from user1;
/* ACL "user2=C*T*c*/user2" still remains in the catalog causing upgrade
failure during pg_restore */
select datacl from pg_database where datname='db1';
datacl
-------------------------------------------------------------------
{=Tc/user1,user1=CTc/user1,user2=C*T*c*/user1,user2=C*T*c*/user2}
This causes upgrade failure as during pg_restore, "user2" tries to grant
permission to itself without any role membership of "user1".
A side effect of this behaviour is that, now even if
"user1" revokes the grant permission from "use2", "user2" can still
grant the permission to others.
Thanks,
Virender
