Re: DROP ROLE as SUPERUSER

On Thu, Feb 20, 2025 at 5:52 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(at)(dot)(dot)(dot)/acmedb10)
Warning: Impersonating user: foobar
========================================
| Privs | dbname | owner |
========================================
| c- | acmedb10 | "Acme-DBA:004k1n" |
| c- | postgres | postgres |
========================================
(where c = CONNECT privilege; and C = CREATE privilege)

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

D:\>ppg ... -d acmedb10 database --acls
Connected OK (postgresql://postgres(at)(dot)(dot)(dot)/acmedb10)
|-----------------------------------|-----------------------------------|-----------|-----------|
| Grantor | Grantee |
Privilege | Grantable |
|-----------------------------------|-----------------------------------|-----------|-----------|
...
| "SCH1:9XabXbNRbVABafYYGiP7nY" | foobar |
CONNECT | NO |
|-----------------------------------|-----------------------------------|-----------|-----------|
6 ACLs to 3 Grantees from 2 Grantors