MIME-Version: 1.0
References: <CAFCRh-_3+E3-pmdH+i5jUE-8Z1jJWxxdP3EcFjTbHVWM+oEweg@mail.gmail.com>
 <CAKFQuwb-pHsxJF22fAp2Vb1jwbQxTVxXhuLzjaocsB5LEUEb5w@mail.gmail.com> <2705563.1740067550@sss.pgh.pa.us>
In-Reply-To: <2705563.1740067550@sss.pgh.pa.us>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Thu, 20 Feb 2025 17:21:13 +0100
Message-ID: <CAFCRh-8cS+crZ52=TQcMWy1rasCiKr9PrnCU9CObXfiNJFXYEg@mail.gmail.com>
Subject: Re: DROP ROLE as SUPERUSER
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003e9490062e954478"
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-8cS%2BcrZ52%3DTQcMWy1rasCiKr9PrnCU9CObXfiNJFXYEg%40mail.gmail.com>
Precedence: bulk

--0000000000003e9490062e954478
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 20, 2025 at 5:05=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com=
>
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silent=
ly
> >> did nothing, even with CASCADE, when I was running it as SUPERUSER,
> >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the
> GRANTOR, do
> >> the REVOKE, which DID something this time, and then I could DROP the
> role.
>
> > This has nothing to do with power/permissions.  It is about not
> specifying
> > =E2=80=9Cgranted by=E2=80=9D in your SQL command and thus failing to fu=
lly and correctly
> > specify the single permission you want to revoke.
>
> It used to be that if a superuser issued GRANT/REVOKE, the operation
> was silently done as the owner of the affected object.  That was
> always a bit of a wart, since among other things it meant that the
> object owner could undo it.  Now you have to say "GRANTED BY <owner>"
> to get that effect.  I'm not entirely sure, but I think this is closer
> to what the SQL standard says.
>

I wasn't aware of GRANTED BY, thanks for that.

But that's not much better. It's basically like the SET ROLE to the GRANTOR
I did.
I guess what I want is GRANTED BY ANYONE! And not have to figure out
GRANTOR(s).

Also, note that GRANTOR is not even the owner of the DATABASE in my case.
--DD

--0000000000003e9490062e954478
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Feb 20, 2025 at 5:05=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">&quot;David G. Johnston&quot; &lt;<a hr=
ef=3D"mailto:david.g.johnston@gmail.com" target=3D"_blank">david.g.johnston=
@gmail.com</a>&gt; writes:<br>
&gt; On Thursday, February 20, 2025, Dominique Devienne &lt;<a href=3D"mail=
to:ddevienne@gmail.com" target=3D"_blank">ddevienne@gmail.com</a>&gt;<br>
&gt; wrote:<br>
&gt;&gt; Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE si=
lently<br>
&gt;&gt; did nothing, even with CASCADE, when I was running it as SUPERUSER=
,<br>
&gt;&gt; preventing DROP&#39;ing the ROLE. I had to manually SET ROLE to th=
e GRANTOR, do<br>
&gt;&gt; the REVOKE, which DID something this time, and then I could DROP t=
he role.<br>
<br>
&gt; This has nothing to do with power/permissions.=C2=A0 It is about not s=
pecifying<br>
&gt; =E2=80=9Cgranted by=E2=80=9D in your SQL command and thus failing to f=
ully and correctly<br>
&gt; specify the single permission you want to revoke.<br>
<br>
It used to be that if a superuser issued GRANT/REVOKE, the operation<br>
was silently done as the owner of the affected object.=C2=A0 That was<br>
always a bit of a wart, since among other things it meant that the<br>
object owner could undo it.=C2=A0 Now you have to say &quot;GRANTED BY &lt;=
owner&gt;&quot;<br>
to get that effect.=C2=A0 I&#39;m not entirely sure, but I think this is cl=
oser<br>
to what the SQL standard says.<br></blockquote><div><br></div><div>I wasn&#=
39;t aware of GRANTED BY, thanks for that.</div><div><br></div><div>But tha=
t&#39;s not much better. It&#39;s basically like the SET ROLE to the GRANTO=
R I did.</div><div>I guess what I want is GRANTED BY ANYONE! And not have t=
o figure out GRANTOR(s).</div><div><br></div><div>Also, note that GRANTOR i=
s not even the owner of the DATABASE in my case. --DD</div></div></div>

--0000000000003e9490062e954478--