+++ author = "Peter Kurfer" title = "RBAC in Supabase with EntraID" description = "How to use EntraID defined roles in Supabase for RLS policies" date = "2024-12-03" tags = [ "supabase", "entraid", "RBAC", "RLS" ] +++

Supabase is an awesome tool for building (CRUD) applications in no time. For those who are not familiar with Supabase, it is an open-source Firebase alternative with a focus on privacy and security. Compared to other tools, Supabase is focused on the Postgres database and provides a lot of features out of the box, like authentication, authorization, and real-time subscriptions. Also their tech stack appeals to me, as they are using Go, Rust, Elixir and (sadly) Node.js for all their services.¹

When building applications - especially in a business context - there's no way around authentication and authorization. A very common approach to authorization is Role-Based Access Control (RBAC). In RBAC, permissions are assigned to roles, and roles are assigned to users and/or groups.

In this post, I want to show you how to use roles defined in EntraID in Supabase for Row-Level Security (RLS) policies. When looking into the Supabase documentation you will notice that they mostly focus on how to integrate Azure EntraID and how to implement authentication in your frontend app. When it comes to authorization, they provide high level documentation on how to use RLS policies in general with Supabase including examples for their helper functions and there's even an article about custom claims and role-based access control (RBAC), but all their documentation assumes that you want to manage roles within Supabase instead of using what your external authentication provider gives you for free.

Basic setup

As also described in the Supabase documentation, the first step is to ensure that you have a so called "App Registration" in your Azure subscription. App Registrations are the entity in Azure that define OpenID Connect applications. Also, application roles are defined in the App Registration - and assigened in the corresponding "Enterprise Application" - why that is, is a topic for another day. Assuming, that you already followed the official docs for the basic authentication for Azure EntraID, you can now directly jump to the "App Roles" section in your App Registration and define your roles:

For this example, I defined the roles:

• Admin
• Reader
• Writer

but you can define as many roles as you like.

Now, as already mentioned, to assign roles, you have to head over to the "Enterprise Applications" section in your Azure subscription and assign the roles to the users or groups you want to have them:

as you may have noticed, I assigned multiple roles to the same user, which results in the user having multiple roles in the token.

Database setup

Now that we have our roles defined and assigned, we can start with the database setup. Before implementing any policy, we first need some tables to work with.

To keep the scenario as simple as possible we'll start with two tables: users and todos:

CREATE SCHEMA IF NOT EXISTS public;

CREATE TABLE IF NOT EXISTS public.users (
  "id" UUID PRIMARY KEY NOT NULL
);

CREATE TABLE IF NOT EXISTS public.todos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner UUID NOT NULL DEFAULT (auth.jwt() -> 'user_metadata' -> 'custom_claims' ->> 'tid')::uuid REFERENCES public.users (id),
  title TEXT NOT NULL,
  description TEXT,
  completed BOOLEAN NOT NULL DEFAULT FALSE
);

ALTER TABLE public.todos enable ROW level security;

Note: normally I'd put the users table in a separate schema e.g. internal to avoid that it's visible to the users through the API, but for this example we'll keep it simple. Alternatively we could limit the access to the users table with RLS policies but again, for the sake of this article we'll skip this part.

Implementing RLS policies

With the tables in place, we can now start with the RLS policies. We want to enforce the following business rules:

• Admin can read and write all todos
• Reader can read all todos
• Writer can create new todos and update their own todos

To be able to implement these rules, we need to be able to access the aforementioned roles from the token. Supabase provides a helper function auth.jwt() to access the users JSON Web Token (JWT). The token contains user_metadata that encapsulates the data coming from the authentication provider. To play around with the token, you can open the SQL editor in the Supabase dashboard, change the role to some existing user (impersonate) and run the following query:

SELECT auth.jwt()->'user_metadata' AS user_metadata

This will return the user_metadata object from the token:

{
  "iss": "https://login.microsoftonline.com/<your EntraID tenant ID>/v2.0",
  "sub": "<subject>",
  "email": "<email>",
  "provider_id": "<provider_id>",
  "custom_claims": {
    "tid": "<object id of your user>",
    "email": "<email>",
    "roles": ["Reader", "Writer"]
  },
  "email_verified": true,
  "phone_verified": false
}

You might already see where this is going. We can access the roles by using:

auth.jwt()->'user_metadata'->'custom_claims'->'roles'

Admin

The Admin role should be able to read and write all todos. To implement this, we need a set of policies:

CREATE POLICY "Role Admin can read all todos"
ON public.todos
FOR SELECT
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Admin' );

There's already a lot going on here, so let's break it down:

• CREATE POLICY is used to create a new policy, the name can be anything you like
• every policy is only applicable to a single table, in this case public.todos
• FOR SELECT specifies the operation the policy is applied to, in this case SELECT, other options are: INSERT, UPDATE, DELETE or ALL
• TO authenticated specifies the role the policy is applied to, in this case authenticated which is the default role for authenticated users in Supabase
• USING specifies the condition that has to be met for selecting the row, in this case we check if the Admin role is present in the token
• WITH CHECK can be used to enforce additional conditions for INSERT, UPDATE or DELETE operations e.g. to ensure that the user can only create todos for itself

but we also want the Admin role to be able to write (INSERT/UPDATE/DELETE) todos:

-- Admin can create todos - including on behalf of others
CREATE POLICY "Role Admin can create todos"
ON public.todos
FOR INSERT
TO authenticated
WITH CHECK ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Admin' );

-- Admin can update todos of everyone
CREATE POLICY "Role Admin can write all todos"
ON public.todos
FOR UPDATE
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Admin' );

-- Admin can delete todos of everyone
CREATE POLICY "Role Admin can delete todos"
ON public.todos
FOR DELETE
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Admin' );

Writers

The Writer role can create new todos and update their own todos. As you might have guessed, we will also need more than one policy to implement this:

CREATE POLICY "Role Writer can read its own todos"
ON public.todos
FOR SELECT
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Writer' AND (owner = (auth.jwt() -> 'user_metadata' -> 'custom_claims' ->> 'tid')::uuid) );

This policy is a bit more complex than the previous one. Not only are we checking the Writer role, but we also check if the owner of the todo is the same as the tid from the token. The tid is the user ID from the authentication provider - in this case the object ID of the user in Azure EntraID.

Remark: This is not the ID of the user within Supabase! Supabase has its own helper auth.uid() to access the user ID but I prefer to use the object ID of the user directly from the token because it avoids one additional layer of indirection. For this reason I also have an independent users table in the database where I store the object ID of the user e.g. with a trigger on the INSERT of a new user.

For the Writer role to be able to create new todos, we need the following policy:

-- Writer can create todos - owner is set to the tid of the user, if provided it will be checked whether the user is the owner
CREATE POLICY "Role Writer can create todos for itself"
ON public.todos
FOR INSERT
TO authenticated
WITH CHECK ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Writer' AND (owner = (auth.jwt() -> 'user_metadata' -> 'custom_claims' ->> 'tid')::uuid) );

-- Writer can delete its own todos
CREATE POLICY "Role Writer can delete todos for itself"
ON public.todos
FOR DELETE
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Writer' AND (owner = (auth.jwt() -> 'user_metadata' -> 'custom_claims' ->> 'tid')::uuid) );

Readers

The Reader role can read all todos:

CREATE POLICY "Role Reader can read all todos"
ON public.todos
FOR SELECT
TO authenticated
USING ( auth.jwt()->'user_metadata'->'custom_claims'->'roles' ? 'Reader' );

As this is basically the same policy as the Admin can read all, we'll not further discuss this policy.

Final thoughts

RLS

PostgreSQL has a lot to offer when it comes to RLS policies, so make sure to check out the official documentation for more information.

EntraID

In real-world applications I'd recommend to create a entraid schema and store some auxiliary functions there similar to the ones provided by Supabase in the auth scheme.

CREATE SCHEMA IF NOT EXISTS entraid;

-- grant usage on the schema to authenticated users
GRANT usage ON schema entraid TO authenticated;

-- helper to return the object ID of the current user
CREATE OR REPLACE function "entraid"."uid" () returns uuid
SET
  search_path = '' AS $$
    BEGIN
        RETURN (auth.jwt() -> 'user_metadata' -> 'custom_claims' ->> 'tid')::uuid;
    END;
$$ language plpgsql;

-- grant execution permissions on the function to authenticated users
GRANT EXECUTE ON function "entraid"."uid" TO authenticated;

-- helper to return the roles defined in EntraID of the current user
CREATE OR REPLACE function "entraid"."roles" () returns jsonb
SET
  search_path = '' AS $$
    BEGIN
        RETURN auth.jwt() -> 'user_metadata' -> 'custom_claims' -> 'roles';
    END;
$$ language plpgsql;

-- grant execution permissions on the function to authenticated users
GRANT EXECUTE ON function "entraid"."roles" TO authenticated;

This would also make the policies more readable and maintainable.