Thread: Revoke Connect Privilege from Database not working
> 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 >
"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
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.
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.
On Monday, April 7, 2025, Ing. Marijo Kristo <marijo.kristo@icloud.com> wrote:
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 rolesRole name | Attributes-------------+------------------------ vault_admin | Superuser, Create rolepostgres=# 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.
Hi,
here is a full reproducer. Also revoking with the granted by clause does not work.
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:On Monday, April 7, 2025, Ing. Marijo Kristo <marijo.kristo@icloud.com> wrote: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 rolesRole name | Attributes-------------+------------------------ vault_admin | Superuser, Create rolepostgres=# 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.