From: Tom Lane <tgl@sss.pgh.pa.us>
To: immerrr again <immerrr@gmail.com>
cc: Pavel Luzanov <p.luzanov@postgrespro.ru>,
        pgsql-general@lists.postgresql.org
Subject: Re: DROP ROLE blocked by pg_init_privs
In-reply-to: <CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
References: <CAERznn-QWVpAvqnyF=rZfiuxkeDG0tym_rY+RuEkSPWvzgi67Q@mail.gmail.com> <ca3fe7b5-2399-4537-bba4-cd000907bda7@postgrespro.ru> <CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi=EdFzY5Fb7hOQ4g_Q@mail.gmail.com> <CAERznn-Hz_Y-V2gYP5UAcO+nU+e39o9WGDYTjpAqddyp8PMp4g@mail.gmail.com> <1484313.1764115685@sss.pgh.pa.us> <CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
Comments: In-reply-to immerrr again <immerrr@gmail.com>
	message dated "Wed, 26 Nov 2025 01:33:47 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <1507598.1764128700.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 25 Nov 2025 22:45:00 -0500
Message-ID: <1507599.1764128700@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1507599.1764128700%40sss.pgh.pa.us>
Precedence: bulk

immerrr again <immerrr@gmail.com> writes:
> On Wed, Nov 26, 2025 at 1:08 AM Tom Lane <tgl@sss.pgh.pa.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.

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