S
Supabase•3y ago
archer

Help with SQL conditional statements inside Supabase policy.

I have a table called "project" with a row called "visibility" which contains a string. How can I use a conditional statement to check for the visibility of the project being equal to "public" in order to allow for anonymous select access to the table? thank you!
4 Replies
garyaustin
garyaustin•3y ago
https://supabase.com/docs/guides/auth/row-level-security Policies are just Postgres SQL where statements. You can do selects and joins and also call functions as you need.
Supabase Documentation
Row Level Security
Secure your data using Postgres Row Level Security.
archer
archerOP•3y ago
would this be correct? how can i reference the visibility table from within the policy?
No description
archer
archerOP•3y ago
and I have a Tile table which refers to a Page which refers to a Project which ultimately has the "visibility" row. Would that nestedness create a lot of issues? I'm new to writing SQL like this
garyaustin
garyaustin•3y ago
You have to do a selects on each table with a foreign key (or some other key) and then you and wrap that in an EXISTS. 
USING (EXISTS
             (SELECT 1 FROM user_website
              WHERE user_website.website_id = website.website_id
              AND user_website.user_id = uid()
             )
         );
USING (EXISTS
             (SELECT 1 FROM user_website
              WHERE user_website.website_id = website.website_id
              AND user_website.user_id = uid()
             )
         );
That is just one level deep.
There are examples online and here, but I can't point them to you right now. But it is all basic SQL and you can even test most of it in the SQL editor (not counting role and RLS on other tables). You do have to have RLS select access on each table you use, unless you move the code to a "security definer" function to bypass the RLS.

Did you find this page helpful?