RE: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine docker image, get 12.2 when running postgres -V command inside container

Update to this issue. I have found that if I change image2 to use the Postgres:12.4-alpine base image instead of the image1:12.4-alpine image, and add the image1 init script contents to the Postgres-ssl.sh script in image2, then the Postgres -V command returns the correct 12.4 response.

Michael W. Esmeraldo
Sr. Enterprise Architect

-----Original Message-----
From: PG Bug reporting form <noreply(at)postgresql(dot)org>
Sent: Wednesday, September 30, 2020 10:36 AM
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: Esmeraldo, Michael W. <MEsmeraldo(at)MIB(dot)com>
Subject: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine docker image, get 12.2 when running postgres -V command inside container

WARNING: This email originated from outside of MIB. Do not click links or open attachments unless you recognize the sender and know the content is safe.

The following bug has been logged on the website:

Bug reference: 16648
Logged by: Mike Esmeraldo
Email address: mesmeraldo(at)mib(dot)com
PostgreSQL version: 12.4
Operating system: alpine (docker)
Description:

I am using the 12.4-alpine image from the dockerhub official postgres image (https://urldefense.proofpoint.com/v2/url?u=https-3A__hub.docker.com_-5F_postgres&d=DwIGaQ&c=geOCcXTP0Pb3-gBXuO9swg&r=Awhb5dytK2ZEAiuCiqITJ-jdPSszJv6CieaFQvOU_Ng&m=fmiT2nrgXaeDf_ofvqEzyUZm-CBVNpuoVkl829hLr94&s=UKquh_lVGv57T8lCwMosyuI3xS7qQXhVOLRfg-vVX-Y&e= ) as a base image for my postgres container image (Image1). In my image, I simply add an initial script
(init.sh) in the /docker-entrypoint-initdb folder. This script sets password encryption to scram-sha-256, creates a role(dba_role) and user (dba), sets password for user, and grants permissions to role. This works fine and when I run the container, I see all the objects in the DB created as I expected, and when running the postgres -V command inside the container, I get the expected 12.4 returned.

IMAGE1 TAG : image1:12.4-alpine
IMAGE1 dockerfile :
FROM postgres:12.4-alpine
COPY init.sh /docker-entrypoint-initdb/init.sh

init.sh :
#!/bin/bash
echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf
echo "# TYPE DATABASE USER CIDR-ADDRESS METHOD" >
$PGDATA/pg_hba.conf
echo "local all all trust" >> $PGDATA/pg_hba.conf
echo "hostnossl all all 0.0.0.0/0 scram-sha-256" >>
$PGDATA/pg_hba.conf

set -e

psql -e -v ON_ERROR_STOP=1 --username "postgres" --dbname "postgres"
<<-EOSQL
set password_encryption = 'scram-sha-256';
ALTER ROLE postgres set password_encryption = 'scram-sha-256';
CREATE ROLE dba_role SUPERUSER CREATEDB CREATEROLE INHERIT NOLOGIN REPLICATION;
ALTER ROLE dba_role set password_encryption = 'scram-sha-256';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dba_role;
GRANT ALL PRIVILEGES ON DATABASE "postgres" to dba_role;
CREATE USER dba;
GRANT dba_role to dba;
EOSQL

# This creates the password for the dba user from the docker secret & ensures its encrypted with scram-sha-256 export usql="ALTER USER dba WITH PASSWORD '"$(cat /run/secrets/dba.password)"';"
psql -v ON_ERROR_STOP=1 --username "postgres" -c "$usql"
unset usql

# This creates the password for the postgres user from the docker secret & ensures its encrypted with scram-sha-256 export usql="ALTER USER postgres WITH PASSWORD '"$(cat /run/secrets/POSTGRES_PASSWORD)"';"
psql -v ON_ERROR_STOP=1 --username "postgres" -c "$usql"
unset usql

The point of creating this image was so that development teams in our company can use Image1 as a base image, and get the dba_role and dba user created automatically for them. When using image1 for a base image, this works fine for Databases that do not contain sensitive data and therefore do not require SSL only connections, or an auditing.

From there, we use image1 as a base image for image2 which disallows non SSL connections. Image2 inherits what image1 has in additon, we have a script
(postgres-ssl.sh) that Image2 places in the /docker-entrypoint-initdb folder as well. This script turns on ssl and sets some values in the postgresql.conf file to only allow SSL connectiosn and reject non-SSL connections.

IMAGE2 Dockerfile:
FROM image1:12-4.alpine
COPY postgres-ssl.sh /docker-entrypoint-initdb/postgres-ssl.sh

postgres-ssl.sh:
echo "ssl = on" >> $PGDATA/postgresql.conf echo "ssl_cert_file = '/run/secrets/server.crt'" >> $PGDATA/postgresql.conf echo "ssl_key_file = '/run/secrets/server.key'" >> $PGDATA/postgresql.conf

echo "# TYPE DATABASE USER CIDR-ADDRESS METHOD" >
$PGDATA/pg_hba.conf
echo "local all all trust" >> $PGDATA/pg_hba.conf
echo "hostnossl all all 0.0.0.0/0 reject" >>
$PGDATA/pg_hba.conf
echo "hostssl all all 0.0.0.0/0 scram-sha-256" >>
$PGDATA/pg_hba.conf

While Image2 deploys and works fine, only allowing SSL connections and rejecting all non-SSL connections, when we run the postgres-V command from inside the container, we get 12.2 as the response and not the 12.4 as expected.

I have included the above dockerfiles and scripts to assist in reproducing this issue. I would appreciate any help that can be provided. As I stated, the database is working as expected, but the discrepancy in the version reporting is something that our auditors flagged as a possible issue.

NOTICE: The information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure. If you have received this communication in error, please notify us immediately by replying to this message and deleting it from your computer. Thank you.