Effective Supabase RBAC

NOTE this Blog is still a WIP

Supabase is awesome - Probably everyone

Its a no brainer to use supabase to get started on projects that quickly prototype and scale without shifting stacks but when scaling we do need to make sure we do it the right way, here we talk about something extremely essential for many apps so lets take a deep dive into effective Supabase Role-Based Access Control (RBAC)! In this guide, I'll walk you through the essential steps to set up RBAC within your Supabase project, ensuring secure and granular access controls for your application.

To begin with RBAC, it's crucial to establish a solid foundation. We'll start by creating the necessary database tables that will define roles, permissions, and their associations. Let's dive right in!,

Decide if you use an external superadmin panel or not, if you want an external super admin panel you create a different schema to make it bulletproof

Start off with with the ROLES table, this will have the roles name and description which will be used to map towards permissions and more that we will be defining in the future.

CREATE TABLE
    IF NOT EXISTS public.role (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
        name TEXT NOT NULL UNIQUE,
        display_name TEXT NOT NULL,
        description TEXT,
        active BOOLEAN NOT NULL DEFAULT TRUE,
        created_at TIMESTAMP
        WITH
            TIME ZONE NOT NULL DEFAULT now (),
            updated_at TIMESTAMP
        WITH
            TIME ZONE NOT NULL DEFAULT now (),
             deleted_at TIMESTAMP
        WITH
            TIME ZONE
    );

Next up we create the permissions table:

create table if not exists public.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 NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ NULL
);

This has the type column which can be any one of SELECT INSERT UPDATE or DELETE

then object column which has the table name

so when we say a permission of SELECT ON BLOGS meaning the user has view permission for blogs.

Now that we have role and permission its time to map it. Like if I am a USER what permissions are supposed to be with me:

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

role_id and permission_id maps the role and permission, we make both of them primary keys so that we do not have duplicate pairs

With this we are almost done, now to assign users to a role we need another table, or if you wanna map a role to the user table you could do that too, but i like keeping it in a separate table to make admin easy.

create table if not exists public.user_role(
  user_id UUID NOT NULL REFERENCES public.interviewers(id),
  role_id UUID NOT NULL REFERENCES public.role(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ NULL,
 
  PRIMARY KEY(user_id, role_id)
);

The table Schema is done! To make admin easy, lets create views to make sense of things without jumping in and out of multiple tables:

CREATE or replace view public.interviewer_role_map as SELECT
    i.name,
    i.email,
    ur.role_id,
    i.id,
    r.name as role_name
FROM
    public.interviewer i
JOIN
    public.user_role ur ON i.id = ur.user_id
JOIN
    public.role r ON ur.role_id = r.id;

interviewer_role_map helps us get an instant list of all users with relevant data and their assigned roles.

Next we make granular_permissions view to see what exactly is going on when we set our RLS.

create or replace view public.granular_permissions as
select
 
  ur.user_id as user_id,
  r.name as role,
  p.action as permission
 
from public.permission p
 
left join public.role_permission rp on
  rp.permission_id = p.id
 
left join public.role r on
  r.id = rp.role_id
 
left join public.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;

We are done with everything related to definition now. Lets define some roles.

insert into public.role (name,description,active,updated_at) values
('Super Admin', 'Owns Everything, can assign and unassign roles, CRUD access for all tables', 'true', CURRENT_TIMESTAMP),
('Manager', 'Same as Super Admin except Employee Master and rbac assignment', 'true', CURRENT_TIMESTAMP),
('User', 'Read only Access', 'true', CURRENT_TIMESTAMP);

here we have 3 simple roles:

User- A person who is authenticated but has least privilege our business logic will auto-assign user role to a person when they first login
Manager (you can call this an admin too) - A person with elevated permissions
Super User - A person who has the highest privilege usually reserved to perform potentially destructive actions like assigning others roles, deleting users etc.

Next up we populate the permission table with all the 4 CRUD operations on each table:

insert into
    public.permission (type, object)
values
    ('SELECT', 'USERS'),
    ('INSERT', 'USERS'),
    ('UPDATE', 'USERS'),
    ('DELETE', 'USERS'),
    ('SELECT', 'BLOGS'),
    ('INSERT', 'BLOGS'),
    ('UPDATE', 'BLOGS'),
    ('DELETE', 'BLOGS'),

Now we map the roles to the permissions: it makes sense to give super admin roles for both users and blogs, but a manager should only have access to users and not blogs.

insert into public.role_permission (role_id, permission_id) values
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'USERS' and type = 'SELECT')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'USERS' and type = 'INSERT')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'USERS' and type = 'UPDATE')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'USERS' and type = 'DELETE')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'BLOGS' and type = 'SELECT')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'BLOGS' and type = 'INSERT')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'BLOGS' and type = 'UPDATE')),
    ((select id from public.role where name = 'Super Admin'), (select id from public.permission where object = 'BLOGS' and type = 'DELETE')),
    ((select id from public.role where name = 'Manager'), (select id from public.permission where object = 'USERS' and type = 'SELECT')),
    ((select id from public.role where name = 'Manager'), (select id from public.permission where object = 'USERS' and type = 'INSERT')),
    ((select id from public.role where name = 'Manager'), (select id from public.permission where object = 'USERS' and type = 'UPDATE')),
    ((select id from public.role where name = 'Manager'), (select id from public.permission where object = 'USERS' and type = 'DELETE'));
    ((select id from public.role where name = 'User'), (select id from public.permission where object = 'BLOGS' and type = 'SELECT'));
    ((select id from public.role where name = 'User'), (select id from public.permission where object = 'BLOGS' and type = 'INSERT'));
    ((select id from public.role where name = 'User'), (select id from public.permission where object = 'BLOGS' and type = 'UPDATE'));
    ((select id from public.role where name = 'User'), (select id from public.permission where object = 'BLOGS' and type = 'DELETE'));