Question

Error connecting to Postgres database, but can connect on pgadmin

Im having this error when trying to connect to the database:

[auth][cause]: error: password authentication failed for user "user"

But i can connect to pgadmin normaly with the same credentials so I dont undertand whats going on.

docker-compose.yaml

services:
  postgres:
    image: postgres:latest
    container_name: postgres
    restart: always
    environment:
      POSTGRES_DB: auth_db
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: user@example.com
      PGADMIN_DEFAULT_PASSWORD: password
    ports:
      - "5050:80"
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    depends_on:
      - postgres

volumes:
  postgres_data:
  pgadmin_data:

.env

AUTH_DATABASE_HOST="localhost"
AUTH_DATABASE_PORT=5432
AUTH_DATABASE_NAME="auth_db"
AUTH_DATABASE_USER="user"
AUTH_DATABASE_PASSWORD="password"

pool:

import { Pool } from "pg";

export const pool = new Pool ({
    host: process.env.AUTH_DATABASE_HOST,
    port: parseInt(process.env.AUTH_DATABASE_PORT!, 10), // 10 TO MAKE IT A BASE10
    database: process.env.AUTH_DATABASE_NAME,
    user: process.env.AUTH_DATABASE_USER,
    password: process.env.AUTH_DATABASE_PASSWORD,
})

I tryed to connect directly to the database and got the same error:

psql -h localhost -U user -d auth_db Password for user user: psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "user"

But for some reason it works with pgadmin.

Im using windows and docker desktop with Hyper-V (no wsl)

idk if it helps but this are the user permissions:

"user" "auth_db" "public" "users" "INSERT" "user" "auth_db" "public" "users" "SELECT" "user" "auth_db" "public" "users" "UPDATE" "user" "auth_db" "public" "users" "DELETE" "user" "auth_db" "public" "users" "TRUNCATE" "user" "auth_db" "public" "users" "REFERENCES" "user" "auth_db" "public" "users" "TRIGGER"

 3  60  3
1 Jan 1970

Solution

 1

Check if you have a running postgres.exe service from installing postgres on your Windows machine directly. This will prevent you from connecting to the postgres from the docker container.

See this stackoverflow question

2024-07-15
oussema

Solution

 0

1.Check Environment Variables: Ensure that your application is correctly loading the environment variables. You can do this by logging the environment variables before creating the Pool: `console.log('Database Configuration:', {

host: process.env.AUTH_DATABASE_HOST,
port: process.env.AUTH_DATABASE_PORT,
database: process.env.AUTH_DATABASE_NAME,
user: process.env.AUTH_DATABASE_USER,
password: process.env.AUTH_DATABASE_PASSWORD,

});`

2.Check User and Password: Make sure that the user and password in your .env file match those used to create the PostgreSQL user.

3.Database Initialization: Sometimes, the PostgreSQL container might not initialize the user correctly. You can try connecting to the PostgreSQL container and manually checking the user credentials

4.Docker Networking: Ensure that your application is using the correct hostname. Since your containers are running in a Docker network, try using the service name postgres as the host instead of localhost

5.Check pg_hba.conf: The PostgreSQL pg_hba.conf file controls client authentication. You can access it inside the container and ensure it allows connections from your application:

docker exec -it postgres cat /var/lib/postgresql/data/pg_hba.conf

Ensure there is an entry like:

host    all             all             0.0.0.0/0            md5

6.Docker Logs: Check the logs of the PostgreSQL container for any hints

2024-07-14
RaDmunn