Hi everyone, I posted this thread https://discord.com/channels/839993398554656828/982328388897366066 on #869405720934744086 but I guess it makes more sense to ask for help here, I'm having issues with my db's roles after using prisma

Hello @Jenaro Calvino! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

Ah ups, i forgot you moved the discussion here 😅 anyway, let's continue the discussion in the previous thread :p

Hey Olyno, I'm sorry I don't mean to bother but do you have any idea as to how I could solve this?

Hey Sorry for being inactive about your issue. Honestly, i don't know how to solve it. I thought it could be because of the selfhost version, but you didn't know what it was. I guess the best way would be to wait that the issue you found is closed. We can also ask help to @garyaustin on this issue, he's probably a better magician than me about postgres stuff 😅

Thanks anyways!

Just to be sure, did you run any migrations from Prisma before interacting with the database?

My guess is that in trying to follow that issue you "wiped" out some critical grants/permissions on the schema or tables. Frankly it could be a mess trying to fix them without fully understanding the grant/permission requirements. It might be as simple as running this:

grant usage                     on schema public to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

from that issue... https://github.com/supabase/supabase/blob/d1d6278913f5a752253a3d47a0d4adabd3bfaf7a/docker/volumes/db/init/00-initial-schema.sql#L37-L43 For the client to work anon, authenticated, service role need access to all public tables. But you may be better off just starting over as you may have long term issues if you can't patch them all back up.

Do you think it can be caused by a Prisma? I experienced some issues with Prisma migrations and Supabase

Prisma is does mess with things, but I've not heard of anyone losing access from the client. It seems like in this case more might have been done to the privledges/grants by the user following that issue. So now two possible changes have occurred.

According to the issue, prisma does reset some permissions when migrating and all

Prisma is mystical to me based on the issues I've seen people having with permissions and migrations. You might ask in that github issue also.

By "starting over" you mean a new supabase project entirely?

Yes, but you could end up right back with the same Prisma issue that you started with.

exactly

Did you run the commands I just listed up above.

yes

Do you have a db tool to look at the tables?

grant delete, insert, references, select, trigger, truncate, update on messages to anon;

grant delete, insert, references, select, trigger, truncate, update on messages to authenticated;

grant delete, insert, references, select, trigger, truncate, update on messages to service_role;

Are the grants on my public tables.

You mean like Beekeeper studio?

Yes, I see in your other thread, and I have the same results as my working table. And that you seem to have the privileges assigned. So another thing to deal with when using Prisma is it uses capital letters. Postgres always defaults everything to lowercase unless in the SQL you do "Testing" with doublequotes. In your query that is failing from the client is your table capitalized? The client should handle that, but you have to have the capital letter.

Yes, my query:

let { data, error, status } = await supabase
         .from("User")
         .select(`*`)
         .eq("id", user.id)
         .single();

Do you have RLS on?

It does have RLS on

Turn it off for a second

Ok

Does it work now?

No, I get the same error message

So your User table has the same result from the select * from information_schema.table_privileges where table_name = 'User' As the one you showed for Testing?

No, the User table shows only 'postgres'

Part of the original file with all the grants, sets up the schema to grant all the privileges to any new table created later.

Could this be related?? https://github.com/supabase/supabase/issues/4883#issuecomment-1017955034

If Prisma created the table and did not assign grants they have to be assigned.

I'm creating a new table and then checking the Grants

I thought this: alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role; Would do that.

It seems it doesn't 😢 the privileges on prisma created tables seem to stay the same

Maybe this: GRANT ALL PRIVILEGES ON "TABLE" TO postgres,anon,authenticated,service_role

That seems to have worked!

That is where RLS comes in

Thanks, really, confusing long thread but thank you! 🙏🏼

I always dread answering a Prisma question.... glad we made progress.