Permission denied for table but RLS is disabled?
All of my other tables are working just fine, but for some reason this new table I created isn't. Had RLS enabled and couldn't figure out why it was throwing errors, disabled it for a sanity check and it's still throwing permission error... Help!
Not sure what bit of code is relevant to show here, but please let me know
90 Replies
Please show the error / code / table structure
Code:
Table structure:
hm, that's weird. It looks normal
If you create a new table, do u still have this issue?
Is this local development?
I'll give it a shot - one sec
And no the db is on supabase
Okay so I made a new table, just using the supabase UI and it works fine....
I'm noticing you have alot of camelcase letters in your columns. That is not a good idea unless you are forced to by Prisma. https://discord.com/channels/839993398554656828/885237287280070708/988097402059780098
oh really? I've just been camelcasing everything because I'm using js
i see i see
It will bite you at some point. The API does handle it, but if you write functions or RLS policies you have to double quote everything.
I wonder if somewhere in there is where my issues arose
The table that wasn't working was generated via prisma schema
Your table permission thing is a bit odd. Did you create it any differently than others.
LOL. YEs
Yes, Prisma you have to do a bunch of grant fixes.
But all of my other tables are also generated the same way -- so i'm not really sure
but considering the one i made with the UI works fine i'm guessing that's the issue
Has to do with migrations or some such you do on Prisma. I don't use it.
how do you manage your schemas?
I don't know anything about Prisma.
sure, but in general
do you just keep the plain sql for migrations?
I have an IDE that handles what I do. Supabase has migration support with the CLI tool. That might be a good question to ask if you don't find it quickly in the docs.
That might be a good question to ask if you don't find it quickly in the docs.
what do you mean
do you use datagrip or something?I do as part of PHPStorm
hmm okay i see
I write all mi migrations and
ley to run themanyways thanks for pointing me in the right direction -- I don't use prisma besides just for managing my schema/migrations
oh
I would say a better tool for managing your migrations is knex, it won't do crazy stuff like Prisma does
I'm using Knex in this repo https://github.com/silentworks/appsmith-supabase-auth
GitHub
GitHub - silentworks/appsmith-supabase-auth
Contribute to silentworks/appsmith-supabase-auth development by creating an account on GitHub.
Knex is great because you also get seeding as part of its tooling, so you can get test data into your database quickly.
Cool I'll look into that thank you!
I saw your waitlist sveltekit demo app a while back and started using
ley because of it haha, very nice to handle rls policies and indexes
I'm just not very good at sql so anything that lets me not have to mess around with sql every time i need to make changes to tables is awesome to meAfter reading how silentworks keep proposing knex makes me want to try it, sadly i've become stuck with prisma for now
I really need to update that repo to the latest SvelteKit and Auth Helpers
Same here, definitely going to check it out
Prisma works for a lot of people, but I'm not used to a migration tool blowing away my entire database just to create it again. I've never seen a migration tool that works like that in all my years of coding.
In my case, I don't have your issue with prisma. Granted: i don't use camelCase, I
@map all names to snake case after reading gary mention that issue many times.
I'd recommend running that Grant script once thenGary is a wise man, so his advice is very much golden.
sure, if you ignore the "you'll need to reset your db to apply changes" warning messages you're in for a rude surprise but it's possible to say no and make minor changes like adding default values or whatever before migrating
It's a weird tool sometimes. I set up mine to re-apply the grant after each prisma migration during development. However, production works fine
can you share a link for this? definitely need to get on that
The Grant script is here
oh oh the resetting of privs and such
I think @sebx is after how you do the mapping to snake_case
Prisma
Prisma schema API (Reference)
API reference documentation for the Prisma Schema Language (PSL).
Sorry was trying to get that link
Well at least he did not say "wise old man"...😎
How can you be old at 25 Gary?
LOL
I did try to run the grant script before posting here and got an error relating to the "supabase_admin" role
"supabase_admin" is a reserved role, only superusers can modify itWhere did you try and run it from?
the UI
Is it due to the migration from
supabase_admin to postgres?
Normally, I just ignore it when I apply my grants on Supabase db. (It doesn't affect anything for me)You can tell if the new migration has occurred by doing "select user" in the SQL editor.
is "postgres_temporary_object_holder" the correct response to that query
Well I think that means you are in the middle of the process. Maybe ran the scripts.
same outcome
It's a sign. Ok I'm leaving now.
hahah oh well
thanks for butting in @silentworks
This is an interesting error. Could you also list: prisma version, supabase version, whether this is production db or testing db
I should write that I'm joking here in case someone else comes in the chat doesn't realise it. @NanoBit and @garyaustin knows the Prisma issues better than me so best they handle it.
prisma version 4.3.1, production db
not sure about supabase version, but it's the one running on the platform
using supabase-js@2.0.5 though
I believe the issue is you are in the middle of the conversion from supabase_admin to Postgres as the UI user. You need to temp modify that Prisma process to add your ui user you showed.
hmm not sure what you mean by modifying prisma process
The grant script does not have postgres_temporary_object_holder in the assignments at the end.
is there a grant in the script that should be modified?
While I cannot comment on the grant script and modifying it (as I have not migrated the UI user yet), would you be able to test if fixing the column names help fix RLS issue? or is this purely a GRANT issue?
I'm just guessing at the grant part, that should only affect modifying the tables from the UI as currently your grants do not include postgres_temporary_object_holder as having permission on the tables.
In almost all of the lines there is assignment to
In almost all of the lines there is assignment to
postgres, anon, authenticated, service_role; that is missing postgres_temporary_object_holder
I suspect for the moment postgres_temporary_object_holder has to be added to all of those.
This is all based on this: https://github.com/supabase/supabase/discussions/9314
I suspect they did not think about this Prisma script in interim period. BUT I'm not really that up to speed on this middle time period.GitHub
Security Patch Notice · Discussion #9314 · supabase/supabase
Security Patch Notice To better secure your Supabase server instances, we will be removing superuser access from the dashboard SQL Editor over the next 30 days. Existing projects with tables, funct...
I've asked in that discussion about this case.
I suspect they did not think about this Prisma script in interim period. BUT I'm not really that up to speed on this middle time period.This ... could also be bad for me
Have you run the script they provided for the admin user change?
I have not..
For any projects not migrated after 5 Nov deadline, we will run the migration on your behalf to reassign all entities to postgres role. No temporary role can be used for rollback.I saw this and thought that it would be fine.. Whoops, sorry for hijacking the thread. I'll test things out, but everything seems fine.
sebx has run the admin user change script so is in the middle state until Supabase does what ever they plan to do.
Gonna have to pick this up in the morning guys, I’ll run some tests based on your messages and get back to you 👍
https://github.com/supabase/supabase/discussions/9314#discussioncomment-4082332
Seems like the script was OK just generating false supabase_admin errors as the temp user has postgres user privileges.
GitHub
Security Patch Notice · Discussion #9314 · supabase/supabase
Security Patch Notice To better secure your Supabase server instances, we will be removing superuser access from the dashboard SQL Editor over the next 30 days. Existing projects with tables, funct...
So it would seem that the issues might be relating to a mismatch of permissions applied to supabase created tables and prisma created ones.
New prisma created tables aren't working properly with supabase permissions AND same applies to the other way around: prisma doesn't have persmission to modify tables newly created via supabase ui
Verified by duplicating the prisma generated table on supabase and being able to access it without any issues using the js-client
Then in prisma changing the
@map() to the duplicated table (invites_duplicate) and running prisma db push
Would creating everything again via prisma (or your ORM of choice) work?
To fix the permissions, right?
I'm not sure i trust it to fix itself honestly 😅
I see. Well, I might upgrade my supabase later for the new migration and update you if I have any similar issues. However, all of my table is created via prisma(postgres role), so I prob won't have your issue..
Should I just wait until the migration has been applied for my account then?
If there is no rush, that can be done. Or spin up another test db or work on local db for now
I think it's having more impacts on things than i realized... Looks like joining related data isn't giving me the results it usually does
Joins don't work on the client (RLS disabled), unless using service key
This works in supabase SQL editor, no problem
since prisma created tables are where the issues seem to stem from, I don't know if I trust resetting the db via prisma..
Is there another way to reset the permissions?
Uhm,is there a reason you're using supabaseClient if you have prisma?
Could we backtrack a bit, and may I ask why are you using Prisma? Is it just for migration?
literally just for migrations and handling relations etc. Easier learning curve than sql lmao
Prisma client queries take several seconds compared to supabase client, so i switched 🤷♂️
Does Prisma work client side too? or is it server-side only?
Prisma client queries take several seconds compared to supabase clientHm, is there something wrong? Locally, prisma queries on API route take 50-100ms+ depending on calculations I think.
literally just for migrations and handling relations etc.I'm not sure Prisma Migration is that good yet alone.. It'll be good when you can utilize the whole Prisma ecosystem. Server only.. (need nodejs runtime)
Yea I'm not running a local db — what kinds of query times are you seeing for production / non-local requests with prisma?
Without cold start and cache, around 400ms to 2k ms. Depends also on Vercel.
I’m not sure it’s due to prisma or other factors like being serverless.
Hmm okay, strange
Anyways i’m not really sure what to do… i tried finalizing the super admin migration and didn’t change anything…
I need to make changes to my production db… so this is pretty concerning
How long did you say your requests took? What is several seconds?
Would it be possible to benchmark on a local db as well to make sure it’s not prisma’s fault?
I still use prisma for some more complex queries that I couldn't replicate with the supabase client -- so here's a log from production:
[LONG QUERY - 2498ms] Subscription.findMany Typically it's around 2-4seconds
Note: Although I only use it for complex queries, simple select queries took just as long...
Sure perhaps after I get this whole permissions mess figured out -- feel like I've been locked out of my database at the moment2498 ms from your browser or from Vercel’s api route duration?
With cold boot or without?
From a docker instance running on aws
not sure what would categorize it as a cold boot since it's just a few prisma queries that run, but doesn't seem to matter -- it was happening with every query even back to back
Here's another one -- same query took 3958ms
Oh I thought you were running on Vercel. This is not serverless then right
If you’re not using serverless, I’m not sure if you should use pgbouncer(?). Wouldn’t a direct connection be cheaper