prisma: `db pull` can't connect to supabase when using "[...].pooler.supabase.com:5432"

Bug description

I’ve been trying to connect Prisma with Supabase for a while now but I keep getting an error whenever I try to run these commands: npx prisma db pull npx prisma migrate dev --name init

this is the error: User postgres.[id] was denied access on the database postgres.public

Database Url: DATABASE_URL="postgres://postgres[.id]:[password]@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

I’ve been tinkering with the database Url to try to find a way through this error by using a username in the database url but I get this error when i run npx prisma db pull :

✖ Introspecting based on datasource defined in prisma\schema.prisma
 Error: FATAL: Tenant not found

How to reproduce

I have tried deleting the database, granting the user connect and usage privileges but I keep getting the same error.

Expected behavior

No response

Prisma information

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

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

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

Environment & setup

OS: Windows 10 Pro
Database: PostgreSQL
Node.js version: v20.7.0

Prisma Version

Environment variables loaded from .env
prisma                  : 5.5.2
@prisma/client          : 5.5.2
Current platform        : windows
Query Engine (Node-API) : libquery-engine aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Schema Engine           : schema-engine-cli aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a (at node_modules\@prisma\engines\schema-engine-windows.exe)
Schema Wasm             : @prisma/prisma-schema-wasm 5.5.1-1.aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Default Engines Hash    : aebc046ce8b88ebbcb45efe31cbe7d06fd6abc0a
Studio                  : 0.494.0

About this issue

Original URL
State: closed
Created 8 months ago
Comments: 21 (9 by maintainers)

Most upvoted comments

I was getting a similar error

Error querying the database: FATAL: Tenant or user not found

I was using Supabase as my provider and I found the issue to be with the connection URL that Supabase provides.

On https://supabase.com/dashboard/project/[your-supabase-project], Supabase has a Connect button that opens a modal which gives you info on how to connect to your Supabase. Under ORM tab in that modal, the value of DATABASE_URL is incorrect. Specifically the AWS region. Whatever region you chose when you created the project, the region in DATABASE_URL is always aws-0-eu-central-1 which is incorrect. I changed this region to the region I was using and stopped getting this error.

notadilnaqvi on Feb 24, 2024

I was getting a similar error

Error querying the database: FATAL: Tenant or user not found

I was using Supabase as my provider and I found the issue to be with the connection URL that Supabase provides.

On https://supabase.com/dashboard/project/[your-supabase-project], Supabase has a Connect button that opens a modal which gives you info on how to connect to your Supabase. Under ORM tab in that modal, the value of DATABASE_URL is incorrect. Specifically the AWS region. Whatever region you chose when you created the project, the region in DATABASE_URL is always aws-0-eu-central-1 which is incorrect. I changed this region to the region I was using and stopped getting this error.

This was really helpful 👍🏾 in addition to this, I had to replace the DIRECT_URL referenced in the ORMs tab:

with the one referenced in the Connection String tab:

Now everything is working ✅

abshirahmed on Feb 25, 2024

@RoyJumah I thinkUser postgres.[id] was denied access on the database postgres.public might be a new error message, though it’s actually expected that Migrate and Introspection have issues with the pooled connection string in general.

You must use a direct connection string for Introspection & Migrate. Example

# Connect to Supabase with PgBouncer.
DATABASE_URL="postgres://postgres.__YOUR_SUPABASE_PROJECT__:__PASSWORD__@aws-0-eu-central-1.pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection to the database. Used for migrations.
DIRECT_URL="postgres://postgres:__PASSWORD__@db.__YOUR SUPABASE_PROJECT__.supabase.co:5432/postgres"

See https://www.prisma.io/docs/guides/database/supabase#specific-considerations

Could you try that and let us know how it goes?

Jolg42 on Nov 2, 2023

These are the connections string that I’m currently using
DIRECT_URL="postgres://postgres:[password]@db.aigdooxkrussptkeikqq.supabase.co:5432/postgres"
DATABASE_URL="postgres://postgres:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres"
I’m able to run npx prisma db pull and npx prisma migrate dev --name init without any errors but when I try to seed that is when the error pops up.

I’m experiencing the same issue, even with the additional pgbouncer=true field. I was able to run migrate the first time, but I get the following error for all subsequent requests to the db

Error: P1001: Can't reach database server at `db.xxxxxxxxxxx.supabase.co`:`5432`
Please make sure your database server is running at `db.xxxxxxxxxxx.supabase.co`:`5432`.

abshirahmed on Feb 18, 2024

Adding the PROJECT_ID

What do you mean by that?

janpio on Nov 2, 2023

Note I tried to reproduce to see the error message

npx prisma@latest db pull --url="postgres://postgres.imfuuhzoslsteprqxzdj:PASSWORD@aws-0-eu-central-1.pooler.supabase.com:6543/postgres"
✖ Introspecting
Error: ERROR: prepared statement "s0" does not exist

npx prisma@latest db pull --url="postgres://postgres.imfuuhzoslsteprqxzdj:PASSWORD@aws-0-eu-central-1.pooler.supabase.com:5432/postgres"
✖ Introspecting
Error: P1010
User `postgres.imfuuhzoslsteprqxzdj` was denied access on the database `postgres.public`

I could reproduce by changing the port, the default port for the pooler is 6543. It’s also the same value in this project on Supabase. When changing it to 5432 in the connection string only, then the error message is the same as this issue.

Jolg42 on Nov 2, 2023

Thanks, @Jolg42 .This fixed it 😀. I will take my time to go through the documentation.

RoyJumah on Nov 2, 2023