Why Select policies for read access in Supabase? - Purpose & Use Cases

The Big Idea

What if your database could decide who sees what, all by itself?

The Scenario

Imagine you have a big library of books stored in a database. You want to let some friends read certain books but not others. If you try to control who can read what by manually checking each request every time, it quickly becomes confusing and slow.

The Problem

Manually checking who can read which data means writing lots of code for every case. It's easy to make mistakes and accidentally give access to the wrong people or block the right ones. This slows down your app and makes it hard to keep data safe.

The Solution

Select policies let you set clear rules about who can read data, right inside your database. These rules automatically check every request, so you don't have to write extra code or worry about mistakes. It's like having a smart gatekeeper who knows exactly who can enter.

Before vs After

✗ Before

if user.id == data.owner_id:
    allow_read()

✓ After

create policy "Allow read if owner" on table
  for select
  using (auth.uid() = owner_id);

What It Enables

With select policies, you can safely share data with the right people automatically, making your app faster and more secure.

Real Life Example

A photo sharing app lets users see only their own pictures by using select policies, so no one else can peek at private photos.

Key Takeaways

Manual access checks are slow and error-prone.

Select policies automate and secure read access rules.

This makes apps safer and easier to build.

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