public inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedRevoke Connect Privilege from Database not working
4+ messages / 3 participants
[nested] [flat]
* Revoke Connect Privilege from Database not working
@ 2025-03-31 15:26 Ing. Marijo Kristo <marijo.kristo@icloud.com>
0 siblings, 2 replies; 4+ messages in thread
From: Ing. Marijo Kristo @ 2025-03-31 15:26 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Revoke Connect Privilege from Database not working
@ 2025-04-01 14:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ing. Marijo Kristo <marijo.kristo@icloud.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-04-01 14:13 UTC (permalink / raw)
To: Ing. Marijo Kristo <marijo.kristo@icloud.com>; +Cc: pgsql-sql@lists.postgresql.org
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Revoke Connect Privilege from Database not working
@ 2025-04-01 14:15 David G. Johnston <david.g.johnston@gmail.com>
parent: Ing. Marijo Kristo <marijo.kristo@icloud.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-04-01 14:15 UTC (permalink / raw)
To: Ing. Marijo Kristo <marijo.kristo@icloud.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Aw: Re: Revoke Connect Privilege from Database not working
@ 2025-04-07 08:53 Ing. Marijo Kristo <marijo.kristo@icloud.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ing. Marijo Kristo @ 2025-04-07 08:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-04-07 08:53 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-31 15:26 Revoke Connect Privilege from Database not working Ing. Marijo Kristo <marijo.kristo@icloud.com>
2025-04-01 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-04-01 14:15 ` David G. Johnston <david.g.johnston@gmail.com>
2025-04-07 08:53 ` Aw: Re: Revoke Connect Privilege from Database not working Ing. Marijo Kristo <marijo.kristo@icloud.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox