Skip to content

Recent versions seems to have broken auth when POSTGRES_PASSWORD is set #308

New issue

Have a question about this project? No Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

No Sign up for GitHub

By clicking “No Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? No Sign in to your account

Jump to bottom
Closed
dpnova opened this issue Jul 6, 2017 · 18 comments
Closed

Recent versions seems to have broken auth when POSTGRES_PASSWORD is set #308

dpnova opened this issue Jul 6, 2017 · 18 comments
Labels
question Usability question, not directly related to an error with the image

Comments

@dpnova
Copy link

dpnova commented Jul 6, 2017

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: MagicStack/asyncpg#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)
@yosifkit
Copy link
Member

yosifkit commented Jul 6, 2017

Could be related to #203 (comment).

@vitaly-t
Copy link

vitaly-t commented Jul 6, 2017 •
edited
Loading

@dpnova which version of pg-promise are you using? One of 5.x? Try 6.x, and if the problem is still there, it means the issue isn't with the driver, but elsewhere.

@dpnova
Copy link
Author

dpnova commented Jul 7, 2017

@yosifkit I had removed the volumes between each test

@vitaly-t it seems the libs I'm using pull in 5.x. I can try 6.. thanks.

In general it just seems odd that this started happening after I removed my local postgres images and pulled in the same postgres version but latest dockerhub image..

@vitaly-t
Copy link

vitaly-t commented Jul 7, 2017

In general it just seems odd that this started happening after I removed my local postgres images and pulled in the same postgres version but latest dockerhub image..

In this case it is very unlikely that swapping the driver will make any difference. But you can still try.

@yosifkit
Copy link
Member

yosifkit commented Jul 7, 2017

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 posty
PostgreSQL init process complete; ready for start up.

LOG:  database system was shut down at 2017-07-07 20:32:23 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
$ docker run -it --rm --link posty:postgres postgres:9.6 psql -h postgres -U postgres
Password for user postgres: 
psql (9.6.3)
Type "help" for help.

postgres=#

Can you connect to your postgres container via psql?

@dpnova
Copy link
Author

dpnova commented Jul 7, 2017 via email

@lelit
Copy link

lelit commented Jul 24, 2017

FYI, I distilled this minimalistic recipe that exhibits the problem, but still using docker compose. @dpnova, were you able to determine if the same happens with plain docker or not?

@dpnova
Copy link
Author

dpnova commented Jul 25, 2017 via email

@dpnova
Copy link
Author

dpnova commented Jul 25, 2017 via email

@lelit
Copy link

lelit commented Jul 25, 2017

I added an alternative recipe using plain docker and got the same problem.

@lelit lelit mentioned this issue Jul 25, 2017
Closed
@lelit
Copy link

lelit commented Aug 19, 2017

The problem is still there in PG10b3: asyncpg folks could not replicate the problem with a local build of PostgreSQL.

@dpnova
Copy link
Author

dpnova commented Aug 30, 2017

As noted over at MagicStack/asyncpg#158 the problem appeared around the same time the changes were made to the image build. I reckon d8abce4 looks suspect

@lelit
Copy link

lelit commented Sep 11, 2017

FYI, current asyncpg master does not manifest the problem anymore.

@soosap
Copy link

soosap commented Jan 2, 2018

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

@Sprechen Sprechen mentioned this issue Jan 2, 2018
Closed
@lelit
Copy link

lelit commented Jan 6, 2018

Sorry @soosap, I'm afraid I can be of little help here, because I shifted focus on other things and the transition to pg10 did not actually happen. As said, an asyncpg upgrade did fix my test suites.

Anyway, maybe you can adapt my https://github.com/lelit/asyncpg-issue158 to your use case?

@soosap
Copy link

soosap commented Jan 6, 2018

Hi thanks for getting back on this. I found a workaround - when I use POSTGRES_PASSWORD_FILE and mount the password as a file to /run/secrets (effectively making use of Docker Secrets) then I don't get this error.

@myhro
Copy link

myhro commented Mar 8, 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.

@wglambert wglambert added the question Usability question, not directly related to an error with the image label Apr 25, 2018
@tianon
Copy link
Member

tianon commented Jun 8, 2018

Since the original issue here appears solved, I'm going to close. 👍

@tianon tianon closed this as completed Jun 8, 2018
No Sign up for free to join this conversation on GitHub. Already have an account? No Sign in to comment
Assignees
No one assigned
Labels
question Usability question, not directly related to an error with the image
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
9 participants
@tianon @dpnova @lelit @myhro @yosifkit @vitaly-t @soosap @wglambert and others