Testing RLS policies in Supabase - Step-by-Step Execution

Process Flow - Testing RLS policies

Start: Define RLS Policy

↓

Insert Test Data

↓

Attempt Query as User

↓

Check if Data is Visible

Yes No↓

Policy Allows

↓

Test Pass

↓

End

This flow shows how to test Row Level Security (RLS) policies by defining them, inserting test data, querying as a user, and checking if the data is accessible or blocked.

Execution Sample

Supabase

CREATE POLICY user_select ON todos
  FOR SELECT USING (auth.uid() = user_id);

-- Test as user with id '123'
SELECT * FROM todos;

Defines an RLS policy allowing users to see only their todos, then tests by selecting todos as a user.

Process Table

Step	Action	User ID Context	Query Result	Policy Check
1	Define RLS policy	N/A	Policy created	Policy requires auth.uid() = user_id
2	Insert todo with user_id='123'	N/A	Row inserted	N/A
3	Insert todo with user_id='456'	N/A	Row inserted	N/A
4	Query todos as user '123'	User ID = '123'	Returns todos with user_id='123'	Policy allows access
5	Query todos as user '456'	User ID = '456'	Returns todos with user_id='456'	Policy allows access
6	Query todos as user '789'	User ID = '789'	Returns no rows	Policy blocks access
7	Query todos without user context	No user ID	Returns no rows	Policy blocks access

💡 Testing ends after verifying queries return expected rows based on user ID and policy.

Status Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
todos table rows	empty	1 row (user_id='123')	2 rows (user_id='123','456')	2 rows (user_id='123','456')	2 rows (user_id='123','456')	2 rows (user_id='123','456')	2 rows (user_id='123','456')

Key Moments - 3 Insights

Why does the query return no rows when no user ID is set?

Because the RLS policy requires auth.uid() to match user_id, without a user ID context the policy blocks all rows (see execution_table step 7).

Why can user '123' only see their own todos?

The policy restricts SELECT to rows where auth.uid() equals user_id, so user '123' sees only rows with user_id='123' (see execution_table step 4).

What happens if a todo is inserted with a different user_id?

It exists in the table but is only visible to the matching user due to the policy (see steps 3 and 5).

Visual Quiz - 3 Questions

Look at the execution table, what is the query result at step 6 when user ID is '789'?

A Returns no rows

B Returns all todos

C Returns todos with user_id='789'

D Returns todos with user_id='123'

💡 Hint

Check execution_table row 6 under 'Query Result' column.

At which step does the policy first allow a user to see their todos?

A Step 6

B Step 2

C Step 4

D Step 7

💡 Hint

Look at execution_table rows and find when a user query returns rows.

If the policy was removed, how would the query result at step 7 change?

A Still returns no rows

B Returns all todos

C Returns only user '123' todos

D Returns an error

💡 Hint

With RLS disabled, all rows are accessible.

Concept Snapshot

Testing RLS policies:
1. Define policy restricting access by user ID.
2. Insert test data with different user IDs.
3. Query as different users.
4. Verify only allowed rows are visible.
5. No user context means no access.
6. Helps secure data per user.

Full Transcript

Testing Row Level Security (RLS) policies involves creating a policy that limits data access based on user identity. First, you define a policy that allows users to see only their own data. Then, you insert test data with different user IDs. Next, you run queries as different users to check if they see only their data. If a user tries to query without a user ID, no data is returned because the policy blocks access. This process ensures your data is protected and only visible to authorized users.

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