Supabase Row Level Security: The Patterns That Actually Work

TL;DR

Supabase Row Level Security is the single most important security layer in any Supabase application, and it is the one most developers get wrong. I have shipped RLS policies on uvin.lk↗, EuroParts Lanka, and every client project that runs through my services. Some of those policies worked flawlessly from day one. Others leaked data in ways that did not show up until production traffic exposed the gaps. This guide covers every RLS pattern I use today — SELECT, INSERT, UPDATE, DELETE — along with auth-based policies, role-based access, multi-tenant isolation, the performance implications nobody talks about, and the exact mistakes I made so you can skip the painful part.

What RLS Is and Why It Matters

Row Level Security is a PostgreSQL feature that lets you control which rows a user can access in a table. Instead of writing access control logic in your application code, you write it as SQL policies directly on the database. Every query that hits the table gets filtered through these policies automatically. No exceptions. No bypasses. No "I forgot to check permissions in that one API route."

Here is why that matters for Supabase specifically. When you use the Supabase client library, your frontend is talking directly to PostgreSQL through PostgREST. There is no backend server sitting between the browser and the database to enforce who can see what. RLS is that server. If you do not enable it, anyone with your Supabase URL and anon key — which are both exposed in your frontend code — can read every row in every table.

I am not being dramatic. I have seen production Supabase projects with RLS disabled on tables containing customer emails, order histories, and payment metadata. The developers assumed the anon key was secret. It is not. It is in your JavaScript bundle. Anyone can extract it from your deployed site in about thirty seconds.

The Supabase documentation on RLS↗ explains the basics well. What it does not do is show you which patterns survive real production traffic. That is what this guide is for.

Enabling RLS — The Critical First Step

Before you write a single policy, you need to enable RLS on every table that holds user data. Supabase does not enable it by default when you create tables through the SQL editor.

-- Enable RLS on your tables
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.comments ENABLE ROW LEVEL SECURITY;

Here is the part that catches people: enabling RLS without creating any policies means nobody can access the table. Not even authenticated users. RLS defaults to deny-all. This is actually the behavior you want — it forces you to be explicit about every access pattern.

There is one exception. The postgres role and any role with BYPASSRLS privilege can still access the table. Your Supabase service_role key bypasses RLS entirely. This is by design — your server-side code and Edge Functions that use the service role key can always reach the data. Never expose the service role key to the client.

-- Verify RLS is enabled on all your public tables
SELECT
  schemaname,
  tablename,
  rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Run this query regularly. I run it as part of my deployment checklist. If rowsecurity is false on any table that stores user data, you have a problem.

The Force Row Security Option

There is a lesser-known setting called FORCE ROW LEVEL SECURITY that applies policies even to the table owner:

ALTER TABLE public.profiles FORCE ROW LEVEL SECURITY;

I enable this on every table in production. Without it, the table owner role bypasses RLS, which can create subtle security holes if your migration scripts or admin functions run as the table owner.

SELECT Policies

SELECT policies control which rows a user can read. These are the policies you will write most often.

Public Read Access

Some tables should be readable by everyone — products in a store, published blog posts, public profiles:

-- Anyone can read published products
CREATE POLICY "Products are publicly readable"
  ON public.products
  FOR SELECT
  USING (status = 'published');

The USING clause is the filter. Every SELECT query against products gets this condition appended to its WHERE clause. If a product has status = 'draft', it simply does not exist as far as the query is concerned.

Authenticated-Only Read Access

For data that only logged-in users should see:

-- Only authenticated users can read orders
CREATE POLICY "Authenticated users can read own orders"
  ON public.orders
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

The TO authenticated clause restricts this policy to the authenticated role. The auth.uid() function returns the UUID of the currently authenticated user from the JWT. This is the bread-and-butter pattern for any user-owned data.

Selective Column Visibility

RLS operates at the row level, not the column level. If a user can see a row, they can see all columns. If you need to hide specific columns, use database views:

-- Create a view that excludes sensitive columns
CREATE VIEW public.public_profiles AS
  SELECT id, username, avatar_url, bio, created_at
  FROM public.profiles;

-- Enable RLS on the underlying table
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Allow public read on the view
-- (Views use the permissions of the view definer by default)
GRANT SELECT ON public.public_profiles TO anon, authenticated;

I use this pattern on EuroParts Lanka to let customers see product listings without exposing supplier pricing and cost margins.

INSERT/UPDATE/DELETE Policies

Read policies are straightforward. Write policies are where things get interesting and where most security holes live.

INSERT Policies

INSERT policies use a WITH CHECK clause instead of USING. The WITH CHECK expression is evaluated against the new row being inserted:

-- Users can only insert rows where they are the owner
CREATE POLICY "Users can insert own profiles"
  ON public.profiles
  FOR INSERT
  TO authenticated
  WITH CHECK (id = auth.uid());

-- Users can create orders for themselves
CREATE POLICY "Users can create own orders"
  ON public.orders
  FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

The critical difference: USING filters existing rows (for SELECT, UPDATE, DELETE). WITH CHECK validates new or modified rows (for INSERT, UPDATE). Mix these up and you will either leak data or block legitimate writes.

UPDATE Policies

UPDATE policies can use both USING and WITH CHECK. The USING clause determines which rows the user can target for update. The WITH CHECK clause validates the new values after the update:

-- Users can update their own profile, but cannot change ownership
CREATE POLICY "Users can update own profile"
  ON public.profiles
  FOR UPDATE
  TO authenticated
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

The double check matters. Without the WITH CHECK, a user could update their profile row and change the id column to someone else's UUID, effectively hijacking another profile. The WITH CHECK ensures the updated row still belongs to them.

-- Users can update their orders only if the order is still pending
CREATE POLICY "Users can update pending orders"
  ON public.orders
  FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid() AND status = 'pending')
  WITH CHECK (user_id = auth.uid());

This is a pattern I use on every e-commerce project. You can modify your order while it is pending. Once it moves to processing or shipped, the USING clause silently blocks any updates.

DELETE Policies

DELETE policies use USING only — there is no new row to check:

-- Users can delete their own comments
CREATE POLICY "Users can delete own comments"
  ON public.comments
  FOR DELETE
  TO authenticated
  USING (user_id = auth.uid());

Be careful with DELETE policies. I generally prefer soft deletes (setting a deleted_at timestamp) over hard deletes. It is easier to audit and recover from mistakes:

-- Instead of a DELETE policy, use an UPDATE policy for soft delete
CREATE POLICY "Users can soft-delete own comments"
  ON public.comments
  FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid() AND deleted_at IS NULL)
  WITH CHECK (user_id = auth.uid());

Auth-Based Policies

The auth schema in Supabase gives you several functions you can use inside policies. These go beyond simple auth.uid() matching.

Using JWT Claims

Supabase stores custom claims in the JWT. You can read them with auth.jwt():

-- Only users with a verified email can create listings
CREATE POLICY "Verified users can create listings"
  ON public.listings
  FOR INSERT
  TO authenticated
  WITH CHECK (
    (auth.jwt() ->> 'email_confirmed_at') IS NOT NULL
  );

Using App Metadata

App metadata is set server-side and cannot be modified by the user. This is where you store trusted information like subscription tiers:

-- Only premium users can access premium content
CREATE POLICY "Premium users can read premium content"
  ON public.premium_articles
  FOR SELECT
  TO authenticated
  USING (
    (auth.jwt() -> 'app_metadata' ->> 'plan') = 'premium'
    OR is_free = true
  );

I use this pattern on content platforms where free and paid content live in the same table. The policy handles access control, and the application code never needs to know which plan the user is on.

Setting App Metadata Server-Side

You set app metadata through the Supabase Admin API or an Edge Function:

-- In an Edge Function or server-side code:
-- supabase.auth.admin.updateUserById(userId, {
--   app_metadata: { plan: 'premium', role: 'editor' }
-- })

-- Then reference it in policies:
CREATE POLICY "Editors can update articles"
  ON public.articles
  FOR UPDATE
  TO authenticated
  USING (
    (auth.jwt() -> 'app_metadata' ->> 'role') IN ('editor', 'admin')
  );

Role-Based Access Patterns

Most applications need more than "owner can access their stuff." You need admins, editors, moderators, and sometimes custom roles per organization.

Simple Role Column

The simplest pattern stores the role on the user's profile:

-- Create a role type
CREATE TYPE user_role AS ENUM ('user', 'editor', 'admin');

-- Add it to profiles
ALTER TABLE public.profiles ADD COLUMN role user_role DEFAULT 'user';

-- Admin can read all profiles
CREATE POLICY "Admins can read all profiles"
  ON public.profiles
  FOR SELECT
  TO authenticated
  USING (
    auth.uid() = id  -- Users can read their own profile
    OR
    EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

Using a Dedicated Permissions Table

For more complex systems, I use a separate permissions table. This is the pattern running on EuroParts Lanka where we have shop admins, inventory managers, and customer service reps:

-- Permissions table
CREATE TABLE public.user_permissions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  permission TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, permission)
);

-- Enable RLS
ALTER TABLE public.user_permissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_permissions FORCE ROW LEVEL SECURITY;

-- Helper function for checking permissions
CREATE OR REPLACE FUNCTION public.has_permission(required_permission TEXT)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_permissions
    WHERE user_id = auth.uid()
    AND permission = required_permission
  );
$$;

-- Use it in policies
CREATE POLICY "Inventory managers can update products"
  ON public.products
  FOR UPDATE
  TO authenticated
  USING (public.has_permission('manage_inventory'))
  WITH CHECK (public.has_permission('manage_inventory'));

The SECURITY DEFINER on the function is important. It means the function runs with the privileges of the user who created it (usually the postgres role), bypassing RLS on the user_permissions table itself. Without this, you would need a separate RLS policy on user_permissions that allows reads, which can create circular dependencies.

Mark the function as STABLE so PostgreSQL knows it can cache the result within a single query execution. This matters for performance when the policy is evaluated for every row.

Multi-Tenant RLS

Multi-tenant applications need row isolation between organizations. Every row belongs to a tenant, and users should never see data from another tenant.

The Organization Pattern

-- Organizations table
CREATE TABLE public.organizations (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Membership table
CREATE TABLE public.organization_members (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(organization_id, user_id)
);

-- Every tenant-scoped table has an organization_id
CREATE TABLE public.invoices (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  organization_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  customer_name TEXT NOT NULL,
  amount NUMERIC NOT NULL,
  status TEXT DEFAULT 'draft',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS on all tables
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.invoices ENABLE ROW LEVEL SECURITY;

The Membership Check Function

-- Helper: check if user is a member of an organization
CREATE OR REPLACE FUNCTION public.is_org_member(org_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
  );
$$;

-- Helper: check if user has a specific role in an organization
CREATE OR REPLACE FUNCTION public.has_org_role(org_id UUID, required_role TEXT)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.organization_members
    WHERE organization_id = org_id
    AND user_id = auth.uid()
    AND role = required_role
  );
$$;

Tenant-Scoped Policies

-- Members can read their organization's invoices
CREATE POLICY "Members can read org invoices"
  ON public.invoices
  FOR SELECT
  TO authenticated
  USING (public.is_org_member(organization_id));

-- Only admins can create invoices
CREATE POLICY "Admins can create invoices"
  ON public.invoices
  FOR INSERT
  TO authenticated
  WITH CHECK (public.has_org_role(organization_id, 'admin'));

-- Admins can update invoices
CREATE POLICY "Admins can update invoices"
  ON public.invoices
  FOR UPDATE
  TO authenticated
  USING (public.has_org_role(organization_id, 'admin'))
  WITH CHECK (public.has_org_role(organization_id, 'admin'));

This pattern scales to any number of organizations. Each user sees only the data from organizations they belong to. The SECURITY DEFINER functions handle the membership lookup without requiring RLS policies that reference other RLS-protected tables.

One trap to watch for: if a user belongs to multiple organizations, make sure your application sets the correct organization_id context. I usually store the active organization in the app state and pass it with every request. Do not rely on the user having only one organization — that assumption breaks the moment you add a second.

Common RLS Mistakes — I've Made All of Them

Mistake 1: Forgetting to Enable RLS

The most dangerous mistake. You create a table, add some data, build your UI, everything works. You ship. Now anyone with your anon key can read everything. I have a migration template that enables RLS and forces it on every new table:

-- My standard table creation template
CREATE TABLE public.new_table (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE public.new_table ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.new_table FORCE ROW LEVEL SECURITY;

-- Then add policies...

Mistake 2: Overly Permissive Policies

I once wrote a policy like this early on:

-- DO NOT DO THIS
CREATE POLICY "Users can do everything"
  ON public.profiles
  FOR ALL
  TO authenticated
  USING (true)
  WITH CHECK (true);

This lets any authenticated user read, modify, and delete any other user's profile. The FOR ALL shorthand with USING (true) is almost never what you want. Always write separate policies for SELECT, INSERT, UPDATE, and DELETE.

Mistake 3: Missing WITH CHECK on UPDATE

-- DANGEROUS: Users can update their row but change ownership
CREATE POLICY "Users can update own profile"
  ON public.profiles
  FOR UPDATE
  TO authenticated
  USING (id = auth.uid());
  -- Missing WITH CHECK!

Without WITH CHECK, the user can update the id column to a different UUID. The USING clause only checks the row before the update. Always add WITH CHECK to UPDATE policies.

Mistake 4: Not Handling the Anon Role

If your table should be completely private to authenticated users, make sure you are not accidentally allowing the anon role:

-- This allows BOTH anon and authenticated
CREATE POLICY "Read own data"
  ON public.profiles
  FOR SELECT
  USING (id = auth.uid());

-- auth.uid() returns NULL for anon, so this seems safe...
-- But it's cleaner and more explicit to specify the role:
CREATE POLICY "Read own data"
  ON public.profiles
  FOR SELECT
  TO authenticated
  USING (id = auth.uid());

The first version technically works because auth.uid() returns NULL for anonymous users and NULL = anything evaluates to false in SQL. But being explicit with TO authenticated makes your intent clear and prevents future confusion.

Mistake 5: Circular Policy Dependencies

This is a subtle one. If table A has a policy that queries table B, and table B has a policy that queries table A, you get an infinite loop. PostgreSQL detects this and throws an error, but it is not always obvious why. Use SECURITY DEFINER functions to break the cycle.

Mistake 6: Ignoring Service Role Behavior

Your backend Edge Functions using the service_role key bypass all RLS. This is usually what you want, but it means bugs in your server-side code can expose data that RLS would normally block. Always validate inputs in your Edge Functions even though RLS is not checking them.

Testing RLS Policies

You should never ship RLS policies without testing them. Here is how I test mine.

Testing in the SQL Editor

Supabase lets you impersonate roles in the SQL editor:

-- Test as an anonymous user
SET ROLE anon;
SET request.jwt.claims = '{}';

SELECT * FROM public.products;
-- Should only see published products

RESET ROLE;

-- Test as a specific authenticated user
SET ROLE authenticated;
SET request.jwt.claims = jsonb_build_object(
  'sub', 'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
  'role', 'authenticated',
  'email', 'test@example.com'
)::text;

SELECT * FROM public.orders;
-- Should only see orders for this user_id

RESET ROLE;

Automated Policy Tests

I write SQL tests that run in my CI pipeline:

-- Test: authenticated user can only read own orders
DO $$
DECLARE
  order_count INTEGER;
BEGIN
  -- Set up as user A
  SET LOCAL ROLE authenticated;
  PERFORM set_config(
    'request.jwt.claims',
    jsonb_build_object(
      'sub', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa',
      'role', 'authenticated'
    )::text,
    true
  );

  -- Count orders (should only see user A's orders)
  SELECT count(*) INTO order_count
  FROM public.orders
  WHERE user_id != 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';

  -- This should be 0 — RLS should filter out other users' orders
  IF order_count > 0 THEN
    RAISE EXCEPTION 'RLS FAILURE: User can see % orders that do not belong to them', order_count;
  END IF;

  RAISE NOTICE 'PASS: User can only see own orders';
END $$;

Testing from the Client

You can also test policies using the Supabase JavaScript client with different auth states:

import { createClient } from '@supabase/supabase-js'

// Test as anon
const anonClient = createClient(SUPABASE_URL, SUPABASE_ANON_KEY)

const { data: publicProducts } = await anonClient
  .from('products')
  .select('*')

// All returned products should have status = 'published'
const hasUnpublished = publicProducts?.some(p => p.status !== 'published')
if (hasUnpublished) {
  throw new Error('RLS FAILURE: Anon user can see unpublished products')
}

Test every policy from the perspective of every role that interacts with the table. I keep a test matrix: anon, authenticated owner, authenticated non-owner, admin. If a policy passes for the owner but you never tested non-owner, you have a gap.

Performance Impact of RLS

RLS policies are not free. They are SQL expressions evaluated for every row in every query. If you write slow policies, your entire application slows down.

The Cost of Subqueries

Every subquery in a policy runs for every row being evaluated. A policy like this is expensive:

-- SLOW: subquery runs for every row
CREATE POLICY "Members can read org data"
  ON public.documents
  FOR SELECT
  TO authenticated
  USING (
    organization_id IN (
      SELECT organization_id
      FROM public.organization_members
      WHERE user_id = auth.uid()
    )
  );

Use Functions Instead

Wrapping the check in a STABLE SECURITY DEFINER function allows PostgreSQL to cache the result:

-- FASTER: function result is cached within the query
CREATE OR REPLACE FUNCTION public.user_org_ids()
RETURNS SETOF UUID
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT organization_id
  FROM public.organization_members
  WHERE user_id = auth.uid();
$$;

CREATE POLICY "Members can read org data"
  ON public.documents
  FOR SELECT
  TO authenticated
  USING (organization_id IN (SELECT public.user_org_ids()));

Index Your Policy Columns

Any column referenced in an RLS policy needs an index. If you are filtering by user_id, index it. If you are checking organization_id, index it:

-- These indexes are CRITICAL for RLS performance
CREATE INDEX idx_orders_user_id ON public.orders(user_id);
CREATE INDEX idx_documents_org_id ON public.documents(organization_id);
CREATE INDEX idx_org_members_user_id ON public.organization_members(user_id);
CREATE INDEX idx_org_members_composite
  ON public.organization_members(user_id, organization_id);

Measure the Impact

Use EXPLAIN ANALYZE to see how your policies affect query plans:

-- Check the query plan WITH RLS
SET ROLE authenticated;
SET request.jwt.claims = '{"sub": "your-user-id", "role": "authenticated"}';

EXPLAIN ANALYZE
SELECT * FROM public.orders WHERE status = 'pending';

RESET ROLE;

Look for sequential scans on large tables. If RLS is causing a seq scan where you expect an index scan, your policy columns are missing indexes.

In my experience, well-indexed RLS policies add negligible overhead — usually under 1ms per query. Poorly written policies with unindexed subqueries can add 50-200ms. The difference between "this app feels fast" and "something is wrong" is often one missing index on a column used in an RLS policy.

Key Takeaways

1. Enable RLS on every table. No exceptions. Run the verification query as part of your deployment checklist.

1. Use `FORCE ROW LEVEL SECURITY` on production tables so even the table owner respects policies.

1. Write separate policies for each operation. Never use FOR ALL with USING (true). Be explicit about SELECT, INSERT, UPDATE, and DELETE.

1. Always add `WITH CHECK` to UPDATE policies. Prevent users from transferring ownership of their rows.

1. Use `SECURITY DEFINER` functions for permission checks that reference other RLS-protected tables. Mark them STABLE for caching.

1. Index every column referenced in a policy. Unindexed policy columns are the number one cause of slow Supabase queries.

1. Test policies from every role's perspective. Anon, authenticated owner, authenticated non-owner, and admin. Automate these tests in CI.

1. Prefer app metadata over user metadata for authorization. App metadata is set server-side and cannot be modified by the user.

1. Multi-tenant isolation requires a membership check function. Do not rely on simple column matching when users can belong to multiple organizations.

1. Measure with `EXPLAIN ANALYZE`. Do not guess at performance. RLS policies are invisible in application-level profiling — you have to check at the database level.

RLS is not glamorous. It does not make your app look better or feel faster. But it is the difference between an app that protects user data and an app that leaks it. I have shipped both kinds. The second kind keeps you up at night. Get RLS right from the start, and you sleep well knowing that even if your application code has bugs, the database will not hand over data it should not.

*Written by Uvin Vindula↗ — Web3 and AI engineer building from Sri Lanka and the UK. I ship production Supabase applications through my development services and write about the patterns that survive real traffic. Find me at @IAMUVIN↗.*