postgres: Recent versions seems to have broken auth when POSTGRES_PASSWORD is set

I’ve tried 9.6 and 9.5 and alpine variants too.

Getting rid of the POSTGRES_PASSWORD fixes it so I’m generally ok, but this could trip other people up too… looks like it might be the issue at: https://github.com/MagicStack/asyncpg/issues/158

compose:

        postgres_sx:
                image: postgres
                environment:
                  - POSTGRES_PASSWORD=postgres

postgres_sx_1  | FATAL:  password authentication failed for user "postgres"
postgres_sx_1  | DETAIL:  Connection matched pg_hba.conf line 95: "host all all all md5"
postgres_sx_1  | FATAL:  password authentication failed for user "postgres"
postgres_sx_1  | DETAIL:  Connection matched pg_hba.conf line 95: "host all all all md5"
postgres_sx_1  | FATAL:  password authentication failed for user "postgres"
postgres_sx_1  | DETAIL:  Connection matched pg_hba.conf line 95: "host all all all md5"
services_sx_1  | { error: password authentication failed for user "postgres"
services_sx_1  |     at Connection.parseE (/app/services/sx/node_modules/pg-promise/node_modules/pg/lib/connection.js:539:11)
services_sx_1  |     at Connection.parseMessage (/app/services/sx/node_modules/pg-promise/node_modules/pg/lib/connection.js:366:17)
services_sx_1  |     at Socket.<anonymous> (/app/services/sx/node_modules/pg-promise/node_modules/pg/lib/connection.js:105:22)
services_sx_1  |     at emitOne (events.js:115:13)
services_sx_1  |     at Socket.emit (events.js:210:7)
services_sx_1  |     at addChunk (_stream_readable.js:250:12)
services_sx_1  |     at readableAddChunk (_stream_readable.js:237:11)
services_sx_1  |     at Socket.Readable.push (_stream_readable.js:195:10)
services_sx_1  |     at TCP.onread (net.js:586:20)

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 2
Comments: 18 (3 by maintainers)

Most upvoted comments

Hi @lelit I tried to follow this discussion here but not sure how I can fix this.

I still immediately get this error when I set “POSTGRES_PASSWORD” using v10.1. What is the current state on this? How to fix this?

2018-01-02 10:02:24.537 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-01-02 10:02:24.537 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-01-02 10:02:24.542 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-01-02 10:02:24.556 UTC [64] LOG:  database system was shut down at 2018-01-02 10:02:24 UTC
2018-01-02 10:02:24.561 UTC [1] LOG:  database system is ready to accept connections
2018-01-02 10:02:28.734 UTC [71] FATAL:  password authentication failed for user "prasath"
2018-01-02 10:02:28.734 UTC [71] DETAIL:  Password does not match for user "prasath".
	Connection matched pg_hba.conf line 95: "host all all all md5"
2018-01-02 10:02:28.736 UTC [72] FATAL:  password authentication failed for user "prasath"
2018-01-02 10:02:28.736 UTC [72] DETAIL:  Password does not match for user "prasath".

+16

soosap on Jan 2, 2018

I was facing the same issue when deploying the PostgreSQL image on Kubernetes. The problem was really subtle, but was caused by a trailing newline character on my password environment variable. That’s why when creating base64 secrets using the command line, one should always use echo -n:

$ echo 123 | base64
MTIzCg==
$ echo -n 123 | base64
MTIz

What makes this hard to spot, is that there’s no visible difference when inspecting the container’s environment variables. Here’s an example using the password hunter2 with a trailing \n:

root@test-postgres-5b8884c9db-vgwcq:/# env | grep POSTGRES_PASSWORD
POSTGRES_PASSWORD=hunter2
root@test-postgres-5b8884c9db-vgwcq:/# PGPASSWORD=hunter2 psql -U myapp -h test-postgres
psql: FATAL:  password authentication failed for user "myapp"
root@test-postgres-5b8884c9db-vgwcq:/# [[ "$POSTGRES_PASSWORD" = "hunter2" ]]
root@test-postgres-5b8884c9db-vgwcq:/# echo $?
1
root@test-postgres-5b8884c9db-vgwcq:/# echo "$POSTGRES_PASSWORD" | md5sum
1235d50a49d1d58d7af22ad118827eac  -
root@test-postgres-5b8884c9db-vgwcq:/# echo "hunter2" | md5sum
6a0f0731d84afa4082031e3a72354991  -

And without it:

root@test-postgres-5b8884c9db-zs7m6:/# env | grep POSTGRES_PASSWORD
POSTGRES_PASSWORD=hunter2
root@test-postgres-5b8884c9db-zs7m6:/# PGPASSWORD=hunter2 psql -U myapp -h test-postgres
psql (9.6.8)
Type "help" for help.

myapp=# \q
root@test-postgres-5b8884c9db-zs7m6:/# [[ "$POSTGRES_PASSWORD" = "hunter2" ]]
root@test-postgres-5b8884c9db-zs7m6:/# echo $?
0
root@test-postgres-5b8884c9db-zs7m6:/# echo "$POSTGRES_PASSWORD" | md5sum
6a0f0731d84afa4082031e3a72354991  -
root@test-postgres-5b8884c9db-zs7m6:/# echo "hunter2" | md5sum
6a0f0731d84afa4082031e3a72354991  -

The fact that the PostgreSQL server in this image allows local connections without a password makes the debug of this issue even more confusing:

root@test-postgres-5b8884c9db-vgwcq:/# psql -U myapp
psql (9.6.8)
Type "help" for help.

myapp=# \q

That’s why testing the connection using psql without -h isn’t enough.

+10

myhro on Mar 8, 2018

It works fine without POSTGRES_PASSWORD set in docker compose env. For some reason that’s when it breaks. I’ll try repro with regular docker.

On Sat, 8 Jul. 2017, 6:47 am yosifkit, notifications@github.com wrote:

The Postgres image itself works fine:

$ docker pull postgres:9.6…Digest: sha256:e92fe21f695d27be7050284229a1c8c63ac10d88cba58d779c243566e125aa34 $ docker run -d -e POSTGRES_PASSWORD=12345 --name=posty postgres:9.6 $ docker logs --tail=6 postyPostgreSQL init process complete; ready for start up. LOG: database system was shut down at 2017-07-07 20:32:23 UTCLOG: MultiXact member wraparound protections are now enabledLOG: autovacuum launcher startedLOG: database system is ready to accept connections $ docker run -it --rm --link posty:postgres postgres:9.6 psql -h postgres -U postgresPassword for user postgres: psql (9.6.3)Type “help” for help. postgres=#

Can you connect to your postgres container via psql?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/docker-library/postgres/issues/308#issuecomment-313789088, or mute the thread https://github.com/notifications/unsubscribe-auth/AAOCN5PDWuH-ibb3n-WnZwPpE_8UAIqOks5sLplagaJpZM4OPOEw .

dpnova on Jul 7, 2017