https://supabase.com/ logo
#sql
Title
# sql
a

assistattow

08/28/2021, 2:59 AM
yes 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 AM
since 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 AM
found 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 AM
was worth a shot 🙂
b

burggraf

08/28/2021, 3:03 AM
I’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()
and it’ll only delete records that match the rls.
a

assistattow

08/28/2021, 3:03 AM
hmm thats really odd that it isn't working once it's enabled then
b

burggraf

08/28/2021, 3:03 AM
So no need to send
.eq()
a

assistattow

08/28/2021, 3:04 AM
I 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 AM
Rls policies effectively just get added to the
where
clause.
r

RichCorbs

08/28/2021, 3:05 AM
so you can do both (eq and RLS) if you want to be more specific than RLS alone?
b

burggraf

08/28/2021, 3:06 AM
The
eq
is redundant
Doesn’t hurt but doesn’t do anything.
a

assistattow

08/28/2021, 3:06 AM
without 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 AM
Effectively PostgreSQL is running `where field = ‘x’ AND where field = ‘x’
r

RichCorbs

08/28/2021, 3:08 AM
I 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 AM
Oof
b

burggraf

08/28/2021, 3:08 AM
RLS 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 AM
I mean that's not how it works but yea
a

assistattow

08/28/2021, 3:09 AM
Alright 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 AM
What does your RLS clause look like?
h

HorseShoe

08/28/2021, 3:09 AM
RLS 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 AM
do all the rows in the table match the policy you have defined?
a

assistattow

08/28/2021, 3:09 AM
this is my table. Not the best way of doing things probably
this is the RLS policy
h

HorseShoe

08/28/2021, 3:10 AM
Ah then nvm
Lmao
a

assistattow

08/28/2021, 3:10 AM
the 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 AM
Did you try
auth.uid()
?
Not sure if that is necessary though
h

HorseShoe

08/28/2021, 3:11 AM
Auth.uid() will get converted to uid()
a

assistattow

08/28/2021, 3:11 AM
auth.uid did the same thing
h

HorseShoe

08/28/2021, 3:11 AM
Anyway
a

assistattow

08/28/2021, 3:11 AM
this 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 AM
Without RLS and with the eq do all the rows get deleted too?
a

assistattow

08/28/2021, 3:12 AM
no, only the 7 I want deleted that belong to the user
b

burggraf

08/28/2021, 3:12 AM
Do you have any triggers on that table?
a

assistattow

08/28/2021, 3:12 AM
I haven't setup any triggers I know of
h

HorseShoe

08/28/2021, 3:12 AM
So the problem is that the delete query deleted rows where the RLS shud have failed?
a

assistattow

08/28/2021, 3:13 AM
Yes 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 AM
No but then the RLS expression shudnt let it
a

assistattow

08/28/2021, 3:13 AM
with it disabled it did
with it enabled it did nothing
h

HorseShoe

08/28/2021, 3:13 AM
Oh u disabled RLS?
Lmao
a

assistattow

08/28/2021, 3:13 AM
but thats the issue with eq too
yeah haha I tried both
b

burggraf

08/28/2021, 3:14 AM
So effectively your RLS is always returning FALSE
a

assistattow

08/28/2021, 3:14 AM
yeah 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 AM
RLS 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 AM
Your table name is userworkouts_duplicate?
a

assistattow

08/28/2021, 3:15 AM
I 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 AM
And you’re referencing that table from your client application?
a

assistattow

08/28/2021, 3:15 AM
yup
I tested it on the duplicate without RLS and it works fine
b

burggraf

08/28/2021, 3:16 AM
Just making sure you’re not missing anything small
a

assistattow

08/28/2021, 3:16 AM
I 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 AM
But just try
Using eq and rls works?
a

assistattow

08/28/2021, 3:16 AM
With 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 AM
Wait
What operations have u selected in the RLS policy?
a

assistattow

08/28/2021, 3:18 AM
on the duplicate I have this
is there more to do for delete?
h

HorseShoe

08/28/2021, 3:18 AM
Can u click edit and show?
a

assistattow

08/28/2021, 3:19 AM
when I click edit I only get this
I used the template for delete
h

HorseShoe

08/28/2021, 3:19 AM
Okay so
Try adding select operation to this policy and check with rls on and eq operator
Do you know who the owner of the table is?
a

assistattow

08/28/2021, 3:20 AM
I'm the owner of the table
h

HorseShoe

08/28/2021, 3:21 AM
Check deleting not selecting*
a

assistattow

08/28/2021, 3:21 AM
just trying to workout how to do it haha
b

burggraf

08/28/2021, 3:21 AM
Those two screen shots say
user_id
and
userid
a

assistattow

08/28/2021, 3:22 AM
the seconds the template
the first is my one
my column is userid
b

burggraf

08/28/2021, 3:22 AM
Just checking field names
a

assistattow

08/28/2021, 3:23 AM
think I just fixed it
b

burggraf

08/28/2021, 3:23 AM
In 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 AM
How
a

assistattow

08/28/2021, 3:23 AM
When doing the policy I used all. Since I'm using userid to update,insert,delete and access
h

HorseShoe

08/28/2021, 3:24 AM
I think select is required
That's why I told
For delete
a

assistattow

08/28/2021, 3:24 AM
If 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 AM
Yeah I think u shud create it twice
Try? Just with select and delete
a

assistattow

08/28/2021, 3:27 AM
yep thats it
you need both policies
h

HorseShoe

08/28/2021, 3:27 AM
Neat
b

burggraf

08/28/2021, 3:27 AM
A 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 AM
Nice!
b

burggraf

08/28/2021, 3:28 AM
I 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 AM
Bro is real-time rls coming? I don't mean soon but will it happen?
b

burggraf

08/28/2021, 3:29 AM
Then I read that after updating and deleting data, Postgrest returns the data back so you can confirm it.
So I added the
returning
parameter and it works without the SELECT policy.
h

HorseShoe

08/28/2021, 3:30 AM
Ohh
b

burggraf

08/28/2021, 3:31 AM
We 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 AM
Aight 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 AM
What 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 AM
I mean files in storage
b

burggraf

08/28/2021, 3:33 AM
AWS buckets
We’ve exhausted this thread 🙂
h

HorseShoe

08/28/2021, 3:33 AM
Uhh 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 AM
I use libraries wirh my SupaScript library
h

HorseShoe

08/28/2021, 3:35 AM
Then why Supabase functions? 😂
Let's just talk in general