prisma: migrate deploy: Error: P1010: User `user` was denied access on the database `db.public`

For anyone running into this in the future, you have to map the port to another one, as the error (in my case) came from ports colliding.

Final docker-compose.yml:

version: '3.8'
services:
  postgres:
    image: postgres:13
    restart: always
    container_name: my-db
    ports:
      - "5555:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    volumes:
      - my-db:/var/lib/postgresql/data
volumes:
  my-db:

Then connect to it using postgresql://postgres:postgres@localhost:5555/my-db?schema=public

Should work then!

For me it was my other local running instance of postgres. Previously I had installed postgres.app and pgadmin for another project and had it running in the background. So if you’re running postgres inside docker, be sure to quit any other local postgres instances. Soon as I did that the issue went away.

Does npx prisma db pull work?

no it does not, the error I get is the one posted above…

npx prisma db pull
Environment variables loaded from .env
Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:5432"

✖ Introspecting based on datasource defined in prisma/schema.prisma

Error: P1010

User 'postgres' was denied access on the database 'postgres.public'```

it does, and thanks to you I found the issue, since it’s a peer connection, the way the connection works requires an OS user to initiate the connection directly.

updating pg_hba.conf to reflect a localhost connection trusted for the user solved the issue

it was indeed a problem with Postgres and not prisma.

thank you and absolutely in love with the project

keep it up!

it does, and thanks to you I found the issue, since it’s a peer connection, the way the connection works requires an OS user to initiate the connection directly.

updating pg_hba.conf to reflect a localhost connection trusted for the user solved the issue

it was indeed a problem with Postgres and not prisma.

thank you and absolutely in love with the project

keep it up!

Can you elaborate on how exactly have you modified the config file?

For any other like me that stumbled upon this while trying to use a Remix Stack project with a docker-compose using Postgres included, I simply specified a different volume for the postres data:

Example of my complete docker-compose.yml file:

version: "3.7"
services:
  postgres:
    image: postgres:14-alpine
    container_name: remix-blues-stack
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    ports:
      - "5432:5432"
    volumes:
      - remix-blues-stack:/var/lib/postgresql/data
volumes:
  remix-blues-stack:

@lvbn this issue is closed, please open a new issue. Or comment on this open issue that looks related https://github.com/prisma/prisma/issues/21740

I got something similar, but instead of “at localhost:5432” I got “at aws-0-sa-east-1.pooler.supabase.com:5432”.

The full error message looks like this:

`Environment variables loaded from .env Prisma schema loaded from prisma/schema.prisma Datasource “db”: PostgreSQL database “postgres”, schema “public” at “aws-0-sa-east-1.pooler.supabase.com:5432”

Error: P1010: User postgres.xxx was denied access on the database `postgres.public``

This happens when I try to run:

npx prisma migrate dev --name init

I appreciate a lot if anyone could help me out!

As I just spent 3 hours on the same issue, I’m also gonna chip in here.

❗️❗️ Make sure that all other Postgres apps are paused! ❗️❗️

I had PostgresSQL for mac running on localhost which was causing npx prisma db push to connect to it instead of my docker.

it does, and thanks to you I found the issue, since it’s a peer connection, the way the connection works requires an OS user to initiate the connection directly. updating pg_hba.conf to reflect a localhost connection trusted for the user solved the issue it was indeed a problem with Postgres and not prisma. thank you and absolutely in love with the project keep it up!

Can you elaborate on how exactly have you modified the config file?

I changed host all all 127.0.0.1/32 ident to host all all 127.0.0.1/32 trust in pg_hba.conf file