Обсуждение: Extension disappearing act

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.

TIA, --DD

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 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