Select policies for read access in Supabase - Step-by-Step Execution

Process Flow - Select policies for read access

Client sends SELECT query

↓

Check Row Level Security (RLS) enabled?

No→Allow query

Yes↓

Evaluate SELECT policy conditions

↓

Allow query

↓

Return data or error to client

When a client requests data, Supabase checks if row-level security is on. If yes, it evaluates the read policies. If the policy allows, data is returned; otherwise, access is denied.

Execution Sample

Supabase

CREATE POLICY "Allow read for authenticated users"
ON my_table
FOR SELECT
USING (auth.role() = 'authenticated');

This policy allows users with the 'authenticated' role to read data from 'my_table'.

Process Table

Step	Action	Policy Condition Evaluated	Result	Access Outcome
1	Client sends SELECT query	N/A	N/A	Pending
2	Check if RLS enabled on 'my_table'	N/A	Yes	Pending
3	Evaluate policy: auth.role() = 'authenticated'	auth.role() = 'authenticated'	True	Allow
4	Return data to client	N/A	N/A	Data returned
5	Client sends SELECT query (unauthenticated)	N/A	N/A	Pending
6	Check if RLS enabled on 'my_table'	N/A	Yes	Pending
7	Evaluate policy: auth.role() = 'authenticated'	auth.role() = 'authenticated'	False	Deny
8	Return error to client	N/A	N/A	Access denied

💡 Execution stops after policy evaluation allows or denies access.

Status Tracker

Variable	Start	After Step 3	After Step 7	Final
auth.role()	N/A	'authenticated'	'anon'	N/A
Access Outcome	Pending	Allow	Deny	Data returned or Access denied

Key Moments - 2 Insights

Why does the query get denied even though the table exists?

Because RLS is enabled and the policy condition (auth.role() = 'authenticated') evaluated to False, as shown in step 7 of the execution_table.

What happens if no SELECT policy is defined when RLS is enabled?

All SELECT queries are denied by default when RLS is enabled and no policy matches, so no data is returned.

Visual Quiz - 3 Questions

Look at the execution_table, what is the access outcome at step 4?

A Access denied

B Data returned

C Pending

D Error

💡 Hint

Refer to the 'Access Outcome' column at step 4 in the execution_table.

At which step does the policy condition evaluate to False?

A Step 2

B Step 3

C Step 7

D Step 5

💡 Hint

Check the 'Policy Condition Evaluated' and 'Result' columns in the execution_table.

If RLS was disabled, what would happen to the access outcome?

A Access would be allowed without policy check

B Policy would still be evaluated

C Access would be denied

D Query would fail

💡 Hint

Look at step 2 in the concept_flow where RLS check leads to immediate allow if disabled.

Concept Snapshot

Supabase SELECT policies control read access.
Enable Row Level Security (RLS) on tables.
Create policies with FOR SELECT and USING conditions.
Queries allowed only if policy condition is true.
No matching policy means no access.
Policies use auth.role() or custom logic.

Full Transcript

When a client sends a SELECT query to a Supabase table, the system first checks if Row Level Security (RLS) is enabled. If RLS is off, the query is allowed immediately. If RLS is on, Supabase evaluates the SELECT policies defined on the table. Each policy has a condition, such as checking if the user's role is 'authenticated'. If the condition is true, the query is allowed and data is returned. If false, access is denied and an error is returned. If no policy matches, access is denied by default. This ensures only authorized users can read data. The example policy allows only authenticated users to read from 'my_table'. The execution table shows steps for both authenticated and unauthenticated users, illustrating how access is granted or denied based on policy evaluation.

Practice

1. What is the main purpose of a SELECT policy in Supabase?

easy

A. To control who can read data from a table

B. To control who can write data to a table

C. To create new tables in the database

D. To delete data from a table

Solution

1. Step 1: Understand the role of SELECT policies

   SELECT policies define rules for reading data from tables.

2. Step 2: Differentiate from other policies

   Write policies control data insertion or updates, not reading.

3. Final Answer:

   To control who can read data from a table -> Option A

4. Quick Check:

   SELECT policy = read access control [OK]

Hint: SELECT policies = rules for reading data only [OK]

Common Mistakes:

• Confusing SELECT with INSERT or UPDATE policies
• Thinking SELECT policies create or delete tables
• Assuming SELECT policies control write access

2. Which of the following is the correct syntax to create a read policy allowing all users to select from a table named profiles?

easy

A. CREATE POLICY "Allow all" ON profiles FOR UPDATE USING (true);

B. CREATE POLICY "Allow all" ON profiles FOR INSERT USING (true);

C. CREATE POLICY "Allow all" ON profiles FOR SELECT USING (true);

D. CREATE POLICY "Allow all" ON profiles FOR DELETE USING (true);

Solution

1. Step 1: Identify the correct command for read access

   FOR SELECT is used to allow read (select) operations.

2. Step 2: Confirm the condition for allowing all users

   USING (true) means no restriction, so all users can read.

3. Final Answer:

   CREATE POLICY "Allow all" ON profiles FOR SELECT USING (true); -> Option C

4. Quick Check:

   FOR SELECT + USING (true) = allow all read [OK]

Hint: FOR SELECT + USING (true) allows all reads [OK]

Common Mistakes:

• Using FOR INSERT instead of FOR SELECT
• Forgetting the USING clause
• Using FOR DELETE or UPDATE for read access

3. Given the policy:

CREATE POLICY "User can read own profile" ON profiles FOR SELECT USING (auth.uid() = user_id);

What will happen if a user tries to select a profile where user_id is different from their auth.uid()?

medium

A. The user will be able to read the profile

B. The user will get an empty result (no rows)

C. The query will cause a syntax error

D. The user will be able to update the profile

Solution

1. Step 1: Understand the policy condition

   The policy allows reading only if auth.uid() equals user_id.

2. Step 2: Analyze the case when user_id differs

   If user_id is different, the condition fails, so no rows are returned.

3. Final Answer:

   The user will get an empty result (no rows) -> Option B

4. Quick Check:

   Condition false = no rows returned [OK]

Hint: Read allowed only if auth.uid() matches user_id [OK]

Common Mistakes:

• Assuming user can read all profiles
• Expecting an error instead of empty result
• Confusing read with update permissions

4. You wrote this policy:

CREATE POLICY "Allow read" ON orders FOR SELECT USING (user_id = auth.uid);

But users cannot read their orders. What is the problem?

medium

A. USING clause should be omitted

B. The policy should use FOR UPDATE instead of FOR SELECT

C. The table name is incorrect

D. auth.uid should be called as a function: auth.uid()

Solution

1. Step 1: Check the usage of auth.uid

   auth.uid is a function and must be called with parentheses: auth.uid()

2. Step 2: Understand impact of missing parentheses

   Without (), the condition is invalid and blocks access.

3. Final Answer:

   auth.uid should be called as a function: auth.uid() -> Option D

4. Quick Check:

   auth.uid() is required for correct condition [OK]

Hint: Always call auth.uid() with parentheses [OK]

Common Mistakes:

• Using auth.uid without parentheses
• Confusing FOR SELECT with FOR UPDATE
• Removing USING clause mistakenly

5. You want to allow users to read only their own messages from a messages table. The table has columns id, content, and owner_id. Which policy correctly enforces this read access?

hard

A. CREATE POLICY "Read own messages" ON messages FOR SELECT USING (owner_id = auth.uid());

B. CREATE POLICY "Read own messages" ON messages FOR SELECT USING (owner_id != auth.uid());

C. CREATE POLICY "Read own messages" ON messages FOR SELECT USING (true);

D. CREATE POLICY "Read own messages" ON messages FOR UPDATE USING (owner_id = auth.uid());

Solution

1. Step 1: Identify correct condition for own data

   Users should read rows where owner_id equals their auth.uid()

2. Step 2: Confirm correct policy type and condition

   FOR SELECT with USING (owner_id = auth.uid()) restricts reads properly.

3. Final Answer:

   CREATE POLICY "Read own messages" ON messages FOR SELECT USING (owner_id = auth.uid()); -> Option A

4. Quick Check:

   owner_id = auth.uid() restricts read to own rows [OK]

Hint: Use owner_id = auth.uid() in USING for own read access [OK]

Common Mistakes:

• Using != instead of = in condition
• Allowing all reads with USING (true)
• Using FOR UPDATE instead of FOR SELECT