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

Susian

if you run echo $POSTGRES_PASSWORD on your local machine or in the postgres container, is it the correct value? i don't know how your .env file gets written to your actual environment variables – Freshman

@Freshman when I run echo $POSTGRES_PASSWORD, it gave me no output, but I tried running docker compose convert command and it gave me true value that pulled from the .env file. I added it in the question so that you can see. – Susian

i guess you could try exec into the postgres container and use \password postgres (username) to reset the password. and just to make sure, where are you running the psql command from? – Freshman

I'm running the command inside WSL, outside container – Susian

Where are you running psql from? The container you just started should not leave you with a prompt from which you can run it. – Abound

Answer 1

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

Susian

Answer 2

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

Lunette

This worked for me on Windows 11 with latest Docker Desktop and Ubuntu WSL2 image. – Intenerate

Answer 3

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:

Saiff

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)

Desimone

Answer 5

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

Freshman

yeah I did it this way, but it throw me error saying the password is wrong – Susian

still, the result of your docker compose convert doesn't show any port listed, you say you did it "this way", which way is it? – Freshman

Answer 6

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?

Wylen

sorry, I already add my .env to the question, you may check it now – Susian

that is not how you load env vars from a .env file docs.docker.com/compose/environment-variables/#the-env-file – Wylen

I already changed the compose.yaml file into POSTGRES_PASSWORD: ${POSTGRES_PASSWORD} and the issue still persist. – Susian

Answer 7

Uninstalling PgAdmin fixed the issue for me.

Bacteriology

Answer 8

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

Speculation

Answer 9

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.

Geraldine