Implementing Supabase Row Level Security Policies

steps

1. Enable Row-Level Security for a Table
   Go to the Supabase dashboard, select your project, and navigate to the Table Editor. Choose the table you want to secure (e.g., post), then click Enable Row Level Security.

2. Create a Policy for User-Specific Access
   Once RLS is enabled, no operations (SELECT, INSERT, UPDATE, DELETE) are allowed by default. Use a policy to define access rules:

   sqlCopy

   CREATE POLICY "Enable all actions for user" 
   ON public.post 
   FOR ALL 
   USING (auth.uid() = user_id)
   WITH CHECK (auth.uid() = user_id);

   This allows users to access or modify rows where user_id matches their authenticated ID.

3. Test the Policy for Row Access
   Attempt to fetch rows via your API. Users should only see the rows they are authorized to access:

   bashCopy

   curl -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
   'https://your-project.supabase.co/rest/v1/post'

4. Specify Policies for Read-Only Access to Additional Rows
   To allow users to see published posts:

   sqlCopy

   CREATE POLICY "Enable read access to published posts"
   ON public.post
   FOR SELECT
   USING (is_published = TRUE);

5. Implement Policies for Related Tables
   For associated tables like comments, you can use subqueries:

   sqlCopy

   CREATE POLICY "View comments for visible posts"
   ON public.comment
   FOR SELECT
   USING (EXISTS (
       SELECT 1 FROM public.post 
       WHERE post.id = comment.post_id 
       AND is_published = TRUE
   ));

6. Write Additional Policies for Insert, Update, and Delete Actions
   Control actions such as adding or editing comments:

   sqlCopy

   CREATE POLICY "Insert comments on visible posts" 
   ON public.comment
   FOR INSERT
   USING (
       EXISTS (SELECT 1 FROM public.post 
       WHERE post.id = comment.post_id 
       AND is_published = TRUE) 
       AND auth.uid() = comment.user_id
   );

steps

1. Test Data Visibility
   Verify that rows are only accessible to the intended users:

   bashCopy

   curl -H "Authorization: Bearer VALID_USER_TOKEN" \
   https://your-project.supabase.co/rest/v1/post

   Confirm unauthorized users cannot access private rows.

2. Verify Insert and Update Constraints
   Test adding rows to ensure user identity matches:

   bashCopy

   curl -X POST -H "Authorization: Bearer VALID_USER_TOKEN" \
   -H "Content-Type: application/json" \
   -d '{"title": "New Post", "user_id": "wrong_user_id"}' \
   https://your-project.supabase.co/rest/v1/post

   Expect failure if the user_id doesn’t match.

3. Check Deletion Rules
   Validate delete permissions ensure only row owners or moderators can perform deletions:

   bashCopy

   curl -X DELETE -H "Authorization: Bearer INVALID_USER_TOKEN" \
   'https://your-project.supabase.co/rest/v1/post?id=1'

   Confirm rows remain intact.

4. Handle Complex Relationships
   For nested tables (e.g., comments under posts), test whether access aligns with related table permissions. Use queries or API requests to check for unintended entries.