#sql
Title
# sql
a
assistattow
08/28/2021, 2:59 AMyes sorry
yes sorry
I think maybe the issue is when I'm making the query it isn't sending the userid to check in the RLS. It's checking if userid is equal to the column in the table which explains why it works without RLS
but with RLS maybe the policy is correct but it's just recieving null so it doesn't work
r
RichCorbs
08/28/2021, 3:02 AMsince there is no await on the supabase.auth.user() call then the supabase client can't send the right userid because it doesn't have it?
a
assistattow
08/28/2021, 3:02 AMfound this code online which is essentially what I have
I might try adding await to that and see but because it works without RLS I dont think thats it
nope that wasn't it
r
RichCorbs
08/28/2021, 3:03 AMwas worth a shot 🙂
b
burggraf
08/28/2021, 3:03 AMI’m not familiar with flutter but once you get your rls working you don’t need to send any parameters. You can just do
.delete()a
assistattow
08/28/2021, 3:03 AMhmm thats really odd that it isn't working once it's enabled then
b
burggraf
08/28/2021, 3:03 AMSo no need to send
.eq()a
assistattow
08/28/2021, 3:04 AMI think in flutter eq is checking the actual column value
I have 7 rows with that value and I only want to delete them
b
burggraf
08/28/2021, 3:04 AMRls policies effectively just get added to the
wherer
RichCorbs
08/28/2021, 3:05 AMso you can do both (eq and RLS) if you want to be more specific than RLS alone?
b
burggraf
08/28/2021, 3:06 AMThe
eqDoesn’t hurt but doesn’t do anything.
a
assistattow
08/28/2021, 3:06 AMwithout the eq how would it know which rows to delete?
I'll try removing it and see
I get this response when RLS is disabled
eq is required I think unless thats just because I had the RLS disabled
it wiped the whole table
b
burggraf
08/28/2021, 3:07 AMEffectively PostgreSQL is running `where field = ‘x’ AND where field = ‘x’
r
RichCorbs
08/28/2021, 3:08 AMI see what you are saying. EQ could be used to check some other column and then RLS would come behind and also check that userid matches (or whatever policy has been set). Does that sound right?
h
HorseShoe
08/28/2021, 3:08 AMOof
b
burggraf
08/28/2021, 3:08 AMRLS policies automatically add the RLS expression to the WHERE clause of the statement being run.
Yes that sounds right.
h
HorseShoe
08/28/2021, 3:08 AMI mean that's not how it works but yea
a
assistattow
08/28/2021, 3:09 AMAlright so I removed the eq and enabled RLS and it still has the same issue
but without the eq it wipes all user data in the table
b
burggraf
08/28/2021, 3:09 AMWhat does your RLS clause look like?
h
HorseShoe
08/28/2021, 3:09 AMRLS is just to check whether to allow that query to be run if RLS is truthy, the query is run RLS won't affect the query
r
RichCorbs
08/28/2021, 3:09 AMdo all the rows in the table match the policy you have defined?
a
assistattow
08/28/2021, 3:09 AMthis is my table. Not the best way of doing things probably
this is the RLS policy
h
HorseShoe
08/28/2021, 3:10 AMAh then nvm
Lmao
a
assistattow
08/28/2021, 3:10 AMthe userid is a foreign key
so its a one to many relationship I from the profile table I believe
b
burggraf
08/28/2021, 3:11 AMDid you try
auth.uid()Not sure if that is necessary though
h
HorseShoe
08/28/2021, 3:11 AMAuth.uid() will get converted to uid()
a
assistattow
08/28/2021, 3:11 AMauth.uid did the same thing
h
HorseShoe
08/28/2021, 3:11 AMAnyway
a
assistattow
08/28/2021, 3:11 AMthis is so confusing hahaha
like all the other policies work and they have the same check in them
r
RichCorbs
08/28/2021, 3:12 AMWithout RLS and with the eq do all the rows get deleted too?
a
assistattow
08/28/2021, 3:12 AMno, only the 7 I want deleted that belong to the user
b
burggraf
08/28/2021, 3:12 AMDo you have any triggers on that table?
a
assistattow
08/28/2021, 3:12 AMI haven't setup any triggers I know of
h
HorseShoe
08/28/2021, 3:12 AMSo the problem is that the delete query deleted rows where the RLS shud have failed?
a
assistattow
08/28/2021, 3:13 AMYes from my understanding. By removing the eq it is just told to delete all rows in that table
with RLS it did nothing without it deleted it all
with the eq it only deletes what I want
h
HorseShoe
08/28/2021, 3:13 AMNo but then the RLS expression shudnt let it
a
assistattow
08/28/2021, 3:13 AMwith it disabled it did
with it enabled it did nothing
h
HorseShoe
08/28/2021, 3:13 AMOh u disabled RLS?
Lmao
a
assistattow
08/28/2021, 3:13 AMbut thats the issue with eq too
yeah haha I tried both
b
burggraf
08/28/2021, 3:14 AMSo effectively your RLS is always returning FALSE
a
assistattow
08/28/2021, 3:14 AMyeah it seems
I always get a blank return of []
with RLS disabled I get an actual return of the data being deleted
h
HorseShoe
08/28/2021, 3:14 AMRLS will return false because u have rows that don't pass the expression right? Try turning rls on and then use eq to make sure the row will be true in rls
b
burggraf
08/28/2021, 3:15 AMYour table name is userworkouts_duplicate?
a
assistattow
08/28/2021, 3:15 AMI duplicated the table
I'm running it on the duplicate so I don't delete my actual user data accidently
I setup the policy for the duplicate
b
burggraf
08/28/2021, 3:15 AMAnd you’re referencing that table from your client application?
a
assistattow
08/28/2021, 3:15 AMyup
I tested it on the duplicate without RLS and it works fine
b
burggraf
08/28/2021, 3:16 AMJust making sure you’re not missing anything small
a
assistattow
08/28/2021, 3:16 AMI dont think thats the issue because it works with update, insert and access
its only delete that has the issue
h
HorseShoe
08/28/2021, 3:16 AMBut just try
Using eq and rls works?
a
assistattow
08/28/2021, 3:16 AMWith RLS on and the policy and using eq it does nothing
When I use RLS it just stops working only for delete, everything else works
h
HorseShoe
08/28/2021, 3:17 AMWait
What operations have u selected in the RLS policy?
a
assistattow
08/28/2021, 3:18 AMon the duplicate I have this
is there more to do for delete?
h
HorseShoe
08/28/2021, 3:18 AMCan u click edit and show?
a
assistattow
08/28/2021, 3:19 AMwhen I click edit I only get this
I used the template for delete
h
HorseShoe
08/28/2021, 3:19 AMOkay so
Try adding select operation to this policy and check with rls on and eq operator
b
burggraf
08/28/2021, 3:20 AMDo you know who the owner of the table is?
a
assistattow
08/28/2021, 3:20 AMI'm the owner of the table
h
HorseShoe
08/28/2021, 3:21 AMCheck deleting not selecting*
a
assistattow
08/28/2021, 3:21 AMjust trying to workout how to do it haha
b
burggraf
08/28/2021, 3:21 AMThose two screen shots say
user_iduserida
assistattow
08/28/2021, 3:22 AMthe seconds the template
the first is my one
my column is userid
b
burggraf
08/28/2021, 3:22 AMJust checking field names
a
assistattow
08/28/2021, 3:23 AMthink I just fixed it
b
burggraf
08/28/2021, 3:23 AMIn that link I sent I just read “ (However, the table's owner is typically not subject to row security policies.) “
What was it?
h
HorseShoe
08/28/2021, 3:23 AMHow
a
assistattow
08/28/2021, 3:23 AMWhen doing the policy I used all. Since I'm using userid to update,insert,delete and access
h
HorseShoe
08/28/2021, 3:24 AMI think select is required
That's why I told
For delete
a
assistattow
08/28/2021, 3:24 AMIf I wasn't to do it through all how would I go about doing that?
it doesn't seem to give the option to do select and delete
h
HorseShoe
08/28/2021, 3:25 AMYeah I think u shud create it twice
Try? Just with select and delete
a
assistattow
08/28/2021, 3:27 AMyep thats it
you need both policies
h
HorseShoe
08/28/2021, 3:27 AMNeat
b
burggraf
08/28/2021, 3:27 AMA select policy must be passed when updating or deleting data unless you pass returning: minimal as a parameter.
r
RichCorbs
08/28/2021, 3:28 AMNice!
b
burggraf
08/28/2021, 3:28 AMI ran into this when I wanted an RLS that only allows users to INSERT (for logging data)
My code kept failing until I added an RLS for SELECT.
h
HorseShoe
08/28/2021, 3:28 AMBro is real-time rls coming? I don't mean soon but will it happen?
b
burggraf
08/28/2021, 3:29 AMThen I read that after updating and deleting data, Postgrest returns the data back so you can confirm it.
So I added the
returningh
HorseShoe
08/28/2021, 3:30 AMOhh
b
burggraf
08/28/2021, 3:31 AMWe are aware of RLS Realtime issue but I don’t know when it’s coming. I would expect it soon though because this comes up a lot but I’m not on that team so I can’t say when.
It’s a priority though
h
HorseShoe
08/28/2021, 3:31 AMAight can't wait!
And functions as well
It wud be more complete with them
Btw where does supabase store it's images?
b
burggraf
08/28/2021, 3:32 AMWhat are your main use cases for functions? I’m doing almost everything with PostgreSQL functions and it’s working great so far.
h
HorseShoe
08/28/2021, 3:32 AMI mean files in storage
b
burggraf
08/28/2021, 3:33 AMAWS buckets
We’ve exhausted this thread 🙂
h
HorseShoe
08/28/2021, 3:33 AMUhh just some api calls I think it shud work fine with postgres but it wud be nice to have functions so that I can use libraries
Or maybe I can just them in postgres as well
b
burggraf
08/28/2021, 3:34 AMI use libraries wirh my SupaScript library
h
HorseShoe
08/28/2021, 3:35 AMThen why Supabase functions? 😂
Let's just talk in general