Supabase RLS Guide 2026: Policies That Actually Work

Every Supabase project ships with a public API. That means anyone with your project URL and anon key can query your database. Without Supabase Row Level Security, every row in every table is exposed to every request. No middleware will save you. No client-side filtering matters. The database itself must decide who sees what.

Supabase row level security solves this by pushing authorization into PostgreSQL. Instead of writing access checks in your API layer and hoping nobody finds a bypass, you define SQL policies that the database evaluates on every single query. The row either passes the policy or it does not exist as far as the user is concerned.

This guide covers everything you need to implement supabase row level security in a production SaaS app: from enabling RLS and writing your first policy, to multi-tenant isolation, RBAC patterns, performance optimization, and the mistakes that silently break applications. Whether you are building your first Supabase project or locking down an existing one, this is the reference you will keep coming back to.

Key Takeaways

• RLS is not optional for Supabase apps. Any table without it is publicly accessible through the API.
• Policies use USING for reads/deletes and WITH CHECK for writes. UPDATE needs both.
• Always index columns referenced in RLS policies. Missing indexes are the top performance killer.
• Test policies from the client SDK, not the SQL Editor. The SQL Editor bypasses RLS.
• Start with simple ownership policies, then layer on team and role-based access as your app grows.

Table of Contents

• What Is Supabase Row Level Security?
• How RLS Policies Work Under the Hood
• Step 1: Enable RLS on Your Tables
• Step 2: Write Your First RLS Policy
• Step 3: Create Policies for Every CRUD Operation
• The USING vs WITH CHECK Decision Matrix
• Multi-Tenant RLS Patterns for SaaS
• Role-Based Access Control (RBAC) with RLS
• RLS with Supabase Storage
• RLS with Supabase Realtime
• Performance Optimization for RLS Policies
• Testing and Debugging RLS Policies
• 8 Common RLS Mistakes That Break Production Apps
• RLS vs Application-Level Authorization
• Migrating Existing Tables to RLS
• Conclusion

What Is Supabase Row Level Security?

Supabase row level security is a PostgreSQL feature that controls which rows a user can access in a table. Think of it as a WHERE clause that the database automatically appends to every query based on who is asking.

When you enable RLS on a table, PostgreSQL evaluates your defined policies before returning any results. If a user runs SELECT * FROM projects, they do not get every project in the database. They get only the projects where the policy evaluates to true for their identity.

This is fundamentally different from application-level authorization. Your Next.js middleware, your Express guards, your tRPC procedures - these all run in your application code. If someone discovers a direct API endpoint, or if a developer accidentally removes a check during a refactor, the data leaks. Supabase row level security operates below all of that. The database itself enforces the rules, regardless of how the query arrives.

Here is the mental model. Without RLS:

Client -> Supabase API -> PostgreSQL -> All rows returned

With supabase row level security:

Client -> Supabase API -> PostgreSQL -> Policy check per row -> Only matching rows returned

Every Supabase project exposes a PostgREST API and a Realtime WebSocket connection. Both honor RLS policies. Your anon key (the public one) creates a session with the anon role. Your user's JWT creates an authenticated session. The service_role key bypasses RLS entirely and should never touch client code.

RLS is not a nice-to-have for Supabase apps. It is the authorization layer.

How RLS Policies Work Under the Hood

An RLS policy is a named SQL expression attached to a table, scoped to a specific operation (SELECT, INSERT, UPDATE, or DELETE) and optionally to a specific database role.

Here is the anatomy of a policy:

CREATE POLICY "policy_name"
  ON table_name
  FOR operation           -- SELECT, INSERT, UPDATE, DELETE, or ALL
  TO role                 -- authenticated, anon, or public
  USING (expression)      -- Filters existing rows
  WITH CHECK (expression) -- Validates new/modified data

When multiple policies exist on the same table for the same operation, they combine with OR logic. If any policy evaluates to true, the row is accessible. This is called permissive mode, and it is the default.

Two built-in functions power most policies:

• auth.uid() returns the UUID of the currently authenticated user (extracted from their JWT). Returns null for unauthenticated requests.
• auth.jwt() returns the full JWT payload as JSON, letting you access custom claims like role, org_id, or tenant_id.

The database calls these functions for every row being evaluated. That sounds expensive, but PostgreSQL optimizes this well when the comparison columns are indexed. Since both functions depend on a valid user session, you need a working auth layer before RLS can do anything useful. If you are building with Next.js, our Supabase Auth + Next.js guide walks through the full setup.

Policies are not magic. They are SQL. If you can write a WHERE clause, you can write an RLS policy. The difference is that you do not need to remember to add the WHERE clause. The database adds it for you, every time, on every query.

Step 1: Enable RLS on Your Tables

RLS is disabled by default on new tables. Until you enable it, every row is accessible to anyone with your Supabase project URL and anon key.

Enable RLS via SQL:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Or through the Supabase Dashboard: open the Table Editor, select your table, click Edit Table, and toggle "Enable Row Level Security."

After enabling RLS with no policies defined, all queries return empty results. This is the correct default - deny everything, then selectively allow.

For existing projects with multiple tables, enable RLS on everything at once:

DO $$
DECLARE
  table_record RECORD;
BEGIN
  FOR table_record IN
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
  LOOP
    EXECUTE format(
      'ALTER TABLE public.%I ENABLE ROW LEVEL SECURITY;',
      table_record.table_name
    );
  END LOOP;
END $$;

Design your tables with RLS in mind from the start. Every table that stores user-specific data should include a user_id column:

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE INDEX idx_projects_user_id ON projects(user_id);

The index on user_id is critical. Without it, every RLS policy check triggers a sequential scan. On a table with 100,000 rows, that is the difference between a 2ms query and a 200ms query.

Step 2: Write Your First RLS Policy

The simplest and most common RLS pattern is ownership: users can only access rows where user_id matches their authenticated ID.

CREATE POLICY "Users can view own projects"
  ON projects
  FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

This policy does three things:

1. Applies only to SELECT queries
2. Only runs for authenticated users (not anon)
3. Returns true only when the row's user_id matches the current user's UUID

Now when a user queries projects through the Supabase client:

const { data } = await supabase
  .from('projects')
  .select('*');
// Returns only the authenticated user's projects

The user does not need to add .eq('user_id', userId). The policy handles it. Even if they try .select('*') without any filter, they only see their own rows.

For public data that anyone can read, use a policy without authentication:

CREATE POLICY "Anyone can view published posts"
  ON posts
  FOR SELECT
  TO anon, authenticated
  USING (published = true);

This lets both anonymous visitors and logged-in users see published posts, while unpublished drafts remain hidden.

Step 3: Create Policies for Every CRUD Operation

A complete supabase row level security setup needs policies for each operation your application performs. Here is the full set for a projects table:

SELECT - Users read their own projects:

CREATE POLICY "Users can view own projects"
  ON projects FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

INSERT - Users create projects under their own ID:

CREATE POLICY "Users can create own projects"
  ON projects FOR INSERT
  TO authenticated
  WITH CHECK (user_id = auth.uid());

Notice INSERT uses WITH CHECK instead of USING. This validates that the incoming data contains the correct user_id. Without this policy, a user could insert a row with someone else's user_id.

UPDATE - Users modify only their own projects:

CREATE POLICY "Users can update own projects"
  ON projects FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

UPDATE uses both clauses. USING controls which rows the user can target (they must own it). WITH CHECK validates the new values (they cannot change the user_id to someone else).

DELETE - Users remove only their own projects:

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

If your application allows the same access pattern for all operations, you can consolidate:

CREATE POLICY "Users manage own projects"
  ON projects FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

FOR ALL applies the policy to SELECT, INSERT, UPDATE, and DELETE. The USING clause is used for operations that read existing rows, and WITH CHECK is used for operations that write data.

The USING vs WITH CHECK Decision Matrix

The distinction between USING and WITH CHECK confuses most developers when they first encounter supabase row level security. Here is when each clause applies:

Operation	USING	WITH CHECK	Purpose
SELECT	Yes	No	Filters visible rows
INSERT	No	Yes	Validates new row data
UPDATE	Yes	Yes	USING filters target rows, WITH CHECK validates new values
DELETE	Yes	No	Filters deletable rows

USING answers: "Can this user see or target this row?"

WITH CHECK answers: "Is this user allowed to write this data?"

Here is where the distinction matters in practice. Consider a tasks table where users should only modify their own tasks but cannot reassign them:

CREATE POLICY "Users update own tasks"
  ON tasks FOR UPDATE
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

Without the WITH CHECK, a user could run:

UPDATE tasks SET user_id = 'other-user-uuid' WHERE id = 'task-123';

The USING clause lets them target the row (they own it). But the WITH CHECK clause blocks the update because the new user_id does not match auth.uid(). The write fails.

A more nuanced example for a blog with drafts:

-- Authors can update their own posts
CREATE POLICY "Authors update own posts"
  ON posts FOR UPDATE
  TO authenticated
  USING (author_id = auth.uid())
  WITH CHECK (
    author_id = auth.uid()
    AND status IN ('draft', 'review')
  );

The USING clause lets authors target any of their posts. The WITH CHECK clause restricts what they can save - they cannot set a post to "published" status directly. Only an admin policy (or a separate database function) handles publishing.

Multi-Tenant RLS Patterns for SaaS

For SaaS applications with organizations or teams, single-user ownership policies are not enough. You need tenant isolation - ensuring Company A never sees Company B's data, even by accident.

The JWT Claims Pattern

The cleanest approach stores the tenant identifier in the user's JWT:

1. Add org_id to your tables:

ALTER TABLE projects ADD COLUMN org_id UUID NOT NULL;
CREATE INDEX idx_projects_org_id ON projects(org_id);

2. Set custom claims during authentication using a Supabase Auth hook or Edge Function:

-- In a database function called by Auth hooks
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb AS $$
DECLARE
  user_org_id UUID;
BEGIN
  SELECT org_id INTO user_org_id
  FROM org_members
  WHERE user_id = (event->>'user_id')::UUID
  LIMIT 1;

  RETURN jsonb_set(
    event,
    '{claims,org_id}',
    to_jsonb(user_org_id::TEXT)
  );
END;
$$ LANGUAGE plpgsql;

3. Create the tenant isolation policy:

CREATE POLICY "Tenant isolation"
  ON projects FOR ALL
  TO authenticated
  USING (org_id::TEXT = auth.jwt()->>'org_id')
  WITH CHECK (org_id::TEXT = auth.jwt()->>'org_id');

Every query now filters by organization automatically. A developer cannot accidentally forget the org filter because the database enforces it.

The Membership Table Pattern

For applications where users belong to multiple organizations:

CREATE TABLE org_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  UNIQUE(org_id, user_id)
);

ALTER TABLE org_members ENABLE ROW LEVEL SECURITY;
CREATE INDEX idx_org_members_user ON org_members(user_id);
CREATE INDEX idx_org_members_org ON org_members(org_id);

-- Projects policy using membership check
CREATE POLICY "Org members access projects"
  ON projects FOR SELECT
  TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM org_members
      WHERE org_members.org_id = projects.org_id
      AND org_members.user_id = auth.uid()
    )
  );

The EXISTS subquery is efficient because PostgreSQL can use the indexes on org_members to check membership quickly. This pattern supports users who belong to multiple organizations, seeing data from each org they belong to.

Shared Resources Pattern

For documents or projects shared with specific users:

CREATE TABLE project_shares (
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  permission TEXT NOT NULL DEFAULT 'read',
  PRIMARY KEY (project_id, user_id)
);

CREATE POLICY "Owner or shared access"
  ON projects FOR SELECT
  TO authenticated
  USING (
    user_id = auth.uid()
    OR id IN (
      SELECT project_id FROM project_shares
      WHERE user_id = auth.uid()
    )
  );

This combines ownership with explicit sharing. The owner always has access, plus anyone in the shares table.

Role-Based Access Control (RBAC) with RLS

Most SaaS applications need more than simple ownership. Admins need to see everything. Managers need to see their team's data. Members need to see only their own. Supabase row level security handles this through layered policies and helper functions.

The Admin Helper Function

Create a reusable function that checks admin status:

CREATE TABLE admins (
  user_id UUID PRIMARY KEY REFERENCES auth.users(id)
);

ALTER TABLE admins ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Admins can view admin table"
  ON admins FOR SELECT
  TO authenticated
  USING (user_id = auth.uid());

CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS boolean AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM admins WHERE user_id = auth.uid()
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

The SECURITY DEFINER keyword is important. It means the function runs with the permissions of the user who created it (typically the postgres superuser), bypassing RLS on the admins table itself. Without this, the RLS policy on admins would create a circular dependency.

Now add admin access to any table:

CREATE POLICY "Admin full access"
  ON projects FOR ALL
  TO authenticated
  USING (is_admin() OR user_id = auth.uid())
  WITH CHECK (is_admin() OR user_id = auth.uid());

Since policies combine with OR logic, you can also write this as two separate policies:

CREATE POLICY "Users manage own projects"
  ON projects FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Admins manage all projects"
  ON projects FOR ALL
  TO authenticated
  USING (is_admin());

Both approaches work identically. Separate policies are easier to read and modify independently.

JWT-Based Roles

For applications using custom JWT claims:

CREATE POLICY "Role-based project access"
  ON projects FOR SELECT
  TO authenticated
  USING (
    CASE auth.jwt()->>'role'
      WHEN 'admin' THEN true
      WHEN 'manager' THEN org_id::TEXT = auth.jwt()->>'org_id'
      WHEN 'member' THEN user_id = auth.uid()
      ELSE false
    END
  );

Admins see all projects. Managers see their organization's projects. Members see only their own. The ELSE false ensures unknown roles get nothing.

Org-Level Role Checks

For per-organization roles stored in a membership table:

CREATE OR REPLACE FUNCTION public.get_user_role(check_org_id UUID)
RETURNS TEXT AS $$
  SELECT role FROM org_members
  WHERE user_id = auth.uid()
  AND org_id = check_org_id
  LIMIT 1;
$$ LANGUAGE sql SECURITY DEFINER;

CREATE POLICY "Role-based org access"
  ON projects FOR ALL
  TO authenticated
  USING (
    get_user_role(org_id) IN ('admin', 'manager', 'member')
  )
  WITH CHECK (
    get_user_role(org_id) IN ('admin', 'manager')
  );

Everyone in the org can read. Only admins and managers can write. The function encapsulates the role lookup so policies stay clean.

RLS with Supabase Storage

Supabase Storage uses the same RLS system. The storage.objects table in PostgreSQL holds metadata for every uploaded file, and you create policies on this table just like any other.

Private User Files

CREATE POLICY "Users manage own files"
  ON storage.objects FOR ALL
  TO authenticated
  USING (
    bucket_id = 'user-files'
    AND (storage.foldername(name))[1] = auth.uid()::TEXT
  );

This policy requires files to be stored in a folder named after the user's ID: user-files/{user_id}/document.pdf. The storage.foldername() function extracts path segments.

Team-Shared Storage

CREATE POLICY "Team members access shared files"
  ON storage.objects FOR SELECT
  TO authenticated
  USING (
    bucket_id = 'team-files'
    AND (storage.foldername(name))[1] IN (
      SELECT org_id::TEXT FROM org_members
      WHERE user_id = auth.uid()
    )
  );

Files organized as team-files/{org_id}/report.pdf are accessible to all members of that organization.

Public Bucket Reads

For avatars or public assets:

CREATE POLICY "Public read access"
  ON storage.objects FOR SELECT
  TO anon, authenticated
  USING (bucket_id = 'avatars');

CREATE POLICY "Users upload own avatar"
  ON storage.objects FOR INSERT
  TO authenticated
  WITH CHECK (
    bucket_id = 'avatars'
    AND (storage.foldername(name))[1] = auth.uid()::TEXT
  );

Anyone can view avatars. Only authenticated users can upload, and only into their own folder.

RLS with Supabase Realtime

Supabase Realtime respects RLS policies automatically. When a user subscribes to database changes, they only receive events for rows their policies allow them to SELECT.

Enable Realtime on your table:

ALTER PUBLICATION supabase_realtime ADD TABLE projects;

Subscribe from the client:

const channel = supabase
  .channel('project-changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'projects',
    },
    (payload) => {
      console.log('Change received:', payload);
    }
  )
  .subscribe();

If User A creates a project, User B will not receive the insert event because User B's SELECT policy does not include User A's projects. The filtering happens at the database level before the event is broadcast.

This is one of the strongest arguments for supabase row level security over application-level filtering. Realtime events, REST queries, and direct database connections all enforce the same rules. You define security once and it applies everywhere.

For multi-tenant apps, this means you do not need to filter Realtime subscriptions by organization in your application code. The RLS policies handle it. A user subscribed to projects changes will only see changes within their tenant, automatically.

Performance Optimization for RLS Policies

RLS policies add a WHERE clause to every query. Simple policies with indexed columns add negligible overhead. Complex policies with subqueries, joins, or function calls can destroy performance.

The Performance Hierarchy

From fastest to slowest:

Pattern	Relative Cost	Example
Direct column match	1x (baseline)	user_id = auth.uid()
JWT claim match	1.2x	tenant_id = auth.jwt()->>'org_id'
EXISTS subquery (indexed)	2-3x	EXISTS (SELECT 1 FROM members ...)
IN subquery	3-5x	id IN (SELECT project_id FROM shares ...)
Function call (SECURITY DEFINER)	2-4x	is_admin()
Complex joins	5-11x	Multiple JOINs with conditions

Index Everything in Your Policies

Every column referenced in a policy expression needs an index:

-- For ownership policies
CREATE INDEX idx_projects_user_id ON projects(user_id);

-- For tenant isolation
CREATE INDEX idx_projects_org_id ON projects(org_id);

-- For membership lookups
CREATE INDEX idx_org_members_composite
  ON org_members(org_id, user_id);

Use EXPLAIN ANALYZE to Verify

Run your queries with EXPLAIN ANALYZE to see how RLS policies affect the query plan:

-- Set the role to simulate an authenticated user
SET request.jwt.claims = '{"sub": "user-uuid-here", "role": "authenticated"}';
SET role = 'authenticated';

EXPLAIN ANALYZE SELECT * FROM projects;

-- Reset
RESET role;

Look for sequential scans on large tables. If you see Seq Scan on projects, you are missing an index.

Consolidate Policies

Multiple policies per operation create OR conditions. Five SELECT policies on one table means five expression evaluations per row. Combine them:

-- Instead of 3 separate policies:
CREATE POLICY "Consolidated project access"
  ON projects FOR SELECT
  TO authenticated
  USING (
    user_id = auth.uid()
    OR is_admin()
    OR EXISTS (
      SELECT 1 FROM project_shares
      WHERE project_id = projects.id
      AND user_id = auth.uid()
    )
  );

Cache Role Lookups

For frequently checked roles, use a SECURITY DEFINER function that PostgreSQL can inline:

CREATE OR REPLACE FUNCTION auth.user_role()
RETURNS TEXT AS $$
  SELECT role FROM public.user_profiles
  WHERE id = auth.uid();
$$ LANGUAGE sql STABLE SECURITY DEFINER;

The STABLE keyword tells PostgreSQL the function returns the same result within a single query, allowing it to cache the result instead of calling it per row.

Testing and Debugging RLS Policies

The number one debugging mistake with supabase row level security: testing in the Supabase SQL Editor. The SQL Editor runs as the postgres role, which bypasses all RLS policies. Your query returns every row, you think your policies work, and then production users see nothing.

Test from the Client SDK

The only reliable way to test RLS is through the Supabase client with a real user token:

// Sign in as a test user
const { data: { session } } = await supabase.auth.signInWithPassword({
  email: '[email protected]',
  password: 'test-password',
});

// Now queries respect RLS
const { data, error } = await supabase
  .from('projects')
  .select('*');

console.log('Projects visible:', data?.length);
console.log('Error:', error);

Simulate Roles in SQL

If you must test in SQL, simulate the authenticated role:

-- Set JWT claims
SELECT set_config('request.jwt.claims', json_build_object(
  'sub', 'test-user-uuid',
  'role', 'authenticated',
  'org_id', 'test-org-uuid'
)::TEXT, true);

-- Switch to the authenticated role
SET ROLE authenticated;

-- This query now respects RLS
SELECT * FROM projects;

-- Reset when done
RESET ROLE;

Debug Empty Results

When queries return empty but should not:

1. Verify RLS is enabled: SELECT relname, relrowsecurity FROM pg_class WHERE relname = 'your_table';
2. Check policies exist: SELECT * FROM pg_policies WHERE tablename = 'your_table';
3. Verify the user's JWT contains expected claims
4. Test the policy expression directly: SELECT user_id = auth.uid() FROM your_table LIMIT 5;
5. Check for conflicting restrictive policies (rare, but they override permissive ones)

Debug Over-Permissive Access

When users see too much data:

1. List all policies: SELECT * FROM pg_policies WHERE tablename = 'your_table';
2. Remember that multiple permissive policies combine with OR
3. Check if a policy uses TO public (applies to all roles including anon)
4. Verify auth.uid() returns the expected value for the current session

8 Common RLS Mistakes That Break Production Apps

After working with supabase row level security across dozens of SaaS applications, these are the mistakes that show up repeatedly. Every one of them has caused real data leaks or broken production queries.

1. Forgetting to Enable RLS

The default state of every new table is RLS disabled. If you create a table through the SQL Editor or a migration and forget ALTER TABLE ... ENABLE ROW LEVEL SECURITY, every row is publicly accessible through the Supabase API. The Supabase Dashboard shows a warning for tables without RLS, but the SQL Editor does not.

Fix: Add ALTER TABLE ... ENABLE ROW LEVEL SECURITY to every CREATE TABLE migration. Use the bulk-enable script from Step 1 as a safety net.

2. Enabling RLS Without Creating Policies

The opposite problem. You enable RLS, forget to add policies, and now every query returns empty results. Your application appears broken but there are no error messages because empty results are valid responses.

Fix: Always pair RLS enablement with at least one policy in the same migration file.

3. Testing in the SQL Editor

The SQL Editor runs as the postgres superuser, which bypasses all RLS. You test your queries there, see the expected results, deploy, and then real users see nothing.

Fix: Test through the Supabase client SDK with actual user authentication. Use the role simulation technique from the debugging section for SQL-level testing.

4. Missing Indexes on Policy Columns

A policy like user_id = auth.uid() triggers a sequential scan on the entire table if user_id is not indexed. On 10,000 rows, the query takes 50ms instead of 2ms. On 1,000,000 rows, it times out.

Fix: Create an index on every column referenced in any policy. Run EXPLAIN ANALYZE after enabling RLS to verify the query plan uses index scans.

5. Skipping WITH CHECK on Write Operations

An INSERT policy without WITH CHECK lets users insert rows with any user_id. An UPDATE policy without WITH CHECK lets users change the user_id to someone else's UUID, effectively stealing ownership.

Fix: Always include WITH CHECK (user_id = auth.uid()) on INSERT and UPDATE policies.

6. Using Complex Subqueries Instead of Helper Functions

Inline subqueries in policies get evaluated for every row. A complex membership check repeated across 10 tables means 10 copies of the same subquery in your policy definitions.

Fix: Extract common checks into SECURITY DEFINER functions. They are reusable, testable, and PostgreSQL can optimize them better.

7. Confusing Anon and Service Role Keys

The anon key creates unauthenticated sessions that respect RLS. The service_role key bypasses RLS entirely. Using the service_role key on the client exposes your entire database. Using the anon key in a server-side admin function means your admin operations get blocked by RLS.

Fix: Client-side code uses the anon key exclusively. Server-side admin operations use the service_role key. Never expose the service_role key in browser code, mobile apps, or anywhere public.

8. Not Handling the Null Case

auth.uid() returns null for unauthenticated requests. A policy like user_id = auth.uid() will never match null, which is correct. But a policy like NOT (user_id != auth.uid()) evaluates differently with null values due to SQL three-valued logic.

Fix: Keep policy expressions simple. Use direct equality (user_id = auth.uid()) rather than negated inequality.

RLS vs Application-Level Authorization

A common question when building on Supabase: should you use supabase row level security, application-level checks (like Auth providers or custom middleware), or both?

Where RLS Wins

• Defense in depth. Even if your application code has a bug, the database blocks unauthorized access.
• Consistency. REST API, Realtime subscriptions, direct connections, and Edge Functions all enforce the same rules.
• No "forgot the WHERE clause" bugs. Developers cannot accidentally query all rows.
• Audit trail. Policies are defined in SQL and version-controlled through migrations.

Where Application-Level Wins

• Complex business logic. RLS policies are SQL expressions. If your authorization requires calling external APIs, checking feature flags, or evaluating time-based conditions beyond what SQL handles cleanly, application code is more flexible.
• Performance-sensitive hot paths. Application-level checks can short-circuit before hitting the database. RLS always adds to the query.
• Simpler debugging. Application logs are easier to trace than database policy evaluations.

The Recommended Pattern

Use both. RLS as the security floor, application logic for complex business rules:

1. RLS handles: Data isolation (users see their own data), tenant isolation (orgs see their own data), role-based read/write access
2. Application handles: Feature gating, rate limiting, complex approval workflows, cross-service authorization

This approach is how teams building on the Supabase + Next.js stack typically architect their security. RLS catches what application code misses. Application code handles what SQL cannot express cleanly.

Migrating Existing Tables to RLS

If you have an existing Supabase project without supabase row level security, migrating requires careful sequencing. Enable RLS on a production table without policies and your application breaks. Add policies without proper data preparation and users lose access to their data.

Step-by-Step Migration

1. Audit your tables. Identify which tables have user data and which are reference/lookup tables. Reference tables (countries, categories) may not need per-user policies.

-- Find tables without RLS
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public';

2. Add ownership columns where missing:

ALTER TABLE projects ADD COLUMN user_id UUID REFERENCES auth.users(id);

3. Backfill ownership data. This depends on your application logic. You may need to assign rows to users based on who created them:

-- Example: backfill from an audit trail or creation log
UPDATE projects SET user_id = created_by WHERE user_id IS NULL;

4. Create policies before enabling RLS:

-- Create the policies first
CREATE POLICY "Users manage own projects"
  ON projects FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- Then enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

This order matters. If you enable RLS first, there is a window where all queries return empty.

5. Add a temporary admin bypass during migration:

CREATE POLICY "Temporary migration bypass"
  ON projects FOR ALL
  TO authenticated
  USING (true);

Remove this policy after verifying the real policies work correctly:

DROP POLICY "Temporary migration bypass" ON projects;

6. Test in staging first. Clone your production database to a staging environment and run the migration there. Verify that each user sees the correct data through the client SDK.

Rollback Plan

If something goes wrong:

-- Remove specific policies
DROP POLICY "policy_name" ON table_name;

-- Disable RLS entirely (emergency only)
ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

Disabling RLS immediately restores full access. Use this as an emergency measure only, and re-enable once policies are corrected.

For teams tracking their SaaS infrastructure decisions, document your RLS migration in a runbook. Include the order of operations, rollback commands, and verification queries for each table.

Supabase RLS Checklist for Production

Before shipping your SaaS to production, verify every item:

Security

• Every public schema table has RLS enabled
• Every table with RLS has at least one policy per operation used
• INSERT and UPDATE policies include WITH CHECK clauses
• service_role key is never exposed in client code
• Anon key permissions are restricted to what anonymous users should access

Performance

• Every column referenced in a policy has an index
• Complex checks use SECURITY DEFINER helper functions
• EXPLAIN ANALYZE confirms index scans on key queries
• No more than 5 policies per table per operation

Testing

• Policies tested through client SDK, not SQL Editor
• Each user role verified (anon, member, admin)
• Cross-tenant data isolation confirmed
• Empty state (new user, no data) tested
• Edge cases: deleted users, expired sessions, null values

Maintenance

• Policies are defined in migration files, not manually in Dashboard
• Policy changes go through the same review process as application code
• RLS coverage is checked when new tables are created
• SaaS monitoring tools track query performance post-RLS

Building a SaaS on Supabase means building on PostgreSQL's security model. The teams that treat RLS policies like application code - version-controlled, tested, reviewed - are the ones that avoid the security incidents that show up on Hacker News.

Conclusion

Supabase row level security is not a feature you add later. It is the foundation of how your application handles authorization. Every table exposed through the Supabase API needs RLS enabled with proper policies, or you are one API call away from a data breach.

The patterns in this guide cover 95% of what SaaS applications need: ownership policies for personal data, tenant isolation for organizations, RBAC for different permission levels, and storage/realtime integration for the full Supabase stack. Start with simple user_id = auth.uid() policies, add indexes, test from the client SDK, and layer on complexity only as your access patterns require it.

The biggest risk is not complex policies failing. It is forgetting to add policies at all. Build RLS into your table creation workflow, include policies in every migration, and verify coverage before every deployment.

If you are building your SaaS on Next.js and Supabase, check out our guide to the best Next.js SaaS templates for projects that come with RLS policies pre-configured. For choosing the right auth provider to pair with Supabase RLS, see our auth provider comparison. And if you are still deciding on your database layer, our Supabase pricing breakdown covers what you get at each tier.

---

Related Resources

• Supabase Pricing 2026: Free Tier Limits & Real Costs
• Neon vs Supabase 2026: Benchmarks, Pricing & Verdict
• Firebase vs Supabase 2026: Pricing, Real-Time & Verdict
• Supabase MCP Server: AI Integration Guide
• Auth Providers Compared: Clerk vs Auth0 vs Supabase vs Firebase