[docker] datastore_ro password is not correctly set by the init scripts

[spikeheap]

CKAN Version if known (or site URL)

master, running through docker-compose

Please describe the expected behaviour

I should be able to connect to the Postgres container as the datastore_ro user and read the datastore database.

Please describe the actual behaviour

The datastore_ro user and datastore database are both present in the db container, however attempting to connect from the ckan container as datastore_ro fails with password authentication failed for user.

What steps can be taken to reproduce the issue?

1. Bring up the docker-compose environment.
2. Run an interactive session in a ckan container: dc run --rm --entrypoint bash ckan
3. Within this session, attempt to connect with psql postgresql://datastore_ro:${CKAN_DATASTORE_READ_URL}@db/datastore

[ghost]

Having set up CKAN with docker compose recently, I can confirm the anomaly with the datastore_ro account password in postgresql.

I ran an interactive session into the db container : sudo docker exec -it db /bin/bash
After changing identity to the postgres user I was able to connect to psql and set the datastore_ro password to the value I had defined in the .env file.
I was then able to connect to the database from the ckan container with psql $CKAN_DATASTORE_READ_URL

After checking the Dockerfile of the ckan and db containers, the value set in the .env is correctly set in both containers. The environment variable $DS_RO_PASS actually contains the password from the .env file.

I guess the problem lies in the correct reference to this variable in the ckan/contrib/docker/postgresql/docker-entrypoint-initdb.d/00_create_datastore.sql script.

psql cannot view the container's environment variable.

[spikeheap]

Hi @bpm-fgrenier,

Thanks for taking a look at this. Your notes matches what I found while debugging.

Because psql doesn't do the variable interpolation, I've proposed #3986 to use a shell script to execute the SQL instead. This resolves the issue for me.