public inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Ing. Marijo Kristo <marijo.kristo@icloud.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>
Subject: Aw: Re: Re: Revoke Connect Privilege from Database not working
Date: Mon, 7 Apr 2025 14:27:41 +0000 (UTC)
Message-ID: <d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com> (raw)
In-Reply-To: <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
References: <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: marijo.kristo@icloud.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Aw: Re: Re: Revoke Connect Privilege from Database not working
In-Reply-To: <d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox