Subject: Re: DROP ROLE blocked by pg_init_privs

From: immerrr again <[email-masked]>
Date: Wed, 26 Nov 2025 01:33:47 +0100

Hi Tom,

On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <[email-masked]> wrote:
> The missing step here is
> DROP OWNED BY test_role;
> ...
> See discussion here:
> ...

I had gone through that thread before posting, and yes, I mentioned "DROP
OWNED BY" in my original message. Is this what everyone is doing these
days? People just make sure there are no pg_depends/pg_shdepends that need
transferring, and then just blast the rest with "DROP OWNED BY"?

It just makes me uneasy to run a command with such potential for data loss
in order to remove a role. So much so that I have written a couple of
queries to manually clean up the system tables pg_init_privs/pg_shdepends
instead (see [1]). They seem to unblock dropping the role for now, but I'm
not even sure if I have missed something, or for how long they will keep
working.

I guess, my question is shouldn't there be a "REVOKE INITIAL ... FROM
<user>" command to drop just the initial privilege(-s) without potentially
nuking everything else owned by that user?

Thanks

1.
https://www.postgresql.org/message-id/CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi%3DEdFzY5Fb7hOQ4g_Q%40mail.gmail.com

---

From: immerrr again <[email-masked]>
Date: Wed, 26 Nov 2025 01:35:37 +0100

> I have written a couple of queries to manually clean up the system tables
pg_init_privs/pg_shdepends instead (see [1])

Sorry, wrong link [1]. Should have been

1.
https://www.postgresql.org/message-id/CAERznn-QWVpAvqnyF%3DrZfiuxkeDG0tym_rY%2BRuEkSPWvzgi67Q%40mail.gmail.com

---

From: Ron Johnson <[email-masked]>
Date: Tue, 25 Nov 2025 19:39:48 -0500

On Tue, Nov 25, 2025 at 7:36 PM immerrr again <[email-masked]> wrote:

>
> > I have written a couple of queries to manually clean up the system
> tables pg_init_privs/pg_shdepends instead (see [1])
>
It would be interesting to see what's missing from the
information_schema.*_privileges views:
https://www.postgresql.org/docs/14/information-schema.html

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Nov 2025 22:45:00 -0500

> in order to remove a role.

Well,

(a) if you try to DROP ROLE any role that still owns objects,
it will refuse, and tell you what the role still owns.
(The crux of your problem is that access permissions are not objects.)

(b) the usual procedure is to do REASSIGN OWNED first.  Anything
that remains to be dropped by DROP OWNED must be an access permission
not an object.

(c) you do know that DDL in Postgres is transactional, right?
You can roll it back if you don't like the results.

> So much so that I have written a couple of
> instead (see [1]).

Yup, that's far safer.  No possibility of irretrievably hosing your
database through ill-considered manual catalog changes, for sure.

			regards, tom lane

---

From: immerrr again <[email-masked]>
Date: Wed, 26 Nov 2025 11:38:48 +0100

> You can roll it back if you don't like the results.

Being able to roll back a dropped role doesn't seem like a huge help. I
mean, if I can detect that a table/function/type is missing after the fact,
it's probably even easier to check which ones are preventing the role from
being dropped in the first place, right?

REASSIGN before DROP does help, thanks. There's still a potential for
someone to create or reassign a new object to that role just before it's
dropped, but it's not a big deal.

> database through ill-considered manual catalog changes, for sure.

I wouldn't be discussing it here if I were happy about it.
Carpet-reassigning and -dropping didn't feel right, so I had explored an
alternative path. That one didn't feel right either. It made me wonder: if
there was no PG command for it, was there a yet another approach that was
better? I guess not, and everyone is just happy with REASSIGN+DROP, that's
fine.

Thanks