Dynamic multi-tenant SQL-based RBAC with JWT enhancement, route authorization, and permission management using Supabase.

Dynamic Multi-Tenant RBAC with Route Authorization.sql

-- Role-Based Access Control (RBAC)
-- With live propagation of roles to JWT token in a multi-tenant application using Supabase refreshSession and custom_access_token_hook
-- A method of restricting table and route access to authorized users based on their role or permission level.



-- This SQL implements a dynamic, multi-tenant role-based access control (RBAC) system with route authorization. It includes:

-- A custom access token hook that enriches JWT claims with role and company data.
-- Functions to authorize actions, check route access, and retrieve authorized routes based on user roles.
-- Policies and table definitions to enforce access control within a multi-tenant setup.
-- Indexes to optimize permission and role lookups.
-- Practical examples and notes for dynamic permission updates using Supabase Realtime and JWT refresh.
-- This solution streamlines RBAC and route authorization management in a multi-tenant environment.



-- Function: public.custom_access_token_hook(event jsonb) returns jsonb
--
-- Description:
-- This function is a custom access token hook that modifies the claims in the provided JSONB event.
-- It retrieves the user's role ID and company ID from the database and adds them to the claims in the event.
-- This function is called whenever a new access token is generated.
-- It adds the user's role_id and company_id to the JWT claims.
-- This allows the frontend to access the user's role and company information directly from the JWT token.
--
-- Parameters:
-- - event (jsonb): The input JSONB object containing user information and claims.
--
-- Returns:
-- - jsonb: The modified JSONB object with updated claims.
--
-- Logic:
-- 1. Declare variables to hold claims and user data.
-- 2. Retrieve the user's role ID and company ID from the "company_user_roles" and "companySelected" tables.
-- 3. Update the claims in the event with the retrieved role ID and company ID.
-- 4. Return the modified event.
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
declare
    claims jsonb;
    user_data record;
begin
    select cur."role_id", cs."companyId"
    into user_data
    from public."company_user_roles" cur
    join public."companySelected" cs
      on cs."companyId" = cur."company_id"
    where cs."userId" = (event->>'user_id')::uuid
      and cur."user_id" = (event->>'user_id')::uuid
    limit 1;

    claims := event->'claims';

    if user_data.role_id is not null then
        claims := jsonb_set(claims, '{user_role_id}', to_jsonb(user_data.role_id));
    else
        claims := jsonb_set(claims, '{user_role_id}', 'null');
    end if;

    if user_data."companyId" is not null then
        claims := jsonb_set(claims, '{company_id}', to_jsonb(user_data."companyId"));
    else
        claims := jsonb_set(claims, '{company_id}', 'null');
    end if;

    event := jsonb_set(event, '{claims}', claims);
    return event;
end;
$$;

grant usage on schema public to supabase_auth_admin;

grant execute
  on function public.custom_access_token_hook
  to supabase_auth_admin;

revoke execute
  on function public.custom_access_token_hook
  from authenticated, anon, public;

grant all
  on table public.company_user_roles
to supabase_auth_admin;

grant all
  on table public."companySelected"
to supabase_auth_admin;

create policy "Allow auth admin to read user roles" ON public.company_user_roles
as permissive for select
to supabase_auth_admin
using (true);

create policy "Allow auth admin to read user roles" ON public."companySelected"
as permissive for select
to supabase_auth_admin
using (true);



CREATE POLICY "Allow select for users in the same company"
ON public.company_user_roles
AS PERMISSIVE FOR SELECT
TO authenticated
USING (auth.jwt() ->> 'company_id' = company_user_roles.company_id::text);



-- RBAC function to check if user has permission to perform an action
-- (with company check)
-- (with owner check)
-- (with named permission check)
CREATE OR REPLACE FUNCTION public.authorize(
  requested_permission VARCHAR(255)
)
RETURNS boolean AS $$
DECLARE
  user_role_id uuid;
  selected_company_id uuid;
  requested_permission_id uuid;
  is_owner boolean := FALSE;
BEGIN
  -- Get user_role_id, company_id, and is_owner from JWT
  SELECT
    (auth.jwt() ->> 'user_role_id')::uuid,
    (auth.jwt() ->> 'company_id')::uuid,
    (auth.jwt() ->> 'is_owner')::boolean
  INTO
    user_role_id,
    selected_company_id,
    is_owner;

  -- If the user is an owner, return TRUE
  IF is_owner THEN
    RETURN TRUE;
  END IF;

  -- Get the requested permission_id from the company_permissions table
  SELECT id INTO requested_permission_id
  FROM public.company_permissions
  WHERE name = requested_permission;

  -- Ensure the requested permission exists
  IF requested_permission_id IS NULL THEN
    RETURN FALSE;
  END IF;

  IF EXISTS (
      SELECT 1
      FROM public.company_role_permissions crp
      WHERE crp.company_id = selected_company_id
        AND crp.permission_id = requested_permission_id
        AND crp.role_id = user_role_id
  ) THEN
    RETURN TRUE;
  END IF;

  RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = '';



-- RBAC function to check if user has permission to see and access a route
CREATE OR REPLACE FUNCTION public.authorize_route(
  p_route VARCHAR(255)
)
RETURNS BOOLEAN AS $$
DECLARE
  selected_company_id UUID;
  user_role_id UUID;
BEGIN
  -- Get company_id and user_role_id from JWT
  SELECT 
    (auth.jwt() ->> 'company_id')::uuid,
    (auth.jwt() ->> 'user_role_id')::uuid
  INTO 
    selected_company_id,
    user_role_id;

  IF EXISTS (
    SELECT 1
    FROM public.company_role_permissions crp
    JOIN public.company_permissions cp ON crp.permission_id = cp.id
    WHERE crp.company_id = selected_company_id
      AND crp.role_id = user_role_id
      AND cp.route = p_route
  ) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = '';



-- RBAC function to get authorized routes for the user
CREATE OR REPLACE FUNCTION public.get_authorized_routes()
RETURNS TABLE(route VARCHAR(255), sort_order INT, route_level INT, route_parent UUID) AS $$
DECLARE
  selected_company_id UUID;
  user_role_id UUID;
BEGIN
  -- Get company_id and user_role_id from JWT
  SELECT 
    (auth.jwt() ->> 'company_id')::uuid,
    (auth.jwt() ->> 'user_role_id')::uuid
  INTO 
    selected_company_id,
    user_role_id;

  RETURN QUERY
  SELECT cp.route, cp.sort_order, cp.route_level, cp.route_parent
  FROM public.company_role_permissions crp
  JOIN public.company_permissions cp ON crp.permission_id = cp.id
  JOIN public.company_roles cr ON crp.role_id = cr.id
  WHERE crp.company_id = selected_company_id
    AND crp.role_id = user_role_id
    AND cp.route IS NOT NULL;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = '';


-- Function to check if the user is associated with the company
CREATE OR REPLACE FUNCTION public.is_company_user(company_id UUID)
RETURNS BOOLEAN AS $$
  SELECT EXISTS(
    SELECT 1
    FROM public.company_users cu
    WHERE cu.company_id = (auth.jwt() ->> 'company_id')::uuid
      AND cu.user_id = auth.uid()
  );
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = '';


-- Function to check if the user's current selected company_id matches the row's company_id
CREATE OR REPLACE FUNCTION public.is_same_company(company_id UUID)
RETURNS BOOLEAN AS $$
  SELECT (auth.jwt() ->> 'company_id')::uuid = company_id;
$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = '';



-- RBAC Related Table Definitions ------------------------------------------------

-- Any table that requires RBAC should have a company_id column to associate the data with a specific company.


COMMENT ON TABLE public.company_permissions IS 'Stores the available permissions within the system.  Each permission has a unique name and an optional description.';

  public.company_permissions (
    id uuid not null default gen_random_uuid (),
    name character varying(255) not null,
    description text null,
    sort_order integer null,
    route character varying(255) null,
    route_level integer null,
    route_parent uuid null,
    constraint company_permissions_pkey primary key (id),
    constraint company_permissions_name_key unique (name),
    constraint company_permissions_route_parent_fkey foreign key (route_parent) references company_permissions (id) on delete cascade
  ) tablespace pg_default;

create index if not exists idx_company_permissions_route on public.company_permissions using btree (route) tablespace pg_default;

create index if not exists idx_permissions_name on public.company_permissions using btree (name) tablespace pg_default;

create index if not exists idx_company_permissions_cover on public.company_permissions using btree (id, route, sort_order, route_level, route_parent) tablespace pg_default
where
  (route is not null);
  
-- COMMENT ON COLUMN public.company_permissions.id IS 'Unique identifier for the permission.';
-- COMMENT ON COLUMN public.company_permissions.name IS 'Unique name of the permission (e.g., company.users.read).';
-- COMMENT ON COLUMN public.company_permissions.description IS 'Optional description of the permission.';
-- COMMENT ON COLUMN public.company_permissions.route IS 'Optional route associated with this permission (e.g., /company/add).  If NULL, the permission is not tied to a specific route.';
-- COMMENT ON COLUMN public.company_permissions.sort_order IS 'Integer value used to determine the order in which permissions are displayed in the UI.';
-- COMMENT ON COLUMN public.company_permissions.route_level IS 'Integer value used to determine the level of the route in the hierarchy.';
-- COMMENT ON COLUMN public.company_permissions.route_parent IS 'The parent route of the current route (if applicable).';



COMMENT ON TABLE public.company_role_permissions IS 'Associates permissions with specific roles within a company.  This table defines which roles have access to which permissions.';

  public.company_role_permissions (
    id uuid not null default gen_random_uuid (),
    company_id uuid null,
    role_id uuid not null,
    system boolean not null default false,
    permission_id uuid not null,
    constraint company_role_permissions_pkey primary key (id),
    constraint company_role_permissions_company_id_role_id_permission_id_key unique (company_id, role_id, permission_id),
    constraint company_role_permissions_company_id_fkey foreign key (company_id) references companies (id),
    constraint company_role_permissions_role_id_fkey foreign key (role_id) references company_roles (id) on delete cascade,
    constraint company_role_permissions_permission_id_fkey foreign key (permission_id) references company_permissions (id)
  ) tablespace pg_default;

create index if not exists idx_company_role_permissions on public.company_role_permissions using btree (company_id, role_id, permission_id) tablespace pg_default;

create index if not exists idx_company_role_permissions_company_id_role_id on public.company_role_permissions using btree (company_id, role_id) tablespace pg_default;

create index if not exists idx_company_role_permissions_company_id on public.company_role_permissions using btree (company_id) tablespace pg_default;

-- COMMENT ON COLUMN public.company_role_permissions.id IS 'Unique identifier for the role-permission assignment.';
-- COMMENT ON COLUMN public.company_role_permissions.company_id IS 'The company this role-permission assignment applies to (can be NULL for system-wide permissions).';
-- COMMENT ON COLUMN public.company_role_permissions.role_id IS 'The role that is granted the permission.';
-- COMMENT ON COLUMN public.company_role_permissions.system IS 'Indicates if this is a system-level permission (applies to all companies).';
-- COMMENT ON COLUMN public.company_role_permissions.permission_id IS 'The permission granted to the role.';



COMMENT ON TABLE public.company_roles IS 'Defines the roles within a company.  Each role has a name, description, and is associated with a company.';

  public.company_roles (
    id uuid not null default gen_random_uuid (),
    company_id uuid null,
    role text not null,
    name text not null,
    description text null default ''::text,
    system boolean not null default false,
    constraint company_roles_pkey primary key (id),
    constraint company_roles_company_id_name_key unique (company_id, name),
    constraint company_role_permissions_company_id_fkey foreign key (company_id) references companies (id)
  ) tablespace pg_default;

create index if not exists idx_company_roles_id_name on public.company_roles using btree (id, name) tablespace pg_default;

create index if not exists idx_company_roles_company_id on public.company_roles using btree (company_id) tablespace pg_default;


-- COMMENT ON COLUMN public.company_roles.id IS 'Unique identifier for the role.';
-- COMMENT ON COLUMN public.company_roles.company_id IS 'The company this role belongs to (can be NULL for system-wide roles).';
-- COMMENT ON COLUMN public.company_roles.role IS 'A general role identifier (e.g., "admin", "user").';
-- COMMENT ON COLUMN public.company_roles.name IS 'The name of the role (e.g., "Administrator", "Editor").';
-- COMMENT ON COLUMN public.company_roles.description IS 'Optional description of the role.';
-- COMMENT ON COLUMN public.company_roles.system IS 'Indicates if this is a system-level role (applies to all companies).';



COMMENT ON TABLE public.company_user_roles IS 'Assigns roles to users within a company.  This table links users to their roles within a specific company.';

  public.company_user_roles (
    company_id uuid not null,
    user_id uuid not null,
    role_id uuid not null,
    constraint company_user_roles_pkey primary key (company_id, user_id, role_id),
    constraint company_user_roles_company_id_fkey foreign key (company_id) references companies (id) on delete cascade,
    constraint company_user_roles_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint company_user_roles_role_id_fkey foreign key (role_id) references company_roles (id)
  ) tablespace pg_default;

create index if not exists idx_company_user_roles on public.company_user_roles using btree (company_id, user_id, role_id) tablespace pg_default;

create index if not exists idx_company_user_roles_company_id on public.company_user_roles using btree (company_id) tablespace pg_default;

-- COMMENT ON COLUMN public.company_user_roles.company_id IS 'The company the user is associated with.';
-- COMMENT ON COLUMN public.company_user_roles.user_id IS 'The user who is assigned the role.';
-- COMMENT ON COLUMN public.company_user_roles.role_id IS 'The role assigned to the user.';



COMMENT ON TABLE public.company_users IS 'Associates users with companies.  This table links users to the companies they are associated with.';

  public.company_users (
    company_id uuid not null,
    user_id uuid not null,
    deleted_at timestamp with time zone null,
    constraint company_users_pkey primary key (company_id, user_id),
    constraint company_users_user_id_fkey foreign key (user_id) references auth.users (id) on delete cascade,
    constraint company_users_company_id_fkey foreign key (company_id) references companies (id)
  ) tablespace pg_default;

create index if not exists idx_company_users_company_id on public.company_users using btree (company_id) tablespace pg_default;

-- COMMENT ON COLUMN public.company_users.company_id IS 'The company the user is associated with.';
-- COMMENT ON COLUMN public.company_users.user_id IS 'The user who is associated with the company.';
-- COMMENT ON COLUMN public.company_users.deleted_at IS 'The timestamp when the association was deleted (soft delete).';



COMMENT ON TABLE public.companies IS 'Stores information about companies (tenants).';

  public.companies (
    id uuid not null default gen_random_uuid (),
    name text not null,
    phone text not null,
    email text not null,
    website text null,
    "typeId" uuid not null,
    "createdAt" timestamp with time zone not null default now(),
    created_by uuid not null,
    deleted_at timestamp with time zone null,
    constraint companies_pkey primary key (id),
    constraint companies_createdBy_fkey foreign key (created_by) references profiles (id),
    constraint companies_typeId_fkey foreign key ("typeId") references "companyTypes" (id)
  )



------ TEST CODE ------------------------------------------------



-- RBAC RLS Policy

create policy "Allow authorized delete access" on public.channels 
for insert 
to authenticated 
using ( (SELECT authorize('channels.delete')) );

create policy "Allow authorized delete access" on public.channels 
for delete 
to authenticated 
using ( (SELECT authorize('channels.delete')) );



-- Company Check

CREATE POLICY "Allow select within same company" ON public.your_table
AS PERMISSIVE FOR SELECT
TO authenticated
USING (is_same_company(company_id));



-- RBAC Function Tests

(async () => {
  const { data, error } = await supabase.rpc('get_authorized_routes');
  if (error) {
    console.error('Error fetching get_authorized_routes:', error);
  } else {
    console.table(data);
  }
})();

(async () => {
		const { data, error } = await supabase.rpc('authorize', {
			requested_permission: 'dashboard.select'
		});
		if (error) {
			console.error('Error fetching authorize:', error);
		} else {
			console.log('dashboard.select ?', data);
		}
})();

(async () => {
		const { data, error } = await supabase.rpc('authorize', {
			requested_permission: 'users.select'
		});
		if (error) {
			console.error('Error fetching authorize:', error);
		} else {
			console.log('users.select ?', data);
		}
})();

(async () => {
		// call and log out supabase rpc get_user_role_id
		const { data, error } = await supabase.rpc('authorize_route', {
			p_route: '/company/add'
		}); // Fetch user role ID using the correct RPC method
		if (error) {
			console.error('Error fetching authorize_route:', error);
		} else {
			console.log('/company/add ?', data);
		}
})();



-- WIP - RBAC - Permissions CRUD Operations ------------------------------------------------

-- Sorting Permissions
-- Update sort_order in the database (example in JavaScript/TypeScript):
-- Example using JavaScript/TypeScript (adapt to your backend language)
async function updatePermissionSortOrder(permissionIds: string[]) {
  try {
    for (let i = 0; i < permissionIds.length; i++) {
      const permissionId = permissionIds[i];
      const sortOrder = i + 1; // Start from 1

      // Execute SQL query to update the sort_order for the current permissionId
      // Example using a hypothetical database library:
      const { data, error } = await supabase
        .from('company_permissions')
        .update({ sort_order: sortOrder })
        .eq('id', permissionId);

      if (error) {
        console.error('Error updating sort order:', error);
        throw error; // Re-throw the error to stop the loop
      }
    }
    return { success: true }; // Indicate success
  } catch (error) {
    console.error('Transaction failed:', error);
    return { success: false, error: error }; // Indicate failure
  }
}



-- Notes on Permissions CRUD Operations ------------------------------------------------

-- company_users & company_user_roles tables
-- company_users: This table represents the overall membership of users in companies. It records which users are associated with a company (with support for soft deletion via the deleted_at field). It's used in contexts where you only need to know if a user belongs to a company.
-- company_user_roles: This table maps users to specific roles within a company. A user may have one or more roles, and these roles drive permissions and access control. It’s used when you need to determine what capabilities a user has (e.g., via the custom access token hook or RBAC functions).
-- Even though there is overlap in the user-company association, splitting them helps keep membership and role/permission information separate, making the system more modular and easier to maintain.



-- Jwt Type Definition (Add or remove fields as needed)
export type Jwt =
	| {
			id: string;
			companyId: string;
			userRoleId: string;
			// Add any necessary additional fields here then Jwt retrieval accordingly
	  }
	| undefined;



-- To get the JWT on the server side:

/**
  * Unlike `supabase.auth.getSession()`, which returns the session _without_
  * validating the JWT, this function also calls `getUser()` to validate the
  * JWT before returning the session.
  * TODO: Validate the JWT with the JWT secret
*/
event.locals.safeGetSession = async () => {
  const {
    data: { session }
  } = await event.locals.supabase.auth.getSession();
  if (!session) {
    return { session: null, user: null, jwt: null };
  }

  const {
    data: { user },
    error
  } = await event.locals.supabase.auth.getUser();
  if (error) {
    // JWT validation has failed
    return { session: null, user: null, jwt: null };
  }

  const _jwt = session?.access_token; // Get JWT from session

  let jwt: Jwt = undefined;
  try {
    const decodedJwt = JSON.parse(atob(_jwt.split('.')[1])); // Decode JWT
    jwt = {
      id: decodedJwt.sub,
      companyId: decodedJwt.company_id,
      userRoleId: decodedJwt.user_role_id,
    };
  } catch (error) {
    jwt = undefined;
    console.error('Failed to decode JWT');
  }

  return { session, user, jwt };
};



-- To get the JWT on the client side:

/**
	 * It's fine to use `getSession` here, because on the client, `getSession` is
	 * safe, and on the server, it reads `session` from the `LayoutData`, which
	 * safely checked the session using `safeGetSession`.
	 */
	let session;

	try {
		const {
			data: { session: s }
		} = await supabase.auth.getSession();
		session = s;
	} catch (err) {
		console.error('Error during getSession', err);
		if (isBrowser()) {
			goto('/');
		}
		return;
	}

	if (!session) {
		if (isBrowser()) {
			goto('/');
		}
		return;
	}

	const _jwt = session?.access_token; // Get JWT from session

	let jwt: Jwt;
	try {
		const decodedJwt = JSON.parse(atob(_jwt.split('.')[1])); // Decode JWT
		jwt = {
			id: decodedJwt.sub,
			companyId: decodedJwt.company_id,
			userRoleId: decodedJwt.user_role_id
		};
	} catch (error) {
		jwt = undefined;
		console.error('Failed to decode JWT');
	}

	if (!session || !jwt || !supabase) {
		if (isBrowser()) goto('/');
		return;
	}



-- To update the user role on the client side dynamically, you can use the following approach:
-- 1. When a user logs in, fetch the user's role and company ID from the JWT token.
-- 2. Store the user's role and company ID in the client-side state (e.g., React context, Vuex store).
-- 3. Use Supabase Realtime and subscribe to UPDATE events on the company_user_roles table.
-- 4. When an UPDATE event occurs, check if the updated row corresponds to the current user's selected company (done in RLS using is_same_company()) and if the role has changed.
-- 5. If the conditions are met, call supabase.auth.refreshSession() to update the user's role in the JWT token.
-- Here's an example of the logic for a Realtime event handler:
rolesChannel = supabase
  .channel('roles-channel')
  .on(
    'postgres_changes',
    { event: 'UPDATE', schema: 'public', table: 'company_user_roles' },
    async (payload: any) => {
      if (
        (payload.new?.user_id === jwt?.id || payload.old?.user_id === jwt?.id) &&
        payload.new?.role_id !== payload.old?.role_id
      ) {
        await supabase?.auth.refreshSession();
      }
    }
  )
  .subscribe();

-- 6. In the auth state change event, check if the user's role has changed and invalidate the cache if needed.
-- Here's an example of the logic for the auth state change event:
const { data } = supabase.auth.onAuthStateChange(async (event, newSession) => {
  if (newSession?.expires_at !== session?.expires_at) {
    invalidate('supabase:auth').catch((error) => console.error('invalidate error:', error));
  }
  if (newSession) {
    try {
      jwtOld = jwt;

      const _jwt = newSession?.access_token; // Get access token from the new session

      let newJwt: Jwt = undefined;
      try {
        const decodedJwt = JSON.parse(atob(_jwt.split('.')[1])); // decode the JWT
        newJwt = {
          id: decodedJwt.sub,
          companyId: decodedJwt.company_id,
          userRoleId: decodedJwt.user_role_id
        };
      } catch (error) {
        newJwt = undefined;
        console.error('Failed to decode JWT');
      }

      if (jwt?.userRoleId !== jwtOld?.userRoleId) {
        jwt = newJwt;
        await invalidateAll(); // Invalidate all to force a re-fetch of data or do a page reload
      }
    } catch (error) {
      console.error('JWT decode error:', error);
    }
  }
});