Thread

Revoke role membership does not remove cascade privileges

Virender Singla <virender.cse@gmail.com> — 2025-10-01T09:32:21Z

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