Supabase: Debugging RLS Policies (Row Level Security)

1. "Data Exists, But I See Nothing?"

In the Supabase dashboard, the users table is full of 100 rows. But when you run select() in your Flutter app, you get an empty list ([]). No errors. It just pretends the data "doesn't exist".

final data = await supabase.from('users').select();
print(data); // []

"At least give me an error!" The culprit behind this chilling phenomenon is RLS (Row Level Security). Supabase follows the philosophy: "If you are not explicitly allowed, you cannot see anything."

2. Principle: Implicit Deny

The moment you Enable RLS, all requests are blocked by default. It acts like a firewall. Unless you punch a hole, nothing passes through. You must add a policy like "Enable Read Access to Everyone" for data to be visible.

Postgres permission system is simple:

1. Table Level Permissions (Select, Insert, etc.) -> Supabase handles this.
2. Row Level Permissions (RLS) -> This is the problem.

When RLS is on, Postgres filters out rows that don't match the policy conditions from the query result. It doesn't say "Access Denied"; it lies and says "There are 0 rows for you." (Hiding existence for security). That's why you get an empty result instead of an error.

3. Problem 1: Missing SELECT Policy (Most Common)

This is the most common mistake. You created a policy to insert data (INSERT), but forgot the policy to read it (SELECT).

-- ❌ Half-baked Policy (Write Only)
CREATE POLICY "Users can insert their own profile" 
ON public.profiles FOR INSERT 
WITH CHECK (auth.uid() = user_id);

With this, you can sign up and create a profile, but when you try to view it, you get an empty array. "Why can't I see the post I just wrote?"

Solution: You must add a SELECT policy.

-- ✅ Add Read Policy
CREATE POLICY "Users can view their own profile" 
ON public.profiles FOR SELECT 
USING (auth.uid() = user_id);

What if you want "I see my own profile, others see only my name"? You need two policies. It gets complex. So usually, we make the profiles table public (Read Access for Everyone) and move sensitive info (email, phone) to a separate table like private_profiles.

4. Problem 2: anon vs authenticated Roles

"I want guests (non-logged-in users) to see posts too." Be careful with the Target roles setting. By default, it's often set to authenticated (logged-in users).

• authenticated: Applies only to logged-in users (Valid JWT).
• anon: Applies only to guest users (No JWT).
• public (Default): Applies to EVERYONE.

If you want everyone to see it, leave the Role empty or set it to public. A policy marked TO authenticated acts as if the data doesn't exist for non-logged-in users.

5. Advanced: USING vs WITH CHECK

There are two conditional clauses in policies. Confusing them creates security holes.

1. USING: Used when checking existing rows (SELECT, UPDATE, DELETE). "Am I allowed to touch this row?"
2. WITH CHECK: Used when validating new data (INSERT, UPDATE). "Am I allowed to write this data?"

For UPDATE (Uses Both):

• USING: "Is this my post?" (Target selection) -> auth.uid() == user_id
• WITH CHECK: "Is the new content valid?" (Post-change validation) -> new.role != 'admin'

What if you use USING without WITH CHECK? You can take "my post (USING passes)" and "change user_id to someone else". The ownership of the post is transferred.

6. Deep Dive: Security Definer (Bypassing RLS)

RLS is powerful but sometimes gets in the way. For example, a trigger that "adds a row to profiles on signup". Right after signup, the user might still be anon or the permission check might be tricky.

This is where Postgres Functions and SECURITY DEFINER come in.

-- This function runs not with the Caller's (Invoker) privileges,
-- but with the Creator's (Definer: usually Admin) privileges.
CREATE OR REPLACE FUNCTION create_user_profile()
RETURNS TRIGGER 
SECURITY DEFINER 
AS $
BEGIN
  INSERT INTO public.profiles (user_id) VALUES (new.id);
  RETURN new;
END;
$ LANGUAGE plpgsql;

Using SECURITY DEFINER allows you to bypass RLS and manipulate data with admin privileges. However, abuse can lead to security holes, so use it only for essential system logic.

7. Deep Dive: RLS Performance (Indexing)

RLS policies are essentially SQL WHERE clauses. A policy auth.uid() = user_id means executing WHERE user_id = '...' for every query.

This means if the user_id column is not indexed, performance will tank. You won't notice with small data, but with 100k+ rows, SELECT * FROM posts becomes incredibly slow. It has to Full Scan every row asking "Is this yours?".

Always index columns used in RLS conditions.

8. Case Study: The Admin Dashboard Trap

I was building an Admin Dashboard (view all users), but encountered a situation where "I'm definitely logged in as Admin, but I see 0 rows". It turned out to be my RLS policy.

-- Old Policy: Users can only see their own data
CREATE POLICY "Users can see own data" ON users
FOR SELECT USING (auth.uid() = id);

Because of this, even the Admin could only see "their own data (1 row)". Supabase doesn't have a magic is_admin flag. Admins are just authenticated users. The solution was adding an OR condition.

CREATE POLICY "Users can see own data OR Admin sees all" ON users
FOR SELECT USING (
  auth.uid() = id 
  OR 
  (SELECT role FROM profiles WHERE id = auth.uid()) = 'admin'
);

Now Admins can see everything. Lesson: RLS has no exceptions. You must explicitly create a "Backdoor Policy" for Admins.

9. Debugging Tips: Acting as Role

To test permission issues in the Supabase SQL Editor:

-- Act as anonymous user (Test guest access)
SET ROLE anon;
SELECT * FROM posts; -- If empty, check anon policy

-- Act as logged-in user
-- (Hard to simulate JWT, but you can hardcode your UUID in RLS policy for testing)

Or better, use the "Policy Tester" feature in the Supabase Dashboard to simulate queries as a specific User ID.

10. Glossary

• RLS (Row Level Security): A Postgres feature that restricts access to rows based on user roles and attributes.
• Security Definer: A setting that makes a function execute with the privileges of the user who created it (usually Administrator).
• Implicit Deny: The security principle where everything is denied by default unless explicitly allowed.
• JWT (JSON Web Token): The token used by Supabase Auth to identify users. RLS policies often check claims inside this token.

11. FAQ: Common Pitfalls

Q: I enabled RLS but still see everything. A: Check if you are using the service_role key in your backend. Only the anon and public keys are subject to RLS. The Service Role key bypasses RLS entirely.

Q: Can I use RLS strictly for one column? A: No, RLS is Row Level. For Column Level Security (e.g., hiding just the email address), you need to move that column to a separate table (e.g., private_profiles) or use a View.

Q: My policy logic is too complex. A: If you have 10 OR conditions, performance will tank. Wrap complex logic in a Postgres Function and call that function in your policy. USING ( is_admin(auth.uid()) ).

12. Summary for MFA

If you use Multi-Factor Authentication: You can restrict access to "MFA Verified" sessions only.

-- Only allow if user has verified MFA at least level 2
CREATE POLICY "Sensitive Data"
ON secrets
USING (auth.jwt() ->> 'aal' = 'aal2');

This adds a layer of security for sensitive tables (e.g., Billing Info).

13. Summary

1. RLS means Block-by-default.
2. Empty Array = Missing SELECT Policy.
3. Know the difference: USING vs WITH CHECK.
4. Index your RLS columns for performance.

Supabase is passive-aggressive. Silence means Access Denied.