Beyond the Basics: Practical Supabase RLS Patterns for Production

''' ## Your First Supabase RLS Policy is Probably Wrong Let's be honest. When you first start with Supabase, Row Level Security (RLS) feels like magic. A simple policy—`auth.uid() = user_id`—and suddenly your data is protected. It's a fantastic starting point, and for simple "user owns their own data" apps, it works. But at Leftlane.io, we build applications for businesses, and business logic is rarely that simple. What happens when you need teams? Different permission levels? Content that can be both public and private? Your simple policy shatters into a dozen complex, unmanageable rules. The truth is, most tutorials stop at the easy stuff. To build robust, scalable apps, you need to think in terms of **Supabase RLS patterns** that go beyond the basics. These aren't just SQL snippets; they are architectural decisions that define how your app manages data access. ### The All-Too-Common Trap The trap is trying to manage complex permissions with a flat list of user-centric policies. You end up with convoluted `OR` conditions checking if a user is a project manager, or an admin, or the document author, all in a single, monolithic policy. It's a recipe for security holes and maintenance nightmares. Let's move beyond that and into patterns that we use to ship real-world products. ## Pattern 1: The "Organization" or "Team" Model This is the bedrock for any B2B SaaS application. Users don't just own data; they are members of an organization that owns the data. This requires a shift in your data model and your RLS logic. First, you need a way to link users to teams. A simple junction table is perfect: * `organizations` (id, name, ...) * `users` (id, ...) * `memberships` (org_id, user_id, role) Now, instead of checking if the logged-in user *owns* a piece of data (like a `project`), you check if they are a *member of the organization* that owns it. The key is creating a PostgreSQL security helper function. ```sql -- in your SQL editor create function is_org_member(org_id uuid) returns boolean as $$ select exists ( select 1 from memberships where memberships.org_id = is_org_member.org_id and memberships.user_id = auth.uid() ); $$ language sql security definer; ``` With this function, your RLS policy on the `projects` table becomes clean and readable: ```sql -- RLS Policy on 'projects' for SELECT is_org_member(projects.org_id) ``` Now, any user who is a member of the organization can see its projects. This is a fundamental Supabase RLS pattern that scales. ## Pattern 2: Role-Based Access Control (RBAC) Of course, not every team member has the same permissions. This is where the `role` column in our `memberships` table comes into play. It might contain values like `admin`, `editor`, or `viewer`. Building on our team pattern, we can write more granular policies for different actions. Let's say only admins and editors can create new projects. * **`SELECT` Policy:** Remains the same. Anyone in the org can see projects. `is_org_member(projects.org_id)` * **`INSERT` Policy:** Becomes more restrictive. You need a new helper function. ```sql -- Get the role of a user in a specific org create function get_user_role(org_id uuid) returns text as $$ select role from memberships where memberships.org_id = get_user_role.org_id and memberships.user_id = auth.uid() limit 1; $$ language sql security definer; ``` Your `INSERT` policy on `projects` now looks like this: ```sql -- RLS Policy on 'projects' for INSERT get_user_role(projects.org_id) IN ('admin', 'editor') ``` This is powerful. You've decoupled the *action* (creating a project) from the *user* and tied it to a *role*. You can now manage permissions simply by changing a user's role in the `memberships` table, with no RLS changes required. ## Pattern 3: Public vs. Private Content This pattern is common in content management systems, knowledge bases, or any app where some data is for public consumption while other data is internal or in a draft state. Let's imagine a `documents` table with a `status` column that can be `published` or `draft`. The RLS logic for `SELECT` combines multiple conditions: ```sql -- RLS Policy on 'documents' for SELECT ( -- Condition 1: The document is published status = 'published' ) OR ( -- Condition 2: The user is a member of the org that owns the draft is_org_member(documents.org_id) ) ``` This simple `OR` statement creates a powerful and flexible system: * Anonymous users and users from other organizations can only see `published` documents. * Users within the owning organization can see both `published` documents and internal `draft`s. * You could even combine it with the RBAC pattern, allowing only `editor` roles to see `draft` documents. ## RLS is Your Application Architecture At Leftlane.io, we believe well-designed Supabase RLS patterns are more than just a security layer—they are a core component of your application architecture. They force you to design a clean, scalable data model from day one. Stop adding endless `OR` clauses to `auth.uid() = user_id`. Start thinking in terms of composable patterns: teams, roles, and status. By using helper functions and clear junction tables, you create a data access layer that is secure, maintainable, and ready for the real-world complexity your application will inevitably face. '''