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.

-- 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:

-- 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.

-- 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:

-- 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:

-- 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 drafts.
• 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.