I am getting into Supabase and to practice I am making a suuuper simplified website-builder.
However I am having troubles with the row-level-security policies.

I have three tables:

• user → with users' information like first name, last name, etc.
• website → all websites
• user_website → Contains the information which website belongs to which person (since a website can be owned/editted by multiple users)

I didn't find any useful resource, because honestly I still lack the knowledge to know how to search properly for what I need.

I only found simple expressions like (uid() = user_id), but since the "permissions" are stored in another table, I don't know how to access that.

I used queries like the following but it didn't work as intended:

SELECT
  *
FROM
  user_website as uw
  JOIN website as w
  ON uw.website_id = w.website_id
WHERE
  uw.user_id = auth.uid()

Help is much appreciated – thanks!

CodePudding user response：

You could define a policy like that:

CREATE POLICY may_edit ON website
   FOR UPDATE TO PUBLIC
   USING (EXISTS
             (SELECT 1 FROM user_website
              WHERE user_website.website_id = website.website_id
              AND user_website.user_id = uid()
             )
         );

Here, uid() is a function that returns your current user ID.

This policy will let everyone modify their own website.

CodePudding user response：

I called a friend for help and he pointed out a section in the Supabase docs about "policies with joins" ... yet it still didn't work for me.

The reason was that the RLS-policy on the table website references the table user-website, which didn't allow users yet to access anything.

Solution

RLS-policy for select on website:

auth.uid() in (
  select user_id from user_website
  where website_id = website.website_id
)

RLS-policy for select on user-website:

auth.uid() = user_id