Dynamic Role and Permission Management in Supabase: Enhancing Security and Flexibility

8 min readMar 17, 2024

Introduction

Supabase shines as an outstanding cloud backend solution, positioning itself as a strong Firebase alternative. At its heart, Supabase embraces the power of PostgreSQL, wrapping its capabilities in a cloud-friendly package. This combination brings the best of relational databases — robust, scalable, and versatile — right to the fingertips of developers, with an added layer of convenience.

What makes Supabase particularly attractive is its offer of self-hosting. This feature hands control back to the developers, allowing them to steer their deployment strategies as needed. Starting is cost-free, and the platform scales economically, making it a viable option for projects big and small.

Key Features of Supabase:

Remote Procedure Calls (RPC): These simplify the execution of functions directly within the database, enabling straightforward handling of complex operations.
Cloud Functions: Enhance your app’s capabilities with functions that trigger in response to database events or API requests, extending functionality without the server overhead.
Database Management: Supabase provides a fully managed PostgreSQL experience, supporting intricate queries, relationships, and smooth migrations.
Authentication Methods: It offers a broad spectrum of authentication options, from social logins to email/password combos, fortified with built-in security measures.
Storage Management: This feature allows for efficient file storage handling, catering to diverse needs — from user-generated content to essential app assets.
Libraries and SDKs: A rich ecosystem of libraries enables seamless Supabase integration, boosting developer productivity significantly.
Extensive Documentation: From setup to advanced operations, Supabase’s documentation is thorough, ensuring developers have the support they need at every step.
Logs and Monitoring: Real-time logging and monitoring tools provide insight into your application’s performance and activities, helping you stay informed and proactive.
Real-time Database: Implement real-time functionalities effortlessly, allowing applications to react immediately to data changes.

Choosing Supabase for Flutter-based projects was a no-brainer for me. Its integration with Flutter is flawless, enhancing the development process and allowing for the quick, efficient creation of feature-rich applications. Whether you’re crafting dynamic web apps or sophisticated mobile apps, Supabase stands ready as a robust, adaptable platform.

Managing roles and permissions is fundamental to both security and operational efficiency in any application. Supabase elevates this with PostgreSQL’s row-level security (RLS) and customizable policies, granting developers precise control over data access. This capability is crucial for building secure, personalized user experiences with minimal hassle. It’s not just about security; it’s about streamlining interactions and bolstering the backend’s robustness for modern applications.

Pain Points

Supabase introduces a set of RPCs for managing user roles and permissions, which is a step in the right direction. However, developers often encounter limitations that can hinder the flexibility and dynamism needed for modern applications.

One significant issue is the lack of dynamic updates to user roles and permissions. When a user’s role is assigned or updated, it doesn’t automatically reflect in their session. This lack of immediacy can disrupt the user experience, sometimes requiring a logout and login to reset session data.

Furthermore, Supabase’s system primarily handles roles, not permissions. This distinction means there’s no straightforward way to specify varied access levels for different users directly.

For example, if one user needs to read from a database table and another user requires both read and insert permissions, this differentiation demands a slew of custom policies under the current role-level security (RLS) and policy management framework.

Essentially, for each role added — be it ‘client’ for read-only access or ‘admin’ for read and insert permissions — every table in the database needs updating to recognize these roles, compounding the workload with each new role created.

This cumbersome process highlights the need for a simpler, more efficient way to manage access. Ideally, a straightforward RPC could determine whether an authenticated user has the necessary permissions for a given action, boiling down the decision to a simple true/false output.

The Solution

My approach builds on the existing framework proposed by the Supabase community for managing custom claims, enhancing it without making foundational changes. A great starting point for implementing this enhanced approach is the install.sql script available in the Supabase community's GitHub repository. Running this script in the Supabase SQL editor lays the groundwork for the subsequent steps.

Supabase Custom Claims Installation Script

For more detailed information, refer to the Supabase Custom Claims GitHub page.

Now the custimozation part

The first step in this dinamization process is to create a dedicated table for managing roles within Supabase. This roles table is designed to simplify the process of role management, making it more intuitive and less cumbersome for developers to handle role assignments and modifications.

create table
  public.roles (
    id serial,
    name text not null,
    constraint roles_pkey primary key (id),
    constraint roles_name_key unique (name)
  ) tablespace pg_default;

In enhancing the role and permission management in Supabase, let’s dive into the next critical component of the solution: the permissions table.

create table
  public.permissions (
    id serial,
    role_id integer null,
    table_name text null,
    actions text null,
    constraint permissions_pkey primary key (id),
    constraint unique_role_table unique (role_id, table_name),
    constraint permissions_role_id_fkey foreign key (role_id) references roles (id)
  ) tablespace pg_default;

Next, we’ll develop a database function tailored for verifying whether a user’s role grants them the necessary permission for a specific action. This function taps into the user’s role, as indicated in their JWT claims, to perform this check efficiently.

CREATE OR REPLACE FUNCTION check_user_permission_with_claims(table_name text, action_required text)
RETURNS boolean AS $$
DECLARE
    user_role TEXT;
    has_permission BOOLEAN;
BEGIN
    -- Extract the user's role from the JWT claims
    user_role := current_setting('request.jwt.claims', true)::jsonb->'app_metadata'->>'userrole';
    
    -- Dynamically check permissions based on extracted user_role, table_name, and action_required
    EXECUTE format('SELECT EXISTS (
                        SELECT 1
                        FROM permissions p
                        JOIN roles r ON p.role_id = r.id
                        WHERE r.name = %L
                        AND p.table_name = %L
                        AND (p.actions = %L OR p.actions = ''*'')
                    )', user_role, table_name, action_required) INTO has_permission;
RETURN has_permission;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

By employing this method, we streamline the process, enabling the use of four universal policies for various actions — select, insert, update, and delete — across database tables or during policy validation. This approach significantly reduces complexity, eliminating the need to create individual policies for each role.

check_user_permission_with_claims('categories'::text, 'r'::text) -- SELECT
check_user_permission_with_claims('categories'::text, 'i'::text) -- INSERT
check_user_permission_with_claims('categories'::text, 'u'::text) -- UPDATE
check_user_permission_with_claims('categories'::text, 'd'::text) -- DELETE

This approach simplifies access control by allowing the addition of broad verification policies for user access.

To make the process of adding these policies even smoother, let’s introduce a function specifically designed for this task.

CREATE OR REPLACE FUNCTION add_policy_to_authenticated_users(table_name text, permission_type text)
RETURNS void AS $$
DECLARE
    policy_name text;
    policy_command text;
BEGIN
    -- Determine the policy name and policy command based on the permission type
    CASE permission_type
        WHEN 'SELECT' THEN
            policy_name := table_name || '.select';
            policy_command := format('CREATE POLICY %I ON %I FOR SELECT TO authenticated USING (check_user_permission_with_claims(%L, ''r''))', policy_name, table_name, table_name);
        WHEN 'INSERT' THEN
            policy_name := table_name || '.insert';
            policy_command := format('CREATE POLICY %I ON %I FOR INSERT TO authenticated WITH CHECK (check_user_permission_with_claims(%L, ''i''))', policy_name, table_name, table_name);
        WHEN 'UPDATE' THEN
            policy_name := table_name || '.update';
            policy_command := format('CREATE POLICY %I ON %I FOR UPDATE TO authenticated USING (check_user_permission_with_claims(%L, ''u''))', policy_name, table_name, table_name);
        WHEN 'DELETE' THEN
            policy_name := table_name || '.delete';
            policy_command := format('CREATE POLICY %I ON %I FOR DELETE TO authenticated USING (check_user_permission_with_claims(%L, ''d''))', policy_name, table_name, table_name);
        ELSE
            RAISE EXCEPTION 'Unsupported permission type: %', permission_type;
    END CASE;

    -- Enable Row-Level Security on the table if not already enabled
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);

    -- Apply the policy to the table
    EXECUTE policy_command;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

I’ve also devised a concise query to expedite the process of applying general policies to the tables.

do $$
DECLARE
    table_name text := 'products';
    dummy_variable text;
BEGIN
    select add_policy_to_authenticated_users(table_name, 'SELECT') into dummy_variable;
    select add_policy_to_authenticated_users(table_name, 'INSERT') into dummy_variable;
    select add_policy_to_authenticated_users(table_name, 'UPDATE') into dummy_variable;
    select add_policy_to_authenticated_users(table_name, 'DELETE') into dummy_variable;
END $$;

This way, by just managing the roles and permissions table data with using set_claim and get_claim methods from Supabase claims management proposal, we don’t need to separatelly manage each policy for each userrole and permission type on each table.

EXTRA

The check_user_permission_with_claims method can be used also in for database functions access management, like this:

    IF NOT check_user_permission_with_claims('functions.get_categories', 'r') THEN
        RAISE EXCEPTION 'User does not have permission to fetch categories.';
    END IF;

Example of a complete query


BEGIN
    -- Check user permission
    IF NOT check_user_permission_with_claims('functions.get_categories', 'r') THEN
        RAISE EXCEPTION 'User does not have permission to fetch categories.';
    END IF;

    -- If permission check passes, execute the query
    RETURN QUERY SELECT
        c.id,
        c.name::TEXT,
        c.description,
        c.parent_id,
        c.featured,
        c.status::TEXT as status,
        c.disabled_reason,
        c.image_id,
        pc.name::TEXT AS parent_category_name,
        i.name::TEXT AS image_url 
    FROM
        categories c
        LEFT JOIN categories pc ON c.parent_id = pc.id
        LEFT JOIN storage.objects i ON c.image_id = i.id
    WHERE
        c.deleted_at IS NULL;

END;

Concluding Thoughts

This method revolutionizes how user roles and permissions are handled, transitioning to a dynamic management system. By implementing a universal permission rule, denoted as “*”, we can streamline access across all actions — be it reading, inserting, updating, or deleting. For instance, assigning the permission to an admin role effectively grants it comprehensive access across the board, simplifying policy assignments tremendously.

Moreover, this approach enables the external management of roles and permissions. There’s no longer a necessity to log directly into Supabase for these tasks. Through the use of set_claim and the strategic insertion of roles and permissions into their respective tables, new roles can seamlessly integrate with existing policies. This significantly reduces the administrative overhead, making it easier to adapt and scale permissions without altering the fundamental policy configurations on the tables.

In essence, this strategy not only makes managing roles and permissions more efficient but also ensures a level of flexibility and scalability previously unattainable, paving the way for more secure and adaptable applications.

Connect and Share

If you’ve found this guide helpful or wish to discuss it further, I’d love to connect! You can find me on Instagram as @alexbordei, on Twitter @abordei, or visit my website at alexbordei.dev.

I hope you’ve enjoyed this walkthrough and found it valuable. If so, please feel free to share it with others who might also benefit.

Alex Bordei 💙
Organizer of the Flutter Bucharest Meetup, Flutter Speaker
Full-Stack Flutter Developer

Your support and feedback mean the world to me, and I’m eager to hear your thoughts or see how you implement these solutions in your own projects!