Practical Supabase RLS Patterns for Real-World Apps

No-Hype Supabase RLS Patterns That Actually Work

Supabase is a fantastic toolkit for building modern web applications, and its integrated support for PostgreSQL's Row-Level Security (RLS) is a killer feature. But let's be honest: the official docs and most tutorials only scratch the surface. They show you the "is this user the owner of this post?" use case, and then you're on your own.

At Leftlane.io, we've shipped numerous projects using Supabase, and we've developed a set of practical, battle-tested Supabase RLS patterns that go beyond the basics. This isn't hype; it's what we use to build secure, multi-tenant applications for our clients.

The Problem with Basic RLS

Basic RLS policies often look like this:

CREATE POLICY "Users can access their own data."
ON user_profiles FOR SELECT
USING ( auth.uid() = user_id );

This is a great start, but real-world applications are rarely this simple. What happens when you need roles, like "admin" or "editor"? What about team-based access, where multiple users can access the same data? Stacking dozens of simple policies on a single table quickly becomes an unmaintainable mess. Your database becomes slow, and reasoning about who can see what becomes nearly impossible.

The key is to stop thinking about RLS as a flat list of rules and start thinking in terms of reusable, composable patterns.

Pattern 1: The "Roles Table" for Multi-Tenancy

Most SaaS applications are multi-tenant. Users belong to an "organization" or a "team," and their access is defined by their role within that team. The most robust way to model this is with a central "roles" or "members" table.

Imagine a team_members table with three columns: team_id, user_id, and role (e.g., 'admin', 'member').

Now, for any table that belongs to a team (like projects), your RLS policy doesn't need to know anything about a specific user. It only needs to check if the current user is a member of the team that owns the project.

Here's how we implement it:

1. Create a Security Function: First, create a helper function that checks if a user has a specific role in a team. This is crucial for keeping your policies DRY.

   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;
   

2. Write the Policy: Now your policy on the projects table is beautifully simple.

   CREATE POLICY "Team members can access projects."
   ON projects FOR SELECT
   USING ( is_team_member(team_id) );
   

This is one of the most important Supabase RLS patterns you can learn. By centralizing your permissions logic in the team_members table and the is_team_member function, you can secure any number of related tables with a single, readable policy. Adding a new table? Just add the same one-liner policy.

Pattern 2: The "Admin Bypass" Switch

You almost always need a way for super-admins or service roles to bypass RLS entirely. Hardcoding service_role checks into every policy is a bad idea. It’s repetitive and error-prone.

Instead, we create a master function that all our policies can call. This function acts as a global bypass switch.

First, we create a function to check for the admin role:

CREATE OR REPLACE FUNCTION is_admin()
RETURNS boolean AS $$
BEGIN
  RETURN (SELECT rolsuper FROM pg_roles WHERE rolname = current_user) = true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Then, we can use this in our policies. For example, let's update the project access policy to allow admins to see everything.

CREATE POLICY "Team members can access projects, admins can see all."
ON projects FOR SELECT
USING ( is_admin() OR is_team_member(team_id) );

By chaining these checks with OR, we create a clear, hierarchical permission model. The most privileged roles are checked first, then the more specific, object-level permissions.

Putting It All Together: A Layered Approach

For a truly robust setup, you combine these patterns. Thinking in layers is key. Your RLS policies shouldn't be a grab-bag of unrelated rules. They should represent a clear, logical hierarchy of data access.

Here’s a typical hierarchy we use at Leftlane.io:

• Layer 0: Public Data: Is the data explicitly marked as is_public = true? If so, anyone can read it.
• Layer 1: Admin Access: Can the current user bypass all other checks? This is your is_admin() function.
• Layer 2: Role-Based Access: Is the user a member of the team/organization that owns this data? This is your is_team_member() function.
• Layer 3: Ownership-Based Access: Is the user the direct owner of the record? This is the classic auth.uid() = user_id check, used only when there's no concept of a team.

By building your policies with this OR-chained logic (is_public OR is_admin() OR is_team_member(...)), you create a system that is secure, predictable, and—most importantly—maintainable.

Stop wrestling with dozens of conflicting, single-purpose policies. By adopting these Supabase RLS patterns, you can build a security model that scales with your application instead of collapsing under its own weight. It’s a pragmatic approach that has served us and our clients well.