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 1tlQho-00Bvgi-4J for pgsql-general@arkaria.postgresql.org; Fri, 21 Feb 2025 10:56:28 +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 1tlQhm-007LJf-0W for pgsql-general@arkaria.postgresql.org; Fri, 21 Feb 2025 10:56:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tlQhl-007LJV-Kd for pgsql-general@lists.postgresql.org; Fri, 21 Feb 2025 10:56:25 +0000 Received: from mail-oi1-x232.google.com ([2607:f8b0:4864:20::232]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tlQhj-0028ZB-2j for pgsql-general@lists.postgresql.org; Fri, 21 Feb 2025 10:56:25 +0000 Received: by mail-oi1-x232.google.com with SMTP id 5614622812f47-3f40a38cb6bso873558b6e.1 for ; Fri, 21 Feb 2025 02:56:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1740135382; x=1740740182; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=s8O8D6vBewitThkO06ZUCWR8HwYzSYji1UizTbUqiOU=; b=b3V5ne0mxcPvxLj2cyG7Nd95QGjfinqt/s1C3uKyS1XpKAcmVYeGD7uMIQSaojaQ+W yhERAjtvLciKgVlC9sApVOUCFmmiP5eM+fR2TcaV6Ykwug0f9f+JzXiTdRaY7d+gE5M4 kmz2YUgUSRJY2ZZftHJuRWsHFks5FOykMgW+iNMITmkcxZ9RqyBWXJ1Wl7Rw+CcnjwLm GlTxi47CWIkVmkxrswG0JlSpn/y7jlOPwB5yjT9pYWJejzCaNcGDI7MQPw60xF3C4UJk SvEsnQH24eiLZp4hBL9PC5M3bOsEc0vr9qs5hhmPB2zKd53NNcUpLk67Yk91Inh66MBM y95A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1740135382; x=1740740182; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=s8O8D6vBewitThkO06ZUCWR8HwYzSYji1UizTbUqiOU=; b=EiuiNmyp4jOO2+fUvqD45QG25YCi6H4Wku+ufNUosit06rp1MjaVbCFDPdtvJJdBa2 CjOrdMWSN4xNpajyc2oyTq2agjtvSN+ZooutyDNyHEos90MbZQWU2KEArxQeoJJFF8AY Q/0VspVXPBCF0tm5BLFVyg7p/87Rv1eV0n++HU8zipk7NGMO0xCJBxraxwMQavy5EBxk XCqkAgK7r7hQmyu6lA6DgaLN55LrDlVnY1FOenJIgwSDqu71KkYi0BPt82jXwe59rvgH Ul4ySVCknI5qrZCeAft8tkxRo03ZjF3DilCgYDPL8VkVNKy2ntqJAfw/p7IRzyxz7OF8 5TeA== X-Forwarded-Encrypted: i=1; AJvYcCXYoooXsTjtTP33yhDYBH//vYNQOhN6YOC4E0JFAwZme9eZaqOFYIjFvjzIO9QwP3ufsY7wTiney3yVQp7v@lists.postgresql.org X-Gm-Message-State: AOJu0Yzzy3ZMpjoqNVvsdIVXreW9hKJPMT2K/2Jb959FDrvBB1ecn1ky 3vt3BT/BeP9Ld/8jyEmN2sKSjpN3jlmGu6MqYgXWiemYzmVW958UkIjRAAItY8xteeR+2+MWiZg zZMeXNOHYCfy/J1q1OLV+t94ryDo= X-Gm-Gg: ASbGncvuKTRsV5Fu1yUCLythKwmb1gopLhmQ82HnmsS0QZlLTxcQLduMFjxmBF+N5C0 ujYPULv1/cVPoWuRHF6HeG4l6Qi8bg6DxwYnZoiFdjsdApv4Yyc3OvQ3MLyBQfMFqNLRtSJjfX+ 55wOxQ1+bmxA== X-Google-Smtp-Source: AGHT+IFLqiRZXII7STwAO4wQZlkklPl+nG9LNYujk336u8Kf5vHyf6Fnu8QHro2ZjlnZKIH4UuhrVQ0RxmQA5Njbbp4= X-Received: by 2002:a05:6808:f8e:b0:3f4:600:7f73 with SMTP id 5614622812f47-3f4247a0099mr1821655b6e.33.1740135382097; Fri, 21 Feb 2025 02:56:22 -0800 (PST) MIME-Version: 1.0 References: <2705563.1740067550@sss.pgh.pa.us> <2765124.1740070338@sss.pgh.pa.us> In-Reply-To: <2765124.1740070338@sss.pgh.pa.us> From: Dominique Devienne Date: Fri, 21 Feb 2025 11:56:07 +0100 X-Gm-Features: AWEUYZlZ5MU2f2sShCoOAiAh-VnYZ1hw9gxckJxd76fFMe3pY0_YlUphF479aAA Message-ID: Subject: Re: DROP ROLE as SUPERUSER To: Tom Lane Cc: "David G. Johnston" , "pgsql-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004fa995062ea4d7e4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004fa995062ea4d7e4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Feb 20, 2025 at 5:52=E2=80=AFPM Tom Lane wrote: > So grants and revokes are still being done as the object owner by > default. > > Now I'm unclear on exactly what was happening in Dominique's case. > Was the problematic permission granted by somebody other than the > database's owner? > Here's my exact situation (with some renames). The DB Owner (Acme-DBA:...) is not the one that made the GRANT that prevented role foobar from being DROP'd. REVOKE as SUPERUSER was silently doing nothing, until I SET ROLE "SCH1:9XabXbNRbVABafYYGiP7nY" before doing it. ROLE foobar doesn't OWN anything, so David's REASSIGN or DROP OWNED as not relevant here. The point I'm trying to make, is that "hunting down" grantor(s) to connect to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish there was an easier way to drop a role in that situation. --DD D:\>ppg ... -d acmedb10 --impersonate foobar cluster --databases Connected OK (postgresql://postgres@.../acmedb10) Warning: Impersonating user: foobar =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D | Privs | dbname | owner | =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D | c- | acmedb10 | "Acme-DBA:004k1n" | | c- | postgres | postgres | =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D (where c =3D CONNECT privilege; and C =3D CREATE privilege) Can CONNECT to 2 databases (out of 4; 4 matching) D:\>ppg ... -d acmedb10 database --acls Connected OK (postgresql://postgres@.../acmedb10) |-----------------------------------|-----------------------------------|--= ---------|-----------| | Grantor | Grantee | Privilege | Grantable | |-----------------------------------|-----------------------------------|--= ---------|-----------| ... | "SCH1:9XabXbNRbVABafYYGiP7nY" | foobar | CONNECT | NO | |-----------------------------------|-----------------------------------|--= ---------|-----------| 6 ACLs to 3 Grantees from 2 Grantors --0000000000004fa995062ea4d7e4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Feb 20, 2025 at 5:52=E2=80=AFPM T= om Lane <tgl@sss.pgh.pa.us> = wrote:
So grants and revokes are still being d= one as the object owner by
default.

Now I'm unclear on exactly what was happening in Dominique's case.<= br> Was the problematic permission granted by somebody other than the
database's owner?

Here's my exa= ct situation (with some renames).
The DB Owner (Acme-DBA:...) is = not the one that made the GRANT
that prevented role foobar from b= eing DROP'd.

REVOKE as SUPERUSER was silently = doing nothing,
until I SET ROLE "SCH1:9XabXbNRbVABafYYGiP7nY= " before doing it.

ROLE foobar doesn't OW= N anything, so David's REASSIGN or
DROP OWNED as not relevant= here.

The point I'm trying to make, is that &= quot;hunting down" grantor(s) to connect
to DB(s) to be able= to "force drop" a ROLE is a PITA. And I really wish there
<= div>was an easier way to drop a role in that situation. --DD

=
D:\>ppg ... -d acmedb10 --impersonate foobar cluster --databasesConnected OK (postgresql://postgres@.../acmedb10)
Warning: Impersonati= ng user: foobar
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
| Pri= vs | =C2=A0dbname =C2=A0| =C2=A0 =C2=A0 =C2=A0 owner =C2=A0 =C2=A0 =C2=A0 |=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
| =C2=A0c- =C2=A0 | = acmedb10 | "Acme-DBA:004k1n" |
| =C2=A0c- =C2=A0 | postgres | = postgres =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D
(where c =3D CONNECT privilege; and C =3D CREATE priv= ilege)

Can CONNECT to 2 databases (out of 4; 4 matching)

D:\&= gt;ppg ... -d acmedb10 database --acls
Connected OK (postgresql://postgr= es@.../acmedb10)
|-----------------------------------|------------------= -----------------|-----------|-----------|
| =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0Grantor =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Grantee =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| Privilege | Grantable |
|-----------= ------------------------|-----------------------------------|-----------|--= ---------|
...
| &= quot;SCH1:9XabXbNRbVABafYYGiP7nY" | foobar=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | CON= NECT =C2=A0 | =C2=A0 =C2=A0NO =C2=A0 =C2=A0 |
|-------------------------= ----------|-----------------------------------|-----------|-----------|
= 6 ACLs to 3 Grantees from 2 Grantors
--0000000000004fa995062ea4d7e4--