'PostgreSQL role IS and IS NOT a member of another role?

What am I missing here? Or how do I fix?

lake=> REVOKE app_cdc FROM app_flyway;
WARNING:  role "app_flyway" is not a member of role "app_cdc"
lake=> GRANT app_flyway TO app_cdc;
ERROR:  role "app_flyway" is a member of role "app_cdc"

ae_lake=> \du app_cdc;
                List of roles
Role name | Attributes |                          Member of
-----------+------------+--------------------------------------------------------------
app_cdc   |            | {grp_datalake_reader,grp_datalake_writer,grp_read,grp_write}

ae_lake=> \du app_flyway;
                List of roles
Role name  | Attributes |         Member of
------------+------------+---------------------------
app_flyway |            | {grp_admin,grp_admin_pii}

Clearly app_flyway is not a member of app_cdc. But app_cdc is also clearly not a member of app_flyway.

Then, I thought about membership of the grp_* roles. So I did this:

lake.cdc> REVOKE app_flyway FROM grp_datalake_reader, grp_datalake_writer, grp_read, grp_write
[2022-02-04 13:19:31] [01000] role "grp_datalake_reader" is not a member of role "app_flyway"
[2022-02-04 13:19:31] [01000] role "grp_datalake_writer" is not a member of role "app_flyway"
[2022-02-04 13:19:31] [01000] role "grp_read" is not a member of role "app_flyway"
[2022-02-04 13:19:31] [01000] role "grp_write" is not a member of role "app_flyway"
[2022-02-04 13:19:31] completed in 146 ms
lake.cdc> GRANT app_flyway TO app_cdc
[2022-02-04 13:19:39] [0LP01] ERROR: role "app_flyway" is a member of role "app_cdc"

And, for good measure:

lake.cdc> REVOKE app_cdc FROM grp_datalake_reader, grp_datalake_writer, grp_read, grp_write
[2022-02-04 13:21:57] [01000] role "grp_datalake_reader" is not a member of role "app_cdc"
[2022-02-04 13:21:57] [01000] role "grp_datalake_writer" is not a member of role "app_cdc"
[2022-02-04 13:21:57] [01000] role "grp_read" is not a member of role "app_cdc"
[2022-02-04 13:21:57] [01000] role "grp_write" is not a member of role "app_cdc"
[2022-02-04 13:21:57] completed in 145 ms
lake.cdc> GRANT app_flyway TO app_cdc
[2022-02-04 13:22:02] [0LP01] ERROR: role "app_flyway" is a member of role "app_cdc"

Weirder still:

lake=> REVOKE app_flyway FROM grp_admin,grp_admin_pii;
WARNING:  role "grp_admin" is not a member of role "app_flyway"
WARNING:  role "grp_admin_pii" is not a member of role "app_flyway"
REVOKE ROLE
lake=> \du app_flyway;
                    List of roles
 Role name  | Attributes |         Member of
------------+------------+---------------------------
 app_flyway |            | {grp_admin,grp_admin_pii}
postgresql


Solution 1:[1]

OK, this fixed. Sorry to bug folks.

REVOKE app_cdc FROM grp_admin;
REVOKE app_cdc FROM grp_admin_pii;
GRANT app_flyway TO app_cdc;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 quickdraw

Related Questions