Aw:  Re: Re: Revoke Connect Privilege from Database not working

Hi, here is a full reproducer. Also revoking with the granted by clause does not work. #clean initialization postgres=# create database testdb owner postgres; CREATE DATABASE postgres=# create user test_admin createrole; CREATE ROLE postgres=# alter user test_admin with password 'test1234'; ALTER ROLE postgres=# grant connect on database testdb to test_admin with grant option; GRANT #create user and grant connect privilege with test_admin postgres=# set role test_admin; SET postgres=> create user test_user password 'testuserpw'; CREATE ROLE postgres=> grant connect on database testdb to test_user; GRANT #generate the failure by granting test_admin superuser privileges postgres=> reset role; RESET postgres=# alter user test_admin superuser; ALTER ROLE postgres=# set role test_admin; SET postgres=# revoke connect on database testdb from test_user; REVOKE postgres=# drop user test_user; ERROR: role "test_user" cannot be dropped because some objects depend on it DETAIL: privileges for database testdb #test also with "granted by clause" postgres=# revoke connect on database testdb from test_user granted by "test_admin"; REVOKE postgres=# drop user test_user; ERROR: role "test_user" cannot be dropped because some objects depend on it DETAIL: privileges for database testdb #fix by removing superuser privilege from test_admin postgres=# reset role; RESET postgres=# alter user test_admin nosuperuser; ALTER ROLE postgres=# set role test_admin; SET postgres=> revoke connect on database testdb from test_user; REVOKE postgres=> drop role test_user; DROP ROLE Best Regards Marijo Kristo David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> schrieb am 7. Apr. 2025 um 15:42: On Monday, April 7, 2025, Ing. Marijo Kristo < marijo(dot)kristo(at)icloud(dot)com > wrote: Seems like a bug to me. Can someone else verifiy this ? It would help greatly if you create a reproducer that starts from a clean install, creates the roles and database, and demonstrates the issue. postgres=# \du vault_admin; List of roles Role name | Attributes -------------+---------------- -------- vault_admin | Superuser, Create role postgres=# set role vault_admin; You are setting role to another role that has superuser which is basically pointless. Use “granted by” in your revoke command. If that works this isn’t a bug. David J.