Secure Multi-Tenant SaaS: Node.js, PostgreSQL & Row-Level Security for Scale

Introduction & The Problem

The Solution Concept & Architecture

Step-by-Step Implementation

Prerequisites:

• Node.js (LTS version) installed.
• PostgreSQL database (version 9.5+ for RLS) running.
• Basic knowledge of SQL and Node.js/Express.

1. Database Setup (PostgreSQL)

-- Connect as a superuser or admin
CREATE DATABASE saas_multitenant;
CREATE USER saas_user WITH PASSWORD 'strong_password';
GRANT ALL PRIVILEGES ON DATABASE saas_multitenant TO saas_user;

-- Connect to the new database
\c saas_multitenant

-- Create the Tenants table
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create the Users table with tenant_id
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (tenant_id, email) -- Ensure email is unique per tenant
);

-- Create the Products table with tenant_id
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Insert some sample data
INSERT INTO tenants (name) VALUES ('Acme Corp'), ('Globex Inc.');

INSERT INTO users (tenant_id, email, password_hash) VALUES
    ((SELECT id FROM tenants WHERE name = 'Acme Corp'), 'john@acme.com', 'hashed_password_acme'),
    ((SELECT id FROM tenants WHERE name = 'Globex Inc.'), 'jane@globex.com', 'hashed_password_globex');

INSERT INTO products (tenant_id, name, description, price) VALUES
    ((SELECT id FROM tenants WHERE name = 'Acme Corp'), 'Acme Product A', 'Description for Acme Product A', 10.50),
    ((SELECT id FROM tenants WHERE name = 'Acme Corp'), 'Acme Product B', 'Description for Acme Product B', 25.00),
    ((SELECT id FROM tenants WHERE name = 'Globex Inc.'), 'Globex Product X', 'Description for Globex Product X', 150.00);

2. Implementing Row-Level Security (RLS)

-- Enable RLS for users and products tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Create a policy for users table
CREATE POLICY user_isolation_policy ON users
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Create a policy for products table
CREATE POLICY product_isolation_policy ON products
    USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- Optionally, create policies to allow tenants to view their own tenant record (if needed)
-- CREATE POLICY tenant_view_policy ON tenants
--     USING (id = current_setting('app.tenant_id', true)::UUID);

-- Grant specific permissions to our saas_user
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO saas_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO saas_user;
GRANT SELECT ON tenants TO saas_user;

3. Node.js Application Setup

npm init -y
npm install express pg dotenv

DB_HOST=localhost
DB_PORT=5432
DB_USER=saas_user
DB_PASSWORD=strong_password
DB_DATABASE=saas_multitenant

// src/db.js
const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
});

// Middleware to get a client, set tenant_id, and release
async function withTenantDbClient(req, res, next) {
    const client = await pool.connect();
    try {
        const tenantId = req.tenantId; // tenantId set by previous middleware
        if (!tenantId) {
            return res.status(401).json({ message: 'Tenant ID is missing' });
        }

        // Set the session-local app.tenant_id variable
        await client.query("SET LOCAL app.tenant_id = $1", [tenantId]);
        req.dbClient = client; // Attach client to request object
        next();
    } catch (error) {
        console.error('Error setting tenant_id or getting DB client:', error);
        res.status(500).json({ message: 'Database error' });
    } finally {
        // The client must be released after the request is complete
        // This will be handled in a final middleware or route handler
    }
}

module.exports = { pool, withTenantDbClient };

// src/index.js
const express = require('express');
const { pool, withTenantDbClient } = require('./db');

const app = express();
const PORT = process.env.PORT || 3000;

app.use(express.json());

// Middleware to extract tenant ID (e.g., from X-Tenant-Id header or JWT)
app.use((req, res, next) => {
    // In a real application, this would come from an authenticated JWT token
    // For demonstration, we'll use a custom header.
    // Make sure the tenantId provided corresponds to an actual tenant in your 'tenants' table.
    const tenantId = req.headers['x-tenant-id'];
    if (!tenantId) {
        return res.status(400).json({ message: 'X-Tenant-Id header is required.' });
    }
    req.tenantId = tenantId;
    next();
});

// Apply the tenant database client middleware to routes that require RLS
app.use('/api', withTenantDbClient);

// Release the database client after each request
app.use((req, res, next) => {
    res.on('finish', () => {
        if (req.dbClient) {
            req.dbClient.release();
            // console.log('DB client released.'); // For debugging
        }
    });
    next();
});

// --- API Routes ---

// Get all products for the current tenant
app.get('/api/products', async (req, res) => {
    try {
        const { rows } = await req.dbClient.query('SELECT id, name, description, price FROM products');
        res.json(rows);
    } catch (error) {
        console.error('Error fetching products:', error);
        res.status(500).json({ message: 'Failed to fetch products' });
    }
});

// Create a new product for the current tenant
app.post('/api/products', async (req, res) => {
    try {
        const { name, description, price } = req.body;
        if (!name || !price) {
            return res.status(400).json({ message: 'Name and price are required.' });
        }
        // tenant_id is automatically added by RLS policy if not explicitly provided and policy allows
        // However, it's good practice to explicitly include it or rely on the RLS INSERT policy
        // For simplicity, here we let RLS handle it from current_setting('app.tenant_id') for INSERT
        const { rows } = await req.dbClient.query(
            'INSERT INTO products (tenant_id, name, description, price) VALUES (current_setting(\'app.tenant_id\')::UUID, $1, $2, $3) RETURNING id, name, description, price',
            [name, description, price]
        );
        res.status(201).json(rows[0]);
    } catch (error) {
        console.error('Error creating product:', error);
        res.status(500).json({ message: 'Failed to create product' });
    }
});

// Get a specific product by ID for the current tenant
app.get('/api/products/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const { rows } = await req.dbClient.query(
            'SELECT id, name, description, price FROM products WHERE id = $1',
            [id]
        );
        if (rows.length === 0) {
            return res.status(404).json({ message: 'Product not found or not accessible.' });
        }
        res.json(rows[0]);
    } catch (error) {
        console.error('Error fetching product by ID:', error);
        res.status(500).json({ message: 'Failed to fetch product' });
    }
});

// Get all users for the current tenant
app.get('/api/users', async (req, res) => {
    try {
        const { rows } = await req.dbClient.query('SELECT id, email FROM users');
        res.json(rows);
    } catch (error) {
        console.error('Error fetching users:', error);
        res.status(500).json({ message: 'Failed to fetch users' });
    }
});

// Start the server
app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
    console.log('Test with curl, e.g.:');
    console.log('curl -H "X-Tenant-Id: " http://localhost:3000/api/products');
});

process.on('SIGINT', async () => {
    console.log('Shutting down...');
    await pool.end();
    console.log('Database pool closed.');
    process.exit(0);
});

SELECT id, name FROM tenants;

# For Acme Corp
curl -H "X-Tenant-Id: YOUR_ACME_CORP_UUID" http://localhost:3000/api/products

# For Globex Inc.
curl -H "X-Tenant-Id: YOUR_GLOBEX_INC_UUID" http://localhost:3000/api/products

# Attempt to access without tenant ID (should fail)
curl http://localhost:3000/api/products

Optimization & Best Practices

Performance Considerations

• Indexing: Ensure that the tenant_id column in all RLS-enabled tables is indexed. This is crucial for query performance, as every query will implicitly filter by tenant_id.
• RLS Policy Complexity: Keep RLS policies as simple as possible. Complex policies can add overhead. If a table has very specific, varying access rules, consider if a different architectural pattern might be more suitable or if you can simplify the RLS conditions.
• Query Plan Analysis: Regularly use EXPLAIN ANALYZE on your queries to understand how RLS policies affect their execution plans. Identify and optimize any bottlenecks.

Security Best Practices

• Tenant ID Validation: Always validate the incoming tenant_id against your actual list of active tenants in the tenants table. While RLS prevents cross-tenant data access, ensuring a valid tenant ID prevents unnecessary database lookups or potential attacks where a malicious actor tries to guess valid UUIDs.
• Authentication & Authorization: The tenant ID should always be derived from a secure, authenticated source (e.g., a signed JWT token after a user logs in). Never trust a raw header from an unauthenticated request.
• Principle of Least Privilege: Grant your application's database user (saas_user in our example) only the necessary permissions. Avoid giving it superuser or administrative roles. RLS operates regardless of the user's role, but overall security relies on this principle.
• Secure Coding Practices: Always use parameterized queries to prevent SQL injection, even when setting session variables like app.tenant_id.

Schema Design

• Shared vs. Tenant-Specific Data: Not all data needs to be RLS-protected. Global data (e.g., system configurations, public listings) can exist in tables without RLS, while sensitive tenant-specific data requires it. Clearly define which tables fall into which category.
• Foreign Keys: When establishing foreign key relationships between RLS-protected tables, ensure that the tenant_id is part of the foreign key constraint if it's a composite key, or that the foreign key column itself is also filtered by RLS policies if it references an RLS-protected primary key.

Operational Considerations

• Tenant Provisioning: Automate the creation of new tenants in the tenants table. This should be part of your SaaS onboarding workflow.
• Monitoring: Monitor PostgreSQL logs for RLS-related warnings or errors to quickly identify any misconfigurations or potential breaches.

Business Impact & ROI

• Significant Cost Reduction: By sharing a single database instance across multiple tenants, you drastically reduce infrastructure costs compared to provisioning a separate database for each customer. This translates directly to higher profit margins or more competitive pricing for your SaaS.
• Enhanced Security & Compliance: RLS provides a robust, database-enforced layer of data isolation. This significantly mitigates the risk of data leaks and simplifies compliance with regulations like GDPR, HIPAA, or SOC 2, which demand strict separation of customer data. This builds trust with your enterprise clients.
• Faster Time-to-Market: A standardized and secure multi-tenant architecture allows you to onboard new customers more rapidly. You don't need to spin up new database instances or adjust complex schemas for each client, accelerating your sales cycle and growth.
• Simplified Operations & Maintenance: Managing a single, well-optimized PostgreSQL instance is far simpler than juggling dozens or hundreds of disparate databases. This reduces the burden on your DevOps team, freeing them to focus on innovation rather than maintenance.
• Improved Scalability: Your Node.js application can scale horizontally by adding more instances, while PostgreSQL can be scaled vertically or through advanced techniques like read replicas. The RLS approach does not impede database scaling and supports a growing number of tenants efficiently.
• Reduced Developer Overhead: Developers can write cleaner, more focused queries without needing to manually add WHERE tenant_id = '...' clauses everywhere, reducing the chances of human error and improving code maintainability. The database handles the critical security aspect.

Conclusion