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-alpineimage, 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@postgresql.org>
Sent: Wednesday, September 30, 2020 10:36 AM
To: pgsql-bugs@lists.postgresql.org
Cc: Esmeraldo, Michael W. <MEsmeraldo@MIB.com>
Subject: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine docker image, get 12.2 when running postgres -V command
insidecontainer
 

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

The following bug has been logged on the website:

Bug reference:      16648
Logged by:          Mike Esmeraldo
Email address:      mesmeraldo@mib.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
thecontainer, I see all the objects in the DB created as I expected, and when running the postgres -V command inside
thecontainer, 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="ALTERUSER 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
exportusql="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
thedba_role and dba user created automatically for them.  When using image1 for a base image, this works fine for
Databasesthat 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
hasin 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
setssome 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
thepostgres-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
canbe provided.  As I stated, the database is working as expected, but the discrepancy in the version reporting is
somethingthat our auditors flagged as a possible issue.
 

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