docker-postgresql: psql: FATAL: password authentication failed for user "postgres"
$ sudo docker run --name postgresql -itd --restart always --env 'PG_PASSWORD=postgres' --env 'PG_TRUST_LOCALNET=true' --publish 5432:5432 --volume /srv/docker/postgresql:/var/lib/postgresql sameersbn/postgresql:9.6-2
59edeb353dec8c503a7f8d86396decfcdb31b98a3b463c3e9c990083fb4dc59e
$ psql -Upostgres -hlocalhost
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"
About this issue
- Original URL
- State: open
- Created 9 years ago
- Reactions: 72
- Comments: 57 (1 by maintainers)
I had a similar issue, probably not related to the one of @zalper but with the same error message so I’ll leave the solution here for anyone having the same problems.
I’m using Windows and had ProstgreSQL 12 installed.
At the same time I tried to run a postgres:10 docker container.
When I tried to connect to the database running in the docker container using
psql, I always got an error sayingpsql: FATAL: password authentication failed for user "postgres".The issue was that the postgres docker container was using the default port
5432on localhost and the PostgreSQL server on Windows was using the same port on localhost. However, there were no errors when starting either of them.Solutions:
sudo apt install postgresql-client-common postgresql-client libpq-dev)v10, still reproduced. Solution is:
You need to define a password for user postgres
Get container
docker psEnter inside of the container
docker exec -it <hash> bashStart query console
psqlDefine password
ALTER ROLE postgres WITH PASSWORD 'your_password';Then you may grant other users as superuser and etc. (Make your volume persist)
Container Mgmnt:
I had the same problem . My solution for windows :
I solved my problem with this way .
@eddex Just wanted to chime in to say your Option 1 above fixed this for me after I had been scratching my head for a couple of hours. That solution does not appear anywhere else online that I have been able to find and was extremely helpful. Thanks!
Today, I have decided to try and re-create the volume. So I deleted this postgres-v12 and created a new one and just in case I named it postgres12. I updated docker-compose.yml and voila everything started working again. I have never done any manual user updates, so somehow something messed up the postgres user in that volume.
@zalper how should i save your steps in the container!! I did your steps and commit the container but still now saved when running my image again !! BTW i’m NB to docker.
this worked for me… i just forward and expose 5435 on my machine. leave pg on 5432 inside the container. Then it’s easy to config 5435 or any other port for dev use.
my database.env for reference
yes it’s been redacted. i am not actually forcing the default values. just wanted to clarify the file structure and proper envs as noted here: https://www.postgresql.org/docs/9.3/libpq-envars.html
in addition to the others the docker container uses.
there seems to be no reason for 2 different sets of envs, but i haven’t thought through every use case.
so perhaps that’s a possible fix here is using the core PG envs and then update container docs to match.
@eddex Thanks a lot. I was having the same issue with my local Postgres installation and the container I was running by using docker. I just stopped the service on Windows and started working.
You are so beautiful, THANKSS
@eddex - after a couple of hours of struggle, this answer helped me. Didnt realise my Postgres locally starts automatically upon login. As I didnt need the local version anymore, uninstalled it.
A uppercase character in the username will also trigger this error message…
Faced the same issue. Solved it by changing port mapping on docker-compose
almost went crazy because I have tried even reinstalling docker to solve this issue, turns out it was because I forgot I installed a local postgresql. 🤪 thanks @eddex
In my case (dockerizing a rails app) I had to delete
tmp/dbfolder and rerundocker-compose upfor me to solve the issue.@eddex Thanks man!
@eddex also saved the day for me here.
If none of the above helped and you were sure that the port is free, you might consider removing all of your volumes, and turn on your containers again. I faced the same issue and removing the volumes brought me back on track!
docker-compose down -v, where-vwill remove all of your volumesdocker-compose up --buildIN MY CASE, I had to check the container’s ip address and sync it with the .env file.
make sure the container us running -->
docker psit shows the running containers, if empty, run the project containers withdocker compose upor similar commands based on the structurerestart the compose file
IF DIDN’T WORK: 3) set password for the same user:
docker exec -it <postgres container name> sh. --> enter the shell and run the commands below:IF NOT SOLVED YET:
docker inspect <postgres container name>. --> now you can see the container config check the detail and fix any mismatches(in my case the container’s ip address was on 172.18.0.2 but my POSTGRES_HOST was on 127.0.0.1 since I waited to run it locally.)Changing the
Portfixed the issuedocker run --name pg-con -e POSTGRES_PASSWORD=anypassTest -p 8080:5432 postgresyou will use
8080in your connection instead of5432Stopping the postgres service and uninstalling from Windows was my exact issue
You saved me tons of painful 72hours of battle with innocent Intellij IDE and plugins
Do you maybe also know a solution for Mac OSX? I already spent hours on this bad connection error for this docker issue…
Thanks man, helped a lot
This is the root cause for the problem if anyone wondering …
Bro just run docker on another port like 5433, use -
$ sudo docker run --name postgresql -itd --restart always --env ‘PG_PASSWORD=postgres’ --env ‘PG_TRUST_LOCALNET=true’ --publish 5433:5432 …
It worked for me 😃
For me, creating a new volume in docker-compose.yml by changing it in the
volumessession and then runningdocker-compose up --remove-orphans --force-recreate --build postgressolved the problem@eddex after two days of reading all of these forums, I finally found your solution, you saved my life hahaha Thanks my brotha!!
THanks it works for me
Below is my postgres:14 in the docker-compose.yml
I am writing Go Program in the microservice manner. One day before POSTGRES_PASSWORD: password was the password. Next day I changed POSTGRES_PASSWORD: secret Now the said error came.
The folder /db-data/postgres/ which I mentioned below has content populated by docker. Deleted that content and again run docker. Now ok.
postgres: image: ‘postgres:14.0’ ports: - “5432:5432” restart: always deploy: mode: replicated replicas: 1 environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: secret POSTGRES_DB: users volumes: - ./db-data/postgres/:/var/lib/postgresql/data/
@gustavorps I think the solution is an easy one to work around. Using @JustifydWarrior’s fix above:
5433 and 5434 are also reserved for more common uses, however 5435 is not a very common protocol by definition & is most often used as a second postgres port.
@bibhuticoder came up with the same solution.
I think someone should submit a PR to fix this, even if it is a second docker compose file, simply called by using the filename flag.
Disabling the default service, is the simplest solution, but that shouldn’t be the preferred workaround, many of us need both running.
Thanks @eddex ! Solution 1 fixed my issue.
I still got the issue and really don’t know what changed. I was doing some work on my home machine and everything was set up properly and was working. Then I moved working on my working machine, set it up there and it worked as well (and it still works there).
After moving to my working machine I removed the containers and images for postgres:12 from my home machine. Now I want to set the things up again but for some reason it refuses to log me. Haven’t changed any docker-compose settings. I am afraid that if I now remove everything from my working machine and try to set it up again, it’ll stop working there too.
This is my docker-compose.yml file.
I don’t have postgres installed directly on my machine, tried to change ports, passwords, adding networks, re-pulling images, restarting docker etc. and it just won’t work.
Any ideas anyone?
@eddex Thanks! I also benefited from you recalling your problem and solution. I’d not seen it mentioned anywhere else in 2 days of troubleshooting. I had noticed double use of the port in some netstat results, but reasoned it away as being 2 references to the same process since I didn’t know postgres 12 was running on my PC. Bad assumption on my part 😃