Replies: 3 comments 2 replies
Why This Happens (PostgreSQL Privilege Mechanics on Hosted Supabase)Your observation is completely accurate. In PostgreSQL, a role cannot execute
On hosted Supabase:
When you execute Supabase Support will not manually alter privileges on internal system schemas ( Supported Solutions & Best PracticesHere are the three customer-accessible ways to resolve this while preserving role separation and Prisma migration stability: Solution 1: Role Membership & Context Switching (Least-Privilege Migration Execution)If your architecture requires
Because Solution 2: Decouple Domain Tables via a
|
|
The FK can be created once from -- as postgres (SQL editor or psql), Postgres 16+
GRANT app_migrator TO postgres WITH INHERIT TRUE;
ALTER TABLE app.profile
ADD CONSTRAINT profile_id_fkey
FOREIGN KEY (id) REFERENCES auth.users (id) ON DELETE CASCADE;
REVOKE app_migrator FROM postgres GRANTED BY postgres;Owner rights are inherited through membership (Postgres docs, Privileges: that right "can be inherited by members of the owning role"), and adding an FK needs REFERENCES on the referenced table only. The table and the constraint stay owned by For Prisma, keep the migration file with that ALTER TABLE and record it with On Support: the Not run against a hosted project; the rules are from the Postgres 17 docs.
|
|
I found a relevant Supabase Knowledge Base article published on November 4, 2026: "Permission errors when granting custom roles access to the auth schema." It explains an important limitation of hosted Supabase: For this specific case, I would distinguish between two requirements:
I would not recommend granting membership in One important question remains for Supabase Support: Is there an officially supported, customer-accessible procedure for a migration that must create a foreign key directly to I would also appreciate clarification on whether this limitation is intentional for all hosted projects or whether Support can provision the required privilege safely. References:
Thanks for clarifying! |
Uh oh!
There was an error while loading. Please reload this page.
Summary: On hosted Supabase, a dedicated Prisma Migrate role owns our application schema but cannot reference
auth.users(id)from an application profile table.Impact: A development authentication migration is blocked; this is not a production outage.
Expected: The migration role needs only USAGE on
authand REFERENCES onauth.users(id). The API role should retain no access to Auth tables.Observed: The available administrative role is
postgres. It has USAGE and REFERENCES but no grant option for these privileges, is not a member ofsupabase_auth_admin, and does not ownauth.users. After attempting the grants, explicit privilege checks still report false for the migration role. The migration has not been applied.Minimal grants attempted (role name replaced for this public example):
The application table is owned by the migration role;
postgresdoes not inherit that owner's permissions or have TRIGGER permission on that table. We have not changed ownership, edited migration history, granted privileged role membership, or created SECURITY DEFINER functions.Environment: Hosted Supabase development project, PostgreSQL, Prisma 7.10.1, separate API and migration credentials. Verified on November 4, 2026.
Question: What is the supported least-privilege procedure for creating this foreign key with a dedicated migration role? Does granting these privileges require Supabase Support, or is there a documented customer-accessible approach that preserves role separation and Prisma migration history?
All reactions