Error During Migration: must be a member of pg_signal_backend in Supabase with Prisma on Serverless
Hi everyone,
I’m using Supabase as my database and Prisma (version ^5.15.1) as the ORM in a serverless environment. When I try to execute a migration (npx prisma migrate dev), I encounter the following error:
The issue persists and is currently blocking our team from migrating the production schema.
12 Replies
Howdy, friend! I'm the Prisma AI Help Bot — fast answers from me, or slow, hand-crafted wisdom from a dev? Choose wisely, adventurer.
Hey @Manupa Samarawickrama
Were you previously able to execute a migration successfully and now suddenly started getting this error?
Does the database user have sufficient permission to perform migration?
I found this related answer:
https://stackoverflow.com/questions/71269024/would-could-cause-a-postgres-exception-42501-must-be-a-member-of-the-role-whos
Stack Overflow
Would could cause a Postgres exception: 42501: must be a member of ...
I've been running component tests in a .NET 5 application using LightBDD. In each test we bring up an instance of an ASP .NET core "application" that we connect to via grpc. At the end of
@Nurul (Prisma) Yes exactly that's what's happening. It's the same database user I've been using so far. This is a really urgent issue that's blockign my entire org and would really appreciate support
Looks like i can terminate the sessions using the default role
Does the error persist if you try using a different database user?
I havent created any othert users with the admin access, this is the user with the admin access that i got by default from supabase
I followed these instructions to setup. I see now it has been updated but this shoudl work right ? https://web.archive.org/web/20241129133947/https://supabase.com/partners/integrations/prisma
We have been running with the same setup for about a year now
[
{
"file": null,
"host": "db-tsolvadootnjtlmsrvsv",
"metadata": [],
"parsed": [
{
"application_name": "Supavisor",
"backend_type": "client backend",
"command_tag": "DROP DATABASE",
"connection_from": "2406:da1a:6b0:f600:92c2:88be:807f:629b:52372",
"context": null,
"database_name": "postgres",
"detail": null,
"error_severity": "ERROR",
"hint": null,
"internal_query": null,
"internal_query_pos": null,
"leader_pid": null,
"location": null,
"process_id": 131765,
"query": "DROP DATABASE IF EXISTS "prisma_migrate_shadow_db_bc6a14d0-e0cc-444e-8a59-f2ecc536b348" WITH (FORCE)",
"query_id": -2164058870363172000,
"query_pos": null,
"session_id": "6795d434.202b5",
"session_line_num": 5,
"session_start_time": "2025-01-26 06:20:36 UTC",
"sql_state_code": "42501",
"timestamp": "2025-01-26 06:20:39.618 UTC",
"transaction_id": 0,
"user_name": "postgres",
"virtual_transaction_id": "12/5708"
}
],
"parsed_from": null,
"project": null,
"source_type": null
}
]
I was able to observe this error in logs
The same error persists if you kill all database connections and try again, right?
Does
npx prisma migrate deploy command also throw the same error?
I am wondering if something changed recently from Supabase end 🤔Hi @Manupa Samarawickrama did you find a solution to this one. We've also encountered this issue. We have created a new user with the same issue as well as changed the connection strings in our prisma file
yarn prisma migrate deploy works fine, but yarn prisma migrate dev terminates before the migration files are even created locally so there is no deployment to do. It looks like there are some changes to supavisor on Supabase but not sure if related
AHHH fixed it by granting my postgres user with that privilege
-- Grant Prisma the ability to terminate backend processes (fixes migrate dev issue)
GRANT pg_signal_backend TO "prisma";
-- Ensure Prisma has full access to existing objects
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "prisma";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "prisma";
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO "prisma";
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA public TO "prisma";
-- Ensure Prisma has default privileges for any future objects
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES TO "prisma";
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO "prisma";
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON ROUTINES TO "prisma";
Must be some docs I overlooked or something. Anyway, it works.
Yes I was able to "fix" it with the same approach but this lead to it being inconsistent with terminating shadow dbs and it ended up corrupting the schema of my staging db
And I think prisma migrate deploy should be used in hosted environments and migrate dev in local dbs