https://supabase.com/ logo
Join Discord
Powered by
I am trying to create a policy where if the user h...
# help
r
I am trying to create a policy where if the user has the role of "administrator" it allows it to do CRUD. I have a 
profile_roles
table with 
id
referred to 
user.id
from supabase and it also has 
role
which is my own custom values. so a policy for a different table I tried doing:
Copy code
sql
SELECT CASE WHEN EXISTS (
SELECT id, role 
  FROM public.profile_roles 
    WHERE id = auth.uid() 
       AND role = "administrator"
) 
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
basically what I am trying to do is select from 
profile_roles
and see if the 
auth.id
from supabase and 
role
is of admin. if so, pass, if not, fail. however I get an error message saying 
policy syntax error on/near SELECT
any help is appreciated, thanks!
Copy code
sql
CREATE policy "Enable access to all users" 
ON PUBLIC.profiles FOR ALL using 
  (SELECT CASE WHEN EXISTS 
    ( SELECT id, role 
      FROM PUBLIC.profile_roles 
        WHERE id = auth.uid() AND role = "administrator" ) 
     THEN cast(1 AS bit) 
    ELSE cast(0 AS bit) 
  END);

WITH CHECK 
  (SELECT CASE WHEN EXISTS 
    ( SELECT id, role 
      FROM PUBLIC.profile_roles 
        WHERE id = auth.uid() AND role = "administrator" ) 
     THEN cast(1 AS bit) 
    ELSE cast(0 AS bit) 
  END);
Copy code
sql
( SELECT
        CASE
            WHEN (EXISTS ( SELECT profile_roles.id,
                profile_roles.role
               FROM profile_roles
              WHERE ((profile_roles.id = uid()) AND ((profile_roles.role)::text = 'administrator'::text)))) THEN true
            ELSE false
        END AS "case")
using this doesn't give me syntax error anymore but it fails to pass my test cases
s
You should be able to do a simpler query like
Copy code
sql
SELECT EXISTS(
    SELECT 1
    FROM profile_roles pr
    WHERE pr.id = uid() AND pr.role = 'administrator'
)
r
works perfectly, thank you so much
PreviousNext
Powered by