Setting up Roles in StartupBolt

Overview

StartupBolt uses role-based access control (RBAC) to manage permissions for different types of users. The system supports four roles:

Admin: Full access, including managing customer credits, user roles and other settings.
Editor: Can publish and manage any blog posts.
Author: Can create and publish their own blog posts.
Contributor: Can create and edit their own blog posts but cannot publish them.

These roles ensure a structured and secure content management process.

Note: Blog posts are called "articles" in the codebase.

Creating the Tables

To get started, navigate to Supabase > SQL Editor > New Query or Create a new snippet, paste the provided SQL queries, and execute them. Run each query separately by creating a new snippet.

Creating the Roles Table

This table defines all possible user roles.

-- Roles table to define all possible roles
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC')
);

Creating the User Roles Table

This table links users to specific roles.

-- User roles
CREATE TABLE user_roles (
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
    PRIMARY KEY (user_id)  -- One role per user
);

Defining Helper Functions

To simplify role management, we create helper functions that check a user's role dynamically.

Checking a Single Role

This function verifies if a user has a specific role.

-- Helper function to check if user has a specific role
CREATE OR REPLACE FUNCTION public.has_role(user_uuid UUID, role_name TEXT)
RETURNS BOOLEAN 
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 
        FROM public.user_roles ur
        JOIN public.roles r ON r.id = ur.role_id
        WHERE ur.user_id = user_uuid 
        AND r.name = role_name
    );
END;
$$;

Checking Multiple Roles

This function checks if a user has any of the specified roles.

-- Helper function to check if user has any of the specified roles
CREATE OR REPLACE FUNCTION public.has_any_role(user_uuid UUID, role_names TEXT[])
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 
        FROM public.user_roles ur
        JOIN public.roles r ON r.id = ur.role_id
        WHERE ur.user_id = user_uuid 
        AND r.name = ANY(role_names)
    );
END;
$$;

Enabling RLS on the Tables

To enhance security, we enable Row-Level Security (RLS) on both the roles and user_roles tables.

-- Enable RLS on the roles and user_roles table
ALTER TABLE public.roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

Seeding the Roles Table

We populate the roles table with the predefined user roles.

-- Adding Roles
INSERT INTO roles (name, description) VALUES 
    ('admin', 'Full Access'),
    ('editor', 'Can publish anyones blog'),
    ('author', 'Can publish own blogs'),
    ('contributor', 'Can edit, but cant publish blog');

Creating RLS Policies for the Tables

RLS policies define what actions users can perform on the roles and user_roles tables.

Roles Table Policies

These policies control access to the roles table.

-- Roles RLS
CREATE POLICY roles_select 
ON public.roles FOR SELECT
TO authenticated
USING (
    -- Admin can read all roles
    has_role(auth.uid(), 'admin')
    OR 
    -- Users can read their own role
    EXISTS (
        SELECT 1 
        FROM public.user_roles ur
        WHERE ur.user_id = auth.uid()
        AND ur.role_id = id
    )
);
 
CREATE POLICY roles_insert
ON public.roles FOR INSERT 
TO authenticated
WITH CHECK (has_role(auth.uid(), 'admin'));
 
CREATE POLICY roles_update
ON public.roles FOR UPDATE 
TO authenticated
USING (has_role(auth.uid(), 'admin'))
WITH CHECK (has_role(auth.uid(), 'admin'));
 
CREATE POLICY roles_delete
ON public.roles FOR DELETE 
TO authenticated
USING (has_role(auth.uid(), 'admin'));

User Roles Table Policies

These policies control access to the user_roles table.

-- User Roles RLS
CREATE POLICY user_roles_select
ON public.user_roles FOR SELECT 
TO authenticated
USING (
    has_role(auth.uid(), 'admin')
    OR 
    user_id = auth.uid()
);
 
CREATE POLICY user_roles_insert
ON public.user_roles FOR INSERT 
TO authenticated
WITH CHECK (has_role(auth.uid(), 'admin'));
 
CREATE POLICY user_roles_update
ON public.user_roles FOR UPDATE 
TO authenticated
USING (has_role(auth.uid(), 'admin'))
WITH CHECK (has_role(auth.uid(), 'admin'));
 
CREATE POLICY user_roles_delete
ON public.user_roles FOR DELETE 
TO authenticated
USING (has_role(auth.uid(), 'admin'));

Next Steps

Configure Supabase: Follow the Supabase Configuration Guide to create tables, set up RPC functions, and configure triggers to handle customers and payments.

Supabase Setup Supabase Configuration