prisma: Cannot authenticate docker postgres database

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

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 3
Comments: 16 (4 by maintainers)

Most upvoted comments

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

+17

alucardu on Aug 25, 2021

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 https://github.com/prisma/prisma/discussions/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?

+17

janpio on Aug 25, 2021

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.

janpio on Aug 25, 2021

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

ThatRandomGuy09 on Jan 9, 2024

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 😄

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!

TheRealFlyingCoder on Jul 13, 2023

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 😅

TheRealFlyingCoder on Jul 13, 2023

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

TheRealFlyingCoder on Jul 13, 2023

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

Thebluedragon7 on Sep 19, 2022