Multi-Tenancy Approach in PostgreSQL

In the age of SaaS and cloud-based services, applications often cater to multiple clients, each with their distinct sets of data. Ensuring data isolation and privacy becomes paramount in this multi-client environment. Historically, multi-tenancy has been a favored design pattern where a single software instance serves multiple customers. However, the traditional approach of creating separate databases for each client to ensure data isolation comes with its own set of challenges:

Database Limitations: Many database systems or hosted DBaaS (Database as a Service) offerings have limitations on the number of databases that can be spun up in a single instance or cluster. This restricts scalability, especially for platforms with a growing user base.

Resource Overhead: Maintaining separate databases for each client can be resource-intensive. Not only does it require more storage, but the overhead of managing, backing up, and monitoring multiple databases also increases.

Operational Complexity: Deploying schema updates or performing data migrations across multiple databases can become complex and error-prone. Ensuring consistency across all client databases is crucial, yet challenging.

Given these constraints, there’s a clear need for a solution that offers data isolation without the overhead of multiple databases. This is where PostgreSQL’s Row Level Security (RLS) emerges as a promising alternative, providing granular data access controls within a single database instance.

What is Row Level Security?

Row Level Security (RLS) lets you define access control policies at the row level within tables. This means that you can specify which users can SELECT, INSERT, UPDATE, or DELETE rows based on the actual data within the row. It’s a granular approach to access control directly at the database layer.

Implementing Multi-Tenancy using RLS

Let’s take the example of a generic table named client with a column client_id that uniquely identifies each client.

CREATE TABLE client (
    client_id PRIMARY KEY,     
    client_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),               
    registration_date DATE DEFAULT CURRENT_DATE 
);

Our objective is to ensure each client’s data is only accessible to them and no one else. Here’s how to achieve this:

1. Assign a unique database user for each client. For instance, appuser_1001 for the client with client_id 1001.
2. Enable Row Level Security on the client table:

ALTER TABLE client ENABLE ROW LEVEL SECURITY;

3. Drop any existing policy (this step ensures that we’re starting fresh):

DROP POLICY IF EXISTS client_isolation_policy ON client;

4. Create a new RLS policy. This policy validates if the logged-in use has the name format appuser_<client_id>:

CREATE POLICY client_isolation_policy on client
USING (current_user =CONCAT('appuser_', client_id::text));

Now, only the respective database user for a client (e.g., appuser_1001) will be able to access data related to that particular client. Your application's middle-tier or backend should use the specific database user to connect and pull the data, ensuring proper data isolation.

5. This is how we can create the user for the client specific access.

create user appuser_1001 WITH ENCRYPTED PASSWORD 'password';

GRANT connect on database sample_db to appuser_1001;

GRANT USAGE ON SCHEMA public TO appuser_1001;

GRANT select on all tables in schema public to appuser_1001;

GRANT INSERT,UPDATE ON all tables in schema public TO appuser_1001;

Lets Test it out

Running SQL as root or postgres user

select * from client c ;

client_id|client_name|email          |registration_date|
---------+-----------+---------------+-----------------+
     1001|Client 1001|abc@example.com|       2023-10-17|
     1002|Client 1002|abc@example.com|       2023-10-17|

Running SQLas appuser_1001 user:

select * from client c ;

client_id|client_name|email          |registration_date|
---------+-----------+---------------+-----------------+
     1001|Client 1001|abc@example.com|       2023-10-17|

Benefits

• Data Security: Robust data isolation right at the database level.
• Scalability: Simply add a new database user to onboard new clients.
• Flexibility: Policies can be tweaked as per changing requirements without major code changes.

PostgreSQL’s Row Level Security is an effective way to implement multi-tenancy. By ensuring data isolation at the database level, it eliminates the need for filtering data at the application level based on client IDs, streamlining operations and enhancing security.