Re: DROP ROLE blocked by pg_init_privs

On Wed, Nov 26, 2025 at 4:45 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> immerrr again <immerrr(at)gmail(dot)com> writes:
> > On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> The missing step here is
> >> DROP OWNED BY test_role;
>
> > It just makes me uneasy to run a command with such potential for data
> loss
> > in order to remove a role.
>
> ...
> (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.
>

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.

> > 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]).
>
> Yup, that's far safer. No possibility of irretrievably hosing your
> 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