pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container
2+ messages / 2 participants
[nested] [flat]

* BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container
@ 2020-09-30 14:35 PG Bug reporting form <noreply@postgresql.org>
  2020-09-30 15:13 ` RE: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container Esmeraldo, Michael W. <MEsmeraldo@MIB.com>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2020-09-30 14:35 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: mesmeraldo@mib.com

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://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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* RE: [EXTERNAL] BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container
  2020-09-30 14:35 BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container PG Bug reporting form <noreply@postgresql.org>
@ 2020-09-30 15:13 ` Esmeraldo, Michael W. <MEsmeraldo@MIB.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Esmeraldo, Michael W. @ 2020-09-30 15:13 UTC (permalink / raw)
  To: Esmeraldo, Michael W. <MEsmeraldo@MIB.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

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@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 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@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=... ) 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.


^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2020-09-30 15:13 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-30 14:35 BUG #16648: Using postgres:12.4-alpine  docker image, get 12.2 when running postgres -V command inside container PG Bug reporting form <noreply@postgresql.org>
2020-09-30 15:13 ` Esmeraldo, Michael W. <MEsmeraldo@MIB.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox