Search code examples
postgresqldockercontainers

how to get postgres docker container to initialize with scram-sha-256 on any connection?

I’m trying to set a POSTGRES_USER & POSTGRES_PASSWORD with a docker postgres configuration using pg_hba.conf & postgresql.conf files:

docker run --name pg \
-e POSTGRES_USER=myUser \
-e POSTGRES_PASSWORD=myPassword \
-e POSTGRES_DB=myDb \
-v $PWD/pg_hba.conf:/etc/postgresql/pg_hba.conf \
-v $PWD/postgresql.conf:/etc/postgresql/postgresql.conf \
--network data-talk \
-d postgres \
-c config_file=/etc/postgresql/postgresql.conf \
-c hba_file=/etc/postgresql/pg_hba.conf

pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             172.19.0.0/16           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

postgresql.conf

listen_addresses = '*'
password_encryption = 'scram-sha-256'       # md5 or scram-sha-256

From here https://hub.docker.com/_/postgres

Note 1: The PostgreSQL image sets up trust authentication locally so you may notice a password is not required when connecting from localhost (inside the same container). However, a password will be required if connecting from a different host/container.

I want to require a password on any/every connection including local. By changing the pg_hba.conf I thought I could fulfill this request. I am using 172.19.0.0/16 as this is the subnet for the docker network.

When I run the above docker command I check the logs to find this psql: error: could not connect to server: FATAL: password authentication failed for user “myUser”.

Any idea on how to get this to work? I’ve also tried 

docker run --name pg \
-e POSTGRES_PASSWORD=password \
-v $PWD/pg_hba.conf:/etc/postgresql/pg_hba.conf \
-v $PWD/postgresql.conf:/etc/postgresql/postgresql.conf \
--network data-talk \
-d postgres \
-c config_file=/etc/postgresql/postgresql.conf \
-c hba_file=/etc/postgresql/pg_hba.conf

which runs to completion but errors when I try to login locally:

$ docker exec -it pg sh
psql -U postgres
Password for user postgres:
psql: error: could not connect to server: FATAL:  password authentication failed for user "postgres"
Solution

  • Following along here https://github.com/docker-library/postgres/blob/master/12/docker-entrypoint.sh#L202 I assume that on first run and setting up the database the authentication method is md5 , and when it is done the pg_hba.conf file sets it to scram-sha-256 making it unable to authenticate.

    By setting the POSTGRES_INITDB_ARGS and passing along the params to set the initial auth method to scram-sha-256 the password is set with scram-sha-256 for the initialization of the db, and just works.

    docker run --name pg \
-e POSTGRES_USER=myUser \
-e POSTGRES_PASSWORD=myPassword \
-e POSTGRES_DB=myDb \
-e POSTGRES_INITDB_ARGS="--auth-host=scram-sha-256 --auth-local=scram-sha-256" \
-v $PWD/pg_hba.conf:/etc/postgresql/pg_hba.conf \
-v $PWD/postgresql.conf:/etc/postgresql/postgresql.conf \
--network data-talk \
-d postgres \
-c config_file=/etc/postgresql/postgresql.conf \
-c hba_file=/etc/postgresql/pg_hba.conf

    POSTGRES_INITDB_ARGS

    This optional environment variable can be used to send arguments to postgres initdb. The value is a space separated string of arguments as postgres initdb would expect them. This is useful for adding functionality like data page checksums: -e POSTGRES_INITDB_ARGS="--data-checksums"

    The key here is on initdb