Enabling RLS on tables in Supabase - Step-by-Step Execution

Process Flow - Enabling RLS on tables

Start: Table created

↓

Enable RLS on table

↓

Define RLS policies

↓

Test access with policies

↓

Access allowed or denied based on policies

↓

End

This flow shows enabling Row Level Security (RLS) on a table, adding policies, and how access is controlled.

Execution Sample

Supabase

ALTER TABLE todos ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Allow logged-in users"
ON todos
FOR SELECT
USING (auth.uid() = user_id);

This code enables RLS on the 'todos' table and creates a policy allowing users to select rows where their user ID matches.

Process Table

Step	Action	SQL Command	Effect	Result
1	Enable RLS on table	ALTER TABLE todos ENABLE ROW LEVEL SECURITY;	RLS activated on 'todos'	Table now enforces row-level access control
2	Create policy for SELECT	CREATE POLICY "Allow logged-in users" ON todos FOR SELECT USING (auth.uid() = user_id);	Policy added to allow users to select their own rows	Only rows matching user's ID can be selected
3	User tries to SELECT rows	SELECT * FROM todos;	RLS policy applied	User sees only their own rows
4	User tries to SELECT rows of others	SELECT * FROM todos WHERE user_id != auth.uid();	RLS policy blocks access	No rows returned
5	Disable RLS (not recommended)	ALTER TABLE todos DISABLE ROW LEVEL SECURITY;	RLS turned off	All rows accessible without restriction
6	End			RLS controls access based on policies

💡 RLS enabled and policies defined, access controlled by user identity matching user_id

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
RLS Status	Disabled	Enabled	Enabled	Enabled	Enabled	Enabled
Policies	None	None	Allow logged-in users (SELECT)	Allow logged-in users (SELECT)	Allow logged-in users (SELECT)	Allow logged-in users (SELECT)
User Access	Full access	No access	Restricted to own rows	Restricted to own rows	Restricted to own rows	Restricted to own rows

Key Moments - 3 Insights

Why does enabling RLS alone restrict access?

Enabling RLS (Step 1) activates row-level security but without policies (Step 2), no access is allowed by default. Policies must be created to define who can access which rows.

What happens if a user tries to access rows not matching their user_id?

As shown in Step 4, the RLS policy blocks access to rows where auth.uid() does not match user_id, so no rows are returned.

Can disabling RLS after enabling it remove restrictions immediately?

Yes, as in Step 5, disabling RLS turns off row-level security, allowing unrestricted access to all rows.

Visual Quiz - 3 Questions

Look at the execution table, what is the RLS status after Step 2?

A Enabled

B Disabled

C Partially enabled

D Unknown

💡 Hint

Check the 'RLS Status' variable in variable_tracker after Step 2

At which step does the user only see their own rows?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'User Access' variable and execution_table rows describing user SELECT attempts

If no policy is created after enabling RLS, what happens when a user tries to SELECT?

A User sees all rows

B User sees no rows

C User sees only some rows randomly

D Query fails with error

💡 Hint

Refer to key_moments explanation about enabling RLS without policies

Concept Snapshot

Enable RLS on a table with:
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Create policies to allow access:
CREATE POLICY "policy_name" ON table_name FOR action USING (condition);

Without policies, access is blocked.
Policies control which rows users can access based on conditions.

Full Transcript

Enabling Row Level Security (RLS) on a table involves first activating RLS with an ALTER TABLE command. This blocks access until you define policies that specify who can access which rows. For example, a policy can allow users to select only rows where their user ID matches a column. When a user queries the table, the database applies these policies to filter rows accordingly. If a user tries to access rows not allowed by the policy, no rows are returned. Disabling RLS removes these restrictions. This visual trace shows each step from enabling RLS, adding a policy, user access attempts, and the resulting access control.

Practice

1. What does enabling Row Level Security (RLS) on a Supabase table do?

easy

A. It backs up the table data to a secure location.

B. It encrypts the entire table data automatically.

C. It controls which rows a user can access based on their identity.

D. It increases the table's storage capacity.

Solution

1. Step 1: Understand RLS purpose

   RLS restricts access to rows in a table depending on who the user is.

2. Step 2: Compare options

   Only It controls which rows a user can access based on their identity. describes controlling row access by user identity, others describe unrelated features.

3. Final Answer:

   It controls which rows a user can access based on their identity. -> Option C

4. Quick Check:

   RLS = Row access control [OK]

Hint: RLS controls row access by user identity, not encryption or backup [OK]

Common Mistakes:

• Confusing RLS with encryption
• Thinking RLS backs up data
• Assuming RLS increases storage

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

easy

A. ALTER TABLE profiles SET RLS = TRUE;

B. ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

C. ENABLE RLS ON TABLE profiles;

D. SET ROW LEVEL SECURITY ON profiles;

Solution

1. Step 1: Recall correct SQL syntax for enabling RLS

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

2. Step 2: Match options to syntax

   Only ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; matches the exact syntax; others are invalid SQL commands.

3. Final Answer:

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

4. Quick Check:

   ALTER TABLE + ENABLE ROW LEVEL SECURITY = correct syntax [OK]

Hint: Use ALTER TABLE ... ENABLE ROW LEVEL SECURITY to enable RLS [OK]

Common Mistakes:

• Using SET instead of ENABLE
• Wrong command order
• Missing ALTER TABLE keyword

3. Given the following policy on table tasks with RLS enabled:

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

What will happen if a user tries to select tasks where user_id is different from their own?

medium

A. They will see only tasks where user_id matches their auth.uid().

B. They will get a syntax error.

C. They will see all tasks regardless of user_id.

D. They will see no tasks at all.

Solution

1. Step 1: Understand the policy condition

   The policy allows SELECT only if user_id = auth.uid(), meaning users see only their own tasks.

2. Step 2: Predict query result for mismatched user_id

   If user_id differs, the condition fails, so those rows are not visible.

3. Final Answer:

   They will see only tasks where user_id matches their auth.uid(). -> Option A

4. Quick Check:

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

Hint: Policy USING clause filters rows by user identity [OK]

Common Mistakes:

• Assuming all rows are visible
• Expecting syntax errors from policy
• Thinking no rows are visible at all

4. You enabled RLS on the orders table but users report they cannot see any rows even though policies exist. What is the most likely cause?

medium

A. The table has too many rows.

B. The table is missing a primary key.

C. The database connection is lost.

D. RLS is enabled but no policies allow access to rows.

Solution

1. Step 1: Understand RLS behavior

   When RLS is enabled, access depends on policies. Without policies allowing access, no rows are visible.

2. Step 2: Evaluate other options

   Missing primary key, connection loss, or row count do not directly cause no rows to show due to RLS.

3. Final Answer:

   RLS is enabled but no policies allow access to rows. -> Option D

4. Quick Check:

   RLS needs policies to permit row access [OK]

Hint: Enable policies after RLS to allow row access [OK]

Common Mistakes:

• Ignoring missing policies
• Blaming unrelated database issues
• Assuming RLS shows all rows by default

5. You want to allow users to insert rows into the messages table only if the sender_id matches their authenticated user ID. Which policy correctly enforces this with RLS enabled?

hard

A. CREATE POLICY "Allow insert if sender matches" ON messages FOR INSERT WITH CHECK (sender_id = auth.uid());

B. CREATE POLICY "Allow insert" ON messages FOR INSERT USING (sender_id = auth.uid());

C. CREATE POLICY "Allow insert" ON messages FOR SELECT WITH CHECK (sender_id = auth.uid());

D. CREATE POLICY "Allow insert" ON messages FOR UPDATE WITH CHECK (sender_id = auth.uid());

Solution

1. Step 1: Identify correct policy type for INSERT

   For INSERT, WITH CHECK controls if new rows meet the condition.

2. Step 2: Match condition to sender_id and auth.uid()

   The condition sender_id = auth.uid() ensures users insert only their own messages.

3. Step 3: Verify options

   CREATE POLICY "Allow insert if sender matches" ON messages FOR INSERT WITH CHECK (sender_id = auth.uid()); uses FOR INSERT with WITH CHECK and correct condition; others use wrong commands or clauses.

4. Final Answer:

   CREATE POLICY "Allow insert if sender matches" ON messages FOR INSERT WITH CHECK (sender_id = auth.uid()); -> Option A

5. Quick Check:

   INSERT policies use WITH CHECK to validate new rows [OK]

Hint: Use WITH CHECK for INSERT policies to validate new rows [OK]

Common Mistakes:

• Using USING instead of WITH CHECK for INSERT
• Applying SELECT or UPDATE instead of INSERT
• Missing condition on sender_id