Supabase RLS Policy Design Patterns Beyond the Basics

Row Level Security is one of the most powerful features in Supabase — and one of the most misunderstood. Most tutorials stop at auth.uid() = user_id. That gets you through a simple personal data model, but the moment you introduce teams, roles, organizations, or any shared resource, you need a more structured approach.

This guide covers advanced RLS policy patterns used in real production apps: multi-role systems, team-based access control, hierarchical permissions, and how to keep policies performant as your data grows. Every pattern includes copy-paste SQL you can adapt immediately.

Estimated read time: 16 minutes

Prerequisites

Supabase project with Auth enabled
Familiarity with basic RLS (CREATE POLICY, auth.uid())
Basic PostgreSQL knowledge (JOINs, functions)
Next.js app using @supabase/ssr or @supabase/auth-helpers-nextjs

How RLS Actually Works (The Mental Model You Need)

Before patterns, get this straight: RLS policies are predicate filters appended to every query. When you write:

CREATE POLICY "users can read own data"
ON profiles
FOR SELECT
USING (auth.uid() = user_id);

Postgres rewrites every SELECT on profiles to include WHERE auth.uid() = user_id. This happens at the database level — no application code can bypass it (unless using the service role key).

Two clauses matter:

USING — filters rows for SELECT, UPDATE, DELETE
WITH CHECK — validates rows on INSERT, UPDATE

Always define both for tables that accept writes.

Pattern 1: Role-Based Access with a Roles Table

The naive approach is storing roles in user_metadata. Don't. Metadata is controlled by the client and can be spoofed. Store roles in a database table.

-- roles table
CREATE TABLE user_roles (
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
  PRIMARY KEY (user_id)
);

-- helper function (security definer = runs as postgres, not the calling user)
CREATE OR REPLACE FUNCTION get_user_role()
RETURNS TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT role FROM user_roles WHERE user_id = auth.uid();
$$;

Now use it in policies:

-- admins can read everything, others read only published content
CREATE POLICY "role-based content access"
ON articles
FOR SELECT
USING (
  get_user_role() = 'admin'
  OR status = 'published'
);

-- only admins and editors can insert
CREATE POLICY "editors can create articles"
ON articles
FOR INSERT
WITH CHECK (
  get_user_role() IN ('admin', 'editor')
);

The SECURITY DEFINER function is critical here. Without it, the subquery inside the policy runs as the calling user, which can cause permission errors on the user_roles table itself.

[INTERNAL LINK: supabase-authentication-authorization]

Pattern 2: Team / Organization Membership

This is the most common pattern in SaaS apps. Users belong to organizations, and resources belong to organizations.

-- org membership table
CREATE TABLE org_members (
  org_id UUID NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (org_id, user_id)
);

-- projects belong to orgs
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Policy: members of an org can read its projects.

CREATE POLICY "org members can read projects"
ON projects
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.org_id = projects.org_id
    AND org_members.user_id = auth.uid()
  )
);

-- only org admins/owners can create projects
CREATE POLICY "org admins can create projects"
ON projects
FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.org_id = projects.org_id
    AND org_members.user_id = auth.uid()
    AND org_members.role IN ('owner', 'admin')
  )
);

Performance note: Add an index on org_members(user_id) and org_members(org_id). The EXISTS subquery runs on every row evaluation — without indexes, this becomes a full table scan at scale.

CREATE INDEX idx_org_members_user_id ON org_members(user_id);
CREATE INDEX idx_org_members_org_id ON org_members(org_id);

Pattern 3: Hierarchical Permissions (Owner > Admin > Member)

Sometimes you need cascading permissions: owners can do everything admins can, admins can do everything members can. A helper function keeps policies clean.

CREATE OR REPLACE FUNCTION user_org_role(p_org_id UUID)
RETURNS TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT role FROM org_members
  WHERE org_id = p_org_id AND user_id = auth.uid();
$$;

-- helper: check if user has at least a given role level
CREATE OR REPLACE FUNCTION user_has_org_role(p_org_id UUID, p_min_role TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT CASE
    WHEN p_min_role = 'member' THEN
      user_org_role(p_org_id) IN ('member', 'admin', 'owner')
    WHEN p_min_role = 'admin' THEN
      user_org_role(p_org_id) IN ('admin', 'owner')
    WHEN p_min_role = 'owner' THEN
      user_org_role(p_org_id) = 'owner'
    ELSE false
  END;
$$;

Now policies read like documentation:

-- delete requires owner
CREATE POLICY "owners can delete projects"
ON projects
FOR DELETE
USING (user_has_org_role(org_id, 'owner'));

-- update requires admin or above
CREATE POLICY "admins can update projects"
ON projects
FOR UPDATE
USING (user_has_org_role(org_id, 'admin'))
WITH CHECK (user_has_org_role(org_id, 'admin'));

Pattern 4: Resource Sharing and Invitations

Users sometimes need access to specific resources without being full org members — think shared documents or guest access.

CREATE TABLE resource_shares (
  resource_id UUID NOT NULL,
  resource_type TEXT NOT NULL,
  shared_with UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),
  expires_at TIMESTAMPTZ,
  PRIMARY KEY (resource_id, shared_with)
);

Policy that combines ownership and sharing:

CREATE POLICY "owners and shared users can read documents"
ON documents
FOR SELECT
USING (
  -- owner
  owner_id = auth.uid()
  OR
  -- explicitly shared
  EXISTS (
    SELECT 1 FROM resource_shares
    WHERE resource_shares.resource_id = documents.id
    AND resource_shares.resource_type = 'document'
    AND resource_shares.shared_with = auth.uid()
    AND (resource_shares.expires_at IS NULL OR resource_shares.expires_at > now())
  )
);

Pattern 5: Public + Authenticated Mixed Access

A common pattern for content sites: public content is readable by anyone, private content only by the owner.

CREATE POLICY "public content is readable by all"
ON posts
FOR SELECT
USING (
  visibility = 'public'
  OR author_id = auth.uid()
);

For anonymous users, auth.uid() returns NULL. The OR author_id = auth.uid() clause evaluates to NULL = NULL which is false in SQL — so anonymous users only see public posts. This is correct behavior, but it's worth being explicit about.

If you want to allow anonymous reads of public content but block all writes:

-- no INSERT policy = nobody can insert (including authenticated users)
-- add explicit policies only for the roles that need write access

Absence of a policy is a deny. This is the default-deny model that makes RLS safe.

Pattern 6: Audit Trails Without Bypassing RLS

A common mistake: using the service role in a trigger to write audit logs, which bypasses RLS on the audit table. Instead, use a SECURITY DEFINER function that writes to the audit table directly.

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT,
  operation TEXT,
  row_id UUID,
  changed_by UUID,
  changed_at TIMESTAMPTZ DEFAULT now(),
  old_data JSONB,
  new_data JSONB
);

-- RLS: only admins can read audit logs
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "admins read audit log"
ON audit_log
FOR SELECT
USING (get_user_role() = 'admin');

-- trigger function runs as postgres (SECURITY DEFINER)
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, row_id, changed_by, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    auth.uid(),
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::jsonb ELSE NULL END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

[INTERNAL LINK: supabase-postgres-functions-triggers-guide]

Common Pitfalls

Forgetting WITH CHECK on UPDATE policies. USING controls which rows can be targeted, but WITH CHECK controls what the row looks like after the update. Without WITH CHECK, a user could update a row they own to assign it to another user.

Using auth.jwt() claims for authorization. JWT claims are set at login time and don't reflect real-time role changes. A user whose role was revoked still has the old claim until their token expires. Use database lookups via SECURITY DEFINER functions instead.

Not testing with the anon key. Always test your policies using the anon key (not the service role) in the Supabase dashboard SQL editor. The service role bypasses everything.

Policies on tables without indexes. Every policy condition is evaluated per row. A subquery in a policy without an index causes a full table scan for every row in the outer query. Profile with EXPLAIN ANALYZE before going to production.

Multiple permissive policies are OR'd together. If you have two FOR SELECT policies on the same table, a row is visible if either policy passes. This is often surprising. Use a single policy with explicit OR conditions if you want clear logic.

Testing Your Policies

Use the Supabase SQL editor with SET LOCAL role = authenticated and SET LOCAL request.jwt.claims = '{"sub": "<user-id>"}' to simulate a specific user:

-- simulate a specific user
SET LOCAL role = authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "your-user-uuid-here"}';

-- now run your query — it will respect RLS as that user
SELECT * FROM projects WHERE org_id = 'your-org-id';

This is far faster than testing through your application layer.

Summary and Next Steps

Advanced RLS comes down to a few principles: store roles in the database (not JWT claims), use SECURITY DEFINER functions to encapsulate expensive lookups, index every column used in policy conditions, and always test with the anon key.

The patterns here — role tables, org membership, hierarchical roles, resource sharing — cover the vast majority of real-world authorization requirements. Combine them as needed for your data model.

推荐订阅源

DEV Community