Re: DROP ROLE as SUPERUSER

On Thu, Feb 20, 2025 at 5:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Thursday, February 20, 2025, Dominique Devienne <ddevienne(at)gmail(dot)com>
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> 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
> > “granted by” in your SQL command and thus failing to fully 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