Re: Revoke Connect Privilege from Database not working

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_db
>
>
If 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.

[1] https://www.postgresql.org/docs/current/sql-revoke.html