Postgres: Cannot create a database with --auth-local=md5

[deinspanjer]

I am pretty sure this worked with an older version of the docker-entrypoint.sh, but I might be confusing it for a custom one I was using. :/

If you try to use the --auth-local=md5 argument to initdb, it fails because the password is not set.

Looking at entrypoint, the password handling comes after initdb, but it seems like it would make sense to add host all all all @authmethodhost@ to the pg_hba.conf.sample file and then let initdb do its thing instead of adding the line afterward.

[deinspanjer]

I was almost able to work around it for now by running with the following:

        echo "${POSTGRES_PASSWORD:?Please export POSTGRES_PASSWORD}" > .pgpass
        docker run --name db -d \
            -e POSTGRES_USER=myuser \
            -e POSTGRES_PASSWORD \
            -e PGPASS=$POSTGRES_PASSWORD \
            -e TZ=UTC \
            -e POSTGRES_INITDB_ARGS="--auth=md5 --pwfile=/tmp/pgpass" \
            -p 5432:5432 \
            --volume $(pwd)/.pgpass:/tmp/pgpass \
            postgres:9.6

But was still having problems with the later psql commands not being able to authenticate. :/

[deinspanjer]

Does anyone have ideas for getting md5 to work?

[yosifkit]

If you set a POSTGRES_PASSWORD (or an equivalent _FILE), the auth method is set to md5. This is only different for localhost (which is only accessible within the container's network namespace), which is generated from the sample file host all all 127.0.0.1/32 trust.

[deinspanjer]

My problem is that because the local line in the pg_hba.conf file is not also set to md5, non-superuser access to foreign servers is broken. Here is a quick test case to demonstrate the problem:

$ docker run --name pg -d -e POSTGRES_USER=tester  -e POSTGRES_PASSWORD=tester -p 5432:5432  -e TZ=UTC  postgres:latest
...
LOG:  database system is ready to accept connections

$ psql -h localhost -p 5432 -U tester -d postgres
postgres=# create user tester2 with password 'tester2;
CREATE ROLE
postgres=# grant connect on database postgres,tester to tester2;
GRANT
postgres=# create table foo as (select 'baz'::text as bar);
SELECT 1
postgres=# grant all on table foo to tester2;
GRANT
postgres=# \q

$ psql -h localhost -p 5432 -U tester -d tester
tester=# create extension postgres_fdw;
CREATE EXTENSION
tester=# create server postgres foreign data wrapper postgres_fdw options (host 'localhost', dbname 'postgres', port '5432');
CREATE SERVER
tester=# grant usage on foreign server postgres to tester2;
GRANT
tester=# create user mapping for tester2 server postgres options ( user 'tester2', password 'tester2');
CREATE USER MAPPING
tester=# \q

$ psql -h localhost -p 5432 -U tester2 -d tester
tester=# import foreign schema public from server postgres into public;
ERROR:  password is required
DETAIL:  Non-superuser cannot connect if the server does not request a password.
HINT:  Target server's authentication method must be changed.

$ docker exec pg sed -i.bak -Ee '/^host.*trust$/d; /^local.*trust/s/trust/md5/' /var/lib/postgresql/data/pg_hba.conf
$ docker restart pg
pg

$ psql -h localhost -p 5432 -U tester2 -d tester
tester=# import foreign schema public from server postgres into public;
IMPORT FOREIGN SCHEMA
tester=> select * from foo;
 bar
-----
 baz

[deinspanjer]

Note that with the above change to pg_hba.conf, the postgres user will no longer be able to log in since it was not created with a password, only the tester account was.

[neerdoc]

I have a similar problem which seems to be a race condition. I have set

      POSTGRES_PASSWORD: "<obfuscated>"
      POSTGRES_INITDB_ARGS: "-A md5"

in my docker-compose.yml file, and when starting the first time, I get an error stating

db_1   | server started
db_1   | Password for user postgres: 
db_1   | 2017-12-12 15:24:47.784 UTC [39] FATAL:  password authentication failed for user "postgres"
db_1   | 2017-12-12 15:24:47.784 UTC [39] DETAIL:  Password does not match for user "postgres".
db_1   |    Connection matched pg_hba.conf line 80: "local   all             all                                     md5"
db_1   | psql: FATAL:  password authentication failed for user "postgres"

So it is clear that the pg_hba.conf file is set correctly to test for md5 password but it fails. However, simply executing docker-compose down and then then docker-compose up again allows me to login.

Don't know if it helps, but I struggled for hours before I accidently ran docker-compose up two times and noticed that it solved/worked around the issue.

I also edited the docker-entrypoint.sh file to create a temporary password file:

echo "$POSTGRES_PASSWORD" > /tmp/admin_pass
eval "initdb --username=postgres --pwfile=/tmp/admin_pass $POSTGRES_INITDB_ARGS"
rm /tmp/admin_pass

[tevren]

@neerdoc, As @yosifkit mentioned, If you set a POSTGRES_PASSWORD, you don't have to set -A md5 as docker-entrypoint.sh will set the authmethod parameter: https://github.com/docker-library/postgres/blob/ef4545c07bd97e5585bb9e7f2680a4859b9ccf3c/docker-entrypoint.sh#L66-L69

[tianon]

Re-reading this old thread and realizing we never clarified the use case for doing this. :sweat_smile:

What's the use case for explicitly setting --auth-local=md5 on the PostgreSQL daemon?

[deinspanjer]

If you are trying to have a test environment that is similar to production, especially if prod is AWS RDS, it is useful to set this.

[tianon]

I've filed https://github.com/docker-library/postgres/pull/493 which uses initdb more directly to manipulate the initial superuser (instead of creating a new superuser after the fact) which simplifies the script slightly and makes --auth-local=md5 work. :tada:

[deinspanjer]

That is wonderful! Thank you for following up on this issue @tianon !