Supabase RLS Audit Checklist

Supabase RLS Audit Checklist

Row Level Security is the primary authorization mechanism for Supabase applications. A single misconfigured policy can expose your entire database through the auto-generated REST API. This checklist provides a systematic approach to auditing RLS.

Step 1: Inventory All Tables

Start by listing every table in the public schema and its RLS status:

SELECT
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Every table with rowsecurity = false is fully accessible to anyone with the anon or authenticated key. Enable RLS immediately on any unprotected table:

ALTER TABLE public.my_table ENABLE ROW LEVEL SECURITY;

Note that enabling RLS without adding any policies will deny all access by default, which is a safe starting point.

Step 2: Review Existing Policies

List all policies for every table:

SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Look for red flags:

• qual = 'true' or with_check = 'true': These allow unrestricted access and are almost never correct for user-facing tables.
• Policies granted to anon for INSERT, UPDATE, or DELETE: The anonymous role should rarely have write access.
• Missing with_check on INSERT/UPDATE policies: Without a WITH CHECK clause, users may insert rows they cannot later read, or modify fields they should not control.

Step 3: Test with Multiple User Contexts

Use the Supabase client to impersonate different users and verify that each user can only see and modify their own data:

// Sign in as User A
const { data } = await supabase
  .from('documents')
  .select('*');
// Should only return User A's documents

// Attempt to read User B's document by ID
const { data: other } = await supabase
  .from('documents')
  .select('*')
  .eq('id', userBDocumentId);
// Should return empty array, not User B's document

Repeat for INSERT, UPDATE, and DELETE operations. Pay special attention to:

• Horizontal privilege escalation: Can User A update User B's row by providing User B's ID?
• Vertical privilege escalation: Can an authenticated user access admin-only rows?
• Field-level issues: Can a user set the role column to admin on INSERT or UPDATE?

Step 4: Audit SECURITY DEFINER Functions

Functions marked SECURITY DEFINER execute with the privileges of the function owner, which typically bypasses RLS. List them:

SELECT
  n.nspname AS schema,
  p.proname AS function_name,
  p.prosecdef AS security_definer
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.prosecdef = true;

For each SECURITY DEFINER function, verify that:

• The function performs its own authorization checks internally.
• Input parameters are validated and cannot be used for SQL injection.
• The function is not callable by the anon role unless explicitly intended.

Step 5: Junction Tables and Indirect Access

Junction tables (e.g., team_members linking users to teams) often need their own RLS policies. Without them, an attacker can:

• Add themselves to any team by inserting a row into the junction table.
• Enumerate team membership by reading the junction table.

CREATE POLICY "Members can read their own team memberships"
  ON public.team_members FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY "Only team admins can add members"
  ON public.team_members FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.team_members
      WHERE team_id = team_members.team_id
        AND user_id = auth.uid()
        AND role = 'admin'
    )
  );

Step 6: Realtime Subscriptions

Realtime subscriptions also respect RLS, but they filter at the row level after the database event fires. Test that subscribing to a table's changes does not leak rows belonging to other users. Use the browser DevTools Network tab to inspect WebSocket frames.

Step 7: Document and Automate

Add SQL comments to every policy explaining its intent. Set up a CI step or periodic scan (using AuditYour.app) to detect any table that has RLS disabled or uses overly permissive policies.