Re: Error Creating pgcrypto Extension

On 9/18/26 7:41 AM, Adrian Klaver wrote:
> 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. ... "

In previous post I should have added a caveat, that from here:

https://www.postgresql.org/docs/18/sql-createdatabase.html

"template

The name of the template from which to create the new database, or
DEFAULT to use the default template (template1).
"

So it is possible for someone to create a database using a template
database other then template1.

>
> 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
>>
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com