Supabase RLS Patterns: A Practical Guide for Pragmatic Developers

''' Supabase is a fantastic platform, but let's be honest: Row Level Security (RLS) can feel like a dark art. The official docs are a great starting point, but translating Postgres policy syntax into secure, scalable, real-world application logic is a significant leap. Get it wrong, and you're looking at a catastrophic data leak. At Leftlane.io, we've shipped numerous projects on Supabase for our clients. We've wrestled with RLS, made mistakes, and landed on a set of battle-tested patterns that work. This isn't a theoretical exercise; this is a practical guide to the most common Supabase RLS patterns you'll actually need. ## The Foundation: Deny by Default Before you write a single `CREATE POLICY` statement, you must adopt a security-first mindset. For every table you create, your first two steps should be: 1. Enable Row Level Security on the table. 2. Ensure there is no permissive `ALLOW` policy for `public`. This "deny by default" posture is non-negotiable. It means that until you explicitly grant access, no one can see, modify, or delete any data. It forces you to be intentional about every permission in your database, which is exactly the kind of discipline RLS requires. You are creating a whitelist for data access, not a blacklist. ## Common Supabase RLS Patterns With our secure foundation in place, let's build on it with specific, useful policies. These three patterns cover about 90% of the use cases we see in the wild. ### Pattern 1: Users Own Their Data This is the quintessential RLS pattern. A user should only be able to access records they created or that are explicitly assigned to them. Think of a `todos` app or a user's own profile data. Let's assume a `profiles` table with a `user_id` column that is a foreign key to `auth.users(id)`. A simple policy to ensure users can only see and update their own profile looks like this: ```sql -- Allow users to read their own profile CREATE POLICY "Users can read their own profile" ON profiles FOR SELECT USING (auth.uid() = user_id); -- Allow users to update their own profile CREATE POLICY "Users can update their own profile" ON profiles FOR UPDATE USING (auth.uid() = user_id); ``` The `auth.uid()` function is the magic ingredient here, returning the ID of the currently authenticated user. The `USING` clause acts as a filter; if the expression returns true for a given row, the operation is allowed. ### Pattern 2: The Multi-Tenancy SaaS Pattern This is the cornerstone of most B2B applications. Users belong to an organization or a team and can only access data within that tenant's boundary. Imagine you have `projects` that belong to an `organization`. Users are linked to organizations via a `memberships` table. - `organizations` (id, name) - `projects` (id, org_id, name) - `memberships` (org_id, user_id) The goal is to allow a user to see a project only if they are a member of the organization that owns the project. This requires a subquery in your policy. ```sql -- Allow members to see projects in their organization CREATE POLICY "Members can view projects in their org" ON projects FOR SELECT USING ( EXISTS ( SELECT 1 FROM memberships WHERE memberships.org_id = projects.org_id AND memberships.user_id = auth.uid() ) ); ``` This policy is more complex but incredibly powerful. For any `SELECT` on the `projects` table, Postgres will check if a corresponding entry exists in the `memberships` table linking the current user to the project's organization. This is one of the most critical Supabase RLS patterns to master for SaaS apps. ### Pattern 3: Public, but Read-Only What if you have data that anyone can see, but only certain people can change? Think of a blog, a public directory, or product listings on an e-commerce site. Here, you need to combine policies. We can create a permissive `SELECT` policy for everyone and then a restrictive `UPDATE` or `DELETE` policy for specific roles, like an `admin`. Let's use a `posts` table as an example. ```sql -- 1. Allow public, anonymous read access CREATE POLICY "Posts are publicly readable" ON posts FOR SELECT USING (true); -- 2. Allow authenticated users to insert posts CREATE POLICY "Authenticated users can create posts" ON posts FOR INSERT WITH CHECK (auth.role() = 'authenticated'); -- 3. Allow owners to update their own posts CREATE POLICY "Owners can update their own posts" ON posts FOR UPDATE USING (auth.uid() = user_id); ``` Here we've created three distinct rules: 1. Anyone (`USING (true)`) can read posts. 2. Any signed-in user can create one. 3. Only the user who created the post (`auth.uid() = user_id`) can update it. ## RLS Best Practices Checklist As you implement these Supabase RLS patterns, keep these rules of thumb in mind: * **Keep it Simple:** Complex policies are hard to debug and reason about. If a policy looks like a tangled mess of subqueries, consider if you can simplify your logic, perhaps by using a Postgres function. * **Use Helper Functions:** For very complex rules that are repeated across many policies (like checking a user's subscription status), encapsulate that logic in a `SECURITY DEFINER` Postgres function. This keeps your policies clean and your logic DRY. * **Test Your Policies:** Supabase provides a `pg_tle` extension that allows you to write actual unit tests for your policies. Use it! Manually testing every permutation of user roles and actions is a recipe for disaster. * **Don't Forget Storage:** Supabase Storage also uses Postgres RLS for access policies. The same principles apply. Ensure you have policies on the `storage.objects` table to prevent unauthorized file access. * **Document Everything:** Write comments in your SQL migrations explaining *why* a policy exists. Your future self will thank you. Row Level Security is a defining feature of the Supabase stack. It's powerful, performant, and when wielded correctly, provides a robust security model right at the database layer. By mastering these fundamental patterns, you're well on your way to building secure and scalable applications. Need help untangling your RLS logic or building your next Supabase project? That's what we do at Leftlane.io. Get in touch. '''