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]

#713 (comment)

[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.

[johnthreevolts]

I'm facing the same issue.
As suggested this helps:

- POSTGRES_INITDB_ARGS=--auth-host=scram-sha-256

But it would be nicer to set password_encryption in postgresql.conf to variable value.
So there wouldn't be required to duplicate it in another parameter.
Something like:

echo "password_encryption = ${POSTGRES_HOST_AUTH_METHOD:-md5}" >> postgresql.conf

It will set variable value if it is set, and md5 if not.

[tianon]

I think this is technically fixed most appropriately by #897 👍

[preethaml7]

I am still seeing this issue with the latest image (version 14).
Here is my docker-compose:

version: '3.3'
services:
postgres:
image: postgres:latest
container_name: postgres
restart: always
environment:
POSTGRES_USER: testUser
POSTGRES_PASSWORD: testPassword
POSTGRES_DB: test_db
POSTGRES_HOST_AUTH_METHOD: scram-sha-256
POSTGRES_INITDB_ARGS: '--auth-host=scram-sha-256 --auth-local=scram-sha-256'
ports:
- 5432:5432
volumes:
- postgres:/var/lib/postgresql/data

pg_hba.conf

local all all scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
host all all ::1/128 scram-sha-256

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

host all all all scram-sha-256

postgresql.conf

listen_addresses = '*'

These are my config, Expected: connection allowed from any IP using username and password set while creating the Postgres container, but the docker environment variables set are not respected and I am not able to login using the credentials.

These are the logs I see from the container:
2022-06-12 13:35:29.974 UTC [33] FATAL: password authentication failed for user "testUser"
2022-06-12 13:35:29.974 UTC [33] DETAIL: Connection matched pg_hba.conf line 86: "host all all 0.0.0.0/0 scram-sha-256"

From the logs, it seems that the connection is reaching the container, and the set config to allow connections from any host is working, but the credentials seem to be the issue here.

Can someone please help? what am I doing wrong here? Based on this issue, the fix is already merged but I am still seeing the same error.

This is the workaround I have found so far to be working after banging my head on the wall for 7 hours and multiple trials and errors later. Thought to share as it might save some immediate headache to other devs trying to set up the same now. But it is tedious to set up a docker container and follow these additional steps to reset the password and then log back into DB.

docker-compose (working for now):

version: '3.3'
services:
postgres:
image: postgres:latest
container_name: postgres
restart: always
environment:
POSTGRES_USER: testUser
POSTGRES_PASSWORD: testPassword
POSTGRES_DB: test_db
ports:
- 5432:5432
volumes:
- postgres:/var/lib/postgresql/data

1. Once the container is created edit pg_hba.conf file and update IPv4 connections allowed from 127.0.0.1/32 to 0.0.0.0/0
   and then restart the container.
2. Connect to the database without any credentials and update the password for the user specified in the initial docker-
   compose file --> ALTER USER testUser WITH PASSWORD 'testPassword';
3. Go back and edit pg_hba.conf file to update all 'trust' entries to 'scram-sha-256' and restart the container and
   you should be able to log in with your username and password now.

[ppacher]

Thanks @preethaml7 for that work around. I was fighting the exact same thing yesterday evening also on a postgres:14 container.

For me this happened after changing pg_hba.conf. With the initial auth config created by the container the login was working for some time now. I then changed that file to be more restrictive and require mTLS (clientcert=verify-ca) for the superuser while still using scram-sha-256. From that on, password authentication always failed. I needed to reset the password using ALTER USER to get it to work again.