# How to Restrict Access by Role in Supabase RLS

- Tool: Supabase
- Difficulty: Advanced
- Time required: 20-25 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+, PostgreSQL 14+
- Last updated: March 2026

## TL;DR

To restrict database access by user role in Supabase, store roles either in user JWT claims (app_metadata) or in a dedicated roles table, then reference them in RLS policies. JWT-based roles are fastest because the role is read directly from the token without an extra query. Table-based roles offer more flexibility for complex permission models. Both approaches use auth.jwt() in the RLS policy to check the current user's role before allowing or denying the operation.

## Role-Based Access Control with Supabase RLS Policies

Row Level Security is the backbone of Supabase's security model, but basic owner-only policies are not enough for applications with multiple user roles like admin, editor, and viewer. This tutorial shows you how to extend RLS with role-based checks so that different users have different levels of access to the same table. You will learn both the JWT claims approach and the roles table approach, with trade-offs for each.

## Before you start

- A Supabase project with RLS enabled on target tables
- Users set up with Supabase Auth
- Understanding of basic RLS policies (using auth.uid())
- For JWT approach: ability to set app_metadata via admin API or Edge Function

## Step-by-step guide

### 1. Choose between JWT claims and a roles table

There are two main patterns for role-based RLS. The JWT claims approach stores the user's role in app_metadata, which is embedded in the JWT token. RLS policies read it with auth.jwt()->'app_metadata'->>'role' — no database query needed, making it the fastest option. The roles table approach stores roles in a separate table and joins against it in policies. This is slower per query but lets you change roles instantly without waiting for token refresh. Choose JWT for simple role models (admin/user) and a roles table for complex multi-role or multi-tenant systems.

```
-- Option A: Read role from JWT claims (fast, no query)
-- Set role via admin API: supabase.auth.admin.updateUserById(userId, { app_metadata: { role: 'admin' } })
-- Access in RLS:
(select auth.jwt()->'app_metadata'->>'role') = 'admin'

-- Option B: Read role from a roles table (flexible, queryable)
-- Create a user_roles table and join in policies:
EXISTS (
  SELECT 1 FROM user_roles
  WHERE user_roles.user_id = (select auth.uid())
  AND user_roles.role = 'admin'
)
```

**Expected result:** You have chosen the approach that fits your application's complexity and latency requirements.

### 2. Set user roles via JWT app_metadata

To use the JWT approach, set the user's role in app_metadata using the admin API. This must be done server-side (Edge Function or backend) because app_metadata cannot be set from the client. Once set, the role is included in every JWT the user receives. The auth.jwt() function in SQL policies reads the decoded token.

```
// Server-side: Set a user's role using the admin API
// MUST use the service role key — never expose this client-side
import { createClient } from '@supabase/supabase-js'

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

// Assign admin role
const { data, error } = await supabaseAdmin.auth.admin.updateUserById(
  userId,
  { app_metadata: { role: 'admin' } }
)

// Assign editor role
await supabaseAdmin.auth.admin.updateUserById(
  userId,
  { app_metadata: { role: 'editor' } }
)

// The JWT now contains: { app_metadata: { role: 'admin' } }
// Accessible in SQL via: auth.jwt()->'app_metadata'->>'role'
```

**Expected result:** The user's JWT contains the assigned role in app_metadata, readable by RLS policies.

### 3. Write RLS policies that check JWT roles

Create policies that read the role from the JWT and grant or deny access accordingly. Admins can typically perform all operations, editors can read and update, and viewers can only read. Use the OR operator to combine role checks with ownership checks for layered permissions.

```
-- Table: articles
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- Admins can do everything
CREATE POLICY "Admins have full access" ON articles
  FOR ALL TO authenticated
  USING (
    (select auth.jwt()->'app_metadata'->>'role') = 'admin'
  );

-- Editors can read all and update their own
CREATE POLICY "Editors can read all articles" ON articles
  FOR SELECT TO authenticated
  USING (
    (select auth.jwt()->'app_metadata'->>'role') IN ('editor', 'admin')
  );

CREATE POLICY "Editors can update own articles" ON articles
  FOR UPDATE TO authenticated
  USING (
    (select auth.jwt()->'app_metadata'->>'role') = 'editor'
    AND (select auth.uid()) = author_id
  );

-- Viewers can only read published articles
CREATE POLICY "Viewers read published" ON articles
  FOR SELECT TO authenticated
  USING (
    status = 'published'
    OR (select auth.jwt()->'app_metadata'->>'role') IN ('editor', 'admin')
  );
```

**Expected result:** Admins can perform all CRUD operations, editors can read all and edit their own articles, and viewers can only see published articles.

### 4. Create a roles table for flexible role management

For complex permission models, create a user_roles table that maps users to roles. This approach supports multiple roles per user, time-limited roles, and tenant-scoped roles. RLS policies use EXISTS subqueries to check role membership. Add an index on user_id for fast lookups since this query runs on every request.

```
-- Create the roles table
CREATE TABLE user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
  created_at timestamptz DEFAULT now(),
  UNIQUE(user_id, role)
);

-- Index for fast lookups in RLS policies
CREATE INDEX idx_user_roles_user_id ON user_roles (user_id);

-- RLS on the roles table itself (only admins can manage)
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own roles" ON user_roles
  FOR SELECT TO authenticated
  USING ((select auth.uid()) = user_id);

CREATE POLICY "Admins can manage all roles" ON user_roles
  FOR ALL TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = (select auth.uid()) AND role = 'admin'
    )
  );

-- Use in other table policies
CREATE POLICY "Editors can update articles" ON articles
  FOR UPDATE TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = (select auth.uid())
      AND role IN ('editor', 'admin')
    )
  );
```

**Expected result:** Role assignments are stored in the database and checked by RLS policies on every request.

### 5. Test role-based policies with different users

Verify your policies work by testing with users assigned different roles. Sign in as each role and attempt operations that should be allowed and denied. In the SQL Editor, you can also test policies directly by setting the role and JWT claims for the session.

```
-- Test as an admin (in SQL Editor)
SET request.jwt.claims = '{"sub": "admin-uuid", "role": "authenticated", "app_metadata": {"role": "admin"}}';
SET role = 'authenticated';
SELECT * FROM articles; -- Should return all
INSERT INTO articles (title, author_id) VALUES ('Test', 'admin-uuid'); -- Should work

-- Test as a viewer
SET request.jwt.claims = '{"sub": "viewer-uuid", "role": "authenticated", "app_metadata": {"role": "viewer"}}';
SET role = 'authenticated';
SELECT * FROM articles; -- Should return only published
INSERT INTO articles (title, author_id) VALUES ('Test', 'viewer-uuid'); -- Should fail

-- Reset
RESET role;
RESET request.jwt.claims;
```

**Expected result:** Each role sees only the data and operations permitted by the RLS policies. Unauthorized operations return empty results or errors.

## Complete code example

File: `role-based-rls.sql`

```sql
-- ================================================
-- Role-Based Access Control with Supabase RLS
-- ================================================

-- 1. Create the articles table
CREATE TABLE articles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  content text DEFAULT '',
  status text DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
  author_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- 2. Create the user_roles table
CREATE TABLE user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
  created_at timestamptz DEFAULT now(),
  UNIQUE(user_id, role)
);

CREATE INDEX idx_user_roles_lookup ON user_roles (user_id, role);
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;

-- 3. Helper function for role checking
CREATE OR REPLACE FUNCTION public.has_role(required_role text)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.user_roles
    WHERE user_id = (select auth.uid())
    AND role = required_role
  );
$$;

-- 4. RLS policies for articles
CREATE POLICY "Admins: full access" ON articles FOR ALL TO authenticated
  USING (public.has_role('admin'));

CREATE POLICY "Editors: read all" ON articles FOR SELECT TO authenticated
  USING (public.has_role('editor') OR public.has_role('admin'));

CREATE POLICY "Editors: insert own" ON articles FOR INSERT TO authenticated
  WITH CHECK (
    (public.has_role('editor') OR public.has_role('admin'))
    AND (select auth.uid()) = author_id
  );

CREATE POLICY "Editors: update own" ON articles FOR UPDATE TO authenticated
  USING (public.has_role('editor') AND (select auth.uid()) = author_id)
  WITH CHECK (public.has_role('editor') AND (select auth.uid()) = author_id);

CREATE POLICY "Viewers: read published" ON articles FOR SELECT TO authenticated
  USING (status = 'published');

-- 5. RLS policies for user_roles
CREATE POLICY "Read own roles" ON user_roles FOR SELECT TO authenticated
  USING ((select auth.uid()) = user_id);

CREATE POLICY "Admins manage roles" ON user_roles FOR ALL TO authenticated
  USING (public.has_role('admin'));
```

## Common mistakes

- **Setting roles in raw_user_meta_data instead of app_metadata, allowing users to change their own role** — undefined Fix: Always store roles in app_metadata, which can only be modified server-side with the service role key. raw_user_meta_data is writable by the user via updateUser().
- **Not handling the case where a user has no role assigned** — undefined Fix: Add a default policy for users with no role that grants minimal access (e.g., read published content only). Test with a user who has NULL app_metadata to verify.
- **Calling auth.jwt() without wrapping in a SELECT subquery, causing per-row evaluation** — undefined Fix: Use (select auth.jwt()->'app_metadata'->>'role') instead of auth.jwt()->'app_metadata'->>'role'. The select wrapper caches the value for the entire statement.
- **Expecting JWT role changes to take effect immediately** — undefined Fix: JWT claims update only on token refresh (default: every hour). Either force a token refresh after role changes, or use a roles table for instant effect.

## Best practices

- Use app_metadata for roles (not raw_user_meta_data) because users cannot modify it from the client
- Wrap auth.jwt() and auth.uid() in SELECT subqueries for per-statement caching in RLS policies
- Create a helper function like has_role() to keep RLS policies readable and DRY
- Add indexes on the user_id column in roles tables since RLS checks run on every query
- Test policies with each role to verify both allowed and denied operations work correctly
- Use SECURITY DEFINER on helper functions that query the roles table, with search_path = '' for safety
- Default to the most restrictive access when no role is assigned — deny everything unless explicitly permitted
- Document your role hierarchy and which operations each role can perform on each table

## Frequently asked questions

### Should I use JWT claims or a roles table for role-based access?

Use JWT claims for simple role models (admin vs user) where role changes are infrequent. Use a roles table for complex permission models with multiple roles per user, tenant-scoped roles, or when role changes need to take effect immediately without waiting for token refresh.

### Can a user have multiple roles?

With the roles table approach, yes — each user can have multiple rows in user_roles. With JWT claims, store roles as an array in app_metadata and check with @> containment in policies. The roles table approach is more flexible for multi-role scenarios.

### How do I promote a user to admin safely?

Call supabase.auth.admin.updateUserById(userId, { app_metadata: { role: 'admin' } }) from a server-side function using the service role key. Always verify that the requesting user is already an admin before allowing role changes.

### What happens if RLS policies conflict for the same role?

PostgreSQL evaluates RLS policies with OR logic for the same operation. If any policy allows the action, it proceeds. This means a viewer SELECT policy and an admin SELECT policy both apply — the admin sees everything because their policy returns true for all rows.

### Do role checks in RLS affect query performance?

JWT-based role checks are essentially free — they read from the decoded token in memory. Roles table checks add a subquery per statement (not per row if you use a helper function with SELECT caching). Add an index on user_id to keep the lookup fast.

### Can RapidDev help design a role-based access control system for my Supabase project?

Yes. RapidDev can design and implement a complete RBAC system including role hierarchies, RLS policies, admin interfaces for managing roles, and thorough testing across all permission levels.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-restrict-access-by-role-in-supabase-rls
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-restrict-access-by-role-in-supabase-rls