Setting Postgres extensions via POSTGRESQL_EXTENSIONS env

[parasense]

Hey Gang!

I'm attempting to start Postgres-15 with an extension: POSTGRESQL_EXTENSIONS="pg_trgm" but it does not seem to work as expected

The shell script here seems to be where the action happens:

postgresql-container/15/root/usr/share/container-scripts/postgresql/common.sh

Lines 491 to 498 in 4d36fdd

	create_extensions()
	{
	if [ -v POSTGRESQL_EXTENSIONS ]; then
	for EXT in $POSTGRESQL_EXTENSIONS; do
	psql -c "CREATE EXTENSION IF NOT EXISTS ${EXT};"
	done
	fi
	}

For context, I'm setting up a Quay instance with Postgres and that requires the pg_trgm extension.

Steps to reproduce:

##
## Setup storage and fix permissions
##
sudo mkdir -pv /mnt/quay/postgres-data
sudo setfacl -m u:26:-wx /mnt/quay/postgres-data

##
## Run the container
##
sudo podman pod create \
  --name quay-pod \
  -p 80:8080 \
  -p 443:8443

sudo podman run \
  --name quay_postgres \
  --pod quay-pod \
  --rm -d \
  -e POSTGRESQL_USER=quayuser \
  -e POSTGRESQL_PASSWORD=quaypass \
  -e POSTGRESQL_DATABASE=quay \
  -e POSTGRESQL_ADMIN_PASSWORD=adminpass \
  -e POSTGRESQL_EXTENSIONS="pg_trgm " \
  -v /mnt/quay/postgres-data:/var/lib/pgsql/data:Z \
  quay.io/fedora/postgresql-15

##
## The verification step will show if the "pg_trgm" extension was installed.
##
sudo podman exec -it quay_postgres  psql -U quayuser -d quay -c "\dx pg_trgm"

     List of installed extensions
 Name | Version | Schema | Description 
------+---------+--------+-------------
(0 rows)

The pg_trgm extension does in fact work if setup after the container is running.

sudo podman exec -it quay_postgres psql -U quayuser -d quay -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
sudo podman exec -it quay_postgres  psql -U quayuser -d quay -c "\dx pg_trgm"

CREATE EXTENSION
                                  List of installed extensions
  Name   | Version | Schema |                            Description
---------+---------+--------+-------------------------------------------------------------------
 pg_trgm | 1.6     | public | text similarity measurement and index searching based on trigrams
(1 row)

So it appears the feature to run extensions from the environment variable is not working, at least not in this case.
I see there are tests for the feature:

postgresql-container/test/run_test

Lines 1079 to 1088 in 4d36fdd

	# run_env_extension_load_test
	# --------------------
	# Tries to load pgaudit extension using environment variables
	# `POSTGRESQL_EXTENSIONS` and `POSTGRESQL_LIBRARIES`
	run_env_extension_load_test() {
	DOCKER_EXTRA_ARGS="
	-e POSTGRESQL_EXTENSIONS=pgaudit
	-e POSTGRESQL_LIBRARIES=pgaudit"
	run_pgaudit_test
	}

But I'm not sure how well that test works, and I've not tried running the test myself (not yet).

Can somebody chime in and say if the issue is legit?
Thanks in advance!

[parasense]

After more investigation I figured out what is happening.

The Postgres was in fact setting the database extension specified in the environment variable POSTGRESQL_EXTENSIONS=pg_trgm, but just not to the right database. The way the current scripting works:

   if [ -v POSTGRESQL_EXTENSIONS ]; then 
     for EXT in $POSTGRESQL_EXTENSIONS; do 
       psql -c "CREATE EXTENSION IF NOT EXISTS ${EXT};" 
     done 
   fi

That is potentially a BUG because the the SQL does not specify a target database. The extension is set on the default management database postgres, which may or may not be ideal. I've no idea if there are extensions a DBA would want to set of the postgres management database (probably not).

Ideally we would specify the database given to the container with POSTGRESQL_DATABASE if set. But I'm not sure the database is even created at the point-in-time where the extensions are setup. So it might cause an error if we set the extensions on a non existing database or with a non-existing user. So the script might have to be refactored to have extensions happen towards later phase of execution.

Another option is to set the extension to the template1 database which has the effect of propagating to any newly created databases on the container, and that might be unwanted overkill. It would sorta work around the problem of having to move the extensions steps to a later phase of the script, but now we have the extension inherited by all the things.

Now that I got the issue figured out, I'd be happy to write a patch to fix, but I'd appreciate some discussion from the SCL community first.