How to Implement RLS in PostgreSQL for Multi-Tenant SaaS

Introduction

When building a multi-tenant SaaS platform, ensuring data isolation is one of the biggest challenges. You need to make sure that each customer (tenant) only sees their own data and that’s where Row-Level Security (RLS) in PostgreSQL comes in handy.

With RLS, you can set up rules that automatically restrict access at the database level, preventing tenants from accidentally (or maliciously) accessing each other’s data. This is a powerful way to enforce security, especially when using a single database for multiple tenants.

In this guide, we’ll walk you through how to enable and configure RLS in PostgreSQL for your Next.js-based SaaS platform.

If you’re already working on optimizing your Next.js performance or handling authentication with Supabase, this article will help you take the next step in securing your multi-tenant architecture.

Why Does RLS Matter for SaaS?

When scaling your SaaS, you need the right database structure to handle growing users without compromising security or performance.

Our article on building scalable SaaS with Next.js covers 10 key strategies, including performance, authentication, and database best practices.

RLS fits into this by ensuring each tenant’s data stays private and secure, which is crucial for trust and compliance.

If you’re working with Supabase for authentication, you might also want to check out our guide on securing your SaaS with Supabase. It explains how to handle user roles, session-based access, and more—elements that pair perfectly with RLS to create a secure multi-tenant setup.

Now, let’s dive into how to implement RLS in PostgreSQL step by step.

1. Understanding Multi-Tenancy Models

Before implementing Row-Level Security (RLS) in PostgreSQL, it’s important to understand how multi-tenancy works.

Multi-tenancy is a way of designing your SaaS platform so that multiple businesses (tenants) can use the same system without interfering with each other’s data.

There are three main ways to organize a multi-tenant database:

Let’s look at some code examples below so you can have better understanding in the practical way.

1. Single Database, Shared Tables (Most Common for SaaS)

All tenants share the same database and tables, but each row contains a tenant identifier (tenant_id).

Example: A Multi-Tenant users Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL,  -- Identifies which tenant owns this user
    email TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL
);

Each row belongs to one specific tenant, making it easier to scale while keeping data separated logically.

Pros: Simple, cost-effective, and scales well.

Cons: Requires strong security enforcement (like RLS) to prevent data leaks.

2. Separate Schemas for Each Tenant

Each tenant has its own schema within a shared database.

Example: Per-Tenant Schemas

CREATE SCHEMA tenant_123e4567; -- Tenant A
CREATE SCHEMA tenant_987f6543; -- Tenant B

Tables are duplicated inside each schema:

CREATE TABLE tenant_123e4567.users (...);
CREATE TABLE tenant_987f6543.users (...);

Pros: Better isolation, easy to enforce security.

Cons: More complex and harder to scale as the number of tenants grows.

3. Separate Databases for Each Tenant

Each tenant gets its own database, completely separate from others.

Example: Per-Tenant Databases

CREATE DATABASE tenant_123e4567;
CREATE DATABASE tenant_987f6543;

This approach provides the highest level of isolation but is also the most expensive and difficult to maintain.

Pros: Best security and performance.

Cons: High cost, complex maintenance, harder to manage backups.

Why Choose the Shared Table Model with RLS?

For most SaaS applications, the shared table model is the best balance between simplicity, cost, and performance.

However, it requires a strong security mechanism, which is exactly what Row-Level Security (RLS) in PostgreSQL provides.

In the next section, we’ll dive into how to implement RLS to enforce tenant isolation securely.

2. Enable Row-Level Security in PostgreSQL

By default, PostgreSQL does not restrict access to specific rows in a table. Any user with the right permissions can see all the data.

To ensure each tenant only sees their own data, we need to enable Row-Level Security (RLS) on the table.

Supabase Warning: RLS is Not Enabled by Default

In our example here, we’ll show you how you can enable RLS for PostGreSQL tables in Supabase.

On Supabase, you’ll get a warning when creating a new table:

“Row Level Security is not enabled for this table. Your data might be exposed to all authenticated users.”

This warning exists because without RLS, all authenticated users could access every row in the table.

Supabase strongly recommends enabling RLS for multi-tenant applications.

The above screenshot shows a security alert of a PostGreSQL table is NOT enabled with RLS in Supabase Security Advisor.

Below are a few steps that how you can turn on RLS on a PostGreSQL table on Supabase.

Our example below is to enable RLS on a table chat_messages which has the following structure:

CREATE TABLE chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique message ID
    tenant_id UUID NOT NULL,  -- Tenant identifier (for multi-tenancy)
    sender_id UUID NOT NULL,  -- User who sent the message
    receiver_id UUID NOT NULL, -- User who receives the message
    message TEXT NOT NULL, -- Message content
    created_at TIMESTAMPTZ DEFAULT now()
);

Step 1: Turn on RLS for the Table

First, we enable RLS on the chat_messages table in our example here:

ALTER TABLE chat_messages ENABLE ROW LEVEL SECURITY;

This tells PostgreSQL to start enforcing security policies on the table. However, by default, no restrictions are applied yet.

On Supabase, you can just use the above sql to execute on the SQL editor as shown in the screenshot below:

Step 2: Restrict Access with a Security Policy

Now, we create a policy to ensure users can only see their own messages.

Below is an example of using Supabase Auth, which you can link the sender_id or receiver_id to the logged-in user’s auth.uid():

CREATE POLICY "User can see their messages"
ON chat_messages 
FOR SELECT 
USING (auth.uid() = sender_id OR auth.uid() = receiver_id);

This ensures that users can only view messages where they are the sender or receiver.

Simply run the above SQL on the Supabase SQL Editor.

Step 3. Check Your RLS Policies

To confirm that RLS policies exist for chat_messages, run the SQL below on the SQL Editor:

SELECT * FROM pg_policies WHERE tablename = 'chat_messages';

3. Assign Tenant-Specific User Roles

In a multi-tenant SaaS, not all users should have the same level of access.

Some users might only see their own data, while admins might need access to everything within their company (tenant). To control this, we assign roles to users.

Why Do We Need Roles?

Think of a workspace like Slack. Employees in one company shouldn’t see messages from another company.

But within their own company, some users might be admins who manage everything, while others can only see their own messages.

We can handle this in PostgreSQL by storing roles in a users table and using Row-Level Security (RLS) policies to enforce access control.

The following example is based on a custom users table which will be needed in some system to store additional information besides the original table for authentication.

The users table may have the following structure:

CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE, 
  email TEXT UNIQUE NOT NULL, 
  tenant_id TEXT NOT NULL, 
  role TEXT CHECK (role IN ('admin', 'member')) DEFAULT 'member',
  created_at TIMESTAMP DEFAULT now()
);

Please note that the above users table has a role column:

• “admin” – Can access all data within their tenant (company).
• “member” – Can only access their own data.

Step 1: Assign Roles to Users

When a new user signs up, we need to assign a role. In Supabase, we can set a default role when inserting a new user:

INSERT INTO users (id, email, tenant_id, role) 
VALUES (auth.uid(), 'user@example.com', 'company_123', 'member');

The auth.uid() function grabs the current user’s ID from Supabase.

Each user belongs to a tenant (company), identified by tenant_id.

If an admin invites a user to be another admin, we can change their role later:

UPDATE users SET role = 'admin' WHERE email = 'newadmin@example.com';

Step 2: Enforce Role-Based Access in RLS Policies

Now, we need to ensure that only admins can access all tenant data, while regular users can only see their own records.

If we want user with role admin to see all messages in their tenant (company):

CREATE POLICY "Admins see all messages in their tenant" 
ON chat_messages 
FOR SELECT 
USING (
  EXISTS (
    SELECT 1 FROM users 
    WHERE users.id = auth.uid() 
    AND users.tenant_id = chat_messages.tenant_id 
    AND users.role = 'admin'
  )
);

Step 4: Testing the Roles

You can test if the RLS rules work by running:

SELECT * FROM chat_messages;

If you’re logged in as a regular user, you should only see your own messages.

If you’re an admin, you should see all messages within your tenant.

Storing roles in the users table with RLS ensures users see only their data, admins manage their company’s data, and tenants stay isolated – keeping your SaaS secure.

4. Enforce RLS Policies for Multi-Tenant Security

Once Row-Level Security (RLS) is enabled, we must define clear policies to ensure:

1. Users only access their own company’s data.
2. Admins can manage their company’s data.
3. No cross-tenant data leaks.

Example: Restrict chat_messages to a user’s tenant

CREATE POLICY "Tenant-based data isolation"
ON chat_messages 
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM users 
    WHERE users.id = auth.uid() 
    AND users.tenant_id = chat_messages.tenant_id
  )
);

The above checks that the user belongs to the same tenant as the message.

Users will only see messages from their own company.

Grant Admins Full Access to Their Tenant’s Data

If we have an admin role, we must allow admins to manage all data under their tenant.

Example: Allow admins to read, insert, update, and delete all tenant data

CREATE POLICY "Admins manage their tenant data"
ON chat_messages 
FOR ALL USING (
  EXISTS (
    SELECT 1 FROM users 
    WHERE users.id = auth.uid() 
    AND users.tenant_id = chat_messages.tenant_id
    AND users.role = 'admin'
  )
);

Admins can read, add, update, and delete messages within their company.

Set a Default Fallback Policy for Safety

To prevent accidental data leaks, we should deny access by default by applying the followings:

ALTER TABLE chat_messages ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON chat_messages FROM PUBLIC;

So, after doing the above:

1. Only users with explicit RLS policies can access the table.
2. Prevents accidental exposure of sensitive tenant data.

RLS ensures tenant isolation—users see only their company’s data, admins manage their company’s data, and a fallback policy prevents accidental access.

5. Testing & Debugging RLS in Supabase

Once Row-Level Security (RLS) is enabled, we need to test if it’s working correctly.

Without proper testing, users might accidentally see data they shouldn’t have access to or get blocked from their own data.

Here are 3 simple ways to check what policies and rules have been applied to tables:

1. Check Active RLS Policies in Supabase

Sometimes, it’s hard to remember which RLS policies are applied to a table.

Below is an SQL statement that you can quickly check what policies you have enabled on a table.

SELECT policyname, tablename, permissive, roles, cmd, qual, with_check
FROM pg_policies WHERE tablename = 'chat_messages';

2. Confirm RLS is Enabled on a Table

If you want to check whether RLS is enabled at all, run:

SELECT relname, relrowsecurity FROM pg_class
WHERE relname = ‘chat_messages’;

If relrowsecurity is false, it means RLS is not enabled, and you need to enable it.

3. Analyze Query Execution with EXPLAIN ANALYZE

To debug RLS behavior, we need to check how PostgreSQL processes a query.

This helps confirm if the RLS policy is being applied correctly and whether the query runs efficiently.

You can do this using:

EXPLAIN ANALYZE SELECT * FROM chat_messages WHERE tenant_id = (SELECT tenant_id FROM users WHERE id = auth.uid());

This query helps check if PostgreSQL is correctly applying the RLS policy by showing how it processes the data.

It verifies whether tenant-specific filtering is working and can also highlight any performance issues if the query runs slowly.

Conclusion

Row-Level Security (RLS) is a powerful way to keep tenant data secure in a multi-tenant SaaS.

By enabling RLS and defining clear policies, we can ensure that users only access the data they’re supposed to see.

Supabase makes this process more approachable by providing built-in tools to manage and test RLS effectively.

While setting up RLS requires careful planning, the effort pays off with stronger data isolation and better security.

Regular testing helps catch potential issues early, ensuring a smooth experience for users. As your SaaS grows, optimizing queries and indexing will help maintain performance at scale.

With the right setup, RLS allows you to build a secure, scalable, and reliable multi-tenant system—without the complexity of managing separate databases for each tenant.