Insert policies for write access in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ security

intermediate

Understanding Supabase Insert Policy Behavior

Given a Supabase table with the following insert policy, what will happen when a user tries to insert a new row?

CREATE POLICY "Allow insert if user is owner" ON todos
FOR INSERT
USING (auth.uid() = owner_id);

A The insert will fail because USING clause is only for SELECT operations.

B The insert will always succeed regardless of the user's ID.

C The insert will succeed only if the user's ID matches the owner_id field in the new row.

D The insert will succeed only if the user is an admin, regardless of owner_id.

💡 Hint

Think about what the USING clause checks during an insert operation.

The USING clause in an insert policy checks the condition against the new row being inserted. Here, it ensures the authenticated user's ID matches the owner_id field in the new row, allowing the insert only if true.

❓ Configuration

intermediate

Correct Insert Policy Syntax for Write Access

Which of the following Supabase policies correctly allows authenticated users to insert rows only if the user_id column matches their own ID?

A CREATE POLICY "Allow user insert" ON messages FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid());

B CREATE POLICY "Allow user insert" ON messages FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

C CREATE POLICY "Allow user insert" ON messages FOR INSERT TO authenticated USING (user_id = auth.uid());

D CREATE POLICY "Allow user insert" ON messages FOR INSERT TO authenticated USING (auth.uid() = user_id);

💡 Hint

Remember that WITH CHECK is used to validate new rows on insert.

For insert operations, WITH CHECK validates the new row's data. The condition user_id = auth.uid() ensures users can only insert rows where the user_id matches their own ID.

❓ Architecture

advanced

Designing Insert Policies for Multi-Tenant Write Access

You have a multi-tenant application where each tenant has a unique tenant_id. You want to allow users to insert data only for their own tenant. Which policy correctly enforces this?

A CREATE POLICY "Tenant insert" ON records FOR INSERT TO authenticated USING (tenant_id = auth.uid());

B CREATE POLICY "Tenant insert" ON records FOR INSERT TO authenticated WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

C CREATE POLICY "Tenant insert" ON records FOR INSERT TO authenticated WITH CHECK (tenant_id = auth.uid());

D CREATE POLICY "Tenant insert" ON records FOR INSERT TO authenticated USING (tenant_id = current_setting('app.current_tenant')::uuid);

💡 Hint

Consider how to compare tenant_id with a session variable during insert.

The WITH CHECK clause validates the new row's tenant_id against the current tenant stored in a session variable. This ensures users can only insert rows for their own tenant.

❓ service_behavior

advanced

Effect of Missing Insert Policy on Write Access

What happens if a Supabase table has no insert policies defined and RLS (Row Level Security) is enabled?

A Insert operations succeed only if the user is authenticated.

B All users can insert rows without restriction.

C Only the table owner can insert rows.

D No user can insert rows; all insert attempts are denied.

💡 Hint

Think about the default behavior of RLS when no policies exist.

When RLS is enabled and no insert policies exist, all insert operations are denied by default to protect data security.

✅ Best Practice

expert

Best Practice for Insert Policies with Complex Conditions

You want to create an insert policy that allows users to insert rows only if they belong to a specific role and the data meets a complex condition involving multiple columns. Which approach is best?

A Use a single <code>WITH CHECK</code> clause combining all conditions with AND operators.

B Create multiple insert policies each checking one condition separately.

C Use <code>USING</code> clause for all conditions since it applies to inserts.

D Disable RLS and rely on application logic to enforce conditions.

💡 Hint

Consider how Supabase evaluates multiple policies and clauses.

A single WITH CHECK clause combining all conditions ensures atomic validation of the new row on insert. Multiple policies can cause unexpected behavior because all must allow the insert.

Practice

1. What is the main purpose of an insert policy in Supabase?

easy

A. To create new tables in the database

B. To delete data from a table

C. To update existing data in a table

D. To control who can add new data to a table

Solution

1. Step 1: Understand insert policy role

   Insert policies define rules about who can add new rows to a table.

2. Step 2: Differentiate from other policies

   Delete and update policies control removing or changing data, not inserting.

3. Final Answer:

   To control who can add new data to a table -> Option D

4. Quick Check:

   Insert policy = control insert access [OK]

Hint: Insert policies control adding data only [OK]

Common Mistakes:

• Confusing insert with update or delete policies
• Thinking insert policies create tables
• Assuming insert policies control data reading

2. Which of the following is the correct syntax to create an insert policy in Supabase?

easy

A. CREATE POLICY "Insert policy" ON table_name FOR UPDATE TO public WITH CHECK (true);

B. CREATE POLICY "Insert policy" ON table_name FOR INSERT TO public USING (true);

C. CREATE POLICY "Insert policy" ON table_name FOR INSERT TO public WITH CHECK (true);

D. CREATE POLICY "Insert policy" ON table_name FOR DELETE TO public USING (true);

Solution

1. Step 1: Identify correct command for insert policy

   Insert policies use FOR INSERT and require WITH CHECK to validate new rows.

2. Step 2: Eliminate wrong options

   Options with FOR UPDATE or FOR DELETE are for other operations; USING is for row filtering on SELECT or DELETE.

3. Final Answer:

   CREATE POLICY "Insert policy" ON table_name FOR INSERT TO public WITH CHECK (true); -> Option C

4. Quick Check:

   Insert policy syntax uses WITH CHECK [OK]

Hint: Insert policies need WITH CHECK clause [OK]

Common Mistakes:

• Using FOR UPDATE or FOR DELETE instead of FOR INSERT
• Using USING instead of WITH CHECK for insert
• Omitting the TO clause

3. Given this insert policy:

CREATE POLICY "Allow user insert" ON messages FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

What happens if a user tries to insert a message with user_id different from their own auth.uid()?

medium

A. The insert fails because the WITH CHECK condition is not met

B. The insert succeeds but the user_id is overwritten automatically

C. The insert succeeds because the policy allows all authenticated users

D. The insert fails because the policy syntax is invalid

Solution

1. Step 1: Understand WITH CHECK condition

   The policy allows insert only if auth.uid() equals the user_id in the new row.

2. Step 2: Analyze user insert attempt

   If user_id differs from auth.uid(), the condition fails, so the insert is rejected.

3. Final Answer:

   The insert fails because the WITH CHECK condition is not met -> Option A

4. Quick Check:

   WITH CHECK enforces insert data validity [OK]

Hint: WITH CHECK must be true for insert to succeed [OK]

Common Mistakes:

• Assuming all authenticated users can insert any user_id
• Thinking Supabase overwrites user_id automatically
• Confusing WITH CHECK with USING clause

4. You wrote this insert policy:

CREATE POLICY "Insert policy" ON orders FOR INSERT TO public USING (true);

Why does this policy not allow any inserts?

medium

A. Because the TO clause should be 'authenticated' not 'public'

B. Because insert policies require WITH CHECK, not USING, to validate inserts

C. Because the policy name cannot contain spaces

D. Because RLS must be disabled for inserts to work

Solution

1. Step 1: Identify correct clause for insert validation

   Insert policies use WITH CHECK to validate new rows, not USING.

2. Step 2: Understand effect of USING clause

   USING filters rows for SELECT, UPDATE, DELETE but does not validate inserts.

3. Final Answer:

   Because insert policies require WITH CHECK, not USING, to validate inserts -> Option B

4. Quick Check:

   Insert policies need WITH CHECK, not USING [OK]

Hint: Use WITH CHECK for insert policies, not USING [OK]

Common Mistakes:

• Using USING instead of WITH CHECK for insert
• Thinking TO public disables inserts
• Believing RLS must be off for inserts

5. You want to allow users to insert rows only if the created_by column matches their user ID and the status column is always set to 'pending'. Which insert policy correctly enforces this?

hard

A. CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid() AND status = 'pending');

B. CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated USING (created_by = auth.uid() AND status = 'pending');

C. CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid());

D. CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated WITH CHECK (status = 'pending');

Solution

1. Step 1: Identify conditions for insert validation

   Both created_by must match auth.uid() and status must be 'pending' for insert to be allowed.

2. Step 2: Choose correct clause and conditions

   WITH CHECK is required for insert validation; USING is incorrect here.

3. Step 3: Verify all conditions included

   CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid() AND status = 'pending'); includes both conditions combined with AND in WITH CHECK clause.

4. Final Answer:

   CREATE POLICY "User insert" ON tasks FOR INSERT TO authenticated WITH CHECK (created_by = auth.uid() AND status = 'pending'); -> Option A

5. Quick Check:

   Insert policy WITH CHECK enforces all conditions [OK]

Hint: Combine all insert conditions in WITH CHECK clause [OK]

Common Mistakes:

• Using USING instead of WITH CHECK for insert
• Checking only one condition, missing the other
• Forgetting to match user ID with auth.uid()