Aw: Re: Revoke Connect Privilege from Database not working - Mailing list pgsql-sql
| From | Ing. Marijo Kristo |
|---|---|
| Subject | Aw: Re: Revoke Connect Privilege from Database not working |
| Date | |
| Msg-id | 7c7f51b1-a625-4652-a2fa-6031ed7b8057@me.com Whole thread Raw |
| In response to | Re: Revoke Connect Privilege from Database not working ("David G. Johnston" <david.g.johnston@gmail.com>) |
| List | pgsql-sql |
Hi,
so I tested it now out again.
Here is the problem:
The vault_admin User only had the createrole privilege and the with "connect" grant option on the database so it can grant conencts to other users.
The vault_admin user granted a connect on the database and later on the vault_admin user also got the superuser privilege and since then it is not able to remove the previously granted connect privileges even when explicitly using the "SET".
The vault_admin User only had the createrole privilege and the with "connect" grant option on the database so it can grant conencts to other users.
The vault_admin user granted a connect on the database and later on the vault_admin user also got the superuser privilege and since then it is not able to remove the previously granted connect privileges even when explicitly using the "SET".
For reproduction i have tested it with those statements as you can see. There is something going on in the background .. Probably the revoke statement is being executed as postgres user.
Seems like a bug to me.
Can someone else verifiy this ?
Can someone else verifiy this ?
postgres=# \du vault_admin;
List of roles
Role name | Attributes
-------------+------------------------
vault_admin | Superuser, Create role
postgres=# set role vault_admin;
SET
postgres=# select current_user,current_role;
current_user | current_role
--------------+--------------
vault_admin | vault_admin
postgres=# revoke connect on database disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
REVOKE
postgres=# drop role "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
ERROR: role "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00" cannot be dropped because some objects depend on it
DETAIL: privileges for database disp_db
postgres=# reset role;
RESET
postgres=# alter user vault_admin nosuperuser;
ALTER ROLE
postgres=> \du vault_admin;
List of roles
Role name | Attributes
-------------+-------------
vault_admin | Create role
postgres=# set role vault_admin;
SET
postgres=# select current_user,current_role;
current_user | current_role
--------------+--------------
vault_admin | vault_admin
postgres=> revoke connect on database disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
REVOKE
postgres=> drop role "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
DROP ROLE
Thanks !
Best Regards
Thanks !
Best Regards
Marijo Kristo
David G. Johnston <david.g.johnston@gmail.com> schrieb am 1. Apr. 2025 um 16:16:On Tue, Apr 1, 2025 at 4:59 AM Ing. Marijo Kristo <marijo.kristo@icloud.com> wrote:> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"=c/vault_admin> Same happens when trying to revoke with the vault admin user:>> disp_db=# select current_user;> current_user> --------------> vault_admin> (1 row)>> disp_db=# revoke connect on database "disp_db" from> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";> REVOKE> disp_db=# drop user> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";> ERROR: role "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"> cannot be dropped because some objects depend on it> DETAIL: privileges for database disp_dbIf you include the "granted by" clause when you perform revoke everything usually just works."If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object." [1]The fact vault_admin is superuser overrides the fact that it is their specific grant that is trying to be revoked.David J.