Good morning, everyone. Thank you for the discussion so far. I work with the original poster and I wanted to be sure that we're not trying to accomplish our goal in the wrong way. So, please allow me to describe what we are trying to do.
We are trying to make our product compliant with the Federal Information Processing Standard (FIPS). So, in addition to converting from MD5 to SCRAM-SHA-256 password hashing and enabling TLS, we also needed to have the pgcrypto extension deployed to each database in our instance. Our initial thought would be to create the extension at the instance level, and then have any databases created inside of that instance get the extension without any extra commands. That's why we were tying this command: CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA pg_catalog CASCADE; However, Tom Lane indicated that this is not supported. So, it seems that we are not using the right method. The question now becomes: Is there a way to set up an instance such that any new databases created underneath it automatically get a given extension (in this case pgcrypto)? What is the best practice to do this? For this discussion, we can assume that we are starting with a fresh instance, without any databases yet created inside of it. Thank you! -- Phil Rau -----Original Message----- From: Daniel Gustafsson <[email protected]> Sent: Friday, September 18, 2026 5:44 AM To: Tom Lane <[email protected]> Cc: David G. Johnston <[email protected]>; Mark Hill <[email protected]>; pgsql-general <[email protected]>; Phil Rau <[email protected]>; Michael King <[email protected]>; Ashley Stephens <[email protected]> Subject: Re: Error Creating pgcrypto Extension > On 18 Sep 2026, at 02:05, Tom Lane <[email protected]> wrote: > On the whole, seeing that installing pgcrypto into pg_catalog has not > worked since v13 and there's been hardly any complaints about that, > I'm not sure that we should risk breaking working setups to allow it. I wouldn't be opposed to removing gen_random_uuid from pgcrypto, it's a hack and documented as obsolete in all supported versions. Supporting installing pgcrypto in pg_catalog would not be a motivator though, trimming unnecessary code from pgcrypto is however appealing -- Daniel Gustafsson
