Re: Error Creating pgcrypto Extension

On 9/18/26 7:33 AM, Phil Rau wrote:
> 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 d

https://www.postgresql.org/docs/18/manage-ag-templatedbs.html

"CREATE DATABASE actually works by copying an existing database. By
default, it copies the standard system database named template1. Thus
that database is the “template” from which new databases are made. If
you add objects to template1, these objects will be copied into
subsequently created user databases. ... "

atabases 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 <daniel(at)yesql(dot)se>
> Sent: Friday, September 18, 2026 5:44 AM
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>; Mark Hill <Mark(dot)Hill(at)sas(dot)com>; pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>; Phil Rau <Phil(dot)Rau(at)sas(dot)com>; Michael King <Michael(dot)King(at)sas(dot)com>; Ashley Stephens <Ashley(dot)Stephens(at)sas(dot)com>
> Subject: Re: Error Creating pgcrypto Extension
>
>> On 18 Sep 2026, at 02:05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com