MIME-Version: 1.0
References: <CAFCRh-_TQagsob_YH0nXktwhuBftNaiZ_VZ4REhZdSEkWXVKXg@mail.gmail.com>
In-Reply-To: <CAFCRh-_TQagsob_YH0nXktwhuBftNaiZ_VZ4REhZdSEkWXVKXg@mail.gmail.com>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Fri, 20 Jun 2025 09:07:59 -0500
Message-ID: <CAHyXU0yuoxKpEd-R-++Fvuw+DX+jJsZuf=pCEAVHVOPUzS-SLg@mail.gmail.com>
Subject: Re: Extension disappearing act
To: Dominique Devienne <ddevienne@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000801d7e0638016449"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0yuoxKpEd-R-%2B%2BFvuw%2BDX%2BjJsZuf%3DpCEAVHVOPUzS-SLg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jun 19, 2025 at 8:09=E2=80=AFAM Dominique Devienne <ddevienne@gmail=
.com>
wrote:

> Hi. Little mystery we don't understand. v17.
>
> Create new DB, owned by dedicated new ROLE.
> Create extension (pgcrypto) in our case. Installed in public, owned by
> DB owner role.
> Create schemas and populate them inside the DB.
> This also creates roles associated to those schemas.
> One of the schema is owned by the DB owner (in case that matters).
> Creates functions using pgcrypto, in some of those schemas.
> Drop all schemas (and associated roles), thus pgcrypto-using functins are
> gone.
> Of course, the DB owner role was not dropped. Can't in fact.
> Somehow, the pgcrypto extension has disappeared, as side-effects of the
> drops.
> We did a LIBPQ trace of the command to does all the drops,
>

 Suggestion:
1. Turn on statement logging to 'all'.  Make sure times are logged
2. Install a trace. this could be as simple as:
select now(), count(*) FILTER (WHERE extname =3D 'pgcrypto') from
pg_extension ;
\watch
...in psql
3. that should nail the time of the drop. at that time, you can then find
the offending statement

merlin

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jun 19,=
 2025 at 8:09=E2=80=AFAM Dominique Devienne &lt;<a href=3D"mailto:ddevienne=
@gmail.com">ddevienne@gmail.com</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">Hi. Little mystery we don&#39;t understand=
. v17.<br>
<br>
Create new DB, owned by dedicated new ROLE.<br>
Create extension (pgcrypto) in our case. Installed in public, owned by<br>
DB owner role.<br>
Create schemas and populate them inside the DB.<br>
This also creates roles associated to those schemas.<br>
One of the schema is owned by the DB owner (in case that matters).<br>
Creates functions using pgcrypto, in some of those schemas.<br>
Drop all schemas (and associated roles), thus pgcrypto-using functins are g=
one.<br>
Of course, the DB owner role was not dropped. Can&#39;t in fact.<br>
Somehow, the pgcrypto extension has disappeared, as side-effects of the dro=
ps.<br>
We did a LIBPQ trace of the command to does all the drops,<br></blockquote>=
<div><br></div><div>=C2=A0Suggestion:</div><div>1. Turn on statement loggin=
g to &#39;all&#39;.=C2=A0 Make sure times are logged</div><div>2. Install a=
 trace. this could be as simple as:</div><div>select now(), count(*) FILTER=
 (WHERE extname =3D &#39;pgcrypto&#39;) from pg_extension ;</div><div>\watc=
h</div><div>...in psql</div><div>3. that should nail the time of the drop. =
at that time, you can then find the offending statement</div><div><br></div=
><div>merlin</div></div></div>

--000000000000801d7e0638016449--