Back to articles
Security & Anti-DDoS

Building Multi-Tenant SaaS Infrastructure: Real-World Comparison Between PostgreSQL Schemas and Row Level Security (RLS)

A detailed technical guide on building a multi-tenant SaaS infrastructure on VPS using PostgreSQL schemas and Row Level Security (RLS), including a real-world comparison of...

DPTCloud Engineering6 min read
Building Multi-Tenant SaaS Infrastructure: Real-World Comparison Between PostgreSQL Schemas and Row Level Security (RLS)

Building a multi-tenant infrastructure for SaaS applications on a VPS is a critical technical challenge, especially when ensuring data isolation between clients without compromising performance or increasing administrative complexity. On a VPS with limited CPU, RAM, and I/O resources, choosing between PostgreSQL schemas and Row Level Security (RLS) to achieve data isolation requires careful evaluation. This guide presents real-world implementation and compares the effectiveness of both methods in terms of security, performance, and data management.

1. Introduction to Multi-Tenancy and Security Requirements

SaaS applications typically serve multiple customers (tenants) sharing a single database. The core requirement is data isolation: each customer's data must be protected from unauthorized access by other customers. On a VPS with constrained CPU, RAM, and I/O resources, the choice of data isolation method directly impacts overall performance and scalability.

The two most common approaches on PostgreSQL are:

  • Schema-based isolation: Creating a separate schema for each customer.
  • Row-Level Security (RLS): Using policies to filter data at the row level within a shared schema.

The choice depends on specific requirements for security, performance, and data management. This guide analyzes real-world scenarios on a VPS environment.

2. Real-World Implementation and Comparison: Schemas vs. RLS

2.1. Approach Using PostgreSQL Schemas

This method creates a distinct schema for each customer within the same database. Each schema functions as a "virtual database".

Advantages:

  • Higher security: Data is completely separated at the schema level. A user cannot access another schema unless granted special privileges.
  • Better performance: Queries run only on data within the current schema, reducing system load.
  • Simpler management: Individual schemas can be created, deleted, backed up, and restored independently.

Disadvantages:

  • Increased complexity: Requires managing multiple schemas and privileges for each.
  • Scalability issues: Managing thousands of schemas can become problematic.
  • Resource-intensive: Each schema may have its own tables and indexes, increasing storage and memory usage.

Implementation example:

-- Create schema for customer
CREATE SCHEMA IF NOT EXISTS tenant_a;
CREATE SCHEMA IF NOT EXISTS tenant_b;

-- Create tables in the respective schema
CREATE TABLE tenant_a.users (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tenant_b.users (id SERIAL PRIMARY KEY, name TEXT);

-- Grant privileges to users
GRANT ALL PRIVILEGES ON SCHEMA tenant_a TO user_tenant_a;
GRANT ALL PRIVILEGES ON SCHEMA tenant_b TO user_tenant_b;

2.2. Approach Using Row Level Security (RLS)

RLS allows defining policies to filter data based on conditions, typically using customer ID or user ID.

Advantages:

  • Simpler management: Only one shared schema is needed, reducing database administration complexity.
  • Scalability: No limit on the number of customers; just add policies.
  • Resource-efficient: No need to create multiple schemas, saving storage and memory.

Disadvantages:

  • Lower security: If policies are misconfigured or bypassed, data exposure can occur.
  • Potential performance impact: Each query must check policies, which can slow down complex queries.
  • Harder to verify: Access control verification is more complex since data resides in a single schema.

Implementation example:

-- Enable RLS on the users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create policy for tenant_a
CREATE POLICY tenant_a_policy ON users
  FOR SELECT USING (tenant_id = 'tenant_a');

-- Create policy for tenant_b
CREATE POLICY tenant_b_policy ON users
  FOR SELECT USING (tenant_id = 'tenant_b');

3. Real-World Comparison on VPS Environment

On a VPS with limited resources (e.g., 2 vCPU, 4GB RAM), performance and stability are critical factors.

Factor PostgreSQL Schemas Row Level Security (RLS)
Security ★★★★★ ★★★★☆
Query Performance ★★★★☆ ★★★☆☆
Management Complexity ★★★☆☆ ★★★★☆
Scalability ★★★☆☆ ★★★★★
Resource Usage ★★★★☆ ★★☆☆☆

4. Recommendations and Combined Solutions

There is no perfect method. However, based on real-world VPS scenarios, a combined approach can be effective:

  • For applications with few customers (under 100), use schema-based isolation to ensure high security and good performance.
  • For applications with thousands of customers, use RLS to optimize resources and management.
  • In both cases, combine with database role permissions and application layer data access to enhance security.

5. Verification and Troubleshooting

To ensure the system functions correctly, perform the following checks:

  • Verify RLS: Test whether RLS policies are working by accessing data with different users.
    SELECT * FROM users;
  • Verify Schema Isolation: Ensure users can only access their own schema.
    SELECT current_schema;
  • Check Performance: Use EXPLAIN ANALYZE to assess query performance.

6. References

DPTCloud Tier III Infrastructure

Recommended NVMe Cloud VPS Plans to Deploy This Setup

Enterprise RAID 10 NVMe SSDs, CMC & FPT District 9 uplinks with Anycast Anti-DDoS and 99.99% SLA.

View 22 VPS Plans
ECO · vn-02

VPS-2E

94.600 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 2 GB
  • SSD: 30 GB NVMe
ECO · vn-02Recommended

VPS-3E

170.500 ₫ / mo

10% VAT included

  • CPU: 2 vCPU
  • RAM: 4 GB
  • SSD: 50 GB NVMe
BASIC · vn-01Recommended

VPS-05

457.600 ₫ / mo

10% VAT included

  • CPU: 4 vCPU
  • RAM: 8 GB
  • SSD: 50 GB NVMe

Related Articles

View all
ZaloZalo SupportDiscord0384 753 478