To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
From: "Ing. Marijo Kristo" <marijo.kristo@icloud.com>
Subject: 
 =?utf-8?B?QXc6wqAgUmU6IFJlOiBSZXZva2UgQ29ubmVjdCBQcml2aWxlZ2UgZnJvbSBE?=
 =?utf-8?B?YXRhYmFzZSBub3Qgd29ya2luZw==?=
Date: Mon, 7 Apr 2025 14:27:41 +0000 (UTC)
Message-id: <d9bf666c-4d11-4196-99a8-b71d01d9ad40@me.com>
Content-Type: multipart/alternative;
 boundary=Apple-Webmail-42--95a78445-f8aa-4876-ac83-2204c5899f7b
MIME-Version: 1.0
In-Reply-To: 
 <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
References: 
 <CAKFQuwa7m2smqqpgPetw=i8Aj-xqg9Zjc5Z2aX3AUwNh96WnXw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/d9bf666c-4d11-4196-99a8-b71d01d9ad40%40me.com>
Precedence: bulk


--Apple-Webmail-42--95a78445-f8aa-4876-ac83-2204c5899f7b
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
    charset=utf-8;
    format=flowed

Hi, here is a full reproducer. Also revoking with the granted by clause do=
es not work. #clean initialization postgres=3D# create database testdb own=
er postgres; CREATE DATABASE postgres=3D# create user test_admin createrol=
e; CREATE ROLE postgres=3D# alter user test_admin with password 'test1234'=
; ALTER ROLE postgres=3D# grant connect on database testdb to test_admin w=
ith grant option; GRANT #create user and grant connect privilege with test=
_admin postgres=3D# set role test_admin; SET postgres=3D> create user test=
_user password 'testuserpw'; CREATE ROLE postgres=3D> grant connect on dat=
abase testdb to test_user; GRANT #generate the failure by granting test_ad=
min superuser privileges postgres=3D> reset role; RESET postgres=3D# alter=
 user test_admin superuser; ALTER ROLE postgres=3D# set role test_admin; S=
ET postgres=3D# revoke connect on database testdb from test_user; REVOKE p=
ostgres=3D# 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=3D# revoke connect on databas=
e testdb from test_user granted by "test_admin"; REVOKE postgres=3D# drop =
user test_user; ERROR: role "test_user" cannot be dropped because some obj=
ects depend on it DETAIL: privileges for database testdb #fix by removing =
superuser privilege from test_admin postgres=3D# reset role; RESET postgre=
s=3D# alter user test_admin nosuperuser; ALTER ROLE postgres=3D# set role =
test_admin; SET postgres=3D> revoke connect on database testdb from test_u=
ser; REVOKE postgres=3D> drop role test_user; DROP ROLE Best Regards Marij=
o 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.kris=
to@icloud.com > wrote: Seems like a bug to me. Can someone else verifiy th=
is ? It would help greatly if you create a reproducer that starts from a c=
lean install, creates the roles and database, and demonstrates the issue. =
postgres=3D# \du vault_admin; List of roles Role name | Attributes -------=
------+---------------- -------- vault_admin | Superuser, Create role post=
gres=3D# set role vault_admin; You are setting role to another role that h=
as superuser which is basically pointless. Use =E2=80=9Cgranted by=E2=80=9D=
 in your revoke command. If that works this isn=E2=80=99t a bug. David J.
--Apple-Webmail-42--95a78445-f8aa-4876-ac83-2204c5899f7b
Content-Type: multipart/related;
    type="text/html";
    boundary=Apple-Webmail-86--95a78445-f8aa-4876-ac83-2204c5899f7b


--Apple-Webmail-86--95a78445-f8aa-4876-ac83-2204c5899f7b
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
    charset=utf-8;

<html><body><div><div>Hi,<br>here is a full reproducer. Also revoking with the granted=
 by clause does not work.<br><br></div><div>#clean initialization<br></div=
></div><div>postgres=3D# create database testdb owner postgres;<br></div><=
div>CREATE DATABASE<br></div><div>postgres=3D# create user test_admin crea=
terole;<br></div><div>CREATE ROLE<br></div><div>postgres=3D# alter user te=
st_admin with password 'test1234';<br></div><div>ALTER ROLE<br></div><div>=
postgres=3D# grant connect on database testdb to test_admin with grant opt=
ion;<br></div><div>GRANT<br></div><div><br></div><div>#create user and gra=
nt connect privilege with test_admin<br></div><div>postgres=3D# set role t=
est_admin;<br></div><div>SET<br></div><div>postgres=3D&gt; create user tes=
t_user password 'testuserpw';<br></div><div>CREATE ROLE<br></div><div>post=
gres=3D&gt; grant connect on database testdb to test_user;<br></div><div>G=
RANT<br></div><div><br></div><div>#generate the failure by granting test_a=
dmin superuser privileges<br></div><div>postgres=3D&gt; reset role;<br></d=
iv><div>RESET<br></div><div>postgres=3D# alter user test_admin superuser;<=
br></div><div>ALTER ROLE<br></div><div>postgres=3D# set role test_admin;<b=
r></div><div>SET<br></div><div>postgres=3D# revoke connect on database tes=
tdb from test_user;<br></div><div>REVOKE<br></div><div>postgres=3D# drop u=
ser test_user;<br></div><div>ERROR:&nbsp; role "test_user" cannot be dropp=
ed because some objects depend on it<br></div><div>DETAIL:&nbsp; privilege=
s for database testdb<br></div><div><br></div><div>#test also with "grante=
d by clause"<br></div><div>postgres=3D# revoke connect on database testdb =
from test_user granted by "test_admin";<br></div><div>REVOKE<br></div><div=
>postgres=3D# drop user test_user;<br></div><div>ERROR:&nbsp; role "test_u=
ser" cannot be dropped because some objects depend on it<br></div><div>DET=
AIL:&nbsp; privileges for database testdb<br></div><div><br></div><div>#fi=
x by removing superuser privilege from test_admin<br></div><div>postgres=3D=
# reset role;<br></div><div>RESET<br></div><div>postgres=3D# alter user te=
st_admin nosuperuser;<br></div><div>ALTER ROLE<br></div><div>postgres=3D# =
set role test_admin;<br></div><div>SET<br></div><div>postgres=3D&gt; revok=
e connect on database testdb from test_user;<br></div><div>REVOKE<br></div=
><div>postgres=3D&gt; drop role test_user;<br></div><div>DROP ROLE<br></di=
v><div><br></div><div>Best Regards<br></div><div>Marijo Kristo</div><div><=
div><br></div><blockquote type=3D"cite"><div>David G. Johnston &lt;david.g=
.johnston@gmail.com&gt; schrieb am 7. Apr. 2025 um 15:42:<br></div><div><b=
r></div><div><br></div><div><div>On Monday, April 7, 2025, Ing. Marijo Kri=
sto &lt;<a href=3D"mailto:marijo.kristo@icloud.com">marijo.kristo@icloud.c=
om</a>&gt; wrote:<br></div><blockquote style=3D"margin:0 0 0 .8ex;border-l=
eft:1px #ccc solid;padding-left:1ex" class=3D"gmail_quote"><div><div><br><=
/div></div><div><div>Seems like a bug to me.<br></div><div>Can someone els=
e verifiy this ?<br></div></div></blockquote><div><br></div><div>It would =
help greatly if you create a reproducer that starts from a clean install, =
creates the roles and database, and demonstrates the issue.<br></div><div>=
<br></div><blockquote style=3D"margin:0 0 0 .8ex;border-left:1px #ccc soli=
d;padding-left:1ex" class=3D"gmail_quote"><div><div><div><div><br></div><d=
iv>postgres=3D# \du vault_admin;<br></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List of roles<br></div><div>&nbsp;=
 Role name&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Attributes<br></div=
></div><div>-------------+----------------<wbr>--------<br></div><div>vaul=
t_admin | Superuser, Create role<br></div><div><br></div><div>postgres=3D#=
 set role vault_admin;<br></div></div></div></blockquote><div><br></div><d=
iv>You are setting role to another role that has superuser which is basica=
lly pointless.<br></div><div><br></div><div>Use =E2=80=9Cgranted by=E2=80=9D=
 in your revoke command.&nbsp; If that works this isn=E2=80=99t a bug.<br>=
</div><div><br></div><div>David J.<br></div><div><br></div></div></blockqu=
ote></div><div><br></div></body></html>
--Apple-Webmail-86--95a78445-f8aa-4876-ac83-2204c5899f7b--

--Apple-Webmail-42--95a78445-f8aa-4876-ac83-2204c5899f7b--