Update and delete policies in Supabase - Step-by-Step Execution

Process Flow - Update and delete policies

Start: User sends request

↓

Check policy type: Update or Delete?

↓

Evaluate Update Policy

↓

Policy allows?

No→Reject Request

Yes↓

Perform Update/Delete

↓

End

When a user requests to update or delete data, Supabase checks the corresponding policy. If the policy allows it, the action proceeds; otherwise, it is rejected.

Execution Sample

Supabase

CREATE POLICY "Allow update if owner" ON todos
FOR UPDATE
USING (auth.uid() = owner_id);

CREATE POLICY "Allow delete if owner" ON todos
FOR DELETE
USING (auth.uid() = owner_id);

Defines update and delete policies that allow only the owner of a todo item to modify or remove it.

Process Table

Step	Request Type	User ID	Record Owner ID	Policy Condition	Policy Result	Action Taken
1	Update	user123	user123	auth.uid() = owner_id -> user123 = user123	True	Update allowed
2	Delete	user123	user456	auth.uid() = owner_id -> user123 = user456	False	Delete rejected
3	Delete	user456	user456	auth.uid() = owner_id -> user456 = user456	True	Delete allowed
4	Update	user789	user123	auth.uid() = owner_id -> user789 = user123	False	Update rejected

💡 Requests are stopped when policy condition is False, preventing unauthorized updates or deletes.

Status Tracker

Variable	Start	After 1	After 2	After 3	After 4
User ID	N/A	user123	user123	user456	user789
Record Owner ID	N/A	user123	user456	user456	user123
Policy Condition Result	N/A	True	False	True	False
Action Taken	N/A	Update allowed	Delete rejected	Delete allowed	Update rejected

Key Moments - 2 Insights

Why is the update rejected in step 4 even though the user is authenticated?

Because the policy checks if the user ID matches the record owner ID. In step 4, user789 is not the owner (user123), so the policy returns False and rejects the update.

What happens if the policy condition returns False?

The request is rejected immediately, as shown in steps 2 and 4 where the policy condition is False and the action is rejected.

Visual Quiz - 3 Questions

Look at the execution table, what is the action taken at step 3?

A Delete allowed

B Update rejected

C Delete rejected

D Update allowed

💡 Hint

Check the 'Action Taken' column in row 3 of the execution table.

At which step does the policy condition become False for an update request?

A Step 1

B Step 4

C Step 2

D Step 3

💡 Hint

Look at the 'Request Type' and 'Policy Condition' columns for update requests in the execution table.

If the record owner ID changes to match the user ID in step 2, what would be the new action taken?

A Delete rejected

B Update rejected

C Delete allowed

D Update allowed

💡 Hint

Refer to the policy condition logic and the action taken when the condition is True.

Concept Snapshot

Update and delete policies control who can modify or remove data.
Policies use conditions like 'auth.uid() = owner_id' to allow only owners.
If the condition is True, the action proceeds; if False, it is rejected.
Define policies separately for UPDATE and DELETE actions.
Always test policies to ensure correct access control.

Full Transcript

When a user tries to update or delete data in Supabase, the system checks the relevant policy. The policy contains a condition, such as verifying if the user's ID matches the owner ID of the record. If this condition is true, the update or delete action is allowed. If false, the request is rejected to protect data. For example, if user123 tries to update their own todo item, the update is allowed. But if user123 tries to delete someone else's todo, the delete is rejected. This ensures only authorized users can change or remove data. Policies must be created for each action type separately. Testing these policies step-by-step helps confirm they work as intended.

Practice

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

easy

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

B. To control who can read data from a table

C. To control who can change existing data in a table

D. To control who can create new tables

Solution

1. Step 1: Understand the role of update policies

   Update policies define who is allowed to modify existing rows in a table.

2. Step 2: Differentiate from other policies

   Read policies control reading, insert policies control adding data, and table creation is unrelated.

3. Final Answer:

   To control who can change existing data in a table -> Option C

4. Quick Check:

   Update policy = control data changes [OK]

Hint: Update policies control data changes, not reads or inserts [OK]

Common Mistakes:

• Confusing update with read or insert policies
• Thinking update policies control table creation
• Assuming update policies allow adding new rows

2. Which keyword is used in Supabase policies to add extra conditions that must be true for an update to succeed?

easy

A. WITH CHECK

B. USING

C. ALLOW

D. WHERE

Solution

1. Step 1: Identify keywords for policy conditions

   Supabase uses USING to specify who can perform an action and WITH CHECK to add extra conditions on updates.

2. Step 2: Match keyword to extra conditions on update

   WITH CHECK ensures the new data meets certain rules before update is allowed.

3. Final Answer:

   WITH CHECK -> Option A

4. Quick Check:

   Extra update conditions = WITH CHECK [OK]

Hint: Use WITH CHECK for extra update conditions, USING for who can act [OK]

Common Mistakes:

• Confusing USING and WITH CHECK keywords
• Thinking WHERE is used in policies
• Assuming ALLOW is a valid keyword

3. Given this update policy snippet:

CREATE POLICY "Update own profile" ON profiles
FOR UPDATE
USING (auth.uid() = profiles.user_id)
WITH CHECK (auth.uid() = profiles.user_id);

What happens if a user tries to update another user's profile?

medium

A. The update is allowed because USING only controls delete actions

B. The update is denied because WITH CHECK condition fails

C. The update is allowed because WITH CHECK is ignored

D. The update is denied because USING condition fails

Solution

1. Step 1: Understand USING condition role

   The USING clause controls who can perform the update. It checks if the user's ID matches the profile's user_id.

2. Step 2: Check condition for updating another user's profile

   If a user tries to update a different profile, auth.uid() = profiles.user_id is false, so update is denied.

3. Final Answer:

   The update is denied because USING condition fails -> Option D

4. Quick Check:

   USING controls update permission = denied if false [OK]

Hint: USING controls who can update; false means deny [OK]

Common Mistakes:

• Thinking WITH CHECK controls permission to update
• Assuming USING applies only to deletes
• Ignoring that both USING and WITH CHECK must pass

4. You wrote this delete policy:

CREATE POLICY "Delete own posts" ON posts
FOR DELETE
USING (user_id = auth.uid());

But users report they cannot delete their posts. What is the likely problem?

medium

A. The policy should use WITH CHECK instead of USING

B. The policy might be missing enabling RLS on the table

C. The policy is correct; problem is elsewhere

D. The policy is missing FOR UPDATE clause

Solution

1. Step 1: Check policy syntax

   The policy syntax is correct for delete with USING condition matching user_id.

2. Step 2: Consider RLS status

   If Row Level Security (RLS) is not enabled on the table, policies have no effect and all actions are denied by default.

3. Final Answer:

   The policy might be missing enabling RLS on the table -> Option B

4. Quick Check:

   RLS must be enabled for policies to work [OK]

Hint: Enable RLS on table to activate policies [OK]

Common Mistakes:

• Confusing USING and WITH CHECK for delete policies
• Forgetting to enable RLS on the table
• Thinking FOR UPDATE is needed for delete policies

5. You want to allow users to update their own orders only if the order status is not 'shipped'. Which policy correctly enforces this?

hard

A. FOR UPDATE USING (auth.uid() = orders.user_id) WITH CHECK (new.status != 'shipped')

B. FOR UPDATE USING (auth.uid() = orders.user_id) WITH CHECK (orders.status != 'shipped')

C. FOR UPDATE USING (auth.uid() = orders.user_id AND orders.status != 'shipped')

D. FOR UPDATE USING (auth.uid() = orders.user_id AND new.status != 'shipped')

Solution

1. Step 1: Understand USING vs WITH CHECK

   USING controls who can perform the update; WITH CHECK ensures the new data meets conditions.

2. Step 2: Apply condition on new data

   The status check must apply to the new value after update, so use new.status != 'shipped' in WITH CHECK.

3. Step 3: Verify options

   FOR UPDATE USING (auth.uid() = orders.user_id) WITH CHECK (new.status != 'shipped') correctly uses USING (auth.uid() = orders.user_id) and WITH CHECK (new.status != 'shipped').

4. Final Answer:

   FOR UPDATE USING (auth.uid() = orders.user_id) WITH CHECK (new.status != 'shipped') -> Option A

5. Quick Check:

   Use WITH CHECK with new. for update conditions [OK]

Hint: Use WITH CHECK with new. to check updated values [OK]

Common Mistakes:

• Checking old data instead of new data in WITH CHECK
• Putting status condition in USING instead of WITH CHECK
• Forgetting to use new. prefix for updated row