Designing a Multi-tenant SAAS Database with Postgres RLS

user_7869513 0
Viewed 680

I want to design a multi-tenant SAAS database with PostgreSQL and RLS. I want to be able to host all users and the tenants in the same database and isolate their data with RLS.

In my use case it makes sense for tenants to share a certain user data to avoid duplication of those data. The tenants will request a permission from users and these permissions are stored in a permissions table and these permissions are used to control the RLS.

However, I need tenants to have user data that's unique to their organization such as join date or other data. What I need to know is how I can design the database to hold all the organization specific data. I have thought of using JSONB to store this data like

user_data_for_tenant

id
user_id
tenant_id
data JSONB

this data is decided by the tenant from the front-end.

Is this okay? How better can I design the database to fit this use case?

I will be using Supabase for DB, Auth, Storage and other uses so the RLS can control access.

rls supabase-database supabase multi-tenant postgresql
1 Answers

I would like to suggest the following design for the data management for permissions (AKA User Entity Permissions)

We can have a table that has the tenant users that are mapped to each entity in your SaaS Application. There will be permissions like Permission-C meaning Permission Create, so on for update, delete and read.

The tenant admin's can configure the access level for each of the users in their tenant based on the roles and we can internally use these roles to manage the policies in the Postgresql so that the queries are working fine based on the tenant level restrictions. Multi-Tenant entity level security implementation for SaaS Application

In case of having record level security, we can have all the ID's that can be accessed like below against each of the CRUD operation in the database.

The problem with the below approach is that as the data grows should the column grow, or you have a flat mapping of the IDS and the permissions. There are options to finetune this if the approach is suitable for your requirement.

row level security mapping

user_5419170
Related