From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Phil Rau <Phil.Rau@sas.com>
To: Daniel Gustafsson <daniel@yesql.se>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Mark Hill <Mark.Hill@sas.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Cc: Michael King <Michael.King@sas.com>
Cc: Ashley Stephens <Ashley.Stephens@sas.com>
Subject: Re: Error Creating pgcrypto Extension
Date: Fri, 18 Sep 2026 07:41:44 -0700
Message-ID: <31bbf380-c8ec-43ff-8609-919a9dbb0f88@aklaver.com> (raw)
In-Reply-To: <PH0PR05MB9987163C3B636557679E714DEC81872@PH0PR05MB998716.namprd05.prod.outlook.com>
References: <BYAPR05MB6632AA793C3F51F016FE752EEEB82@BYAPR05MB6632.namprd05.prod.outlook.com>
<CAKFQuwaCnT854sPzRJGnO83ReCtKoi9DcFxj0cxBe617uZbxaA@mail.gmail.com>
<947662.1789689902@sss.pgh.pa.us>
<BE670ABC-CF65-4811-9E23-61031609D160@yesql.se>
<PH0PR05MB9987163C3B636557679E714DEC81872@PH0PR05MB998716.namprd05.prod.outlook.com>
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@yesql.se>
> Sent: Friday, September 18, 2026 5:44 AM
> To: Tom Lane <tgl@sss.pgh.pa.us>
> Cc: David G. Johnston <david.g.johnston@gmail.com>; Mark Hill <Mark.Hill@sas.com>; pgsql-general <pgsql-general@lists.postgresql.org>; Phil Rau <Phil.Rau@sas.com>; Michael King <Michael.King@sas.com>; Ashley Stephens <Ashley.Stephens@sas.com>
> Subject: Re: Error Creating pgcrypto Extension
>
>> On 18 Sep 2026, at 02:05, Tom Lane <tgl@sss.pgh.pa.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.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, Phil.Rau@sas.com, daniel@yesql.se, tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, Mark.Hill@sas.com, pgsql-general@lists.postgresql.org, Michael.King@sas.com, Ashley.Stephens@sas.com
Subject: Re: Error Creating pgcrypto Extension
In-Reply-To: <31bbf380-c8ec-43ff-8609-919a9dbb0f88@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox