Postgres: initial user does not respect $POSTGRES_HOST_AUTH_METHOD=scram-sha-256

[nicexe]

Here is how to replicate the issue.

docker-compose.yml:

version: '3.7'

services:
  database:
    image: postgres:12
    env_file: "./env/.env.db"
  db-test:
    image: postgres:12
    env_file: "./env/.env.db"

./env/.env.db:

POSTGRES_PASSWORD=pass
POSTGRES_USER=user
POSTGRES_DB=db
POSTGRES_HOST_AUTH_METHOD=scram-sha-256

Run docker-compose up -d and then docker-compose run db-test /bin/bash -c 'psql -d $POSTGRES_DB -U $POSTGRES_USER -h $DB_HOST'
Enter pass as your password.
It would fail.

Now modify ./env/.env.db as such:

POSTGRES_PASSWORD=pass
POSTGRES_USER=user
POSTGRES_DB=db
#POSTGRES_HOST_AUTH_METHOD=scram-sha-256

Run docker-compose down && docker-compose up -d and then docker-compose run db-test /bin/bash -c 'psql -d $POSTGRES_DB -U $POSTGRES_USER -h $DB_HOST' again.
Enter pass as your password.
This time it would succeed.

I purposely didn't persist any data for the database to start with a new clean environment on each try.

[wglambert]

Can't reproduce. Is your database being fully initialized in time?

$ docker run -d --rm --name postgres -e POSTGRES_USER=user -e POSTGRES_PASSWORD=pass -e POSTGRES_DB=db -e POSTGRES_HOST_AUTH_METHOD=scram-sha-256 postgres:12
552e1ffaef464c9a09e3dface53418841c834ab4edb284c354a6b1085fddb350

$ docker exec -it postgres bash -c 'psql -d $POSTGRES_DB -U $POSTGRES_USER'
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.

db-# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 user      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

db-# \q

$ docker exec -it postgres bash

root@552e1ffaef46:/# cat /var/lib/postgresql/data/pg_hba.conf | grep -v '^#\|^$'
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host all all all scram-sha-256

[yosifkit]

https://github.com/docker-library/postgres/pull/713#issuecomment-614854429

[nicexe]

It seems like the database fully initializes. The list of roles is identical to yours and the entries in pg_hba.conf match.

When I try to log in this is what happens:

$ docker-compose exec db-test /bin/bash -c 'psql -d $POSTGRES_DB -U $POSTGRES_USER -h $DB_HOST'
Password for user user:
psql: error: could not connect to server: FATAL:  password authentication failed for user "user"
$ 

This is the output from the logs:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2020-05-06 16:32:57.125 UTC [47] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-05-06 16:32:57.128 UTC [47] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-06 16:32:57.152 UTC [48] LOG:  database system was shut down at 2020-05-06 16:32:56 UTC
2020-05-06 16:32:57.160 UTC [47] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down...2020-05-06 16:32:57.419 UTC [47] LOG:  received fast shutdown request
.2020-05-06 16:32:57.422 UTC [47] LOG:  aborting any active transactions
2020-05-06 16:32:57.423 UTC [47] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2020-05-06 16:32:57.424 UTC [49] LOG:  shutting down
2020-05-06 16:32:57.444 UTC [47] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2020-05-06 16:32:57.529 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-05-06 16:32:57.529 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-05-06 16:32:57.529 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-05-06 16:32:57.535 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-05-06 16:32:57.550 UTC [65] LOG:  database system was shut down at 2020-05-06 16:32:57 UTC
2020-05-06 16:32:57.555 UTC [1] LOG:  database system is ready to accept connections
2020-05-06 16:46:18.875 UTC [122] FATAL:  password authentication failed for user "user"
2020-05-06 16:46:18.875 UTC [122] DETAIL:  User "user" does not have a valid SCRAM verifier.
        Connection matched pg_hba.conf line 95: "host all all all scram-sha-256"
2020-05-06 16:46:24.209 UTC [124] FATAL:  password authentication failed for user "user"
2020-05-06 16:46:24.209 UTC [124] DETAIL:  User "user" does not have a valid SCRAM verifier.
        Connection matched pg_hba.conf line 95: "host all all all scram-sha-256"
2020-05-06 16:46:52.497 UTC [127] FATAL:  password authentication failed for user "user"
2020-05-06 16:46:52.497 UTC [127] DETAIL:  User "user" does not have a valid SCRAM verifier.
        Connection matched pg_hba.conf line 95: "host all all all scram-sha-256"

[nicexe]

#713 (comment)

setting POSTGRES_INITDB_ARGS=--auth-host=scram-sha-256 made it work.