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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: mesmeraldo(at)mib(dot)com
Subject: BUG #16648: Using postgres:12.4-alpine docker image, get 12.2 when running postgres -V command inside container
Date: 2020-09-30 14:35:55
Message-ID: 16648-e0fd91f8deb5f72a@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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://hub.docker.com/_/postgres) 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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Esmeraldo, Michael W. 2020-09-30 15:13:25 RE: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine docker image, get 12.2 when running postgres -V command inside container
Previous Message Tom Lane 2020-09-30 14:16:33 Re: [EXTERNAL] Re: ERROR: insufficient columns in the PRIMARY KEY constraint definition