Insert policies for write access in Supabase - Step-by-Step Execution

Process Flow - Insert policies for write access

Start: User tries to insert data

↓

Check Insert Policy Exists?

No→Deny Insert

Yes↓

Evaluate Policy Condition

↓

Allow Insert

↓

Data Inserted

When a user tries to insert data, the system checks if an insert policy exists. If yes, it evaluates the policy condition. If the condition is true, insert is allowed; otherwise, it is denied.

Execution Sample

Supabase

create policy "Allow insert for authenticated users"
  on messages
  for insert
  with check (auth.role() = 'authenticated');

This policy allows only authenticated users to insert data into the messages table.

Process Table

Step	Action	Policy Check	Condition Evaluation	Result
1	User attempts to insert a message	Insert policy exists	auth.role() = 'authenticated' ?	Evaluated
2	Check user role	N/A	User role is 'authenticated'	True
3	Allow insert	N/A	N/A	Insert permitted
4	Data inserted into messages table	N/A	N/A	Success
5	User with role 'anonymous' tries insert	Insert policy exists	auth.role() = 'authenticated' ?	Evaluated
6	Check user role	N/A	User role is 'anonymous'	False
7	Deny insert	N/A	N/A	Insert denied

💡 Insert allowed only if user role is 'authenticated'; otherwise denied.

Status Tracker

Variable	Start	After Step 2	After Step 6	Final
user_role	unknown	authenticated	anonymous	varies
insert_allowed	false	true	false	varies

Key Moments - 2 Insights

Why does the insert fail for some users even though the policy exists?

Because the policy condition (auth.role() = 'authenticated') evaluates to false for users without the authenticated role, as shown in execution_table rows 5-7.

What happens if no insert policy is defined?

Insert is denied by default since the system checks for policy existence first (execution_table step 1). Without a policy, no insert is allowed.

Visual Quiz - 3 Questions

Look at the execution_table, what is the user_role at step 2?

A authenticated

B admin

C anonymous

D unknown

💡 Hint

Check the 'Condition Evaluation' column at step 2 where user role is evaluated.

At which step does the insert get denied for an anonymous user?

A Step 4

B Step 5

C Step 7

D Step 3

💡 Hint

Look for 'Insert denied' in the 'Result' column for anonymous user attempts.

If the policy condition changed to allow all roles, how would step 7 change?

A Insert would still be denied

B Insert would be allowed

C Policy check would fail

D No change

💡 Hint

Refer to the 'Condition Evaluation' and 'Result' columns in execution_table steps 6 and 7.

Concept Snapshot

Insert policies control who can add data.
They check user roles or conditions.
If condition true, insert allowed.
If false or no policy, insert denied.
Use 'create policy' with 'for insert' and 'using' clause.
Example: allow only authenticated users.

Full Transcript

When a user tries to insert data into a table, Supabase checks if an insert policy exists for that table. If no policy exists, the insert is denied by default. If a policy exists, Supabase evaluates the condition defined in the policy, such as checking if the user's role is 'authenticated'. If the condition is true, the insert is allowed and the data is saved. If false, the insert is denied. For example, a policy can allow only authenticated users to insert data. This ensures write access is controlled securely.

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()