DEV Community

Cover image for Dead simple Role-based access control in supabase
not Michal
not Michal

Posted on • Edited on

1

Dead simple Role-based access control in supabase

UPDATE: I got featured on Community Highlights!

I shared this post 👇 and it gained more attention than I expected (considering my usual standards 😅). Here’s a breakdown of how everything comes together.

1. Defining Roles

For a more human-friendly approach to referencing roles, you can create a custom enum type:

CREATE TYPE user_role AS ENUM ('spots_moderator', 'admin');
Enter fullscreen mode Exit fullscreen mode

2. Setting Up the “user_roles” Table

The user_roles table allows us to link roles with users. To ensure the security of user_roles, enable Row-Level Security (RLS) and implement the policy:

Image description

To secure user_roles enable RLS (Row-level security) and add following policy auth.uid() = user_id

Image description

This policy grants authenticated users access to read the roles they possess.

Key points:

  • user_id references auth.users.id
  • The role is based on the user_role type (the enum type from step 1.)
  • RLS enables reading of roles owned by the user

3. Implementing Row-Level Security for Role-Specific Access

You can now control access to specific rows within a table, such as spot_proposals in this case, by users with particular roles. This is done by specifying the following RLS condition:

(auth.uid() IN ( SELECT user_roles.user_id
   FROM user_roles
  WHERE (user_roles.role = 'spots_moderator'::user_role)))
Enter fullscreen mode Exit fullscreen mode

Image description

Key points:

  • user_roles has an RLS policy, allowing authenticated users to read their roles
  • The RLS policy on spot_proposals restricts access to specific roles, such as spots_moderator

This setup ensures a straightforward yet effective role-based access control mechanism in Supabase.

Looking for more Supabase content? Join me on Twitter for the latest updates, tutorials, and insights! Follow along to stay in the loop.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (1)

Collapse
 
devkiran profile image
Kiran Krishnan

This is a good idea.

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more