supabase-enterprise-rbac（Supabase enterprise rbac 安全）

Supabase Enterprise RBAC

Overview

Supabase supports custom role-based access control (RBAC) by storing role information in app_metadata on the user's JWT, then reading those claims in RLS policies via auth.jwt() ->> 'role'. This skill implements a complete RBAC system: defining roles in app_metadata, writing RLS policies that enforce role hierarchies, scoping access by organization, managing roles through the Admin API, and protecting API endpoints with role checks — all using real createClient from @supabase/supabase-js.

When to use: Building multi-role applications (admin/editor/viewer), implementing organization-scoped access, creating custom permission systems beyond Supabase's built-in anon/authenticated roles, or scoping API operations by user role.

Prerequisites

• @supabase/supabase-js v2+ with service role key for admin operations
• Understanding of JWT claims and Supabase's auth.jwt() SQL function
• Database access via SQL Editor or psql for RLS policy creation
• Supabase project with authentication configured

Instructions

Step 1: Define Roles via app_metadata and JWT Claims

Store custom roles in the user's app_metadata using the Admin API. These claims appear in every JWT the user receives and are available in RLS policies.

Set user roles with the Admin API:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// Define the role hierarchy
type AppRole = 'admin' | 'editor' | 'viewer' | 'member';

interface AppMetadata {
  role: AppRole;
  org_id: string;
  permissions?: string[];
}

// Assign a role to a user (admin operation)
async function setUserRole(userId: string, role: AppRole, orgId: string) {
  const { data, error } = await supabase.auth.admin.updateUserById(userId, {
    app_metadata: {
      role,
      org_id: orgId,
    },
  });

  if (error) throw new Error(`Failed to set role: ${error.message}`);

  console.log(`User ${userId} assigned role "${role}" in org "${orgId}"`);
  return data.user;
}

// Assign granular permissions (optional, for fine-grained control)
async function setUserPermissions(
  userId: string,
  permissions: string[]
) {
  const { data, error } = await supabase.auth.admin.updateUserById(userId, {
    app_metadata: { permissions },
  });

  if (error) throw new Error(`Failed to set permissions: ${error.message}`);
  return data.user;
}

// Bulk role assignment (e.g., onboarding a team)
async function assignTeamRoles(
  orgId: string,
  assignments: { userId: string; role: AppRole }[]
) {
  const results = await Promise.allSettled(
    assignments.map(({ userId, role }) => setUserRole(userId, role, orgId))
  );

  const succeeded = results.filter((r) => r.status === 'fulfilled').length;
  const failed = results.filter((r) => r.status === 'rejected').length;
  console.log(`Assigned ${succeeded} roles, ${failed} failures`);
}

Read roles from the JWT in application code:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Get the current user's role from their JWT
async function getCurrentUserRole(): Promise<AppRole | null> {
  const { data: { user }, error } = await supabase.auth.getUser();
  if (error || !user) return null;

  return (user.app_metadata?.role as AppRole) ?? null;
}

// Get the current user's organization
async function getCurrentOrg(): Promise<string | null> {
  const { data: { user } } = await supabase.auth.getUser();
  return user?.app_metadata?.org_id ?? null;
}

// Check if current user has a specific role or higher
function hasRole(userRole: AppRole, requiredRole: AppRole): boolean {
  const hierarchy: Record<AppRole, number> = {
    admin: 4,
    editor: 3,
    member: 2,
    viewer: 1,
  };
  return hierarchy[userRole] >= hierarchy[requiredRole];
}

// Middleware-style role check for API routes
async function requireRole(requiredRole: AppRole) {
  const role = await getCurrentUserRole();
  if (!role || !hasRole(role, requiredRole)) {
    throw new Error(
      `Access denied: requires "${requiredRole}" role, user has "${role ?? 'none'}"`
    );
  }
}

Step 2: RLS Policies with JWT Role Claims

Write Row Level Security policies that read auth.jwt() ->> 'role' and auth.jwt() -> 'app_metadata' ->> 'org_id' to enforce role-based and organization-scoped access.

Role-based RLS policies:

-- Create a helper function to extract role from JWT
CREATE OR REPLACE FUNCTION public.get_user_role()
RETURNS text AS $$
  SELECT coalesce(
    auth.jwt() -> 'app_metadata' ->> 'role',
    'viewer'  -- default role if not set
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Create a helper function to extract org_id from JWT
CREATE OR REPLACE FUNCTION public.get_user_org_id()
RETURNS text AS $$
  SELECT auth.jwt() -> 'app_metadata' ->> 'org_id';
$$ LANGUAGE sql STABLE SECURITY DEFINER;

-- Enable RLS on all tables
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.team_members ENABLE ROW LEVEL SECURITY;

-- Projects: org members can read, editors+ can create/update, admins can delete
CREATE POLICY "org_members_read_projects" ON public.projects
  FOR SELECT USING (
    org_id = get_user_org_id()
  );

CREATE POLICY "editors_create_projects" ON public.projects
  FOR INSERT WITH CHECK (
    org_id = get_user_org_id()
    AND get_user_role() IN ('admin', 'editor')
  );

CREATE POLICY "editors_update_projects" ON public.projects
  FOR UPDATE USING (
    org_id = get_user_org_id()
    AND get_user_role() IN ('admin', 'editor')
  );

CREATE POLICY "admins_delete_projects" ON public.projects
  FOR DELETE USING (
    org_id = get_user_org_id()
    AND get_user_role() = 'admin'
  );

-- Documents: org-scoped with role-based write access
CREATE POLICY "org_read_documents" ON public.documents
  FOR SELECT USING (
    org_id = get_user_org_id()
  );

CREATE POLICY "editors_write_documents" ON public.documents
  FOR INSERT WITH CHECK (
    org_id = get_user_org_id()
    AND get_user_role() IN ('admin', 'editor')
  );

CREATE POLICY "owner_or_admin_update_documents" ON public.documents
  FOR UPDATE USING (
    org_id = get_user_org_id()
    AND (
      created_by = auth.uid()
      OR get_user_role() = 'admin'
    )
  );

-- Team members: admins manage team, members can read
CREATE POLICY "org_read_team" ON public.team_members
  FOR SELECT USING (
    org_id = get_user_org_id()
  );

CREATE POLICY "admins_manage_team" ON public.team_members
  FOR ALL USING (
    org_id = get_user_org_id()
    AND get_user_role() = 'admin'
  );

Organization-scoped access table schema:

-- Organizations table
CREATE TABLE public.organizations (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Team members junction table
CREATE TABLE public.team_members (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  org_id uuid REFERENCES public.organizations(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member' CHECK (role IN ('admin', 'editor', 'member', 'viewer')),
  invited_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now(),
  UNIQUE(org_id, user_id)
);

-- Projects scoped to organizations
CREATE TABLE public.projects (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  org_id uuid REFERENCES public.organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  created_by uuid REFERENCES auth.users(id),
  created_at timestamptz DEFAULT now()
);

-- Index for fast org-scoped queries
CREATE INDEX idx_team_members_org ON public.team_members(org_id);
CREATE INDEX idx_team_members_user ON public.team_members(user_id);
CREATE INDEX idx_projects_org ON public.projects(org_id);

Step 3: API Key Scoping and Role Enforcement in Application Code

See API key scoping and role enforcement for server-side withRole() middleware, per-request client creation, admin panel operations (list members, invite users, change roles), and organization management patterns.

Output

After completing this skill, you will have:

• Role assignment via app_metadata — admin.updateUserById() sets role claims on user JWTs
• JWT claim extraction — get_user_role() and get_user_org_id() SQL helper functions
• Role-based RLS policies — SELECT/INSERT/UPDATE/DELETE scoped by role hierarchy (admin > editor > member > viewer)
• Organization-scoped access — multi-tenant isolation via org_id in JWT claims and RLS policies
• Application-layer enforcement — withRole() middleware for API routes with proper 401/403 responses
• Admin panel operations — list members, invite users, change roles with both database and JWT updates
• Role hierarchy checking — hasRole() function supporting role escalation comparison

Error Handling

Examples

Example 1 — Quick role check in a component:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, anonKey);

async function canEditProject(): Promise<boolean> {
  const { data: { user } } = await supabase.auth.getUser();
  const role = user?.app_metadata?.role;
  return role === 'admin' || role === 'editor';
}

Example 2 — Verify RLS policies work correctly:

-- Test as an editor in org-123
SET request.jwt.claims = '{"sub": "user-uuid", "role": "authenticated", "app_metadata": {"role": "editor", "org_id": "org-123"}}';

-- Should return only org-123 projects
SELECT * FROM projects;

-- Should succeed (editors can create)
INSERT INTO projects (org_id, name, created_by) VALUES ('org-123', 'Test', 'user-uuid');

-- Should fail (editors cannot delete)
DELETE FROM projects WHERE id = 'some-project-id';

RESET request.jwt.claims;

Example 3 — Onboard a new organization:

async function onboardOrganization(orgName: string, adminEmail: string) {
  // 1. Create the organization
  const { data: org } = await adminClient
    .from('organizations')
    .insert({ name: orgName, slug: orgName.toLowerCase().replace(/\s+/g, '-') })
    .select('id')
    .single();

  // 2. Assign the creator as admin
  const { data: { users } } = await adminClient.auth.admin.listUsers();
  const adminUser = users.find((u) => u.email === adminEmail);

  if (adminUser && org) {
    await setUserRole(adminUser.id, 'admin', org.id);
    await adminClient.from('team_members').insert({
      org_id: org.id,
      user_id: adminUser.id,
      role: 'admin',
    });
  }

  return org;
}

Resources

• Custom Claims and RBAC — Supabase Docs
• Auth Admin updateUserById — Supabase Docs
• Row Level Security — Supabase Docs
• auth.jwt() Function — Supabase Docs
• Multi-tenancy Patterns — Supabase Docs
• inviteUserByEmail — Supabase Docs

Next Steps

• For database migration patterns, see supabase-migration-deep-dive
• For security hardening and API key scoping, see supabase-security-basics
• For data handling and GDPR compliance, see supabase-data-handling