Docker Compose w/ PostgreSQL - psql Password Authentication failed

Question

I set up the PostgreSQL using Docker Compose and the content of the file (compose.yaml) is like so:

name: postgres-container
services:
  database:
    image: postgres
    restart: always
    environment:
      - POSTGRES_PASSWORD
    // OR POSTGRES_PASSWORD = ${POSTGRES_PASSWORD}
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

I ran docker compose up command inside the terminal and then after initializing the server and database, I tried to connect to the PostgreSQL using psql -h localhost -U postgres.

Then it prompt me for password so I entered the password that matched exactly in my .env file in my project folder but I'm still unable to enter the PostgreSQL server and gave me error.

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "postgres"

Below is my .env file:

# When adding additional env variables, the schema in /env/schema.mjs should be updated accordingly

# Prisma
DATABASE_URL=postgres://postgres:postgres@localhost/crud?connect_timeout=10

# Next Auth
NEXTAUTH_SECRET=...
NEXTAUTH_URL=http://localhost:3000

# Next Auth Google Provider
GOOGLE_CLIENT_ID=...
GOOGLE_CLIENT_SECRET=...

# Next Auth Discord Provider
DISCORD_CLIENT_ID=...
DISCORD_CLIENT_SECRET=...

# PostgreSQL Auth
POSTGRES_PASSWORD=postgres

How do I solve this issue? I already did:

• Delete volume that store the data
• Delete the container that runs
• Delete the PostgreSQL image

And when I ran docker compose convert command, it gave me true value:

name: postgres-container
services:
  database:
    environment:
      POSTGRES_PASSWORD: postgres
    image: postgres
    networks:
      default: null
    restart: always
    volumes:
    - type: volume
      source: pgdata
      target: /var/lib/postgresql/data
      volume: {}
networks:
  default:
    name: postgres-container_default
volumes:
  pgdata:
    name: postgres-container_pgdata

Answer 1

After a day of fiddling what fixed it for me was:

ports:
      - "5434:5432" # So 5434 for local host, but still 5432 in the container

and then access by:

psql -h localhost -p 5434 -U root -d users

Answer 2

For me (running on a Ubuntu base, connecting to a docker image) I had to use the IP address that docker was using for the image to connect instead of "localhost".

I found the IP that the image was working on by running

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' image-name-here

Not sure why using localhost was giving me a "password failed" error, but here we are.

I'm sure there's a better way (like setting to expose a port or something) but this worked me and was a good enough solution.

Answer 3

Check your password too. If you have special characters ($@#%&*) in your password, you need to specially handle them or remove them. This is because, by default, most clients will use a URL-based approach to connect to the DB. These special characters might have side effects on the URL processing (unintended delimiting and such).

Answer 4

There is a chance your local postgres server is running on port 5432

When you try to connect on docker postgres port 5432 you are connecting to your local machine postgres server

Stop the local postgres server using pg_ctl -D "C:\Program Files\PostgreSQL\16\data" stop and try again with docker postgres credentials

pg_ctl is located in C:\Program Files\PostgreSQL\16\bin (depends on OS and server version)

Answer 5

It happens when your local postgresql server is running, just stop local postgresql server and try, hope it will resolve the issue.

Answer 6

if you are sure that your postgres credential are correct, then based on official postgres docker image docs at this link, setting these values as environment for the docker container will solve this issue

POSTGRES_DB=pur your postgres default db name here
POSTGRES_USER=set a uername for your default database
POSTGRES_PASSWORD=HfG3@fj0OfjvHdkf234ja(some random password)

# these 2 variable are for preventing password authentication failed for user $USER error
POSTGRES_HOST_AUTH_METHOD=scram-sha-256
POSTGRES_INITDB_ARGS=--auth-host=scram-sha-256

note that if you are using volumes to persist PostgreSQL data, you should remove that volume since previous PostgreSQL configs are still there and will overrides your new settings, Do a docker stop on your PostgreSQL container, and start it up again. You should now be able to connect to your local server in the container, just fine.

official postgres docker images docs:

Answer 7

Uninstalling PgAdmin fixed the issue for me.

Answer 8

The Solution:

1. Make sure your PostgreSQL container port is exposed in the Docker Compose file (answered by @ussu)

services:
  db:
    container_name: priority-music-db
    image: postgres
    restart: unless-stopped
    environment:
      - POSTGRES_PASSWORD
      - POSTGRES_USER

    // EXPOSE YOUR PORT HERE
    ports:
      - 5432:5432

    volumes:
      - priority-music-volume:/var/lib/postgresql/data

volumes:
  priority-music-volume:
    name: priority-music-volume

2. When trying to access the database using psql, make sure you running the command in the container!

• Run docker exec -it <container_name> /bin/bash
• Now you are inside the container, use the psql -h localhost -U <postgres_username> command to access your PostgreSQL server

Answer 9

Try psql -h database -U postgres if you're inside a container span up with compose ?
Or if you're running psql from your local machine you'll need to expose the port 5432 of your service with

  ports:
    - 5432:5423

Answer 10

From your docker-compose example it seems that you are not setting the environment variable POSTGRES_PASSWORD to any value. Is that true or you just omitted it for the post?