Row-Level Security in Supabase: Patterns for Multi-User Apps

Row-Level Security is the feature that makes Supabase viable for production applications. Without it, your database is wide open to any authenticated user. With it, you get fine-grained access control enforced at the database level — no middleware, no ORM filters, no hoping your API layer catches every case.

We have implemented RLS across every Supabase project we ship, from LancerSpace (multi-user freelancer workspaces) to MindHyv (team-based business management). The patterns repeat. The mistakes also repeat if you do not know what to watch for.

The Basics: How RLS Works

When you enable RLS on a table, PostgreSQL blocks all access by default. You then write policies that grant specific access. A policy is a SQL expression that returns true or false for each row. If it returns true, the user can perform the specified operation on that row.

-- Enable RLS (blocks everything by default)
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Allow users to see their own projects
CREATE POLICY "Users can view own projects"
  ON projects
  FOR SELECT
  USING (user_id = auth.uid());

The auth.uid() function returns the currently authenticated user’s ID from the Supabase JWT. Every query that hits this table now automatically filters to rows where user_id matches the caller.

This is important: RLS is not an API filter. It is enforced by PostgreSQL itself. Even if someone bypasses your API and hits the database directly through the Supabase client, the policies still apply. This is defense in depth, and it is the reason we trust Supabase for multi-tenant applications.

Pattern 1: User Owns Data

The simplest pattern. Each row belongs to a single user.

-- Profiles table
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own profile"
  ON profiles FOR SELECT
  USING (id = auth.uid());

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

-- Invoices table
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own invoices"
  ON invoices FOR SELECT
  USING (user_id = auth.uid());

CREATE POLICY "Users can create invoices"
  ON invoices FOR INSERT
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users can update own invoices"
  ON invoices FOR UPDATE
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "Users can delete own invoices"
  ON invoices FOR DELETE
  USING (user_id = auth.uid());

Note the difference between USING and WITH CHECK. USING filters which existing rows the user can see or modify. WITH CHECK validates what values can be inserted or updated to. For UPDATE, you need both — USING ensures they can only update their own rows, and WITH CHECK ensures they cannot change the user_id to someone else’s.

This is the pattern we use throughout LancerSpace for personal resources: invoices, proposals, client records.

Pattern 2: Team Membership

Most SaaS applications have teams or organizations. A user can access data that belongs to any team they are a member of.

-- Teams and membership
CREATE TABLE teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE team_members (
  team_id UUID REFERENCES teams(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member', -- 'owner', 'admin', 'member', 'viewer'
  joined_at TIMESTAMPTZ DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

-- Projects belong to teams
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES teams(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Users can view projects in their teams
CREATE POLICY "Team members can view projects"
  ON projects FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM team_members
      WHERE user_id = auth.uid()
    )
  );

-- Only admins and owners can create projects
CREATE POLICY "Admins can create projects"
  ON projects FOR INSERT
  WITH CHECK (
    team_id IN (
      SELECT team_id FROM team_members
      WHERE user_id = auth.uid()
      AND role IN ('owner', 'admin')
    )
  );

This pattern uses a subquery against the team_members table. For every row in projects, PostgreSQL checks whether the current user has a membership in the project’s team.

Pattern 3: Role-Based Access

Building on team membership, you can enforce different permissions for different roles:

-- Helper function to check team role
CREATE OR REPLACE FUNCTION get_team_role(check_team_id UUID)
RETURNS TEXT
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT role FROM team_members
  WHERE team_id = check_team_id
  AND user_id = auth.uid()
$$;

-- Viewers can only read
CREATE POLICY "Viewers can view projects"
  ON projects FOR SELECT
  USING (get_team_role(team_id) IS NOT NULL);

-- Members can create
CREATE POLICY "Members can create projects"
  ON projects FOR INSERT
  WITH CHECK (get_team_role(team_id) IN ('owner', 'admin', 'member'));

-- Only admins and owners can delete
CREATE POLICY "Admins can delete projects"
  ON projects FOR DELETE
  USING (get_team_role(team_id) IN ('owner', 'admin'));

-- Only owners can update team settings
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Owners can update team"
  ON teams FOR UPDATE
  USING (get_team_role(id) = 'owner')
  WITH CHECK (get_team_role(id) = 'owner');

The SECURITY DEFINER on the function is deliberate. It means the function runs with the permissions of the function creator (usually the database owner), bypassing RLS on the team_members table itself. Without this, you would hit a circular dependency: the policy on projects queries team_members, which has its own RLS, which might query something else.

Pattern 4: Admin Bypass

Sometimes you need a service role or admin user to bypass RLS entirely. Supabase handles this with the service role key, which bypasses RLS by default. But what about admin users within your application?

-- Check if user is an app-level admin
CREATE OR REPLACE FUNCTION is_admin()
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role = 'admin'
  )
$$;

-- Admin can see all projects
CREATE POLICY "Admins can view all projects"
  ON projects FOR SELECT
  USING (is_admin());

-- Admin can update all projects
CREATE POLICY "Admins can update all projects"
  ON projects FOR UPDATE
  USING (is_admin());

PostgreSQL evaluates policies with OR logic by default. If any policy returns true, access is granted. So the admin policies work alongside the team membership policies — team members can access their own projects, and admins can access everything.

Pattern 5: Shared Resources with Access Tokens

Some resources need to be shared via link, like a publicly shared invoice or a portfolio page. We handle this with a combination of public flags and share tokens:

CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  share_token TEXT UNIQUE DEFAULT encode(gen_random_bytes(16), 'hex'),
  is_public BOOLEAN DEFAULT false,
  -- ... other columns
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Owner can do everything
CREATE POLICY "Owner full access"
  ON invoices FOR ALL
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

-- Public invoices can be viewed by anyone (including anonymous)
CREATE POLICY "Public invoices are viewable"
  ON invoices FOR SELECT
  USING (is_public = true);

For share-token access, we typically handle that through a server-side API route that uses the service role client, since anonymous users cannot pass custom parameters into RLS policies easily. The API verifies the token and returns the data.

Common Mistakes

Forgetting SELECT Policies

This is the most common one. You write INSERT and UPDATE policies but forget SELECT. The user creates a record and then cannot see it.

-- This is not enough!
CREATE POLICY "Users can create posts"
  ON posts FOR INSERT
  WITH CHECK (user_id = auth.uid());

-- You ALSO need this
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (user_id = auth.uid());

Every table with RLS enabled needs at least a SELECT policy. Without it, the table appears empty to all users.

Infinite Recursion

If a policy on table A queries table B, and table B has a policy that queries table A, PostgreSQL will recurse until it hits the stack limit and throws an error.

-- BAD: This will cause infinite recursion if both tables have RLS
CREATE POLICY "View projects" ON projects FOR SELECT
  USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid()));

CREATE POLICY "View team_members" ON team_members FOR SELECT
  USING (team_id IN (SELECT team_id FROM projects WHERE ...));

The fix: use SECURITY DEFINER functions for cross-table lookups, which bypass RLS on the queried table. Or restructure your policies so the dependency is one-directional.

Performance Traps

RLS policies run for every row. A subquery in a policy on a large table can destroy performance if the subquery is not indexed:

-- Make sure this is fast
CREATE INDEX idx_team_members_user_id
  ON team_members (user_id);

-- And this composite index for role lookups
CREATE INDEX idx_team_members_user_role
  ON team_members (user_id, role);

We always run EXPLAIN ANALYZE on our most common queries after setting up RLS to catch performance issues early. A policy that adds 200ms to every query is worse than no security at all — because your team will be tempted to disable it.

Not Testing Policies

RLS policies are invisible logic. They do not show up in your application code, which means they are easy to get wrong and hard to debug. Test them explicitly:

-- Test as a specific user
SET request.jwt.claims = '{"sub": "user-id-here"}';
SET role TO authenticated;

-- This should return only the user's data
SELECT * FROM projects;

-- This should fail
INSERT INTO projects (team_id, name)
VALUES ('team-they-dont-belong-to', 'Hacked');

-- Reset
RESET role;

We include RLS tests in our migration workflow. Every migration that adds or modifies policies includes test queries that verify the expected behavior. We wrote about our overall CI approach in CI/CD for Small Teams.

RLS and the Supabase Client

One thing that trips people up: the Supabase JavaScript client uses the anon key by default, which means RLS always applies. But if you are calling from a server-side function (API route, edge function) using the service_role key, RLS is bypassed entirely.

// Client-side: RLS applies, user only sees their data
const { data } = await supabase.from("projects").select("*");

// Server-side with service role: RLS is BYPASSED
const { data } = await supabaseAdmin.from("projects").select("*");
// This returns ALL projects — be careful

This is by design. Your server-side code should use the service role when it needs to act on behalf of the system (sending emails to all users, running reports). But never expose the service role key to the client.

For more on how we use Supabase across our stack, see Why We Use Supabase.

Practical Advice

Start with the simplest policies that work. User-owns-data covers a surprising number of cases. Add team membership when you actually need it, not because you think you might.

Use SECURITY DEFINER functions for cross-table lookups. They are the escape hatch that makes complex policies possible without infinite recursion.

Index every column referenced in a policy. Run EXPLAIN ANALYZE before and after enabling RLS.

And test your policies. Not in production, not by clicking around in the app, but with explicit SQL queries that verify the expected behavior for each role and scenario. The security you do not test is the security you do not have.

If you are building something similar, reach out at [email protected].