Stop Fumbling: Practical Supabase RLS Patterns for Real-World Apps

## Supabase RLS Is a Superpower, If You Wield It Correctly At Leftlane.io, we're big fans of Supabase. It packages the power of PostgreSQL into a developer-friendly experience that lets us ship robust web apps incredibly fast. One of its true superpowers is Row Level Security (RLS). RLS pushes security logic directly into the database, allowing you to define fine-grained access policies right on your data tables. No more writing repetitive, error-prone authorization boilerplate in your API layer. But with great power comes the potential to create a tangled mess. We've seen projects where dozens of conflicting policies on a single table make debugging a nightmare. When you ask, "Why can't this user see this row?" the answer is buried under layers of complex, overlapping SQL logic. It doesn't have to be this way. After building and shipping numerous Supabase projects, we've settled on a few battle-tested **Supabase RLS patterns** that keep our security logic clean, maintainable, and easy to reason about. ## The Core Principle: One Policy to Rule Them All (Per Action) The biggest mistake we see is creating multiple `SELECT` policies for different roles or conditions. For example, a policy for anonymous users, another for authenticated users, and a third for team admins. This forces Postgres to evaluate and combine them, and it forces you to mentally juggle the logic. Our approach is simpler: aim for a single `USING` policy per action (`SELECT`, `UPDATE`, `DELETE`). This policy should contain all the logic necessary to determine if an action is allowed. This makes your security rules declarative and located in one place. ## Pattern 1: The "Public, Private, or Own" Read A classic scenario: you have a table where some rows are public, while others are private or only accessible to their owner. Think of a `documents` table where some documents are published for the world to see, while others are drafts visible only to the author. Instead of two separate `SELECT` policies, combine the logic with `OR`. ```sql -- Good: A single policy for all read access on a 'documents' table -- using ((status = 'published') OR (auth.uid() = author_id)) ``` This is beautifully simple. A user can select a document if its status is `published` **OR** if their user ID matches the `author_id`. It's immediately obvious what the rules are, and you can add more conditions without adding more policies. This is one of the most fundamental **Supabase RLS patterns** you can learn. ## Pattern 2: The Ownership & Membership Check (with Helper Functions) The next level of authorization complexity is multi-tenancy. Can the current user access this row because they belong to the correct team or organization? Here, the temptation is to write a subquery directly in your policy. ```sql -- Bad: A complex subquery inside your policy -- using ( exists ( -- select 1 from memberships -- where organization_id = documents.organization_id -- and user_id = auth.uid() -- )) ``` This works, but it’s verbose and you'll have to copy-paste this logic for every table in your organization. This is a maintenance nightmare. The moment your membership logic changes, you have to hunt down and update every policy. The better pattern is to encapsulate this logic in a PostgreSQL function. ```sql -- 1. Create a helper function to check membership create or replace function is_org_member(org_id uuid) returns boolean as $$ select exists ( select 1 from memberships where organization_id = org_id and user_id = auth.uid() ); $$ language sql security definer; -- 2. Use the clean, reusable function in your policy -- Good: A simple, declarative, and reusable policy -- using ( is_org_member(organization_id) ) ``` This is a massive improvement. Your policy is now readable and reusable. If you need to add role-based checks (`is_org_admin`, for example), you can simply update the function in one place, and the security change propagates everywhere. ### Essential RLS Best Practices As you implement these patterns, keep these core practices in mind: * **Deny by Default:** Always start by enabling RLS and creating a restrictive `DENY ALL` policy. Then, selectively open up access with your `USING` policies. `ALTER TABLE documents ENABLE ROW LEVEL SECURITY;` is your best friend. * **Use `USING` for reads:** The `USING` clause applies to existing rows for `SELECT`, `UPDATE`, and `DELETE` operations. It answers the question, "Which rows am I allowed to see and act upon?" * **Use `WITH CHECK` for writes:** The `WITH CHECK` clause applies to new or updated rows for `INSERT` and `UPDATE` operations. It answers the question, "Is the data I'm trying to write valid according to the rules?" You often use the same logic for both. * **Embrace `security definer` functions:** Functions marked with `security definer` run with the permissions of the user who defined them, not the calling user. This allows your membership check function to query the `memberships` table even if the calling user doesn