DMARC-Filter: OpenDMARC Filter v1.3.2 mta-p5.oit.umn.edu 4W7R0r54v4z9vKZK
MIME-Version: 1.0
References: <CAOLfK3Wx2VMhNONFpNpnA0LCMXpQgmSadGicJ3K+LF+ayeTWxQ@mail.gmail.com>
 <c09df100-6d68-4a86-901f-e1c11a27da27@aklaver.com>
In-Reply-To: <c09df100-6d68-4a86-901f-e1c11a27da27@aklaver.com>
From: Matt Zagrabelny <mzagrabe@d.umn.edu>
Date: Mon, 24 Jun 2024 19:43:58 -0500
Message-ID: <CAOLfK3U-Hweja1P4OKjHe-E_nVmHR5uw7LjovFJJ=yech=hM8Q@mail.gmail.com>
Subject: Re: schema privileges and drop role
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: "pgsql-general@postgresql.org >> PG-General Mailing List" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002bfef8061bac32cd"
Archived-At: <https://www.postgresql.org/message-id/CAOLfK3U-Hweja1P4OKjHe-E_nVmHR5uw7LjovFJJ%3Dyech%3DhM8Q%40mail.gmail.com>
Precedence: bulk

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

Hi Adrian,

Thanks for the reply!

On Mon, Jun 24, 2024 at 7:03=E2=80=AFPM Adrian Klaver <adrian.klaver@aklave=
r.com>
wrote:

> On 6/24/24 17:00, Matt Zagrabelny wrote:
> > Greetings,
> >
> > I have a database that I recently changed the ownership for and now I a=
m
> > attempting to drop the previous owner of the database:
> >
> > test_db=3D# drop ROLE legacy_owner;
> > ERROR:  role "legacy_owner" cannot be dropped because some objects
> > depend on it
> > DETAIL:  privileges for schema public
> >
> > I don't know where to look to find out what I need to alter to be able
> > to remove the legacy role and internet searches came up fruitless.
>
> Generally best to start with the docs:
>
> https://www.postgresql.org/docs/current/sql-droprole.html
>
> "A role cannot be removed if it is still referenced in any database of
> the cluster; an error will be raised if so. Before dropping the role,
> you must drop all the objects it owns (or reassign their ownership) and
> revoke any privileges the role has been granted on other objects. The
> REASSIGN OWNED and DROP OWNED commands can be useful for this purpose;
> see Section 22.4 for more discussion."
>

Cool. I gave it a try, but came up with the same error:

test_db=3D# REASSIGN OWNED by legacy_owner TO new_owner;
REASSIGN OWNED
Time: 0.212 ms
test_db=3D# drop role legacy_owner;
ERROR:  role "legacy_owner" cannot be dropped because some objects depend
on it
DETAIL:  privileges for schema public
test_db=3D#

I'd still like to see how to list the "privileges for schema public", but I
do appreciate the REASSIGN OWNED command.

Thanks!

-m


>
> >
> > Does anyone have any hints or advice on where to look?
> >
> > Thanks for the help!
> >
> > -m
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Hi Adrian,</div><div><br></div><div>=
Thanks for the reply!</div></div><br><div class=3D"gmail_quote"><div dir=3D=
"ltr" class=3D"gmail_attr">On Mon, Jun 24, 2024 at 7:03=E2=80=AFPM Adrian K=
laver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@aklave=
r.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex">On 6/24/24 17:00, Matt Zagrabelny wrote:<br>
&gt; Greetings,<br>
&gt; <br>
&gt; I have a database that I recently changed the ownership for and now I =
am <br>
&gt; attempting to drop the previous owner of the database:<br>
&gt; <br>
&gt; test_db=3D# drop ROLE legacy_owner;<br>
&gt; ERROR: =C2=A0role &quot;legacy_owner&quot; cannot be dropped because s=
ome objects <br>
&gt; depend on it<br>
&gt; DETAIL: =C2=A0privileges for schema public<br>
&gt; <br>
&gt; I don&#39;t know where to look to find out what I need to alter to be =
able <br>
&gt; to remove the legacy role and internet searches came up fruitless.<br>
<br>
Generally best to start with the docs:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/sql-droprole.html" rel=
=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/s=
ql-droprole.html</a><br>
<br>
&quot;A role cannot be removed if it is still referenced in any database of=
 <br>
the cluster; an error will be raised if so. Before dropping the role, <br>
you must drop all the objects it owns (or reassign their ownership) and <br=
>
revoke any privileges the role has been granted on other objects. The <br>
REASSIGN OWNED and DROP OWNED commands can be useful for this purpose; <br>
see Section 22.4 for more discussion.&quot;<br></blockquote><div><br></div>=
<div>Cool. I gave it a try, but came up with the same error:</div><div><br>=
</div><div>test_db=3D# REASSIGN OWNED by legacy_owner TO new_owner;<br>REAS=
SIGN OWNED<br>Time: 0.212 ms<br>test_db=3D# drop role legacy_owner;<br>ERRO=
R: =C2=A0role &quot;legacy_owner&quot; cannot be dropped because some objec=
ts depend on it<br>DETAIL: =C2=A0privileges for schema public<br>test_db=3D=
#</div><div><br></div><div>I&#39;d still like to see how to list the &quot;=
privileges for schema public&quot;, but I do appreciate the REASSIGN OWNED =
command.</div><div><br></div><div>Thanks!</div><div><br></div><div>-m<br></=
div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
&gt; <br>
&gt; Does anyone have any hints or advice on where to look?<br>
&gt; <br>
&gt; Thanks for the help!<br>
&gt; <br>
&gt; -m<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div></div>

--0000000000002bfef8061bac32cd--