Extension disappearing act

On 6/19/25 15:09, Dominique Devienne 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,
and there's no explicit drop of the extension.

All of the above is done using a LOGIN role that has CREATEROLE and CREATEDB.

Extensions are not supposed to implicitly disappear, are they?

No, extensions should not disappear.

Any idea, what we're missing, that might explain pgcrypto's disappearance?
We're stumped for now.

The only idea I can come up with is that pgcrypto was in one of those
dropped schemas (but I know you're saying it was in public).

Are you able to reproduce this? If yes, can you share a reproducer?

regards

--
Tomas Vondra

On 6/19/25 06:09, Dominique Devienne 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.

Does 'all' include the public schema?

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.

Was it in fact installed in the public schema?

TIA, --DD

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Jun 19, 2025 at 4:18 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 6/19/25 06:09, Dominique Devienne wrote:

Hi. Little mystery we don't understand. v17.

Does 'all' include the public schema?

No. We don't touch `public` at all, beside pgcrypto ending up inside it.

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.

Was it in fact installed in the public schema?

Sure was. It's my own command, but good enough. --DD

D:\>ppg -c my17 -d dd_v168a database_ --extensions
Connected OK (postgresql://ddevienne@localhost:5417/dd_v168a)
=======================================================
| Name | Version | Owner | Schema |
=======================================================
| pgcrypto | 1.3 | "Acme-DBA:000ik2" | public |
| plpgsql | 1.0 | postgres | pg_catalog |
=======================================================
2 installed extensions (out of 61)

Which runs

SELECT extname, extversion,
extowner::regrole::text as owner,
extnamespace::regnamespace::text as "schema"
FROM pg_extension
ORDER BY 1

On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne 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,
and there's no explicit drop of the extension.

All of the above is done using a LOGIN role that has CREATEROLE and CREATEDB.

Extensions are not supposed to implicitly disappear, are they?
Any idea, what we're missing, that might explain pgcrypto's disappearance?
We're stumped for now.

So are we. Why do you keep us guessing instead of posting a reproducer?

Yours,
Laurenz Albe

On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote:

Hi. Little mystery we don't understand. v17.
We're stumped for now.

So are we. Why do you keep us guessing instead of posting a reproducer?

Hi. Simply because there's too much proprietary stuff, I'm afraid.
And it's likely some stupid mistakes on our part anyway. That I can't see...
Still, the fact I see nothing extension-related in the libpq trace is
intriguing, isn't it?

On 6/20/25 09:35, Dominique Devienne wrote:

On Thu, Jun 19, 2025 at 6:35 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Thu, 2025-06-19 at 15:09 +0200, Dominique Devienne wrote:

Hi. Little mystery we don't understand. v17.
We're stumped for now.

So are we. Why do you keep us guessing instead of posting a reproducer?

Hi. Simply because there's too much proprietary stuff, I'm afraid.
And it's likely some stupid mistakes on our part anyway. That I can't see...
Still, the fact I see nothing extension-related in the libpq trace is
intriguing, isn't it?

PQtrace logs client-server communication. I would not expect it to say
anything about actions that happen on the server, like for example
automatically dropping objects in a schema, after the schema is dropped.

I think the best way to move this forward is sharing a reproducer. If
you have too much proprietary stuff, you'll have to remove those bits,
or rather replace them with something you can share.

In fact, a reproducer is meant to be "minimal" - the smallest example
causing the issue. So creating reproducers generally means simplifying
the example as much as possible anyway. And I wouldn't be surprised if
in the process of doing that you find the answer yourself.

regards
--
Tomas Vondra

On Thu, Jun 19, 2025 at 8:09 AM 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 = '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

On 6/19/25 07:54, Dominique Devienne wrote:

On Thu, Jun 19, 2025 at 4:18 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 6/19/25 06:09, Dominique Devienne wrote:

Hi. Little mystery we don't understand. v17.

Does 'all' include the public schema?

No. We don't touch `public` at all, beside pgcrypto ending up inside it.

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.

Was it in fact installed in the public schema?

Sure was. It's my own command, but good enough. --DD

D:\>ppg -c my17 -d dd_v168a database_ --extensions
Connected OK (postgresql://ddevienne@localhost:5417/dd_v168a)
=======================================================
| Name | Version | Owner | Schema |
=======================================================
| pgcrypto | 1.3 | "Acme-DBA:000ik2" | public |
| plpgsql | 1.0 | postgres | pg_catalog |
=======================================================
2 installed extensions (out of 61)

Which runs

SELECT extname, extversion,
extowner::regrole::text as owner,
extnamespace::regnamespace::text as "schema"
FROM pg_extension
ORDER BY 1

After you run the DROP commands the above query does not return
pgcrypto, correct?

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2025-Jun-19, Dominique Devienne 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.

I would investigate this using an event trigger attached to the sql_drop
event. That should allow you to identify exactly when the extension is
dropped. Something like

CREATE OR REPLACE FUNCTION report_dropped()
RETURNS event_trigger
AS $$
DECLARE r record;
BEGIN
FOR r IN SELECT * from pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE
'orig=% normal=% istemp=% type=% identity=% name=% args=%',
r.original, r.normal, r.is_temporary, r.object_type,
r.object_identity, r.address_names, r.address_args;
END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER svar_regress_event_trigger_report_dropped ON sql_drop
EXECUTE PROCEDURE report_dropped();

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)