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

Поиск
Список
Период
Сортировка
От Ing. Marijo Kristo
Тема Aw:  Re: Re: Revoke Connect Privilege from Database not working
Дата
Msg-id d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com
обсуждение исходный текст
Ответ на Re: Re: Revoke Connect Privilege from Database not working  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-sql
Дерево обсуждения
Hi,
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 roles
  Role name  |       Attributes
-------------+------------------------
vault_admin | Superuser, Create role

postgres=# 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.


В списке pgsql-sql по дате отправления: