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 (the older @supabase/auth-helpers-nextjs is archived)

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);

For queries executed under a role subject to this policy, PostgreSQL evaluates the ownership predicate in the database. That protection does not cover every connection: superusers, roles with BYPASSRLS, and normally the table owner bypass RLS. A service-role connection is another privileged case. Test the role your application actually uses, per PostgreSQL's row security policy documentation.

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 — see Supabase's RLS guide for the full policy syntax and the auth.uid() helper this pattern relies on.

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 runs with its creator's privileges, so the user_roles lookup is not itself filtered by the calling user's RLS policies on that table. That power has a cost: Supabase's RLS guide warns that a security definer function in an exposed schema is callable over the Data API with the creator's privileges, so create these helpers in a schema that is not exposed through the API rather than in public.

Supabase Auth + Middleware: Session Guide for Next.js 15

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 author_id = auth.uid() comparison then evaluates to NULL, and PostgreSQL treats a USING result of false or null as "row not visible" (CREATE POLICY) — 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;
$$;

Supabase Postgres Functions and Triggers: Complete Developer Guide

Common Pitfalls#

Assuming an UPDATE policy without WITH CHECK is unchecked. USING controls which rows can be targeted, and WITH CHECK controls what the row may look like after the update. When an UPDATE (or ALL) policy omits WITH CHECK, PostgreSQL reuses the USING expression for the new row (CREATE POLICY), so USING (owner_id = auth.uid()) alone already blocks reassigning a row to another user. Write an explicit WITH CHECK when the post-update rule must differ from the targeting rule.

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.

Testing as a privileged role. The Supabase SQL editor runs as the postgres user and service_role bypasses RLS (Supabase's RLS guide), so a query that works there proves nothing about your policies. Test with the anon key or a real user session from the client, or switch role inside a transaction as shown below.

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. SET LOCAL only lasts until the end of the current transaction, and outside a transaction block it emits a warning and has no effect (PostgreSQL SET), so wrap the test in BEGIN … ROLLBACK:

BEGIN;
-- 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';
ROLLBACK;

This is far faster than testing through your application layer. If you want to see this play out against every role at once without touching a real project, the Supabase RLS Playground runs a real Postgres engine (via WebAssembly) in your browser and executes the same query as anon, two different signed-in users, and service_role in one pass — useful for checking a pattern above before wiring it into a migration.

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.

Related reading:

• Advanced Authentication Patterns with Next.js and Supabase
• Next.js + Supabase Production Launch Checklist (47 Items)
• Multi-Tenant SaaS Architecture with Next.js and Supabase

Pre-Release Audit Checklist#

Before shipping any RLS policy change to production, run through this gate:

• RLS enabled on all tenant tables — run SELECT schemaname, tablename, rowsecurity FROM pg_tables WHERE schemaname = 'public'; and verify no tenant table has rowsecurity = false
• SELECT policy exists for authenticated users on every readable table
• WITH CHECK defined for every table that accepts INSERT or UPDATE
• Policy filter columns are indexed — verify with EXPLAIN ANALYZE on a realistic row count
• Tested with a real non-admin token (not service_role)
• Tested with a user who should be blocked — reads return an empty result rather than an error, while a blocked INSERT or UPDATE that fails WITH CHECK raises an error (CREATE POLICY)

Quick audit SQL:

SELECT schemaname, tablename, policyname, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Known incident patterns:

• INC-002: Supabase returns empty array for existing rows — missing SELECT policy on a table with RLS enabled
• INC-015: RLS policy slows API to unusable latency — subquery in policy without index on the filter column

Further reading: 11 Supabase Auth Lessons From a Year in Production.