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> <CAERznn-LSryEQuQAgMTZXYPi9NA-4M28VnOBVt0A4Uaxxwij+w@mail.gmail.com>
 <1507599.1764128700@sss.pgh.pa.us>
In-Reply-To: <1507599.1764128700@sss.pgh.pa.us>
From: immerrr again <immerrr@gmail.com>
Date: Wed, 26 Nov 2025 11:38:48 +0100
Message-ID: <CAERznn8MPbW8XBi-y4NB5X99hVTQH_bGdBEP_C=kFATciqOQJA@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="000000000000040e2806447d0158"
Archived-At: <https://www.postgresql.org/message-id/CAERznn8MPbW8XBi-y4NB5X99hVTQH_bGdBEP_C%3DkFATciqOQJA%40mail.gmail.com>
Precedence: bulk

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

On Wed, Nov 26, 2025 at 4:45=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> immerrr again <immerrr@gmail.com> writes:
> > On Wed, Nov 26, 2025 at 1:08=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wr=
ote:
> >> 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_shdepen=
ds
> > 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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Nov 26, 2025 at 4:45=E2=80=AFAM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">immerrr again &lt;<a href=3D"mailto:imm=
errr@gmail.com" target=3D"_blank">immerrr@gmail.com</a>&gt; writes:<br>
&gt; On Wed, Nov 26, 2025 at 1:08=E2=80=AFAM Tom Lane &lt;<a href=3D"mailto=
:tgl@sss.pgh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>
&gt;&gt; The missing step here is<br>
&gt;&gt; DROP OWNED BY test_role;<br>
<br>
&gt; It just makes me uneasy to run a command with such potential for data =
loss<br>
&gt; in order to remove a role.<br>
<br>...<br>
(b) the usual procedure is to do REASSIGN OWNED first.=C2=A0 Anything<br>
that remains to be dropped by DROP OWNED must be an access permission<br>
not an object.=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">
<br>
(c) you do know that DDL in Postgres is transactional, right?<br>
You can roll it back if you don&#39;t like the results.<br></blockquote><di=
v><br></div><div>Being able to roll back a dropped role doesn&#39;t seem li=
ke a huge help. I mean, if I can detect that a table/function/type is missi=
ng after the fact, it&#39;s probably even easier to check which ones are pr=
eventing the role from being=C2=A0dropped in the first place, right?</div><=
div><br></div><div>REASSIGN before DROP does help, thanks. There&#39;s stil=
l a potential for someone to create or reassign a new object to that role j=
ust before it&#39;s dropped, but it&#39;s not a big deal.</div><div>=C2=A0<=
/div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">
&gt; So much so that I have written a couple of<br>
&gt; queries to manually clean up the system tables pg_init_privs/pg_shdepe=
nds<br>
&gt; instead (see [1]).<br>
<br>
Yup, that&#39;s far safer.=C2=A0 No possibility of irretrievably hosing you=
r<br>
database through ill-considered manual catalog changes, for sure.<br></bloc=
kquote><div><br></div><div>I wouldn&#39;t be discussing it here if I were h=
appy about it. Carpet-reassigning and -dropping didn&#39;t feel right, so I=
 had explored an alternative path. That one didn&#39;t feel right either. I=
t made me wonder: if there was no PG command for it, was there a yet anothe=
r approach that was better? I guess not, and everyone is just happy with RE=
ASSIGN+DROP, that&#39;s fine.</div><div><br></div><div>Thanks</div></div></=
div>

--000000000000040e2806447d0158--