Subject: Re: DROP ROLE as SUPERUSER

From: Dominique Devienne <[email-masked]>
Date: Fri, 21 Feb 2025 11:56:07 +0100

On Thu, Feb 20, 2025 at 5:52 PM Tom Lane <[email-masked]> 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
========================================
| 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@.../acmedb10)
|-----------------------------------|-----------------------------------|-----------|-----------|
|              Grantor              |              Grantee              |
Privilege | Grantable |
|-----------------------------------|-----------------------------------|-----------|-----------|
...
| "SCH1:9XabXbNRbVABafYYGiP7nY" | foobar                            |
CONNECT   |    NO     |
|-----------------------------------|-----------------------------------|-----------|-----------|
6 ACLs to 3 Grantees from 2 Grantors

---

From: Tom Lane <[email-masked]>
Date: Fri, 21 Feb 2025 09:33:56 -0500

> was an easier way to drop a role in that situation. --DD

REASSIGN OWNED then DROP OWNED is the recommended path.

			regards, tom lane

---

From: Dominique Devienne <[email-masked]>
Date: Fri, 21 Feb 2025 15:35:19 +0100

> REASSIGN OWNED then DROP OWNED is the recommended path.

Hi. Am I missing something? foobar does not OWN anything in this case.
So I don't see how these recommendations are relevant to this particular
case. --DD

---

From: Tom Lane <[email-masked]>
Date: Fri, 21 Feb 2025 09:44:22 -0500

> So I don't see how these recommendations are relevant to this particular

DROP OWNED also removes relevant permission entries (which can be
thought of as things owned by the role, if you hold your head at
the right angle).  See its man page.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Fri, 21 Feb 2025 07:45:32 -0700

>From “drop owned”:

 Any privileges granted to the given roles on objects in the current
database or on shared objects (databases, tablespaces, configuration
parameters) will also be revoked.

So, the command does more than the name suggests.

David J.

---

From: Dominique Devienne <[email-masked]>
Date: Fri, 21 Feb 2025 16:02:24 +0100

> So, the command does more than the name suggests.

OK, thanks Tom and David. I was misled by the name indeed. --DD

---

From: Dominique Devienne <[email-masked]>
Date: Tue, 29 Apr 2025 16:50:40 +0200

> > So I don't see how these recommendations are relevant to this particular

Hi. Resurrecting this older thread, as I'm running into an issue with this.

> the right angle).  See its man page.

Except when it doesn't... I'm doing the DROP OWNED, and I'm getting
several "WARNING:  no privileges could be revoked for ..." for SCHEMAs
and ROUTINEs. (which BTW break my unit tests, which monitor outputs).

So the "removes relevant permission entries" part is kinda broken IMHO.
Or at least, not at all as convenient as one might hope it to be. FWIW. --DD

---

From: Tom Lane <[email-masked]>
Date: Tue, 29 Apr 2025 11:31:44 -0400

> and ROUTINEs. (which BTW break my unit tests, which monitor outputs).

[ shrug... ]  With no details, it's impossible to comment on this.

			regards, tom lane