On Thu, Jun 19, 2025 at 8:09 AM Dominique Devienne <ddevienne@xxxxxxxxx> 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
