https://supabase.com/ logo
Join Discord
Powered by
Hey everyone, I'm trying to build an RLS policy th...
# sql
j
Hey everyone, I'm trying to build an RLS policy that checks if a text[] contains a particular value. The text[] is returned from a function and that function grabs it's value from a table:
Copy code
create or replace function public.user_orgs() returns uuid[] as $$
  select array_agg(org_id) from users_orgs where user_id = auth.uid() limit 1;
$$ language sql stable;
And the RLS policy has a 
WITH CHECK
that I'm trying to make work like this:
Copy code
"admin" in (select roles from users_orgs where user_id = auth.uid() and org_id = orgs.id)
If I use admin in 
"
quotes it interprets it as a column name and if I use 
'
quotes it has a malformed array error.
t
single quotes is better though you just have more then 1 issue.
user_orgs
returns type 
uuid[]
how are you magically naming it 
roles
when you do call the function / rpc 
users_orgs.roles
j
@tourdownunder In the RLS WITH CHECK that I posted I'm not using the function to try to reduce the number of things that is going wrong. Note that there's a table named 
users_orgs
and the function is 
user_orgs
so that might be where there's some confusion. The 
roles
column in 
users_orgs
is 
text[]
. Single quotes cause: 
malformed array literal: "admin"
t
Okay. Is there any other arrays in your RLS policy.
j
Not that I'm aware of?
It's just interpreting 
'admin'
as an array and getting upset about it I assume?
t
its text not possible
j
Unless you're referring to the 
roles
which is an array
t
whats the full policy
j
Copy code
CREATE POLICY "Allow org modifications by admins" ON "public"."orgs"
AS PERMISSIVE FOR UPDATE
TO authenticated, service_role
USING ('admin' in (select roles from users_orgs where user_id = auth.uid() and org_id = orgs.id limit 1))
t
You should be able to add the 
'admin'
to within the where statement and remove the in all together.
j
So 
where roles = 'admin'
you mean? and it will automagically look into the array?
t
so roles is an array ?
so you can't use 
in
you need to use an array opperation
though you will likely need to cast 
'admin'
as an array with the only 1 element to have some success.
I think this is what you want here: > anyarray <@ anyarray → boolean > > Is the first array contained by the second? > > ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] → t
j
Awesome, I don't know how I missed this thanks
PreviousNext
Powered by