Docker password authentication failed for user "postgres"

Question

I'm writing a docker-compose file to launch some services. But the db service is a trouble maker, I always get this error:

FATAL: password authentication failed for user "postgres" DETAIL: Password does not match for user "postgres". Connection matched pg_hba.conf line 95: "host all all all md5"

I've read a lot of threads, and I've correctly set the POSTGRES_USER and POSTGRES_PASSWORD. I have also remove the previous volumes and container to force postgresql to re-init the password. But I can't figure out why it's still not working.

So what is the correct way to force the re-initialization of the postgresql image. So I would be able to connect to my database.

I've seen that this error: Connection matched pg_hba.conf line 95: "host all all all md5", and I've heard about the postgres conf file. But it's an official container it's supposed to work, isn't it ?

version: '3'
services:
  poll:
    build: poll
    container_name: "poll"
    ports:
      - "5000:80"
    networks:
      - poll-tier
    environment:
      - REDIS_HOST=redis
    depends_on:
      - redis

  worker:
    build: worker
    container_name: "worker"
    networks:
      - back-tier
    environment:
      - REDIS_HOST=redis
      - POSTGRES_HOST=db
      - POSTGRES_DB=postgres
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=root
    depends_on:
      - redis
      - db

  redis:
    image: "redis:alpine"
    container_name: "redis"
    networks:
      - poll-tier
      - back-tier

  result:
    build: result
    container_name: "result"
    ports:
      - "5001:80"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=root
      - POSTGRES_HOST=db
      - RESULT_PORT=80
    networks:
      - result-tier
    depends_on:
      - db

  db:
    image: "postgres:alpine"
    container_name: "db"
    restart: always
    networks:
      - back-tier
      - result-tier
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=root
      - POSTGRES_DB=postgres

volumes:
  db-data:
    driver: local

networks:
  poll-tier: {}
  back-tier: {}
  result-tier: {}

I'm expected to get the db connected, and not password authentication failed for user "postgres".

Answer 1

In my case, it was caused by postgres.exe service on Windows 10 running in the background. When I stopped the service, uninstalled PostgreSQL 12 from Windows, and restarted, I could finally connect to Postgres Docker container.

EDIT: Stopping the service may also work until the next start, of course. Or you can disclude the service from auto startup.

Answer 2

Make sure your APPs (not the database container) are actually using the POSTGRES_USER and POSTGRES_PASSWORD variables. I suspect they are looking for something like DB_USER or similar and so aren't getting the right values in.

By default, every PostgreSQL database driver and admin tool defaults to the postgres user. This may explain why the error message complains about postgres even if the environment variable isn't being used.

A good way to verify is to change all references to the database user in the docker-compose file to something like postgres2. I suspect you'll still see apps complaining that password auth failed for postgres.

Answer 3

Very late to the party but still leaving this behind for future references :

Assuming you are a Windows user.

Try using the port 5433 instead of 5432.

As there is some internal windows issue in using the port for the same.

Below is the Docker Command for the same

docker run --name your_docker_instance_name -e POSTGRES_USER=your_pg_user_name -e POSTGRES_PASSWORD=your_secret_password -e POSTGRES_DB=your_db_name -p 5433:5433 -d postgis/postgis:your_pg_version

Your PSQL link would become something like

postgresql://your_pg_user_name:your_secret_password@localhost:5433/your_db_name?schema=public

Answer 4

You need to add: connect_timeout=5

Example:

postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable&connect_timeout=5