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.g.johnston@gmail.com> schrieb am 7. Apr. 2025 um 15:42:
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.
