Testing RLS policies in Supabase - Deep Dive

Overview - Testing RLS policies

What is it?

Row-Level Security (RLS) policies control who can see or change specific rows in a database table. Testing RLS policies means checking that these rules work correctly to protect data. It ensures users only access data they are allowed to see or modify. This is important for keeping data safe and private.

Why it matters

Without testing RLS policies, unauthorized users might see or change sensitive data, causing privacy breaches or data corruption. Testing helps catch mistakes early, preventing security problems that could harm users or businesses. It builds trust that the system enforces data access rules properly.

Where it fits

Before testing RLS policies, you should understand basic database concepts and how RLS policies are written. After testing, you can learn about monitoring security and automating tests for continuous protection. Testing RLS fits into the broader journey of securing cloud databases.

Mental Model

Core Idea

Testing RLS policies is like checking the locks on each door in a building to make sure only the right people can enter specific rooms.

Think of it like...

Imagine a hotel where each guest can only enter their own room. RLS policies are the electronic locks on each door, and testing them is trying the keys to confirm guests can’t open other rooms.

┌───────────────┐
│ Database Table│
├───────────────┤
│ Row 1: User A │
│ Row 2: User B │
│ Row 3: User C │
└─────┬─────────┘
      │
      ▼
┌───────────────────────┐
│ RLS Policy Checks User │
│ identity and row data  │
└─────────┬─────────────┘
          │
          ▼
┌───────────────────────┐
│ Access Granted or Denied│
└───────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Row-Level Security Basics

Concept: Learn what RLS policies are and how they control access to rows in a database table.

RLS policies are rules set on database tables that decide which rows a user can see or change. For example, a policy might say a user can only see rows where the 'owner_id' matches their user ID. This protects data by limiting access to only relevant rows.

Result

You understand that RLS policies filter data at the row level based on user identity or other conditions.

Knowing that RLS works by filtering rows helps you see why testing must check both allowed and blocked access cases.

2

Foundation

Setting Up RLS Policies in Supabase

Concept: Learn how to enable and write RLS policies in Supabase for a table.

In Supabase, you enable RLS on a table and write policies using SQL expressions. For example, a policy might be: 'USING (auth.uid() = owner_id)' which means only the owner can access their rows. You can create multiple policies for different actions like SELECT, INSERT, UPDATE, DELETE.

Result

You can create basic RLS policies that restrict access based on user ID.

Understanding how to write policies is essential before testing them, as tests verify these rules work as intended.

3

Intermediate

Testing RLS with Supabase Client

🤔Before reading on: do you think testing RLS requires admin access or can be done with normal user credentials? Commit to your answer.

Concept: Learn how to test RLS policies by simulating user requests with different credentials using Supabase client libraries.

You test RLS by connecting as different users and trying to read or modify rows. For example, log in as User A and try to select rows owned by User B. The query should return no rows. Then try selecting User A's rows, which should succeed. This confirms the policy works.

Result

You can verify that users only access rows allowed by the RLS policies.

Testing with real user credentials reveals if policies correctly enforce access or if gaps exist.

4

Intermediate

Writing Automated RLS Tests

🤔Before reading on: do you think manual testing is enough for RLS policies in production? Commit to your answer.

Concept: Learn to write automated tests that run queries as different users to check RLS enforcement continuously.

Use testing frameworks to run queries with various user tokens. For example, write tests that assert User A can read their rows but not others. Automate these tests to run on every code change to catch policy breaks early.

Result

You have repeatable tests that ensure RLS policies stay correct over time.

Automated tests prevent accidental policy changes from exposing data, improving security reliability.

5

Advanced

Testing Complex RLS Conditions

🤔Before reading on: do you think RLS policies can handle multi-condition rules like roles and ownership combined? Commit to your answer.

Concept: Learn how to test RLS policies that use multiple conditions, such as user roles and ownership checks together.

Some policies allow access if a user is an admin or owns the row. Testing must cover all cases: admin users accessing any row, regular users accessing only their rows, and others denied. Write tests for each scenario to confirm correct behavior.

Result

You can verify that complex policies correctly differentiate users by role and ownership.

Testing all logical branches in policies prevents security holes from overlooked conditions.

6

Expert

Detecting RLS Policy Bypass Risks

🤔Before reading on: do you think RLS policies alone guarantee full data protection? Commit to your answer.

Concept: Understand how misconfigurations or other database features can bypass RLS and how to test for these risks.

RLS can be bypassed if users have elevated privileges or if policies are too permissive. Test by trying to access data with different roles and permissions, including direct SQL access. Also, check for missing policies on all operations. Use audit logs to detect unexpected access.

Result

You identify potential bypasses and fix them before attackers exploit them.

Knowing RLS limits and testing for bypasses is critical for real-world security, beyond just writing policies.

Under the Hood

RLS policies work by adding a filter condition to every query on a table. When a user runs a query, the database automatically applies the policy's condition to limit rows. This happens inside the database engine before returning results, ensuring unauthorized rows never leave the database.

Why designed this way?

RLS was designed to enforce security at the database level, independent of application code. This prevents mistakes or bypasses in apps and centralizes access control. The filter approach is efficient and flexible, allowing complex conditions based on user identity or other data.

┌───────────────┐
│ User Query    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database Engine│
│ applies RLS   │
│ filter policy │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Filtered Rows │
│ returned only │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think RLS policies protect data even if a user has admin database rights? Commit yes or no.

Common Belief: RLS policies protect data from all users, including admins.

Reality: Database admins can bypass RLS policies because they have full access rights.

Why it matters: Assuming RLS protects admins can lead to sensitive data exposure if admin accounts are compromised.

Quick: Do you think testing RLS once manually is enough for long-term security? Commit yes or no.

Common Belief: Once RLS policies are tested manually, they will always work correctly.

Reality: RLS policies can break after changes; continuous automated testing is needed to maintain security.

Why it matters: Without ongoing tests, policy changes or database updates can accidentally expose data.

Quick: Do you think RLS policies automatically apply to all database operations like SELECT, INSERT, UPDATE, DELETE? Commit yes or no.

Common Belief: RLS policies apply to all operations by default.

Reality: RLS policies must be explicitly defined for each operation; missing policies can allow unauthorized actions.

Why it matters: Incomplete policies can let users modify or delete data they shouldn't, causing data loss or corruption.

Quick: Do you think RLS policies can fully replace application-level security? Commit yes or no.

Common Belief: RLS policies alone are enough to secure all data access.

Reality: RLS is one layer; application security and network controls are also needed for full protection.

Why it matters: Relying only on RLS can leave gaps if other security layers are weak.

Expert Zone

1

RLS policies can interact unexpectedly with database roles and grants, requiring careful testing of combined permissions.

2

Performance can degrade if RLS policies are complex; experts optimize policies and indexes to maintain speed.

3

Supabase's auth.uid() function depends on JWT tokens; understanding token structure helps debug RLS failures.

When NOT to use

Avoid relying solely on RLS for multi-tenant applications with complex sharing rules; consider row-level encryption or separate schemas instead.

Production Patterns

In production, teams automate RLS tests in CI pipelines, use audit logging to monitor access, and combine RLS with API gateways for defense in depth.

Connections

Access Control Lists (ACLs)

Both control access but ACLs work at resource level, RLS at data row level.

Understanding ACLs helps grasp why RLS adds finer-grained security inside databases.

Unit Testing in Software Development

Testing RLS policies is like unit testing code functions to verify behavior.

Knowing testing principles from software helps design effective automated RLS tests.

Physical Security Locks

RLS policies act like locks on data rows, controlling who can enter (access) each.

Recognizing security as layered locks clarifies why testing each lock matters.

Common Pitfalls

#1 Assuming RLS policies apply to all operations without defining them.

Wrong approach:

CREATE POLICY "select_policy" ON table FOR SELECT USING (auth.uid() = owner_id);

Correct approach:

CREATE POLICY "select_policy" ON table FOR SELECT USING (auth.uid() = owner_id);
CREATE POLICY "update_policy" ON table FOR UPDATE USING (auth.uid() = owner_id);

Root cause:

Believing one policy covers all actions leads to missing protections on updates or deletes.

#2 Testing RLS only with admin credentials or without simulating real users.

Wrong approach:

Running queries as a superuser to check data access.

Correct approach:

Running queries as different authenticated users with limited permissions to verify access.

Root cause:

Not simulating real user contexts misses how RLS filters data per user.

#3 Writing overly broad RLS policies that allow too much access.

Wrong approach:

CREATE POLICY "open_policy" ON table FOR SELECT USING (true);

Correct approach:

CREATE POLICY "owner_only" ON table FOR SELECT USING (auth.uid() = owner_id);

Root cause:

Misunderstanding policy conditions leads to ineffective security.

Key Takeaways

Row-Level Security policies protect data by filtering rows based on user identity or conditions.

Testing RLS policies requires simulating real user access to confirm correct data visibility and modification rights.

Automated tests are essential to maintain RLS policy effectiveness as systems evolve.

RLS policies must be defined for each database operation to fully secure data.

Understanding RLS internals and limitations helps prevent security bypass and performance issues.

Practice

1. What is the main purpose of Row Level Security (RLS) policies in Supabase?

easy

A. To encrypt data stored in the database

B. To speed up database queries by indexing rows

C. To backup data automatically every hour

D. To control which rows a user can access or modify in a table

Solution

1. Step 1: Understand RLS purpose

   RLS policies restrict access to rows based on user identity or conditions.

2. Step 2: Compare options

   Only To control which rows a user can access or modify in a table describes controlling access to rows per user, which is the core of RLS.

3. Final Answer:

   To control which rows a user can access or modify in a table -> Option D

4. Quick Check:

   RLS controls row access = A [OK]

Hint: RLS controls data visibility per user, not performance or backups [OK]

Common Mistakes:

• Confusing RLS with indexing or encryption
• Thinking RLS handles backups
• Assuming RLS speeds up queries

2. Which SQL command correctly enables Row Level Security on a table named profiles in Supabase?

easy

A. ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

B. ALTER TABLE profiles SET ROW LEVEL SECURITY ON;

C. ALTER TABLE profiles ENABLE RLS;

D. ALTER TABLE profiles ENABLE ROW_LEVEL_SECURITY;

Solution

1. Step 1: Recall correct syntax for enabling RLS

   The correct SQL command is ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;.

2. Step 2: Match options with syntax

   Only ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; matches the exact syntax supported by Supabase/PostgreSQL.

3. Final Answer:

   ALTER TABLE profiles ENABLE ROW LEVEL SECURITY; -> Option A

4. Quick Check:

   Enable RLS syntax = A [OK]

Hint: Use exact phrase ENABLE ROW LEVEL SECURITY after ALTER TABLE [OK]

Common Mistakes:

• Using 'RLS' shorthand incorrectly
• Using underscores incorrectly
• Using SET instead of ENABLE

3. Given this RLS policy on messages table:

CREATE POLICY "Users can view their messages" ON messages
FOR SELECT USING (user_id = auth.uid());

What will happen if user with auth.uid() = '123' runs SELECT * FROM messages;?

medium

A. They will see all messages regardless of user_id

B. They will see only messages where user_id = '123'

C. They will see no messages because policy blocks all

D. They will get a syntax error

Solution

1. Step 1: Understand the policy condition

   The policy allows SELECT only if user_id = auth.uid(). For user '123', only rows with user_id '123' are visible.

2. Step 2: Predict query result

   The SELECT query returns only messages owned by user '123'. No syntax errors occur.

3. Final Answer:

   They will see only messages where user_id = '123' -> Option B

4. Quick Check:

   Policy filters rows by user_id = auth.uid() = B [OK]

Hint: Policy filters rows by user_id matching auth.uid() [OK]

Common Mistakes:

• Assuming all rows are visible
• Thinking policy blocks all rows
• Confusing syntax error with policy effect

4. You wrote this RLS policy:

CREATE POLICY "Allow updates" ON profiles
FOR UPDATE USING (user_id = auth.uid());

But users report they cannot update their profiles. What is the likely problem?

medium

A. auth.uid() returns NULL causing policy to fail

B. RLS is not enabled on the profiles table

C. The policy should use WITH CHECK instead of USING

D. The policy name is invalid

Solution

1. Step 1: Understand difference between USING and WITH CHECK

   USING controls which rows can be selected or updated; WITH CHECK controls which updated rows are allowed after update.

2. Step 2: Identify update permission issue

   For UPDATE, WITH CHECK is needed to allow the new row values. Using only USING blocks updates even if row is accessible.

3. Step 3: Check other options

   RLS must be enabled, but question implies it is. auth.uid() returning NULL is unlikely if user is logged in. Policy name is valid.

4. Final Answer:

   The policy should use WITH CHECK instead of USING -> Option C

5. Quick Check:

   Update policies need WITH CHECK = A [OK]

Hint: Use WITH CHECK for update permission, not just USING [OK]

Common Mistakes:

• Using only USING clause for updates
• Forgetting to enable RLS
• Assuming policy name affects behavior

5. You want to test if your RLS policy on orders table correctly restricts users to only see their own orders. Which approach is best to verify this?

hard

A. Run SELECT * FROM orders; as different authenticated users and check results

B. Disable RLS temporarily and check if all orders are visible

C. Check the policy text only without running queries

D. Run SELECT * FROM orders; as the database admin user

Solution

1. Step 1: Understand testing RLS requires simulating user access

   Testing RLS means running queries as different users to see if access is limited correctly.

2. Step 2: Evaluate options

   Run SELECT * FROM orders; as different authenticated users and check results runs queries as different users, which tests the policy effectively. Disable RLS temporarily and check if all orders are visible disables RLS, which doesn't test policy. Check the policy text only without running queries only reads policy text, no test. Run SELECT * FROM orders; as the database admin user uses admin user who bypasses RLS.

3. Final Answer:

   Run SELECT * FROM orders; as different authenticated users and check results -> Option A

4. Quick Check:

   Test RLS by querying as different users = D [OK]

Hint: Test RLS by querying as different users, not admin [OK]

Common Mistakes:

• Testing only as admin user
• Disabling RLS to test policies
• Not running actual queries