Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u1jRY-007AdK-2z for pgsql-sql@arkaria.postgresql.org; Mon, 07 Apr 2025 10:11:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1u1jRW-00El91-F2 for pgsql-sql@arkaria.postgresql.org; Mon, 07 Apr 2025 10:11:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1u1iED-00E40U-W8 for pgsql-sql@lists.postgresql.org; Mon, 07 Apr 2025 08:53:14 +0000 Received: from ci74p00im-qukt09082701.me.com ([17.57.156.16]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1u1iEC-003RD1-0e for pgsql-sql@lists.postgresql.org; Mon, 07 Apr 2025 08:53:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; bh=0VWQm+kjvA83tsFkSGaSnqlE08clDZnldCQiaJEucwI=; h=To:From:Subject:Date:Message-id:Content-Type:MIME-Version:x-icloud-hme; b=D9q7LAVsEvHBc1zz/GeaY17tkLV1fAUWmA/JhHn1CbUfDC9KwwjyrdaO+wLJZYSQj 18F7qyXlllWqKlRWhxy2+ZKeuoN+goljSJfb6L3EXRpoDOggDzFlH8iGFgQ1ys5zL5 SceqjejR1RtFhSMcpyLIscJ95bFjgC7BTggx4FZB3QxOTeIwYS1UdcNU/O9YNd1cST sNlN+VujTfh+AgDqVI+lYq+LTwA5e5rhhXMRyEzS3kUSGgFQyNvj2Igo4zrYHMFilU CzLmgaK8LrGZaMICEgHSeXSxiOWhHF+7o6bBYFGYzt7eu8tMW//V1p3VsFpz0xt8s6 uhuWNUZFXzaBA== Received: from ci74p00im-qukt09082701.me.com (unknown [17.57.156.16]) by ci74p00im-qukt09082701.me.com (Postfix) with ESMTPS id 0073F4A0033E; Mon, 7 Apr 2025 08:53:07 +0000 (UTC) Received: from p00-mailws2-64655bbcdf-srlpx (ci77p00im.dlb-asmtpoutvip.me.com [10.168.160.28]) by ci74p00im-qukt09082701.me.com (Postfix) with ESMTPSA id 527224A001F3; Mon, 7 Apr 2025 08:53:07 +0000 (UTC) To: "David G. Johnston" Cc: pgsql-sql@lists.postgresql.org From: "Ing. Marijo Kristo" Subject: =?utf-8?B?QXc6wqAgUmU6IFJldm9rZSBDb25uZWN0IFByaXZpbGU=?= =?utf-8?B?Z2UgZnJvbSBEYXRhYmFzZSBub3Qgd29ya2luZw==?= Date: Mon, 7 Apr 2025 08:53:07 +0000 (UTC) X-Mailer: iCloud MailClientcurrent MailServer2502B13.7c5914c7dad3 Message-id: <7c7f51b1-a625-4652-a2fa-6031ed7b8057@me.com> Content-Type: multipart/alternative; boundary=Apple-Webmail-42--c702a1a2-4698-4930-8447-0c98497cc625 MIME-Version: 1.0 In-Reply-To: References: <18873-c148b32c0e501cc0@postgresql.org> <6C13A1CC-3841-4A5E-BC78-C8F9C5B120BB@icloud.com> X-Proofpoint-ORIG-GUID: zx6nENLfNm0ciP0JFHeQAtJ3xGpvk9Ub X-Proofpoint-GUID: zx6nENLfNm0ciP0JFHeQAtJ3xGpvk9Ub X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1095,Hydra:6.0.680,FMLib:17.12.68.34 definitions=2025-04-07_02,2025-04-03_03,2024-11-22_01 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 malwarescore=0 clxscore=1011 suspectscore=0 phishscore=0 mlxscore=0 adultscore=0 mlxlogscore=999 bulkscore=0 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.19.0-2308100000 definitions=main-2504070063 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Webmail-42--c702a1a2-4698-4930-8447-0c98497cc625 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8; format=flowed Hi, so I tested it now out again. Here is the problem: The vault_admin Use= r 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 use= r 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 p= reviously granted connect privileges even when explicitly using the "SET".= For reproduction i have tested it with those statements as you can see. T= here is something going on in the background .. Probably the revoke statem= ent is being executed as postgres user. Seems like a bug to me. Can someon= e else verifiy this ? postgres=3D# \du vault_admin; List of roles Role nam= e | Attributes -------------+------------------------ vault_admin | Superu= ser, Create role postgres=3D# set role vault_admin; SET postgres=3D# selec= t current_user,current_role; current_user | current_role --------------+--= ------------ vault_admin | vault_admin postgres=3D# revoke connect on data= base disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:= 00"; REVOKE postgres=3D# drop role "dev_oidc-m-kristo-rewe-group-at-2025_0= 2_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 DE= TAIL: privileges for database disp_db postgres=3D# reset role; RESET postg= res=3D# alter user vault_admin nosuperuser; ALTER ROLE postgres=3D> \du va= ult_admin; List of roles Role name | Attributes -------------+------------= - vault_admin | Create role postgres=3D# set role vault_admin; SET postgre= s=3D# select current_user,current_role; current_user | current_role ------= --------+-------------- vault_admin | vault_admin postgres=3D> revoke conn= ect on database disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T1= 0_27_16+00:00"; REVOKE postgres=3D> drop role "dev_oidc-m-kristo-rewe-grou= p-at-2025_02_24T10_27_16+00:00"; DROP ROLE Thanks ! Best Regards Marijo Kr= isto David G. Johnston schrieb am 1. Apr. 202= 5 um 16:16: On Tue, Apr 1, 2025 at 4:59 AM Ing. Marijo Kristo < marijo.kri= sto@icloud.com > wrote: > "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_0= 6_30+00:00"=3Dc/vault_admin > Same happens when trying to revoke with the = vault admin user: > > disp_db=3D# select current_user; > current_user > --= ------------ > vault_admin > (1 row) > > disp_db=3D# revoke connect on dat= abase "disp_db" from > "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_3= 0+00:00"; > REVOKE > disp_db=3D# drop user > "dev_oidc-m-kristo-rewe-group= -at-2025_02_28T09_06_30+00:00"; > ERROR: role "dev_oidc-m-kristo-rewe-grou= p-at-2025_02_28T09_06_30+00:00" > cannot be dropped because some objects d= epend 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.post= gresql.org/docs/current/sql-revoke.html --Apple-Webmail-42--c702a1a2-4698-4930-8447-0c98497cc625 Content-Type: multipart/related; type="text/html"; boundary=Apple-Webmail-86--c702a1a2-4698-4930-8447-0c98497cc625 --Apple-Webmail-86--c702a1a2-4698-4930-8447-0c98497cc625 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8;
Hi,
so I tested it now out again. <= br>
Here is the problem:
The vault_admin User only had the cr= eaterole privilege and the with "connect" grant option on the database so = it can grant conencts to other users.
The vault_admin user granted a co= nnect on the database and later on the vault_admin user also got the super= user privilege and since then it is not able to remove the previously gran= ted 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 re= voke statement is being executed as postgres user.

Seems like a bug to me.
Can someone else verifiy this ?

postgres=3D# \du vault_admin;
 &n= bsp;          List of roles
  Role name  |       = Attributes
-------------+------------------------
<= /div>
vault_admin | Superuser, Create role

postgres=3D# set role vault_admin;
SET
postg= res=3D# select current_user,current_role;
current_user | cur= rent_role
--------------+--------------
vault_= admin  | vault_admin
postgres=3D# revoke connect on dat= abase disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00= :00";
REVOKE
postgres=3D# drop role "dev_oidc-= m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
ERROR:&nb= sp; role "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00" canno= t be dropped because some objects depend on it
DETAIL: = privileges for database disp_db

postgres=3D#= reset role;
RESET
postgres=3D# alter user vau= lt_admin nosuperuser;
ALTER ROLE
postgres=3D&g= t; \du vault_admin;
       Lis= t of roles
  Role name  | Attributes
-------------+-------------
vault_admin | Create role
postgres=3D# set role vault_admin;
SET
postgres=3D# select current_user,current_role;
current_use= r | current_role
--------------+--------------
vault_admin  | vault_admin
postgres=3D> revoke conn= ect on database disp_db from "dev_oidc-m-kristo-rewe-group-at-2025_02_24T1= 0_27_16+00:00";
REVOKE
postgres=3D> drop ro= le "dev_oidc-m-kristo-rewe-group-at-2025_02_24T10_27_16+00:00";
<= div>DROP ROLE

Thanks !
Best Regards
Marijo Kristo<= /div>

David G. Johnston = <david.g.johnston@gmail.com> schrieb am 1. Apr. 2025 um 16:16:


<= div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-seri= f">On Tue, Apr 1, 2025 at 4:59=E2=80=AFAM Ing. Marijo Krist= o <marijo.kristo@icloud.com= > wrote:

> "dev_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30= +00:00"=3Dc/vault_admin   

> Sam= e happens when trying to revoke with the vault admin user:
&= gt;
> disp_db=3D# select current_user;
>= ; current_user
> --------------
> vault_= admin
> (1 row)
>
> di= sp_db=3D# revoke connect on database "disp_db" from
> "de= v_oidc-m-kristo-rewe-group-at-2025_02_28T09_06_30+00:00";
&g= t; REVOKE
> disp_db=3D# 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+0= 0: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 co= mmand is performed as though it were issued by the owner of the affected o= bject." [1]

The fact vault_admin is superuser o= verrides the fact that it is their specific grant that is trying to be rev= oked.

David J.


<= /div>

--Apple-Webmail-86--c702a1a2-4698-4930-8447-0c98497cc625-- --Apple-Webmail-42--c702a1a2-4698-4930-8447-0c98497cc625--