Add Roles & Permissions to Any Web App With Supabase

A role describes who someone is in your app. A permission describes one thing that role is allowed to do.

Example roles

Admin

Manages users, edits everything and views reports.

Editor

Creates and edits content.

Viewer

Views content only.

These are only examples. Use the roles your app actually needs — Admin and Member, Owner, Manager and Staff, or names of your own.

Plan Your Roles & Permissions

Plan Your Roles & Permissions

Describe the roles your app needs and let AI turn them into a clear role and permission matrix before anything is built.

I want to add roles and permissions to this existing web app. It already uses Supabase Auth for sign-in. What the app does: {{APP_PURPOSE}} Roles I need: {{ROLES}} What each role should be able to view: {{VIEW_ACCESS}} What each role should be able to create, edit, delete or manage: {{CHANGE_ACCESS}} Role that new users should get by default: {{DEFAULT_ROLE}} Do not change any files, and do not change the database. This task only defines the access model. Look through the project only enough to use its real names — the actual pages, features and data, such as projects, invoices or posts — so the permissions describe this app rather than a generic template. Do not audit authentication or security rules yet. That is the next step. Turn my answers into a clear role and permission matrix: 1. Roles: each role, with one sentence on who it is for 2. Permissions: each one named as resource.action, for example posts.create, posts.delete, users.manage or reports.view. Include only permissions this app actually needs. 3. Matrix: a table with one row per permission and one column per role, marking which roles have it 4. Own records: if a permission should only apply to the user's own records, such as editing their own posts, say so. Keep "own" and "all" as separate permissions when both are needed. 5. Default role: the role every new account receives. It must be the role with the least access, never an admin-level role. Rules: - If I named roles, use my names exactly. Do not rename them or add roles I did not ask for. If something essential seems to be missing, ask me first. - Each user has exactly one role. - Give every role the least access that still lets it do its job. - If my answers conflict or leave a gap, ask me instead of guessing. Finish with the complete matrix in a compact form I can copy and keep. Then wait for my approval. Do not implement anything.

What does your web app do?

A client portal where our team shares project updates and files with customers...

Which roles do you need?

What should each role be able to view?

Admin: everything. Editor: all posts, including drafts. Viewer: published posts only.

What should each role be able to create, edit, delete or manage?

Admin: manage users and delete anything. Editor: create and edit posts, but not delete them. Viewer: nothing.

Which role should new users get by default?

Viewer

Adjust the matrix with the AI until it matches how your app should work. Keep this conversation open — every later prompt builds on the approved matrix. If you ever start a new conversation, paste the matrix in first.

Approve the Role Matrix

You should now know exactly which roles and permissions your app needs.

Every role my app needs is listed, using my names

Each role has only the permissions it really needs

New users get the role with the least access

I saved a copy of the final matrix

Confirm Supabase Sign-In Works

Roles are added on top of the sign-in your app already has.

People can already sign up and sign in to my app with Supabase Auth

The data I want to protect is stored in Supabase tables

Add User Authentication to Any Website with Supabase

Set up sign-in first if your app does not have it yet, then come back.

/video/add-user-authentication-supabase

This guide decides what each role is allowed to do. Keeping each user's private records away from other users is a separate job with its own guide — the two work together.

Make Sure Users Can Only Access Their Own Data With Supabase

Lock each user's private records to their own account.

/video/users-own-data-supabase

Inspect the Current Access Setup

Nothing changes in this step. The AI finds out how access works today and plans the smallest safe changes.

Check the Web App

Let AI inspect how sign-in, data access and any existing roles work today, then return a short plan before changing anything.

Using the role and permission matrix we just approved, inspect this project before anything is added. Do not change any files or the database in this task. If the approved matrix is not in this conversation, STOP and ask me to paste it. Find and report: - How Supabase Auth is set up: the Supabase client, sign-in, sessions and how the app reads the signed-in user - The pages and routes that require a signed-in user, and how they are protected today - The Supabase tables behind the data in the matrix, and which features read or change each one - Any existing role column, roles table, is_admin flag, admin check, custom claim or other authorization logic - Any role or permission read from user_metadata or another value users can edit themselves - Edge Functions, server routes, API endpoints and database functions that read or change this data - Places where access is controlled only in the frontend, such as hidden buttons or client-side route guards - Whether a Supabase Secret or service-role key appears anywhere in browser code Do NOT assume the SQL in this repository matches what is live in Supabase. Policies and functions are often changed directly in the Supabase Dashboard. Give me ONE read-only SQL query to run in the Supabase SQL Editor. For the relevant tables only, it should report: - Whether Row Level Security is enabled - Each policy's name, command, roles, USING and WITH CHECK expressions - Any existing role-related tables, columns, types or functions - Any existing function that looks like an auth hook The query must read metadata only. Do not select or print any user rows. Do not modify anything. Also ask me to open Authentication → Hooks in the Supabase Dashboard and tell you whether a Custom Access Token hook is already enabled. That setting is not visible in SQL. I will run the query and paste the result back into this conversation. Then give me a short implementation plan: 1. Current setup: how sign-in and access control work today 2. Existing authorization: anything we must reuse or extend instead of duplicating 3. What each role will protect: each table and action mapped to its permission from the matrix 4. Frontend-only gaps: access that is currently enforced only in the browser 5. Server-side actions that need permission checks 6. Smallest safe plan: the changes, in order 7. Blockers: anything I must fix first Rules for the plan: - If an authorization system already exists, extend it. Do not create a second one. - Do not rebuild or replace authentication. - Do not restructure existing tables or data unless it is genuinely required. - Keep any existing per-user protection, where users can only access their own records. Role rules must work alongside it. Wait for my approval before changing anything.

Run the read-only SQL it gives you in the Supabase SQL Editor, then paste the result back into the same conversation. Approve the plan once it maps every table and action to your matrix.

Create the Role Structure

Tables for your roles, their permissions and each user's role, built from the approved matrix.

Create the Role Structure

Generate the SQL for roles, permissions and user roles, with safe access rules and a default role for every new account.

Create the Supabase database structure for the role and permission matrix we approved, following the implementation plan. If the approved matrix or the plan is not in this conversation, STOP and ask me for them. Use the current recommended Supabase pattern for role-based access control, adapted to this project: - The app's roles, for example as a Postgres enum or a small roles table - The app's permissions, named exactly as in the matrix - A role_permissions table that maps each role to its permissions, filled in from the matrix - A user_roles table that links each user (auth.users id) to exactly one role If the plan found an existing role system, extend it instead of creating a second one. Use naming consistent with the project. Security requirements: - Enable Row Level Security on every new table - Signed-in and signed-out users must not be able to create, change or delete roles, permissions or role assignments from the browser. Nobody can give themselves a role. - If the app needs to read the role-to-permission mapping, allow signed-in users read-only access to that mapping only. Users must not read other users' role assignments. - Never store roles or permissions in user_metadata or any other field users can edit. - Never put the service role key or any Secret key in browser code. Default role: - Every new account must receive the default role from the matrix automatically, for example through a minimal database trigger when a new auth user is created. Keep it simple and reliable — a failing trigger would block sign-ups. - The default role must never depend on anything the user sends during sign-up, including user_metadata. - Give existing users without a role the default role too. Do not give anyone an elevated role in this SQL. Give me: 1. A short list of what the SQL creates 2. ONE SQL block to run in the Supabase SQL Editor, written so it is safe to run again 3. What I should see in Supabase afterwards If this project keeps database changes in migration files, also save the same SQL as a new migration so the repository matches what is live. Do not create the access-token hook or change existing table policies yet. Those come next.

Read the list of what the SQL creates Open the SQL Editor in your Supabase project Paste the SQL and run it once Confirm it finishes without errors

Expected result

The Table Editor shows your new role tables, the role-to-permission rows match your matrix, and every existing user has the default role.

Add the Access-Token Hook

A small database function that adds each user's role to their sign-in token, where Supabase can trust it.

Add the Access-Token Hook

Create the Custom Access Token hook that adds the user's role to their token, with the permissions Supabase Auth needs and nothing more.

Now create a Supabase Custom Access Token hook that adds each user's role to their access token, using the role structure we just created. Requirements: - A Postgres function, for example public.custom_access_token_hook, that looks up the user's role in user_roles and adds it as a custom claim, for example user_role - Keep every existing claim. Never change or remove the standard role claim — Supabase uses it for the database role, so the app role needs its own claim name. - If the user has no role, set the custom claim to null. Do not guess a role. - If this project already has a Custom Access Token hook, extend that function instead of replacing it, so its existing claims keep working - Keep it fast: one simple lookup, no network calls Permissions — follow the current Supabase requirements for supabase_auth_admin: - Grant usage on the schema to supabase_auth_admin - Grant execute on the hook function to supabase_auth_admin - Revoke execute on the hook function from authenticated, anon and public - Grant supabase_auth_admin only the read access the hook needs on user_roles, and add a Row Level Security policy that lets supabase_auth_admin read it - Do not expose the hook function or other users' role assignments to anon or authenticated users Give me: 1. ONE SQL block to run in the Supabase SQL Editor, safe to run again 2. The exact schema and function name I should select when I enable the hook in the Supabase Dashboard 3. What should happen after I enable it If this project keeps database changes in migration files, also save the same SQL as a new migration. Do not change existing table policies or app code yet.

Run it in the SQL Editor the same way as the previous SQL, and note the function name the AI gives you.

Expected result

The SQL finishes without errors. Nothing changes for your users yet — the hook only runs once you enable it.

Enable the Hook

Tell Supabase Auth to run the hook every time it issues a token.

Supabase Authentication Hooks

Add a new hook and choose Custom Access Token Choose Postgres as the hook type Select the schema and function the AI named Enable the hook and save

Expected result

Signing in still works as before, and every new sign-in now receives a token that carries the user's role.

If sign-in stops working

Turn the hook off on the same Hooks page — sign-in works again right away. Then give the AI the error from your app or from the Auth logs in Supabase, fix the function, and enable the hook again. The most common cause is a missing permission that stops supabase_auth_admin from reading the role table.

Assign Test Roles

Use test accounts only, never a real user's account.

Set a Test Account's Role

Get a short SQL snippet that gives one account a role, plus a read-only query to check every account's role.

Give me a short SQL snippet I can run in the Supabase SQL Editor to set one account's role, using the role structure we created. - Find the account by its email address - Replace any role the account already has, so it keeps exactly one role - Put the email address and the role name in clearly marked placeholders I can edit - Accept only role names from our matrix Also give me one read-only query that lists each account's email with its current role, so I can check the result. This is for my test accounts only. Do not build an admin screen for managing roles.

Sign up at least two test accounts in your app In the SQL Editor, run the snippet once per account, giving each a different role — include your default role and your most powerful role Run the check query and confirm every test account has the role you expect

IMPORTANT

Roles are assigned only here or through a trusted server-side path. Nothing inside your app may let an account change its own role.

Verify the Role Claim

Confirm the role reaches the sign-in token before anything depends on it.

Check the Role Claim

Add a temporary development-only check that shows the signed-in user's role claim, then remove it once confirmed.

Help me confirm the Custom Access Token hook works. Add a temporary, development-only check that shows the signed-in user's role claim from their current Supabase session — for example, logged once to the browser console after sign-in. - Read the claim from the session's access token, for example with supabase.auth.getClaims() if this project's Supabase client supports it - Show only the role claim. Never print or log the full access token. - Make sure the check cannot run in production Tell me exactly where to look. When I confirm it works, remove the check again.

Sign out, then sign in with your first test account Confirm the check shows the role you assigned to it Repeat with your other test accounts Ask the AI to remove the temporary check

Role changes need a fresh token

After a role changes, the user's current token can still carry the old role until the session refreshes. While testing, sign out and back in, or refresh the session, before judging whether a new role works.

Frontend vs. backend

Hiding a button or a page is user experience. Refusing the request in Supabase is security. This step makes Supabase refuse anything a role is not allowed to do — even when someone calls the API directly.

Protect the Database Tables

Row Level Security decides what each role can read, create, edit and delete.

Enforce the Permissions With RLS

Update the Row Level Security policies so every action requires the right permission, alongside your existing per-user rules.

Now enforce the approved role and permission matrix in Supabase, using the role claim from the access-token hook and the role_permissions table. If the approved matrix, the implementation plan or the live policy report is not in this conversation, STOP and ask me for them. Do not guess the current policies. 1. Create one reusable permission check, for example a function authorize('posts.delete') that returns true only when the current user's role has that permission. It must read the role from the trusted access-token claim through auth.jwt(), never from user_metadata or anything the browser sends. Make it security definer with a fixed, empty search_path, and have every policy call it instead of repeating role logic. 2. Update the Row Level Security policies on each table in the plan so every action — read, create, edit and delete — requires the matching permission from the matrix. 3. Keep existing per-user data protection. Where a permission covers only the user's own records, the policy must check both the permission and ownership with auth.uid(). Do not rewrite ownership rules that already work. 4. Replace, do not just add. Supabase policies combine permissively: if an existing policy already allows every signed-in user, a new role policy next to it changes nothing. Replace or remove only the in-scope policies that allow more than the matrix. Rules: - Never use true for data that should be restricted - Do not disable Row Level Security to make something work - Leave intentionally public tables, unrelated tables and their policies unchanged - Signed-out users must not reach protected data - Follow current Supabase guidance for policy performance, for example wrapping auth.jwt() and the permission check in a select Give me: 1. A plain-language list of every policy you will add, change or remove, and why 2. ONE SQL block to run in the Supabase SQL Editor 3. ONE read-only query I can run afterwards to confirm the live policies 4. What I should see afterwards If this project keeps database changes in migration files, also save the same SQL as a new migration. Do not run anything for me, and do not change app code yet.

Review the policy changes before you run them

This SQL can replace existing access rules. Read the AI's list of changes first — a wrong policy can expose private data or lock your real users out.

Run the SQL once in the Supabase SQL Editor Run the read-only check query and paste the result back into the conversation Continue once the AI confirms every table in the plan matches your matrix

Expected result

Every table in your plan now requires the right permission for each action, and no remaining policy allows more than your matrix.

Protect Server-Side Actions

Edge Functions, server routes and database functions need the same checks. If the plan found none, this prompt simply confirms that.

Protect Server-Side Actions

Make every privileged Edge Function, server route and database function check the caller's permission on the server.

Now protect the server-side actions from the implementation plan: Edge Functions, server routes, API endpoints and database functions that read restricted data or perform a privileged action. For each one: - Verify the caller's session on the server before doing anything, and reject requests without a valid session - Check the required permission on the server, using the verified token's role claim or the user's current role in the database. Never trust a role, permission or user id sent in the request body, query string or headers. - A database function marked security definer bypasses Row Level Security, so it must check the permission itself - Keep the Supabase Secret or service-role key on the server only - Return a clear permission error when access is refused If there are no such actions, say so and change nothing. When you are done, list each action, the permission it now requires, and exactly where the check happens. Run the project's build afterwards.

Update the Interface for Each Role

The interface follows the rules Supabase now enforces. It adds a clear experience on top of them and never replaces them.

Connect Roles to the Interface

Show each role the right navigation, pages and actions from one shared permission helper, while keeping your existing design.

Now update the app's interface so each user gets the experience their role allows, using the approved role and permission matrix and the implementation plan. The database rules from the previous step stay the security boundary. These interface changes are for a clear user experience — they must never loosen or replace those rules. Requirements: - Read the signed-in user's role from the trusted access-token claim, and their permissions from the role_permissions mapping or the permission check we created. Never from user_metadata, localStorage or anything else the user can edit. - Put the permission logic in ONE shared place, for example a can('posts.delete') helper, and use it everywhere. Do not scatter role-name checks across components. - Show each role the navigation, pages and actions it is allowed to use - Hide or disable actions the user cannot perform - Protect role-specific routes: a user without permission who opens one directly is redirected or sees a clear no-access message - Update the role when the session refreshes, so a role change shows up after the user signs in again - If Supabase refuses a request, show a clear message instead of a broken screen - Preserve the existing design, layout and components Do not: - Build a new admin dashboard. If the app already has one, apply the same permission checks to it where appropriate. - Rebuild authentication or add a second authorization system - Change database policies to make the interface work When you are done, list the files you changed and what each role now sees. Run the project's build afterwards.

Expected result

Each test account sees only the navigation, pages and actions its role allows, and opening a restricted page directly shows a clear no-access message or a redirect.

Add an Admin Dashboard to Any AI-Built Web App

Want a full admin area for managing users and records? Build it with its own guide.

/video/add-an-admin-dashboard-to-any-ai-built-web-app

Test Each Role in the App

Use your test accounts. A private browser window makes switching between them easy.

Sign in with one of your test accounts Work through the checks below Sign out and repeat with every other test account

The pages and actions this role is allowed to use are available

Restricted buttons, links and pages are hidden or disabled

Opening a restricted page directly by its URL is refused or redirected

Actions this role is allowed to do still work

Verify the Database Boundary

A hidden button is not proof. This checks that Supabase itself refuses what each role is not allowed to do.

Verify Every Role in Supabase

Test each role directly against Supabase so forbidden reads, writes and server actions are proven to be refused.

Test the role rules directly at the Supabase level, not through the interface. Use the safest method already available in this project: 1. An existing test runner or test utilities, if the project has them 2. Otherwise a temporary LOCAL-ONLY script that uses the project's normal Supabase client and its Publishable or public key Do NOT use the Secret or service-role key. It bypasses Row Level Security, so the test would prove nothing. If you need to sign in as my test accounts, ask me to put their credentials in local, uncommitted environment variables. Never commit passwords, never put them in source code and never print them. For each test account, based on the approved matrix: - FORBIDDEN: wherever the role has restrictions, try at least one read, create, edit and delete it is NOT allowed to do. Expected: refused — an error, no rows returned or zero rows changed. Re-read afterwards to confirm nothing changed. - ALLOWED: perform one action the role IS allowed to do. Expected: it works. - SERVER ACTIONS: call each protected Edge Function, server route or database function without the required permission. Expected: refused. - OWN RECORDS: where a permission covers only the user's own records, try to change another account's record. Expected: refused. - SIGNED OUT: repeat a protected request with no session. Expected: no protected data and no changes. Use test data only. Do not change production data. Remove any temporary script afterwards and do not commit it. Report expected versus actual for every test, then finish with exactly one verdict: ROLES ENFORCED or NEEDS ATTENTION If NEEDS ATTENTION, list only what is still failing.

Continue when the AI reports ROLES ENFORCED. If it reports NEEDS ATTENTION, fix the listed issues and run the check again.

Test a Role Change

When a role changes, the permissions must follow.

Change one test account's role with the SQL snippet from Step 3 Sign out and back in with that account, or refresh its session Check what the account can see and do now

The account now has what its new role allows

Anything the new role is not allowed to do is refused

Test a Brand-New Account

A new sign-up must never start with more access than you planned.

Sign up a brand-new test account Run the check query from Step 3 and look up its role Sign in with it and try the app

The new account has your default role

It cannot reach anything beyond what the default role allows

Your web app now has real role-based access control: each user sees the right experience, and restricted data and actions are protected by Supabase — even if someone tries to bypass the interface.