Cannot authenticate docker postgres database

[alucardu]

Bug description

Prisma cannot authenticate with a postgres database inside docker.

How to reproduce

create a yml file:

version: "3"
services:
  postgres:
    container_name: postgres
    image: postgres:13
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: college_db

a .env file:

DATABASE_URL="postgresql://username:mypassword@localhost:5432/college_db?schema=public"

and a schema.prisma file:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

run docker-compose up and then in a different terminal run npx prisma db pull

Returns error:

Error: P1000 Authentication failed against database server at localhost, the provided database credentials for username` are not valid.

Expected behavior

Prisma should be able to authenticate and connect to the server.

Prisma information

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

Environment & setup

• OS: Windows 10
• Database: Postgres
• Node.js version: v12.18.4

Prisma Version

"prisma": "^2.30.0",

I've also read that you need to replace your docker service name (postgres) with the localhost in the url

services:
  postgres:

So the URL would be this: DATABASE_URL="postgresql://username:mypassword@postgres:5432/college_db?schema=public" but this returns the error:

Error: P1001 Can't reach database server at postgres:5432 Please make sure your database server is running at
postgres:5432`.

[janpio]

That the error message for localhost is about the password indicates that there might be another Postgres server running at localhost outside of the Docker container, which Prisma connects to but that does not have the same credentials configured as the Docker container.

From the screenshots in the related discussion #8925 it looks to me that the psql connection is made from the Docker container internally, while the connection from Prisma is attempted from the outside. So possibly the hostname just does not work, or some ports are closed or similar.

@alucardu Can you try to connect to the server from the same location you are trying to use Prisma?

[alucardu]

@janpio thanks a lot! I had installed postgres on my Windows machine as well. So "for some reason" prisma was using that instance of postgres instead of the one I had running docker. I deinstalled postgres from my Windows system and now everything is running as expected.

[janpio]

The reason would probably be that Docker can not make its instance of Postgres available at localhost:5432 when something is already running there.

Great you figured this out and it now works for you.

[Thebluedragon7]

That the error message for localhost is about the password indicates that there might be another Postgres server running at localhost outside of the Docker container, which Prisma connects to but that does not have the same credentials configured as the Docker container.

From the screenshots in the related discussion #8925 it looks to me that the psql connection is made from the Docker container internally, while the connection from Prisma is attempted from the outside. So possibly the hostname just does not work, or some ports are closed or similar.

@alucardu Can you try to connect to the server from the same location you are trying to use Prisma?

I'm having similar issue but I'm not using docker. Instead it's a cloud server and it's mysql

[TheChanDI]

I am having the same issue. I am using it on my MacBook intel chip. I checked my activity controller if any instance of postgres is running and it is not running.

[Hsin1025]

I have the same issue, but I cannot find any postgres running on my machine.(using mac M1)

[casklord]

What I had to do was uninstall postgres and then reinstall it, I think this is because a previous application had set it up and the traffic was diverted to that SQL server, but I didn't have the ability to shut down the other one

[drewhoffer]

I ran into a similar issue when trying out Postgres. I ended up following a guide which set a volume. Upon going into the postgres container I noticed that it was constantly restarting with a message about incorrect data (using v13 is incompatible with v15). Once I deleted the volume and restarted the container I could connect just fine.

I also had to stop my postgres13 instance that was running as Prisma would try to connect to that.

Current docker-compose:

# Use root/example user/password credentials
version: '3.1'


services:

  postgres:
    image: postgres
    restart: always
    hostname: postgres
    container_name: postgres
    environment:
      POSTGRES_DB: example
      POSTGRES_USER: user
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

  adminer:
      image: adminer
      hostname: adminer
      container_name: adminer
      restart: always
      ports:
          - 8080:8080

[TheRealFlyingCoder]

Edit: I'm silly, ignore.

Just adding my issue here as it seems to be relevant.

1. I have a local Docker instance running at localhost:5432

2. I run fly proxy 5435:5432 -a my-hosted-db to get proxy access to the Dev DB on port 5435

3. If I run prisma migrate deploy with an updated DATABASE_URL, on localhost:5435 it will still try to connect to my docker instance on localhost:5432

4. If I compose down my local instance, and re-run the scripts prisma migrate deploy will correctly run on the 5435 port.

This caught me out for AGES because it tells you that it's connecting on the right port, but actually it's looking at the local docker instance.

[janpio]

That sounds like your Docker instance was actually listening at 5435 and that made fly proxy not work as you intended. Prisma really just tries to connect to the port you configure in the connection string, no further logic.

[TheRealFlyingCoder]

That sounds like your Docker instance was actually listening at 5435 and that made fly proxy not work as you intended. Prisma really just tries to connect to the port you configure in the connection string, no further logic.

Yes you are right! I forgot to get back and delete the comment, somehow everything was spun up on the same port, and I'm an idiot 😅

[janpio]

Are you on Mac? I think there that port reuse is actually possible.
On Windows that would probably just error out, because that is... helpful :D

[TheRealFlyingCoder]

Are you on Mac? I think there that port reuse is actually possible. On Windows that would probably just error out, because that is... helpful :D

Nah, i'm, on Windows... to be honest it's a Fly.io bug that you can proxy to an "already in use" port without an error message, that's where the confusion came in!

[ThatRandomGuy09]

@janpio had the same issue, thanks to you, it worked For me

[satishkumarsajjan]

The reason would probably be that Docker can not make its instance of Postgres available at localhost:5432 when something is already running there.

Great you figured this out and it now works for you.

worked for me too.

[Surajjberaa]

Thanks it worked for me 👏🏻