Testing RLS policies in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Understanding RLS Policy Effect on SELECT Queries

You have a table orders with a Row Level Security (RLS) policy that allows users to see only their own orders based on user_id. What will be the result of the following query when run by user with user_id = 5?

SELECT * FROM orders;

Supabase

CREATE POLICY user_orders ON orders FOR SELECT USING (user_id = auth.uid());

A Returns only rows where user_id equals 5.

B Raises an error due to missing policy for SELECT.

C Returns no rows because no explicit grant is given.

D Returns all rows from orders regardless of user_id.

💡 Hint

Think about how RLS filters rows based on the policy condition.

The RLS policy restricts SELECT queries to rows where user_id matches the current user's ID (5). So only those rows are returned.

❓ security

intermediate

Effect of Missing RLS Policy on UPDATE

Consider a table profiles with RLS enabled but no UPDATE policy defined. What happens when a user tries to update their own profile?

Supabase

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

A The update succeeds only for the user's own row.

B The update fails with a permission error.

C The update succeeds for all rows.

D The update silently ignores the RLS and updates all rows.

💡 Hint

Remember what happens when no policy exists for a command with RLS enabled.

When RLS is enabled and no policy exists for UPDATE, all UPDATE commands are denied by default, causing a permission error.

❓ Configuration

advanced

Testing RLS Policy with Supabase Auth Function

You want to test if your RLS policy correctly restricts DELETE operations to the owner of the row. Which SQL command will simulate a DELETE as a user with auth.uid() = 'abc123'?

A SET LOCAL auth.uid = 'abc123'; DELETE FROM items WHERE id = 10;

B SET ROLE 'abc123'; DELETE FROM items WHERE id = 10;

C SET LOCAL jwt.claims.sub = 'abc123'; DELETE FROM items WHERE id = 10;

D EXECUTE AS USER 'abc123'; DELETE FROM items WHERE id = 10;

💡 Hint

Supabase uses JWT claims to identify users in RLS policies.

Setting jwt.claims.sub simulates the authenticated user's ID for RLS evaluation in Supabase.

❓ Architecture

advanced

Designing RLS Policies for Multi-Tenant Apps

You are designing a multi-tenant app where each tenant's data is stored in the same table with a tenant_id column. Which RLS policy best ensures users see only their tenant's data?

A CREATE POLICY tenant_access ON data FOR ALL USING (tenant_id = auth.uid());

B CREATE POLICY tenant_access ON data FOR ALL USING (tenant_id = 'fixed-tenant-id');

C CREATE POLICY tenant_access ON data FOR ALL USING (tenant_id IS NOT NULL);

D CREATE POLICY tenant_access ON data FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);

💡 Hint

Consider how tenant context is passed securely to the database.

Using a session variable like app.current_tenant allows the app to set tenant context securely for RLS filtering.

✅ Best Practice

expert

Ensuring RLS Policy Coverage for All Commands

You have enabled RLS on a table documents. You want to ensure users can only SELECT, INSERT, UPDATE, and DELETE their own documents. Which set of policies covers all these commands correctly?

A

CREATE POLICY select_policy ON documents FOR SELECT USING (owner_id = auth.uid());
CREATE POLICY insert_policy ON documents FOR INSERT WITH CHECK (owner_id = auth.uid());
CREATE POLICY update_policy ON documents FOR UPDATE USING (owner_id = auth.uid()) WITH CHECK (owner_id = auth.uid());
CREATE POLICY delete_policy ON documents FOR DELETE USING (owner_id = auth.uid());

B

CREATE POLICY all_policy ON documents FOR ALL USING (owner_id = auth.uid());
CREATE POLICY insert_policy ON documents FOR INSERT WITH CHECK (owner_id = auth.uid());

C

CREATE POLICY select_update_policy ON documents FOR SELECT, UPDATE USING (owner_id = auth.uid());
CREATE POLICY insert_delete_policy ON documents FOR INSERT, DELETE WITH CHECK (owner_id = auth.uid());

D CREATE POLICY full_access ON documents FOR ALL USING (true);

💡 Hint

Remember that INSERT policies require WITH CHECK clauses and UPDATE policies need both USING and WITH CHECK.

Option A correctly defines separate policies for each command with proper USING and WITH CHECK clauses to restrict access to the owner's documents.

Practice

1. What is the main purpose of Row Level Security (RLS) policies in Supabase?

easy

A. To encrypt data stored in the database

B. To speed up database queries by indexing rows

C. To backup data automatically every hour

D. To control which rows a user can access or modify in a table

Solution

1. Step 1: Understand RLS purpose

   RLS policies restrict access to rows based on user identity or conditions.

2. Step 2: Compare options

   Only To control which rows a user can access or modify in a table describes controlling access to rows per user, which is the core of RLS.

3. Final Answer:

   To control which rows a user can access or modify in a table -> Option D

4. Quick Check:

   RLS controls row access = A [OK]

Hint: RLS controls data visibility per user, not performance or backups [OK]

Common Mistakes:

• Confusing RLS with indexing or encryption
• Thinking RLS handles backups
• Assuming RLS speeds up queries

2. Which SQL command correctly enables Row Level Security on a table named profiles in Supabase?

easy

A. ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

B. ALTER TABLE profiles SET ROW LEVEL SECURITY ON;

C. ALTER TABLE profiles ENABLE RLS;

D. ALTER TABLE profiles ENABLE ROW_LEVEL_SECURITY;

Solution

1. Step 1: Recall correct syntax for enabling RLS

   The correct SQL command is ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;.

2. Step 2: Match options with syntax

   Only ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; matches the exact syntax supported by Supabase/PostgreSQL.

3. Final Answer:

   ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; -> Option A

4. Quick Check:

   Enable RLS syntax = A [OK]

Hint: Use exact phrase ENABLE ROW LEVEL SECURITY after ALTER TABLE [OK]

Common Mistakes:

• Using 'RLS' shorthand incorrectly
• Using underscores incorrectly
• Using SET instead of ENABLE

3. Given this RLS policy on messages table:

CREATE POLICY "Users can view their messages" ON messages
FOR SELECT USING (user_id = auth.uid());

What will happen if user with auth.uid() = '123' runs SELECT * FROM messages;?

medium

A. They will see all messages regardless of user_id

B. They will see only messages where user_id = '123'

C. They will see no messages because policy blocks all

D. They will get a syntax error

Solution

1. Step 1: Understand the policy condition

   The policy allows SELECT only if user_id = auth.uid(). For user '123', only rows with user_id '123' are visible.

2. Step 2: Predict query result

   The SELECT query returns only messages owned by user '123'. No syntax errors occur.

3. Final Answer:

   They will see only messages where user_id = '123' -> Option B

4. Quick Check:

   Policy filters rows by user_id = auth.uid() = B [OK]

Hint: Policy filters rows by user_id matching auth.uid() [OK]

Common Mistakes:

• Assuming all rows are visible
• Thinking policy blocks all rows
• Confusing syntax error with policy effect

4. You wrote this RLS policy:

CREATE POLICY "Allow updates" ON profiles
FOR UPDATE USING (user_id = auth.uid());

But users report they cannot update their profiles. What is the likely problem?

medium

A. auth.uid() returns NULL causing policy to fail

B. RLS is not enabled on the profiles table

C. The policy should use WITH CHECK instead of USING

D. The policy name is invalid

Solution

1. Step 1: Understand difference between USING and WITH CHECK

   USING controls which rows can be selected or updated; WITH CHECK controls which updated rows are allowed after update.

2. Step 2: Identify update permission issue

   For UPDATE, WITH CHECK is needed to allow the new row values. Using only USING blocks updates even if row is accessible.

3. Step 3: Check other options

   RLS must be enabled, but question implies it is. auth.uid() returning NULL is unlikely if user is logged in. Policy name is valid.

4. Final Answer:

   The policy should use WITH CHECK instead of USING -> Option C

5. Quick Check:

   Update policies need WITH CHECK = A [OK]

Hint: Use WITH CHECK for update permission, not just USING [OK]

Common Mistakes:

• Using only USING clause for updates
• Forgetting to enable RLS
• Assuming policy name affects behavior

5. You want to test if your RLS policy on orders table correctly restricts users to only see their own orders. Which approach is best to verify this?

hard

A. Run SELECT * FROM orders; as different authenticated users and check results

B. Disable RLS temporarily and check if all orders are visible

C. Check the policy text only without running queries

D. Run SELECT * FROM orders; as the database admin user

Solution

1. Step 1: Understand testing RLS requires simulating user access

   Testing RLS means running queries as different users to see if access is limited correctly.

2. Step 2: Evaluate options

   Run SELECT * FROM orders; as different authenticated users and check results runs queries as different users, which tests the policy effectively. Disable RLS temporarily and check if all orders are visible disables RLS, which doesn't test policy. Check the policy text only without running queries only reads policy text, no test. Run SELECT * FROM orders; as the database admin user uses admin user who bypasses RLS.

3. Final Answer:

   Run SELECT * FROM orders; as different authenticated users and check results -> Option A

4. Quick Check:

   Test RLS by querying as different users = D [OK]

Hint: Test RLS by querying as different users, not admin [OK]

Common Mistakes:

• Testing only as admin user
• Disabling RLS to test policies
• Not running actual queries