Multi-Tenant Applications with RLS on Supabase (Postgress)

It all started when one of our clients was looking to scale their application to serve multiple organizations through a single platform. The application was built on Supabase, which provided them with a fully managed database powered by PostgreSQL.They wanted to implement a multi-tenant architecture but were concerned about data security and isolation. The challenge was clear: how could they ensure strict data separation between tenants without the complexity and cost of maintaining separate databases for each one? This is where Row-Level Security (RLS) came into play, offering a way to solve the problem by providing fine-grained access control at the database level, making multi-tenancy secure, efficient, and scalable.

What are we covering in the blog?

• What is Multi-Tenancy?
• What is Row Level Security(RLS)?
• How Does RLS Help in Multi-Tenancy?
• Setting Up RLS.
• Best Practices & Challenges.

Understanding Multi-Tenancy

Multi-tenancy is an architectural model where a single application or database serves multiple customers (tenants). Each tenant’s data is isolated, ensuring that one tenant cannot access another’s data. This approach is widely used in SaaS platforms, where a single application serves many organizations.

Here, instead of maintaining separate instances for each customer, a multi-tenant system shares the same resources such as servers, databases, and processing power. This enables businesses to scale rapidly. RLS plays a key role in this model by providing strict data isolation at the row level, ensuring each tenant can only access their own data.

Understanding RLS?

Row-Level Security (RLS), as the name suggests, is a database feature that enables you to control access to data at the individual row level, ensuring that users can only view the data they are authorized to access. This is offered by most databases and warehouse solutions like Postgres, MySQL, MS-SQL, snowflake, etc.

Unlike traditional methods where access control is managed at the application layer, RLS ensures that sensitive data is automatically filtered based on the user’s identity or other attributes, minimizing the risk of unauthorized access. RLS simplifies security management by centralizing and automating the enforcement of rules.

Components of an RLS policy:

Let’s break down each part of the given RLS policy: (This should be in H2)

1. CREATE POLICY “office_managers”: This creates a new RLS policy named “office_managers”. The policy’s name helps to identify the policy in the database.

2. ON “office”.”user_salaries”: This part specifies the table to which the policy applies. In this case, the policy is applied to the user_salaries table, which is in the office schema.

3. AS PERMISSIVE: This defines the behavior of the policy. PERMISSIVE means that access to the rows is allowed if any of the active policies permit access. In other words, if multiple policies are in place, and at least one grants access, the user can access the row.

4. FOR SELECT: This specifies the type of SQL operation the policy applies to. In this case, FOR ALL means the policy is concerned with SELECT, INSERT, UPDATE, DELETE queries on the user_salaries table.

5. TO authenticated: This specifies the role that the policy applies to. The authenticated role indicates that the policy will apply to any users who are authenticated.

6. USING (true): The USING clause defines the condition that must be met for a user to read (SELECT) a row from the table.

7. WITH CHECK (true): The WITH CHECK clause defines the condition that must be met when attempting to insert or update rows in the table.

Note: Although true is used in the example for both USING and WITH CHECK, realistically, there should always be a condition that evaluates to either true or false to ensure proper access control. For example, instead of true, a more practical condition could be something like: USING (tenant_id = current_setting('myapp.tenant_id')::int) OR WITH CHECK (tenant_id = current_setting('myapp.tenant_id')::int). This condition ensures that a user can only access rows where the tenant_id matches the tenant_id set in the current session. This would prevent users from accessing or modifying data belonging to other tenants.

How does RLS help?

RLS strengthens data security and simplifies access control by enforcing restrictions directly at the database level. This centralization improves security and supports multi-tenant environments. It gives:

1. Fine-Grained Access Control: RLS restricts database access at the row level, ensuring that users can only access specific rows that they are authorized to view based on predefined policies. This enhances data security by preventing unauthorized data access within the same table.
2. Security: Rather than relying on application logic or external systems, RLS centralizes the security control within the database, making it easier to manage and enforce data access permissions.
3. Simplifies the Application Logic: With RLS, database queries automatically filter data based on the user’s role, allowing customized access controls to be enforced without altering application logic. This ensures that sensitive data is visible only to authorized users.
4. Multi-Tenancy Support: Effortlessly manage multiple clients or business units within a single system, while maintaining shared resources for cost efficiency.
5. Performance Benefits: RLS helps with performance by reducing the need for complex filtering and access control logic in the application layer.

Setting Up RLS for Multi-Tenant Architectures

Here’s how we set up RLS for a multi-tenant architecture:

1. Define a Tenant Identifier
2. Create RLS Policies Based on Tenant ID
3. Optimize for Performance:

Best Practices

1. Understand Your Users
2. Define Clear Roles
3. One Policy Per Operation, Per Role
4. Understand Your Users
5. Periodic Reviews and Updates
6. Monitor Access Logs
7. Use WHERE in Queries

Challenges and Limitations of RLS in Multi-Tenancy

1. Performance Overhead
2. Policy Conflicts
3. Debugging Difficulty

Key Takeaways

Row-Level Security (RLS) is a crucial component for ensuring secure and efficient multi-tenancy in modern applications. By enforcing fine-grained access control directly at the database level, RLS ensures that each tenant’s data is isolated while sharing the same infrastructure. It simplifies security management, reduces the risk of unauthorized access, and improves scalability. Despite some challenges, such as performance overhead and policy complexity, RLS remains an essential tool for organizations looking to build secure, cost-effective, and scalable multi-tenant systems.

This article was originally published on our blog. Read the full version here: Multi-Tenant Applications with RLS on Supabase (Postgress) – A Beginner's Guide

Published by: Rahul Kumar