# Supabase RLS Guide 2026: Policies That Actually Work > Implement Supabase Row Level Security the right way — RLS policies, multi-tenant patterns, RBAC, performance, and the 8 mistakes that break production. Source: https://designrevision.com/blog/supabase-row-level-security --- 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?](#what-is-supabase-row-level-security) - [How RLS Policies Work Under the Hood](#how-rls-policies-work-under-the-hood) - [Step 1: Enable RLS on Your Tables](#step-1-enable-rls-on-your-tables) - [Step 2: Write Your First RLS Policy](#step-2-write-your-first-rls-policy) - [Step 3: Create Policies for Every CRUD Operation](#step-3-create-policies-for-every-crud-operation) - [The USING vs WITH CHECK Decision Matrix](#the-using-vs-with-check-decision-matrix) - [Multi-Tenant RLS Patterns for SaaS](#multi-tenant-rls-patterns-for-saas) - [Role-Based Access Control (RBAC) with RLS](#role-based-access-control-rbac-with-rls) - [RLS with Supabase Storage](#rls-with-supabase-storage) - [RLS with Supabase Realtime](#rls-with-supabase-realtime) - [Performance Optimization for RLS Policies](#performance-optimization-for-rls-policies) - [Testing and Debugging RLS Policies](#testing-and-debugging-rls-policies) - [8 Common RLS Mistakes That Break Production Apps](#8-common-rls-mistakes-that-break-production-apps) - [RLS vs Application-Level Authorization](#rls-vs-application-level-authorization) - [Migrating Existing Tables to RLS](#migrating-existing-tables-to-rls) - [Conclusion](#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: ```sql 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](/blog/supabase-auth-nextjs) 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: ```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: ```sql 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: ```sql 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. ```sql 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: ```typescript 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: ```sql 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:** ```sql 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:** ```sql 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:** ```sql 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:** ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql -- 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: ```sql 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: ```sql -- 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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** ```sql 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** ```sql 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: ```sql 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: ```sql ALTER PUBLICATION supabase_realtime ADD TABLE projects; ``` Subscribe from the client: ```typescript 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: ```sql -- 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: ```sql -- 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: ```sql -- 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: ```sql 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: ```typescript // Sign in as a test user const { data: { session } } = await supabase.auth.signInWithPassword({ email: 'test@example.com', 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: ```sql -- 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](/blog/auth-providers-compared) 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](/blog/prisma-vs-drizzle) 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. ```sql -- Find tables without RLS SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public'; ``` **2. Add ownership columns where missing:** ```sql 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: ```sql -- 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:** ```sql -- 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:** ```sql CREATE POLICY "Temporary migration bypass" ON projects FOR ALL TO authenticated USING (true); ``` Remove this policy after verifying the real policies work correctly: ```sql 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: ```sql -- 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](/blog/vercel-vs-railway), 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](/blog/saas-reporting-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. {{ partial:cta/forge }} ## 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](/blog/best-nextjs-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](/blog/auth-providers-compared). And if you are still deciding on your database layer, our [Supabase pricing breakdown](/blog/supabase-pricing) covers what you get at each tier. --- ## Related Resources - [Supabase Pricing 2026: Free Tier Limits & Real Costs](/blog/supabase-pricing) - [Neon vs Supabase 2026: Benchmarks, Pricing & Verdict](/blog/supabase-vs-neon) - [Firebase vs Supabase 2026: Pricing, Real-Time & Verdict](/blog/supabase-vs-firebase) - [Supabase MCP Server: AI Integration Guide](/blog/supabase-mcp-server) - [Auth Providers Compared: Clerk vs Auth0 vs Supabase vs Firebase](/blog/auth-providers-compared)