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.klaver@aklaver.com
