MIME-Version: 1.0
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>
In-Reply-To: <1484313.1764115685@sss.pgh.pa.us>
From: immerrr again <immerrr@gmail.com>
Date: Wed, 26 Nov 2025 01:33:47 +0100
Message-ID: <CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
Subject: Re: DROP ROLE blocked by pg_init_privs
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Luzanov <p.luzanov@postgrespro.ru>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000578efe0644748d49"
Archived-At: <https://www.postgresql.org/message-id/CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij%2Bw%40mail.gmail.com>
Precedence: bulk

--000000000000578efe0644748d49
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Tom,


On Wed, Nov 26, 2025 at 1:08=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> 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%3D=
EdFzY5Fb7hOQ4g_Q%40mail.gmail.com

--000000000000578efe0644748d49
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hi Tom,</div><div><br></div><div><br=
 class=3D"gmail-Apple-interchange-newline">On Wed, Nov 26, 2025 at 1:08=E2=
=80=AFAM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.u=
s</a>&gt; wrote:</div><div>&gt; The missing step here is</div>&gt;=C2=A0<br=
>&gt; DROP OWNED BY test_role;<div>&gt; ...</div>&gt; See discussion here:<=
div>&gt; ...</div><div><br><div>I had gone through that thread before posti=
ng, and yes, I mentioned &quot;DROP OWNED BY&quot; 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 &quot;DROP OWNED BY&quot;?</div></div><div><br></div><div>It just=
 makes me uneasy to run a command with such potential for data loss in orde=
r to remove a role. So much so that I have written a couple of queries to m=
anually clean up the system tables pg_init_privs/pg_shdepends instead (see =
[1]). They seem to unblock dropping the role for now, but I&#39;m not even =
sure if I have missed something,=C2=A0or for how long they will keep workin=
g.</div><div><br></div><div>I guess, my question is shouldn&#39;t there be =
a &quot;REVOKE INITIAL ... FROM &lt;user&gt;&quot; command to drop just the=
 initial privilege(-s) without potentially nuking everything else owned by =
that user?</div><div><br></div><div>Thanks</div><div><br></div><div>1.=C2=
=A0<a href=3D"https://www.postgresql.org/message-id/CAERznn-SBBqQ3YcdZk9U4m=
qVQPsVgLisi%3DEdFzY5Fb7hOQ4g_Q%40mail.gmail.com">https://www.postgresql.org=
/message-id/CAERznn-SBBqQ3YcdZk9U4mqVQPsVgLisi%3DEdFzY5Fb7hOQ4g_Q%40mail.gm=
ail.com</a></div></div></div>

--000000000000578efe0644748d49--