public inbox for [email protected]  

help / color / mirror / Atom feed

From: immerrr again <[email protected]>
To: Tom Lane <[email protected]>
Cc: Pavel Luzanov <[email protected]>
Cc: [email protected]
Subject: Re: DROP ROLE blocked by pg_init_privs
Date: Wed, 26 Nov 2025 11:38:48 +0100
Message-ID: <CAERznn8MPbW8XBi-y4NB5X99hVTQH_bGdBEP_C=kFATciqOQJA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com>
	<[email protected]>
	<CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi=EdFzY5Fb7hOQ4g_Q@mail.gmail.com>
	<CAERznn-Hz_Y-V2gYP5UAcO+nU+e39o9WGDYTjpAqddyp8PMp4g@mail.gmail.com>
	<[email protected]>
	<CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
	<[email protected]>

On Wed, Nov 26, 2025 at 4:45 AM Tom Lane <[email protected]> wrote:

> immerrr again <[email protected]> writes:
> > On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <[email protected]> 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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: DROP ROLE blocked by pg_init_privs
  In-Reply-To: <CAERznn8MPbW8XBi-y4NB5X99hVTQH_bGdBEP_C=kFATciqOQJA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox