Horton

Thread

  1. Revoke Connect Privilege from Database not working

    Ing. Marijo Kristo <marijo.kristo@icloud.com> — 2025-03-31T15:26:13Z

    > Hello,
> 
> we are using Vault to provision temporary users which get deleted after a
> while by the same user.
> For this purpose we have created a vault_admin user.
> 
> postgres=# \du vault_admin
>            List of roles
>  Role name  |       Attributes
> -------------+------------------------
> vault_admin | Superuser, Create role
> 
> postgres=# \l "disp_db"
> 
>         List of databases
>  Name   |       Owner       | Encoding | Locale Provider |  Collate   |  
> Ctype    | ICU Locale | ICU Rules |                               Access
> privileges
> ---------+-------------------+----------+-----------------+------------+------------+------------+-----------+--------------------------------------------------------------------------------
> disp_db | app_disp_db_admin | UTF8     | libc            | en_US.utf8 |
> en_US.utf8 |            |           |
> app_disp_db_admin=CTc/app_disp_db_admin                                     
> +
>         |                   |          |                 |            |    
>       |            |           | app_disp_db=Tc/app_disp_db_admin          
>                                   +
>         |                   |          |                 |            |    
>       |            |           | pg_database_owner=CTc/app_disp_db_admin   
>                                   +
>         |                   |          |                 |            |    
>       |            |           | vault_admin=c*/app_disp_db_admin          
>                                   +
>         |                   |          |                 |            |    
>       |            |           |
> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00"=c/vault_admin   
> +
>         |                   |          |                 |            |    
>       |            |           | app_disp_db_readonly=c/app_disp_db_admin
> 
> 
> Removing the connect privilege with the Postgres Superuser and with the
> Vault Admin user does not work.
> 
> postgres=# select current_user;
> current_user
> --------------
> postgres
> 
> postgres=# revoke connect on database "disp_db" from
> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";
> REVOKE
> 
> postgres=# 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
> 
> 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
> 
> Does not work via PSQL nor with pgadmin.
> 
> Best Regards
> Marijo Kristo
>

  2. Re: Revoke Connect Privilege from Database not working

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-04-01T14:13:49Z

    "Ing. Marijo Kristo" <marijo.kristo@icloud.com> writes:
>> Removing the connect privilege with the Postgres Superuser and with the
>> Vault Admin user does not work.
>> postgres=# revoke connect on database "disp_db" from
>> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";
>> REVOKE

REVOKE is not being as helpful as it could be here, perhaps:
it is failing to tell you that it's a no-op because there
is no such privilege.  You never granted connect on disp_db
to that user so you can't revoke it either.

The privilege that exists by default, per [1], is that
database connect privileges are granted to PUBLIC (the
pseudo-group of all users).  If that's not what you want,
you have to do

revoke connect on database "disp_db" from public;

and then grant it out again to the users who should have it.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/ddl-priv.html

  3. Re: Revoke Connect Privilege from Database not working

    David G. Johnston <david.g.johnston@gmail.com> — 2025-04-01T14:15:13Z

    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

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

    Ing. Marijo Kristo <marijo.kristo@icloud.com> — 2025-04-07T08:53:07Z

    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". 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 ? 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 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_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