Update and delete policies in Supabase - Deep Dive

Overview - Update and delete policies

What is it?

Update and delete policies in Supabase control who can change or remove data in your database. They are rules that decide if a user is allowed to update or delete a record. These policies help keep your data safe by making sure only the right people can make changes or remove information.

Why it matters

Without update and delete policies, anyone could change or delete your data, which could cause errors, data loss, or security problems. These policies protect your app’s data integrity and privacy by limiting who can modify or remove data. This keeps your app trustworthy and reliable for users.

Where it fits

Before learning update and delete policies, you should understand basic database concepts and how Supabase manages data. After this, you can learn about creating read and insert policies, and then move on to advanced security rules and role management.

Mental Model

Core Idea

Update and delete policies are like security guards that check if someone has permission before changing or removing data.

Think of it like...

Imagine a library where only certain librarians can update book information or remove books from the shelves. These librarians follow rules to decide who can do these actions, just like update and delete policies control data changes.

┌───────────────┐
│   User tries  │
│ to update or  │
│   delete data │
└───────┬───────┘
        │
        ▼
┌─────────────────────────┐
│ Update/Delete Policy     │
│ checks user permissions │
└───────┬─────────┬───────┘
        │         │
   Allowed?     Denied?
    ┌───┴───┐     ┌──┴───┐
    │Change │     │Block │
    │data   │     │action│
    └───────┘     └──────┘

Build-Up - 6 Steps

1

Foundation

What are update and delete policies

Concept: Introduction to the purpose of update and delete policies in Supabase.

Update and delete policies are rules you write to control who can change or remove data in your database tables. They work by checking conditions when a user tries to update or delete a record. If the conditions are met, the action is allowed; otherwise, it is blocked.

Result

You understand that these policies protect data by controlling update and delete actions.

Knowing that policies act as gatekeepers helps you see how data security is enforced at the database level.

2

Foundation

Basics of policy conditions

Concept: How conditions define who can update or delete data.

Conditions are logical expressions that check user identity, roles, or data values. For example, a condition might allow updates only if the user owns the record. These conditions use Supabase's built-in variables like auth.uid() to identify users.

Result

You can write simple conditions that restrict updates and deletes to certain users.

Understanding conditions is key to customizing who can safely modify or remove data.

3

Intermediate

Writing update policies with ownership checks

🤔Before reading on: do you think update policies can check if the user owns the data before allowing changes? Commit to your answer.

Concept: Using ownership checks in update policies to restrict changes to data owners.

You write an update policy that allows a user to update a row only if their user ID matches the owner ID in that row. For example: USING (auth.uid() = owner_id). This ensures users can only update their own data.

Result

Users can update only their own records, preventing unauthorized changes.

Knowing how to enforce ownership prevents accidental or malicious data changes by others.

4

Intermediate

Delete policies and cascading effects

🤔Before reading on: do you think deleting a record can affect related data automatically? Commit to yes or no.

Concept: Delete policies control who can remove data, and deleting can impact related records if foreign keys are set to cascade.

Delete policies check if a user can remove a record, often using similar ownership conditions. When a record is deleted, related records in other tables might also be deleted automatically if cascading is set up, which can cause data loss if not carefully controlled.

Result

You understand that delete policies protect data and that deletes can have wider effects.

Recognizing cascading deletes helps you design safer data relationships and policies.

5

Advanced

Combining update and delete policies with roles

🤔Before reading on: can update and delete policies use user roles to allow different permissions? Commit to your answer.

Concept: Using user roles in policies to give different update and delete permissions to different groups.

You can write policies that check user roles, like admin or editor, to allow broader update or delete rights. For example, admins might update or delete any record, while regular users can only modify their own. This uses conditions like auth.role() = 'admin'.

Result

Your app supports flexible permissions based on user roles.

Using roles in policies enables scalable and maintainable access control.

6

Expert

Performance and security trade-offs in policies

🤔Before reading on: do you think complex policies can slow down database operations? Commit to yes or no.

Concept: Understanding how complex policies affect database performance and security risks.

Complex policies with many conditions or joins can slow down update and delete operations. Overly permissive policies risk data leaks or unauthorized changes. Balancing security and performance requires careful policy design and testing.

Result

You can write efficient, secure policies that protect data without hurting performance.

Knowing the trade-offs helps you build robust systems that scale and stay secure.

Under the Hood

When a user tries to update or delete data, Supabase checks the relevant policy by running the condition as a database query. This query uses the user's identity and the data row to decide if the action is allowed. If the condition returns true, the database executes the update or delete; otherwise, it rejects the request.

Why designed this way?

Supabase uses policies at the database level to enforce security close to the data, reducing risks from application bugs or bypasses. This design follows the principle of 'security in depth' and leverages PostgreSQL's Row Level Security features for fine-grained control.

┌───────────────┐
│ User Request  │
│ (Update/Delete)│
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ Supabase Policy Engine       │
│ - Evaluates policy condition │
│ - Uses user info and row data│
└───────┬─────────────┬───────┘
        │             │
    Condition true  Condition false
        │             │
┌───────▼─────┐  ┌────▼─────┐
│ Allow DB    │  │ Reject   │
│ Operation   │  │ Operation│
└─────────────┘  └──────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think update policies automatically apply to delete actions? Commit to yes or no.

Common Belief: Update policies also control delete actions automatically.

Reality: Update and delete policies are separate; you must write distinct policies for each action.

Why it matters: Assuming one policy covers both can leave delete actions unprotected, risking unauthorized data removal.

Quick: Do you think a user can update any record if no update policy exists? Commit to yes or no.

Common Belief: If no update policy is set, users can update all records by default.

Reality: Without an explicit policy, update actions are denied by default for security.

Why it matters: Not setting policies can cause your app to block legitimate updates, confusing users.

Quick: Do you think complex policies always improve security? Commit to yes or no.

Common Belief: More complex policies always make the system more secure.

Reality: Overly complex policies can introduce errors or performance issues, weakening security.

Why it matters: Complexity can cause mistakes that open security holes or slow down your app.

Quick: Do you think delete policies prevent cascading deletes automatically? Commit to yes or no.

Common Belief: Delete policies stop all cascading deletes from happening.

Reality: Delete policies control who can delete a record, but cascading deletes happen automatically if set in the database schema.

Why it matters: Misunderstanding this can lead to unexpected data loss when related records are deleted.

Expert Zone

1

Policies can use session variables beyond auth.uid(), like custom claims, for fine-grained control.

2

Combining multiple policies on the same table uses logical OR, so any policy allowing the action grants access.

3

Supabase policies run inside the database, so they protect data even if the application layer is compromised.

When NOT to use

Avoid relying solely on update and delete policies for complex business logic or multi-step transactions. Use application-level checks or stored procedures for those cases.

Production Patterns

In production, teams use role-based policies combined with ownership checks to balance flexibility and security. They also audit policy effects regularly and test with real user scenarios to prevent accidental data exposure.

Connections

Role-Based Access Control (RBAC)

Update and delete policies build on RBAC by enforcing permissions at the data row level.

Understanding RBAC helps grasp how policies assign permissions based on user roles, improving security design.

Database Transactions

Policies affect how update and delete operations behave within transactions to maintain data integrity.

Knowing transactions clarifies how multiple changes are safely applied or rolled back when policies allow or block actions.

Legal Compliance (e.g., GDPR)

Policies help enforce data access rules required by privacy laws by restricting who can modify or delete personal data.

Recognizing this connection shows how technical policies support legal responsibilities and user trust.

Common Pitfalls

#1 Allowing updates without ownership checks

Wrong approach:

CREATE POLICY "Allow update" ON table FOR UPDATE TO authenticated USING (true);

Correct approach:

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

Root cause:

Not restricting updates to owners lets any authenticated user change any record, risking data corruption.

#2 Forgetting to create delete policies

Wrong approach:

No delete policy created, so delete requests are sent without checks.

Correct approach:

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

Root cause:

Assuming delete actions are allowed by default leads to blocked deletes or security gaps.

#3 Writing overly complex conditions in policies

Wrong approach:

CREATE POLICY "Complex update" ON table FOR UPDATE TO authenticated USING ((auth.uid() = owner_id AND some_other_condition) OR (role = 'admin' AND another_condition));

Correct approach:

Split complex logic into multiple simpler policies or handle some checks in application code.

Root cause:

Trying to do too much in one policy causes errors and performance issues.

Key Takeaways

Update and delete policies in Supabase control who can change or remove data by checking conditions at the database level.

These policies protect your data by enforcing ownership and role-based permissions, preventing unauthorized modifications.

Update and delete policies are separate and must be written explicitly for each action to ensure proper security.

Complex policies can affect performance and security, so balance simplicity and thoroughness when designing them.

Understanding how policies work under the hood helps you build secure, reliable applications that protect user data.

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