Practical Supabase RLS Patterns for Secure App Development

## Supabase RLS: From "Magic" to Practical Patterns Supabase is a fantastic platform for rapidly building backends. Its party trick? The ability to talk directly to a Postgres database from the browser. This is magic, but it's also a foot-gun. Without a robust security model, you're one API call away from exposing all your users' data. That's where Row Level Security (RLS) comes in. RLS is a Postgres feature that lets you define policies to control which database rows a user can access. Supabase makes it easy to enable, but knowing *how* to structure your policies is a different story. Generic documentation is one thing; battle-tested patterns are another. At Leftlane.io, we've built numerous applications on Supabase, and we've landed on a few core Supabase RLS patterns that cover 99% of use cases. Let's break them down. ### The Foundation: Default Deny Before we even start, rule zero: **Enable RLS on your tables and do not create a permissive `USING (true)` policy.** If RLS is enabled on a table, but there are no policies for a given operation (SELECT, INSERT, UPDATE, DELETE), the operation is denied. This is the secure default you want. You then *add* policies to selectively grant access. Don't start with `public.posts` being wide open and then try to lock it down. Start locked down and open it up intentionally. ### Pattern 1: The "It's Mine" Policy This is the most common and fundamental RLS pattern. A user can only see or modify documents they created. It assumes you have a `user_id` column on your table that matches the authenticated user's ID. Let's say you have a `documents` table. ```sql -- a "documents" table CREATE TABLE documents ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES auth.users(id), content text ); ``` The policy is beautifully simple: ```sql -- Policy: Users can only access their own documents. CREATE POLICY "users_can_access_own_documents" ON documents FOR ALL USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id); ``` * The `USING` clause applies to `SELECT`, `UPDATE`, and `DELETE`. It checks if the current user's ID (`auth.uid()`) matches the row's `user_id`. * The `WITH CHECK` clause applies to `INSERT` and `UPDATE`. It ensures that any new row a user tries to create or modify has their own `user_id`. ### Pattern 2: The "Admin/Team" Policy What if users are part of a team or organization? They should be able to see their team's data, not just their own. This requires a few more tables. 1. `profiles`: To store user-specific data, like their role. 2. `teams`: The teams themselves. 3. `team_members`: A junction table to link users (`profiles`) to `teams`. Now, imagine our `documents` table has a `team_id` instead of a `user_id`. ```sql -- A "documents" table linked to a team CREATE TABLE documents ( id uuid PRIMARY KEY, team_id uuid REFERENCES teams(id), content text ); ``` The RLS policy now needs to check if the user is a member of the team that owns the document. This is a perfect use case for a Postgres security-definer function. ```sql -- Function to check team membership CREATE OR REPLACE FUNCTION is_team_member(team_id_to_check uuid) RETURNS boolean AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM team_members WHERE team_id = team_id_to_check AND user_id = auth.uid() ); END; $$ LANGUAGE plpgsql SECURITY DEFINER; ``` **Why `SECURITY DEFINER`?** The user running the query doesn't have permission to read the `team_members` table directly. `SECURITY DEFINER` makes the function run with the permissions of the function's *creator* (typically a superuser), allowing it to check for membership securely without exposing the whole `team_members` table. Now the policy is simple again: ```sql -- Policy: Users can access documents belonging to their team. CREATE POLICY "team_members_can_access_team_documents" ON documents FOR ALL USING (is_team_member(team_id)); ``` We can also layer in roles. For example, an "admin" might be able to access all documents, regardless of team. ### Pattern 3: Combining Supabase RLS Patterns The real power comes from combining policies. Postgres will allow an operation if **any** policy evaluates to true. Let's say you want to allow access if a user is an admin OR if they are a member of the correct team. You just create two separate policies on the same table. * **Policy 1: "Admins can do anything"** * **Policy 2: "Team members can access their team's documents"** If you're logged in as an admin, the first policy passes. If you're a regular user, Postgres checks the second one. This is clean and much easier to debug than one giant policy with complex `OR` conditions. Here's a list of common patterns we combine: * **Ownership:** Can the user access this because they created it? * **Membership:** Can the user access this because they are part of the team/group that owns it? * **Role:** Can the user access this because they have a specific role (e.g., `admin`, `auditor`)? * **Status:** Can the user access this because the document is in a `published` state? (Great for public-but-not-all data). ### Stop Thinking in Endpoints, Start Thinking in Policies Adopting Supabase RLS patterns requires a mental shift. You're no longer writing `GET /documents/:id` and then adding authorization logic in your controller. Instead, you're defining the *inherent rules* of your data. The security is tied to the data itself, not the API endpoint that happens to access it. This approach is incredibly powerful. It means your security model is enforced whether you're accessing data from the client, a serverless function, or a reporting tool. It's more work upfront, but it pays massive dividends in security and scalability. Need help untangling your Supabase security model or implementing these Supabase RLS patterns? That's what we do at Leftlane.io. We help teams ship secure, scalable applications without getting bogged down in the details.