Docker Compose w/ PostgreSQL - psql Password Authentication failed-CodePudding

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

CodePudding user response：

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?

CodePudding user response：

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