Complete Guide to Supabase Row Level Security

What is Row Level Security?

Row Level Security (RLS) is a PostgreSQL feature that allows you to define policies controlling which rows a user can access in a given table. In Supabase, RLS is the primary mechanism that protects your data from unauthorized access via the public API. Without RLS enabled, any user with your anon key can read, insert, update, or delete every row in your tables.

Why RLS is Critical in Supabase

Supabase exposes your PostgreSQL database directly through PostgREST. Every table is accessible via the REST API unless you explicitly restrict access. This is fundamentally different from traditional backends where a server-side application mediates all database access. If you forget to enable RLS on even one table, that table is wide open.

-- DANGER: Without RLS, this table is publicly accessible
CREATE TABLE user_profiles (
  id uuid REFERENCES auth.users(id),
  email text,
  full_name text,
  billing_address text
);

-- STEP 1: Always enable RLS
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

Core Policy Patterns

Owner-based Access

The most common pattern restricts rows to the user who owns them:

-- Users can only see their own profile
CREATE POLICY "Users can view own profile"
  ON user_profiles
  FOR SELECT
  USING (auth.uid() = id);

-- Users can only update their own profile
CREATE POLICY "Users can update own profile"
  ON user_profiles
  FOR UPDATE
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

The USING clause filters which existing rows are visible. The WITH CHECK clause validates new or updated rows. For UPDATE and INSERT, you typically need both.

Role-based Access

For applications with admin or moderator roles, store roles in a table and reference them in policies:

CREATE POLICY "Admins can view all profiles"
  ON user_profiles
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_roles.user_id = auth.uid()
      AND user_roles.role = 'admin'
    )
  );

Organization / Team-based Access

When users belong to organizations, check membership:

CREATE POLICY "Team members can view team data"
  ON team_documents
  FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members
      WHERE user_id = auth.uid()
    )
  );

Common Pitfalls

1. Forgetting RLS on New Tables

Every new table must have RLS enabled. Add it immediately after CREATE TABLE. A table without RLS and without policies is fully accessible to all API roles.

2. Missing WITH CHECK on INSERT/UPDATE

-- BAD: Users can insert rows for other users
CREATE POLICY "Users can insert"
  ON user_profiles
  FOR INSERT
  USING (true);  -- This does NOT restrict inserts

-- GOOD: Enforce ownership on insert
CREATE POLICY "Users can insert own profile"
  ON user_profiles
  FOR INSERT
  WITH CHECK (auth.uid() = id);

3. Using auth.uid() on Tables Without User Context

Service role calls bypass RLS entirely. If your Edge Functions use the service role key, RLS policies are not evaluated. This is by design but can be surprising.

4. Overly Permissive SELECT Policies

-- BAD: Leaks all user data to any authenticated user
CREATE POLICY "Authenticated users can read"
  ON user_profiles
  FOR SELECT
  USING (auth.role() = 'authenticated');

-- GOOD: Only own data
CREATE POLICY "Users read own data"
  ON user_profiles
  FOR SELECT
  USING (auth.uid() = id);

5. Not Restricting DELETE

If you have policies for SELECT, INSERT, and UPDATE but not DELETE, and RLS is enabled, then by default no one can delete. This is safe. But if you add a permissive DELETE policy, ensure it is scoped correctly.

Performance Considerations

RLS policies add a filter to every query. Subqueries in policies can cause performance issues at scale. Strategies to mitigate this:

• Use security definer functions to cache role lookups
• Add indexes on columns referenced in policies (e.g., user_id, team_id)
• Avoid correlated subqueries in high-traffic policies; consider materialized views or denormalized columns

-- Create a security definer function for role checks
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role = 'admin'
  );
$$;

-- Use in policies for cleaner, potentially cached evaluation
CREATE POLICY "Admin full access"
  ON sensitive_data
  FOR ALL
  USING (is_admin());

Testing Your Policies

Always test RLS policies before deploying. Use Supabase's SQL editor to impersonate roles:

-- Test as a specific user
SET request.jwt.claim.sub = 'user-uuid-here';
SET role = 'authenticated';

-- Try to read data you should not have access to
SELECT * FROM user_profiles;

-- Reset
RESET role;
RESET request.jwt.claim.sub;

Automated Scanning

Manually auditing RLS across dozens of tables is error-prone. Use AuditYour.app to automatically detect tables with missing RLS, overly permissive policies, and missing WITH CHECK clauses. The scanner connects to your Supabase project and tests real API calls against every table to verify that policies are enforced correctly.

Summary Checklist

1. Enable RLS on every table: ALTER TABLE ... ENABLE ROW LEVEL SECURITY;
2. Write explicit policies for SELECT, INSERT, UPDATE, and DELETE
3. Always include WITH CHECK on INSERT and UPDATE policies
4. Scope policies to the minimum necessary access
5. Index columns used in policy expressions
6. Test policies by impersonating different roles
7. Regularly audit with automated tools