https://supabase.com/ logo
#help
Title
# help
j

Jenaro Calvino

06/06/2022, 7:24 PM
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
n

Needle

06/06/2022, 7:24 PM
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.
🆕 Prisma and Postgres usersroles
o

Olyno

06/07/2022, 8:53 AM
Ah ups, i forgot you moved the discussion here 😅 anyway, let's continue the discussion in the previous thread :p
j

Jenaro Calvino

06/08/2022, 1:58 PM
Hey Olyno, I'm sorry I don't mean to bother but do you have any idea as to how I could solve this?
o

Olyno

06/08/2022, 2:32 PM
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 😅
j

Jenaro Calvino

06/08/2022, 2:32 PM
Thanks anyways!
o

Olyno

06/08/2022, 2:34 PM
Just to be sure, did you run any migrations from Prisma before interacting with the database?
g

garyaustin

06/08/2022, 2:39 PM
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:
Copy code
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.
o

Olyno

06/08/2022, 2:49 PM
Do you think it can be caused by a Prisma? I experienced some issues with Prisma migrations and Supabase
g

garyaustin

06/08/2022, 2:54 PM
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.
j

Jenaro Calvino

06/08/2022, 2:54 PM
According to the issue, prisma does reset some permissions when migrating and all
Before running the commands on the issue, I did not have access to the schema, so even less access
g

garyaustin

06/08/2022, 2:56 PM
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.
j

Jenaro Calvino

06/08/2022, 2:56 PM
By "starting over" you mean a new supabase project entirely?
g

garyaustin

06/08/2022, 2:58 PM
Yes, but you could end up right back with the same Prisma issue that you started with.
j

Jenaro Calvino

06/08/2022, 2:58 PM
exactly
It just seems that even after successfully running the commands to add privileges to anon, the tables already created are not affected
I still only see the "postgres" grantee
g

garyaustin

06/08/2022, 3:00 PM
Did you run the commands I just listed up above.
j

Jenaro Calvino

06/08/2022, 3:32 PM
yes
g

garyaustin

06/08/2022, 3:37 PM
Do you have a db tool to look at the tables?
Copy code
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.
j

Jenaro Calvino

06/08/2022, 6:35 PM
You mean like Beekeeper studio?
I've been running this
Copy code
select *
from information_schema.table_privileges
where table_name = 'User'
g

garyaustin

06/08/2022, 6:51 PM
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.
j

Jenaro Calvino

06/08/2022, 7:16 PM
Yes, my query:
Copy code
let { data, error, status } = await supabase
         .from("User")
         .select(`*`)
         .eq("id", user.id)
         .single();
It seems that even after running those grant statements, it doesn't grant access to those tables
https://stackoverflow.com/questions/67551593/supabase-client-permission-denied-for-schema-public This is what I followed to get access to the schema, but didn't work for tables
g

garyaustin

06/08/2022, 7:22 PM
Do you have RLS on?
It looks like your User table does have grants on, at least it matches my table when I used the sql to query the grants. I think if there is a row in that table it has a grant.
j

Jenaro Calvino

06/08/2022, 7:26 PM
It does have RLS on
g

garyaustin

06/08/2022, 7:27 PM
Turn it off for a second
j

Jenaro Calvino

06/08/2022, 7:27 PM
Ok
Done
g

garyaustin

06/08/2022, 7:29 PM
Does it work now?
j

Jenaro Calvino

06/08/2022, 7:37 PM
No, I get the same error message
Copy code
Object {
  "code": "42501",
  "details": null,
  "hint": null,
  "message": "permission denied for table User",
}
g

garyaustin

06/08/2022, 7:43 PM
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?
j

Jenaro Calvino

06/08/2022, 7:45 PM
No, the User table shows only 'postgres'
Since these tables where created by prisma, could it be that Supabase does anything special when creating tables from the UI that where overlooked here?
g

garyaustin

06/08/2022, 7:48 PM
Part of the original file with all the grants, sets up the schema to grant all the privileges to any new table created later.
j

Jenaro Calvino

06/08/2022, 7:48 PM
g

garyaustin

06/08/2022, 7:48 PM
If Prisma created the table and did not assign grants they have to be assigned.
That issue is similar in that the grants did not get assigned.
j

Jenaro Calvino

06/08/2022, 7:53 PM
I'm creating a new table and then checking the Grants
When creating this new table, I see all the grantee there, unlike the tables created by Prisma, is there maybe a manual way to add these grantee for each table?
g

garyaustin

06/08/2022, 7:55 PM
I thought this: alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role; Would do that.
j

Jenaro Calvino

06/08/2022, 7:56 PM
It seems it doesn't 😢 the privileges on prisma created tables seem to stay the same
g

garyaustin

06/08/2022, 7:57 PM
Maybe this: GRANT ALL PRIVILEGES ON "TABLE" TO postgres,anon,authenticated,service_role
Where "TABLE" is your name.
j

Jenaro Calvino

06/08/2022, 7:58 PM
That seems to have worked!
It worked! Thanks, so, I wonder, with these new privileges does it mean that someone with the "anon" role can edit my table?
g

garyaustin

06/08/2022, 8:01 PM
That is where RLS comes in
Or you can revoke the grants for anon and just add select.
j

Jenaro Calvino

06/08/2022, 8:02 PM
Thanks, really, confusing long thread but thank you! 🙏🏼
g

garyaustin

06/08/2022, 8:03 PM
I always dread answering a Prisma question.... glad we made progress.