Supabase Row Level Security Policy Design — Access Control Configuration to Prevent Per-Table Data Exposure

When you first start a project with Supabase, the speed of setup is almost surprising — but that very convenience hides a pitfall. That pitfall is Row Level Security (RLS). When I started out, I created a table in the dashboard, fired off a query from the client, data came back fine, and I thought "great, we're done!" — only to discover later that RLS was turned off, meaning anyone could read the entire dataset. A cold sweat ran down my back.

This isn't just my experience. In January 2025, over 170 apps built with Lovable suffered a real incident where their entire databases were exposed due to missing RLS configuration. Users' personal information and internal business data were readable by anyone through the public API, and those services had to take emergency action. This is exactly why Supabase listed "safer defaults" as its top priority in its 2025 security retrospective.

What sets this post apart from other RLS documentation is that it doesn't stop at explaining concepts — it also covers policy code for real-world scenarios like multi-tenant isolation, RBAC, and public reads, along with the pitfalls you'll run into if you just copy-paste that code blindly. Whether you're new to Supabase or already using it but unsure whether your policies are correct, there's something here for you.

Core Concepts

What RLS Does: The Database Enforces Security Directly

RLS is a PostgreSQL feature that applies access control at the row level for a table. There's a reason Supabase adopted it as its default security model: clients (browsers, mobile apps) send queries directly to the database. Even if someone bypasses your application code, the database itself enforces security — which makes a secure design possible without a separate server middleware layer.

The mechanics are straightforward. When a query hits an RLS-enabled table, PostgreSQL converts the defined policies into implicit WHERE clauses and executes them. Think of the policies you write as filters that automatically attach to every query, and everything else follows naturally.

Key behavioral rule: If you enable RLS but create no policies, every query returns an empty result. It doesn't throw an error — it just looks like there's no data — which makes debugging tricky. Conversely, if you disable RLS, every row in the table is exposed via the API.

USING vs. WITH CHECK: Understanding the Two Expressions

Understanding these two expressions that make up a policy makes everything else fall into place.

There's one easy-to-miss behavioral rule: even if you specify WITH CHECK on a FOR ALL policy, WITH CHECK does not apply to DELETE. Filtering rows to be deleted is handled solely by USING.

-- Basic ownership policy: only access your own data
CREATE POLICY "users_own_data"
ON todos
FOR ALL
TO authenticated
USING (auth.uid() = user_id)       -- read/update/delete: own rows only
WITH CHECK (auth.uid() = user_id); -- write/update: must be saved as own

Why an UPDATE Policy Doesn't Work on Its Own

PostgreSQL's UPDATE implementation first performs a SELECT to locate the target row. This means if there's no SELECT policy, UPDATE won't work either. Always create both policies together to be safe. Before I understood this, I spent a long time scratching my head wondering "I clearly created the UPDATE policy, why isn't it working?"

JWT Claims: app_metadata vs. user_metadata

When implementing RBAC (role-based access control), understanding Supabase's JWT structure is essential. There are two metadata fields in the JWT, and which one to use for role and permission decisions is the key question.

Role and permission information must always be stored in app_metadata and designed to be modified only through the Supabase Admin API.

Practical Application

Example 1: Personal Data Ownership — Read, Write, and Edit Only Your Own Data

This is the most fundamental and widely used pattern. It's well-suited for tables holding user personal information, like a profiles table.

-- Enable RLS (must be included in migration files)
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
 
-- Read only your own profile
CREATE POLICY "read_own_profile"
ON profiles FOR SELECT
TO authenticated
USING (auth.uid() = id);
 
-- Create only your own profile (on first signup)
CREATE POLICY "insert_own_profile"
ON profiles FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = id);
 
-- Update only your own profile
CREATE POLICY "update_own_profile"
ON profiles FOR UPDATE
TO authenticated
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

When I started out, I only created a SELECT policy and spent a long time wondering "why can't I update?" — it's easy to miss that INSERT and UPDATE policies must be created separately.

Example 2: Multi-Tenant Isolation — Separating Data by Organization in SaaS

This is a case you'll inevitably encounter when building SaaS in practice. Multiple organizations share the same table, but each organization must only see its own data.

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY "tenant_isolation"
ON tenant_data FOR ALL
TO authenticated
USING (
  tenant_id = (
    SELECT tenant_id FROM users WHERE users.id = auth.uid()
  )
)
WITH CHECK (
  tenant_id = (
    SELECT tenant_id FROM users WHERE users.id = auth.uid()
  )
);

Performance warning: Policies that include subqueries like the one above can cause the subquery to be re-evaluated for each row, potentially creating performance issues on large tables. Adding indexes on tenant_id and users.id columns is the first step. A safer approach is to encapsulate the permission logic in a SECURITY DEFINER function.

-- Encapsulate tenant lookup in a SECURITY DEFINER function
CREATE OR REPLACE FUNCTION get_user_tenant_id()
RETURNS uuid
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
  SELECT tenant_id FROM users WHERE id = auth.uid();
$$;
 
-- Policy using the function (more readable and reusable)
CREATE POLICY "tenant_isolation_v2"
ON tenant_data FOR ALL
TO authenticated
USING (tenant_id = get_user_tenant_id())
WITH CHECK (tenant_id = get_user_tenant_id());

This approach makes policies more readable, and has the added benefit of letting you reuse the same lookup logic across multiple table policies.

Example 3: RBAC — Role-Based Access Control

This is the pattern to use when you want only administrators to be able to perform certain actions. There are two approaches depending on where you source the role information from.

Approach A: Reference a roles table

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
 
-- Only admins can update posts
CREATE POLICY "admin_update_posts"
ON posts FOR UPDATE
TO authenticated
USING (
  auth.uid() IN (
    SELECT user_id FROM user_roles WHERE role = 'admin'
  )
);

Approach B: Use JWT app_metadata claims (when fast lookups are needed)

ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
 
-- Check role from app_metadata in the JWT
CREATE POLICY "editor_can_write"
ON articles FOR INSERT
TO authenticated
WITH CHECK (
  (auth.jwt() -> 'app_metadata' ->> 'role') = 'editor'
);

Example 4: Public Read + Authenticated Write Only

This pattern is for scenarios like blog posts — anyone can read, but only logged-in users can write. The key is granting a SELECT policy to the anon role (unauthenticated users).

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
 
-- Anyone, including anon, can read
CREATE POLICY "public_read"
ON posts FOR SELECT
USING (true);
 
-- Only authenticated users can write under their own name
CREATE POLICY "auth_insert"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = author_id);

Testing Policies: Verifying Behavior Directly with the Client SDK

The SQL Editor in the dashboard bypasses RLS. To confirm that policies behave as intended, you must run both logged-in and logged-out scenarios using supabase-js.

import { createClient } from '@supabase/supabase-js';
 
const supabase = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);
 
// 1. Attempt to read while unauthenticated (validates anon policy)
const { data: anonData, error: anonError } = await supabase
  .from('posts')
  .select('*');
console.log('anon read:', anonData, anonError);
 
// 2. After login, verify only own data is returned
await supabase.auth.signInWithPassword({ email, password });
const { data: ownData } = await supabase
  .from('profiles')
  .select('*');
console.log('authenticated read:', ownData); // should return only own rows
 
// 3. Attempt INSERT with another user's ID (should be blocked)
const { error: insertError } = await supabase
  .from('profiles')
  .insert({ id: 'other-user-id', name: 'test' });
console.log('blocked insert error:', insertError); // an error here is correct

Pros and Cons

Advantages

Disadvantages and Caveats

First, a note on the service_role key: this key carries superuser privileges that completely bypass RLS. It's used for admin-only operations that can't be handled with ordinary user permissions, such as processing completed payments or privilege escalation — and it must only ever be used in server-side code (Edge Functions, etc.) and must never be exposed to clients.

There are also tools that automatically catch missing security configurations or overly permissive policies. The Security Advisor in the Supabase Dashboard does exactly this — powered by Splinter, an open-source PostgreSQL security linter, it automatically scans for misconfigurations and exposed sensitive columns. Running it periodically on your project is quite useful in practice for catching mistakes before they become problems.

The Most Common Mistakes in Practice

1. Forgetting to enable RLS: If you omit ALTER TABLE ... ENABLE ROW LEVEL SECURITY; after creating a table, all data is public. As of 2025, Supabase displays warning labels in the Dashboard and sends email alerts — but the habit of explicitly including it in migration files is far safer.

2. Enabling RLS without any policies: Every query returns an empty result with no error message, making it look like "the data disappeared." It's better practice to create at least one policy alongside enabling RLS.

3. Overusing USING (true): Using this with the intent of "just opening things up for now" makes all rows accessible to every authenticated user. Use it only on tables that genuinely require public reads, and only on SELECT.

4. Using user_metadata claims for permission checks: This is a field the user can modify directly from the client, making it vulnerable for role and permission decisions. Role information must always be stored in app_metadata and designed to be modified only through the Supabase Admin API.

Closing Thoughts

RLS is automatically appending a WHERE clause to your queries. Keep just that one sentence in mind, and designing policies becomes far more intuitive.

Honestly, at the start I was confused by the difference between USING and WITH CHECK, and I spent a long time struggling with "why isn't data coming back after I created the policy?" But once you understand the underlying mechanics, you can approach it as naturally as writing SQL — and knowing that your security configuration lives at the DB level rather than in your code, giving you peace of mind no matter what a client does, is the true strength of RLS.

Three steps you can start with right now:

1. Audit the RLS status of your current project — if I were you, I'd start here. Check the Supabase Dashboard > Table Editor for any tables showing warning labels, or look at the Security Advisor tab to review Splinter-based automatic scan results.

2. Add RLS configuration explicitly to your migration files — run supabase db diff to export the current policy state to a file, then verify that ENABLE ROW LEVEL SECURITY and policy creation SQL are included in your migrations.

3. Always test policies using the client SDK — using a supabase-js snippet like the one shown earlier, verify directly that data is visible or blocked as intended in both authenticated and unauthenticated states.

References

• Row Level Security | Supabase Official Docs
• RLS Performance and Best Practices | Supabase Troubleshooting
• Supabase Security Retro: 2025
• Security & Performance Advisor | Supabase Features
• Performance and Security Advisors | Supabase Docs
• Custom Claims & RBAC | Supabase Docs
• AI Prompt: Database RLS Policies | Supabase Docs