psql: FATAL: password authentication failed for user "postgres"

[StephanMeijer]

trafficstars

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

[zalper]

v10, still reproduced. Solution is:

You need to define a password for user postgres

1. Get container docker ps

2. Enter inside of the container docker exec -it <hash> bash

3. Start query console psql

4. Define password ALTER ROLE postgres WITH PASSWORD 'your_password';

Then you may grant other users as superuser and etc. (Make your volume persist)

Container Mgmnt:

Up: docker-compose up --remove-orphans --force-recreate --build PostgreSQL

Down: docker-compose down -v

[AhmedBHameed]

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

[eddex]

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 saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on 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:

• Option 1: Stop the PostgreSQL service on Windows
• Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
• Option 3: Change the port of the docker container

[august-of-wind]

@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!

[TJBelcher]

@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 :)

[o-andres-u]

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

[nikluwfy]

@eddex also saved the day for me here.

[Mallikanand]

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

[sameersbn]

/remind me about this on saturday

[reminders[bot]]

@sameersbn set a reminder for May 30th 2020

[reminders[bot]]

:wave: @sameersbn, about this

[khalilahmad0]

@eddex Thanks man!

[STotev]

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.

version: '3.1'

services:

    postgres:
        image: postgres:12
        container_name: "postgres-v12"
        ports:
            - 5432:5432
        environment:
            POSTGRES_PASSWORD: admin
        volumes:
            - db_vol:/var/lib/postgresql/data

    pgadmin:
        image: dpage/pgadmin4
        container_name: pgadmin4
        ports:
            - 3333:80
        environment:
            PGADMIN_DEFAULT_EMAIL: admin
            PGADMIN_DEFAULT_PASSWORD: admin


volumes:
    db_vol:
        external:
            name: "postgres-v12"

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?

[STotev]

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.

[exaucae]

Thanks @eddex ! Solution 1 fixed my issue.

[Maxeeezy]

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 saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on 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:

• Option 1: Stop the PostgreSQL service on Windows
• Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
• Option 3: Change the port of the docker container

Do you maybe also know a solution for Mac OSX? I already spent hours on this bad connection error for this docker issue...

[Maxeeezy]

In my case (dockerizing a rails app) I had to delete tmp/db folder and rerun docker-compose up for me to solve the issue.

[kamalhm]

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

[l-narasimhan]

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.

It worked for me as well

[nox-mthimkulu]

I had the same issue with just running docker run --name pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres and I eventually realised that the command was using the postgres docker image that I had locally, tagged as latest (rather than pulling the latest one from remote). Run d docker image ls

and see what images you currently have. Removing the postgres:latest image I had locally and re-running the command ended up with the latest image being pulled and the login working!

[JustifydWarrior]

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.

services:
  database:
    image: "postgres" # use latest official postgres version
    env_file:
      - database.env # configure postgres
    volumes:
      - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
    ports:
      - 5435:5432
    expose:
      - "5435"

volumes:
  database-data: # named volumes can be managed easier using docker-compose

my database.env for reference

  PGUSER=postgres
  PGPASSWORD=postgres
  PGDATABASE=postgres
  POSTGRES_USER=postgres
  POSTGRES_PASSWORD=postgres
  POSTGRES_DB=postgres

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.

[Limatucano]

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 saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on 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:

• Option 1: Stop the PostgreSQL service on Windows
• Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
• Option 3: Change the port of the docker container

You are so beautiful, THANKSS

[Chicitadel]

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 saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on 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:

* Option 1: Stop the PostgreSQL service on Windows

* Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (`sudo apt install postgresql-client-common postgresql-client libpq-dev`)

* Option 3: Change the port of the docker container

You saved me tons of painful 72hours of battle with innocent Intellij IDE and plugins

[bibhuticoder]

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 saying psql: FATAL: password authentication failed for user "postgres".

The issue was that the postgres docker container was using the default port 5432 on 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:

• Option 1: Stop the PostgreSQL service on Windows
• Option 2 (using WSL): Completely uninstall Protgres 12 from Windows and install postgresql-client on WSL (sudo apt install postgresql-client-common postgresql-client libpq-dev)
• Option 3: Change the port of the docker container

Faced the same issue. Solved it by changing port mapping on docker-compose

services:
  db:
    image: postgres
    container_name: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: ***
      POSTGRES_USER: ***
      POSTGRES_DB: ***
    ports:
      - 5435:5432    ==> changed 5432 to 5435

[gustavorps]

Same issue

[jeremybradbury]

@gustavorps I think the solution is an easy one to work around. Using @JustifydWarrior's fix above:

    ports:
      - 5435:5432
    expose:
      - "5435"

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.

[fahricankacan]

I had the same problem . My solution for windows :

1. Stop docker container
2. win + r and type services.msc
3. Find postgressql and stop
4. Start your container again

I solved my problem with this way .

[escote]

Envs are used by postgres initdb to create your postgresql instance. If you changed yours envs after first execution, you need to delete the postgres container to force a recreation using new envs. If you have defined a volume, you will also need to remove it.

[thalles-victor]

My english is not very well, but i try help you.

In your project run

 ls -a

if exist a folder called database-data (name of the your volume of postgres of the docker-compose.yml), remove this folder and run again

  docker-compose up -d --force-recreate

The my work this way.

Or if it didn't work, try Remove all volumes outside project with

  docker volume rm $(docker volume ls -q)

and run again ```console` docker-compose up -d --force-recreate ``

[coding-x1]

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/