ERROR: permission denied for table schema_migrations
Hi my applicaiton uses the auth schema as part of our Prisma definition (so that the
users table could be pulled alongside a separate UserProfile table w/ a join clause on the ID).
It looks like a change was made recently w/ Supabase that changed the auth permissions to the schema_migrations table in the auth schema, and is preventing us from pushing any schema changes to our DB even if that particular table/schema is not being affected. Do you guys know of a way to grant all privileges/access again for that table to the postgres role?8 Replies
You cannot modify the auth schema anymore.
We weren't adding any tables/columns or anything, just referencing it as a used schema so that the
auth.users table could be pulled natively from PrismaThis was always a gotch in Prisma in that it would wipe the auth schema also occasionally. One solution I saw is users would just use the auth.uid in their own tables and not actually have a foreign key link to auth.users.
So they'd have two separate DB calls, one through Prisma to get a custom user profile (defined by the user) and then another through the Supabase client? (to get the Supabase user profile)
Sorry, I don't use Prisma, it was just an observation. I assumed they had Supabase auth running and wanted to associate Prisma data with a particular user. Not populate the auth.users table from Prisma.
You can certainly update data in the auth tables (at your own risk), but you cannot change the actual auth tables columns, indexes etc.
And the auth schema changes regularly which was causing drift issues if there was a link to auth as Prisma needed to know about it.
Yeah that makes sense, just trying to see what exactly I can do to address this
Cause it's not really sustainable to have to manually push schema changes, voids the whole point of Prisma lol
https://github.com/orgs/supabase/discussions/34270 was the change that actually locked the schemas.
But I saw Prisma users before that breaking the link almost always to auth.user.id and just using the value without the link. That was because everyonce in awhile Prisma would trash the auth schema when it changed thinking it owned it and needed to put it back. Hopefully that makes sense... not real clear on all the migration stuff on Prisma. Prisma changing the auth schema was one of several things that drove SB to locking it down.
But I saw Prisma users before that breaking the link almost always to auth.user.id and just using the value without the link. That was because everyonce in awhile Prisma would trash the auth schema when it changed thinking it owned it and needed to put it back. Hopefully that makes sense... not real clear on all the migration stuff on Prisma. Prisma changing the auth schema was one of several things that drove SB to locking it down.
Thanks for the link
And yeah the reasoning makes complete sense, I had encountered it a few times myself but can't really expect the average user to know how to reconcile it or to even have to in the first place