public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: immerrr again <[email protected]>
Cc: Pavel Luzanov <[email protected]>
Cc: [email protected]
Subject: Re: DROP ROLE blocked by pg_init_privs
Date: Tue, 25 Nov 2025 22:45:00 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
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>
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.
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
> 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.
regards, tom lane
view thread (5+ messages) latest in thread
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: <[email protected]>
* 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