Supabase RLS Hardening: A Practical Guide

Row Level Security (RLS) is PostgreSQL’s answer to fine-grained access control. Supabase exposes it. Every table should have RLS. Most AI-generated codebases ship without it. This is the guide to fixing that.

What is RLS and why does it matter?

RLS policies are SQL rules that run on every query. They decide which rows a user can read, insert, update, or delete. Without RLS, any authenticated user can read or modify any row in any table. With RLS, users can only access the rows the policies allow.

In a Lovable or Bolt-generated app, the typical pattern is:

• RLS is enabled (Supabase turns it on by default).
• The policies are overly permissive — typically USING (true) or USING (auth.role() = 'authenticated'), which means any logged-in user can access any row.

This is a data breach waiting to happen.

The audit

For every table in your Supabase project, check:

1. Is RLS enabled? (SELECT relrowsecurity FROM pg_class WHERE relname = 'your_table')
2. What policies exist? (SELECT * FROM pg_policies WHERE tablename = 'your_table')
3. Does the policy use auth.uid()? If not, it’s probably wrong.
4. Can user B see user A’s data? (Test with two accounts.)

The hardening pattern

For every table that contains user-specific data, write policies like this:

-- Users can only see their own rows
CREATE POLICY "users_select_own" ON your_table
  FOR SELECT USING (auth.uid() = user_id);

-- Users can only insert their own rows
CREATE POLICY "users_insert_own" ON your_table
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Users can only update their own rows
CREATE POLICY "users_update_own" ON your_table
  FOR UPDATE USING (auth.uid() = user_id);

-- Users can only delete their own rows
CREATE POLICY "users_delete_own" ON your_table
  FOR DELETE USING (auth.uid() = user_id);

The key: every policy must reference auth.uid() and compare it to a column that identifies the row owner (typically user_id or owner_id).

Common mistakes

1. USING (true): this allows all authenticated users to see all rows. Never use this for user-specific tables.
2. USING (auth.role() = 'authenticated'): this is almost as bad — any logged-in user sees all rows.
3. No WITH CHECK on INSERT/UPDATE: without WITH CHECK, a user can insert or update rows that belong to other users.
4. Policies that call functions per-row: slow. Rewrite as joins or use SECURITY DEFINER functions for admin queries.
5. Missing RLS on the profiles table: the profiles table usually has all user data. If RLS is missing, every user can see every other user’s profile.

The admin exception

Some queries need to cross user boundaries (admin dashboards, analytics, support tools). For these, use SECURITY DEFINER functions:

CREATE OR REPLACE FUNCTION get_admin_stats()
RETURNS JSON
SECURITY DEFINER
AS $$
  -- This function runs with the privileges of the owner
  -- Add your own auth check here (e.g., check if the user is an admin)
  SELECT json_build_object(
    'total_users', (SELECT count(*) FROM profiles),
    'total_orders', (SELECT count(*) FROM orders)
  );
$$ LANGUAGE sql;

The function runs with the owner’s privileges, bypassing RLS. Add your own auth check inside the function.

Testing

After hardening, test with two user accounts:

1. User A creates a row.
2. User B logs in.
3. User B should NOT see User A’s row.
4. User B should NOT be able to update or delete User A’s row.

If any of these fail, the policy is wrong.

How to engage

If you need help auditing and hardening your Supabase RLS policies, the Supabase Consulting engagement is designed for this. RLS audit and hardening: USD 5K-15K. Full Supabase productionisation: USD 25K-100K.