RBAC enforced by RLS in Supabase

Overview

This article will explain how you can implement a simple role based access control (RBAC) system in Supabase.

Design

--create schema
create schema if not exists rbac;


--create tables
create table if not exists rbac.role(
  id UUID DEFAULT gen_random_uuid() primary key,
  name TEXT NOT NULL,
  description TEXT NULL,
  active BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NULL,
  deleted_at TIMESTAMPTZ NULL
);

create table if not exists rbac.permission(
  id UUID DEFAULT gen_random_uuid() primary key,
  type TEXT NOT NULL, --select or insert or update or delete
  object TEXT NOT NULL, --table name
  action TEXT GENERATED ALWAYS AS (type || ' on ' || object) STORED,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NULL,
  deleted_at TIMESTAMPTZ NULL
);

create table if not exists rbac.role_permission(
  role_id UUID NOT NULL REFERENCES rbac.role(id),
  permission_id UUID NOT NULL REFERENCES rbac.permission(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NULL,
  deleted_at TIMESTAMPTZ NULL,
  
  PRIMARY KEY(role_id, permission_id)
);

create table if not exists rbac.user_role(
  user_id UUID NOT NULL REFERENCES auth.users(id), --Supabase specific field
  role_id UUID NOT NULL REFERENCES rbac.role(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NULL,
  deleted_at TIMESTAMPTZ NULL,
  
  PRIMARY KEY(user_id, role_id)
);


--create view to make RLS easier/readable
create or replace view rbac.granular_permissions as 
select

  ur.user_id as user_id,
  r.name as role,
  p.action as permission

from rbac.permission p

left join rbac.role_permission rp on
  rp.permission_id = p.id

left join rbac.role r on
  r.id = rp.role_id

left join rbac.user_role ur on
  ur.role_id = r.id
  
where 
  (r.active = true and r.deleted_at is null)
  and ur.deleted_at is null
  and rp.deleted_at is null
  and p.deleted_at is null;

ERD of the rbac schema

RLS policies

--RLS policies (4 for each table)
--replace table_name with your table's name
alter table public.table_name enable row level security;

create policy "Enable read for authenticated users" 
on table_name
for select
to authenticated
using (
    exists(
        select 1
        from rbac.granular_permissions gp
        where 
          gp.user_id = auth.uid() --Supabase specific function
          and gp.permission = 'SELECT on TABLE_NAME'
    )
);

create policy "Enable insert for authenticated users" 
on table_name
for insert
to authenticated
with check (
    exists(
        select 1
        from rbac.granular_permissions gp
        where 
          gp.user_id = auth.uid() --Supabase specific function
          and gp.permission = 'INSERT on TABLE_NAME'
    )
);

create policy "Enable update for authenticated users" 
on table_name
for update
to authenticated
using (
    exists(
        select 1
        from rbac.granular_permissions gp
        where 
          gp.user_id = auth.uid() --Supabase specific function
          and gp.permission = 'UPDATE on TABLE_NAME'
    )
)
with check (
    exists(
        select 1
        from rbac.granular_permissions gp
        where 
          gp.user_id = auth.uid() --Supabase specific function
          and gp.permission = 'UPDATE on TABLE_NAME'
    )
);

create policy "Enable delete for authenticated users" 
on table_name
for update
to authenticated
using (
    exists(
        select 1
        from rbac.granular_permissions gp
        where 
          gp.user_id = auth.uid() --Supabase specific function
          and gp.permission = 'DELETE on TABLE_NAME'
    )
);

Breakdown

First, we’re creating a new schema called rbac. From there, we create 4 tables:

• role: this holds the name of our roles (e.g. principal, teacher, student, etc…)
• permission: this should hold 4 records for each table (e.g. INSERT, SELECT, UPDATE, DELETE)
• role_permission: this maps permissions to a role
• user_role: this maps users to a role

We’re also creating a view called granular_permissions to simplify our RLS policies. This view pulls together the user ID, the role, and the permission(s) associated with the role.

For the RLS policies, we will apply 4 to each table (i.e. 1 policy for each operation).

Testing

Consider a school. A school has a principal, teachers, students, etc… . The student should only be able to read grades, the principal should be able to read and change grades, and the teacher should be able to read, add, update, and delete grades.

With this in mind, create a new table on the public schema called GRADES. Add some columns and insert some dummy data.

Next, run the SQL code from the previous section. For the RLS policies, you will need to replace TABLE_NAME with GRADES. This should create a new schema, 4 tables, a view, and apply the RLS policies to the GRADES table.

Next, create 3 new users: one will be the student, one will be the principal, and one will be the teacher.

Now lets set up the roles, permissions, role-permissions, and user-roles. Run the following SQL to insert the data. You will need to update rbac.role.id and auth.users.id to the actual UUIDs.

insert into rbac.role(name, active)
values
    ('principal', true),
    ('teacher', true),
    ('student', true);
    
insert into rbac.permission(type, object)
values
    ('SELECT', 'GRADES'),
    ('INSERT', 'GRADES'),
    ('UPDATE', 'GRADES'),
    ('DELETE', 'GRADES');

--values need to be updated
insert into rbac.role_permission(role_id, permission_id)
values
    ('rbac.role.id for principal role', 'rbac.permission.id for SELECT on GRADES'),
    ('rbac.role.id for principal role', 'rbac.permission.id for UPDATE on GRADES'),
    ('rbac.role.id for teacher role', 'rbac.permission.id for SELECT on GRADES'),
    ('rbac.role.id for teacher role', 'rbac.permission.id for UPDATE on GRADES'),
    ('rbac.role.id for teacher role', 'rbac.permission.id for INSERT on GRADES'),
    ('rbac.role.id for teacher role', 'rbac.permission.id for DELETE on GRADES'),
    ('rbac.role.id for student role', 'rbac.permission.id for SELECT on GRADES');

--values need to be updated
insert into rbac.user_role(user_id, role_id)
values
    ('auth.users.id for principal', 'rbac.role.id for principal role'),
    ('auth.users.id for teacher', 'rbac.role.id for teacher role'),
    ('auth.users.id for student', 'rbac.role.id for student role');

Finally, use Postman or any other API testing tool to test the RBAC system. Authenticate with the user who you designated as your teacher and use the access token to INSERT on the GRADES table — you should get a 201 (created) response. Next, authenticate with your user who you've designated as your principal and use the access token to INSERT into GRADES — you should get a 403 (forbidden) response because it violates the RLS policy. Now, authenticate with your student and use the access token try to INSERT into GRADES — again, you should get a 403 (forbidden) response because it violates the RLS policy.

Final thoughts

There is definitely room to improve on this simple design, but it’s definitely a good idea to keep RBAC simple as it can get complicated quick.

Part Two: Row Level Security

Guide To Design Database For RBAC In MySQL