Configuring PostgreSQL Row Level Security for Multitenancy

Building a multi-tenant Software as a Service (SaaS) backend presents a fundamental architectural decision: how do you isolate customer data securely while maintaining database performance and manageable infrastructure costs? While separate databases offer hard boundaries, managing thousands of database instances creates massive operational overhead. A single shared database with a shared schema is the most resource-efficient approach, but relying entirely on application layer filtering (such as adding WHERE tenant_id = ? to every query) is a risk. One missing condition in a complex JOIN query can leak sensitive customer data to another tenant.

PostgreSQL Row Level Security (RLS) solves this problem at the database engine level. By moving isolation rules into PostgreSQL itself, your database enforces tenant boundaries regardless of how queries are structured in application code. Even if a backend developer forgets to specify a tenant filter in an ORM query, PostgreSQL automatically restricts the result set to rows matching the tenant context of the current session.

In this guide, I will walk you through setting up production-grade PostgreSQL Row Level Security for a multi-tenant application from scratch. We will cover database schemas, session variables, complete security policies, connection pool integration in Node.js, and audit logging patterns.

What You’ll Need

• A PostgreSQL database instance running on a Hetzner VPS or DigitalOcean droplet.
• Domain name registered via Namecheap for securing API endpoints with TLS certificates.
• Node.js runtime environment installed on your local setup or server.
• n8n Cloud for orchestrating automated database health checks and maintenance workflows.

Table of Contents

• Designing the Relational Schema and Enabling RLS
• Constructing PostgreSQL Session Policies
• Integrating RLS with Nodejs Connection Pools
• Testing Tenant Isolation and Auditing Security
• Getting Started

Designing the Relational Schema and Enabling RLS

To implement shared schema multi-tenancy, every tenant-owned table must contain a column identifying the owning tenant, typically named tenant_id. We will construct a schema containing four tables: tenants, users, projects, and tasks.

Deploy your PostgreSQL database on a high-performance instance like a Hetzner VPS to ensure minimal latency during dynamic policy evaluations. Connect to your database using psql or your database GUI tool and run the complete DDL script below to build our foundation:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(63) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant_id ON tasks(tenant_id);
CREATE INDEX idx_tasks_project_id ON tasks(project_id);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;

Notice the final three statements: FORCE ROW LEVEL SECURITY. By default, PostgreSQL bypasses RLS policies for table owners and superusers. Running FORCE ROW LEVEL SECURITY ensures that even if your backend database role owns the table schema, PostgreSQL will strictly enforce the RLS policies on every single query executed by that role.

💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.

Constructing PostgreSQL Session Policies

Now that Row Level Security is enabled on our tables, all queries executed by non-superusers will return zero rows because no rules permit access yet. We need to create a dedicated application role and define RLS policies based on a PostgreSQL session configuration parameter.

We will use the custom setting app.current_tenant_id. During every transaction, the application backend sets this session variable. The database engine then reads app.current_tenant_id to evaluate permissions.

Run the following SQL script to establish roles and set up non-leaky policies for all CRUD operations:

DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_user') THEN
        CREATE ROLE app_user WITH LOGIN PASSWORD 'SecureAppPassword123!';
    END IF;
END
$$;

GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

CREATE POLICY tenant_isolation_users ON users
    FOR ALL
    TO app_user
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

CREATE POLICY tenant_isolation_projects ON projects
    FOR ALL
    TO app_user
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

CREATE POLICY tenant_isolation_tasks ON tasks
    FOR ALL
    TO app_user
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
    WITH CHECK (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

Let’s dissect how this policy functions. The expression current_setting('app.current_tenant_id', true) reads our custom session variable. Passing true as the second argument prevents PostgreSQL from throwing an unhandled exception if the variable has not been initialized in the current session; it simply returns NULL.

The NULLIF wrapper converts empty strings into NULL values, preventing invalid UUID parsing errors. Finally, we cast the value to ::uuid so it matches our primary key types.

The USING clause controls which existing rows are visible for SELECT, UPDATE, and DELETE queries. The WITH CHECK clause validates new data written during INSERT and UPDATE operations, throwing a runtime violation error if an application attempts to insert a record owned by a different tenant ID.

Integrating RLS with Nodejs Connection Pools

In modern web applications, backends communicate with PostgreSQL using database connection pools. Because database clients reuse standard TCP connections across arbitrary HTTP requests, setting standard session state using SET app.current_tenant_id = '...' will cause severe cross-tenant contamination. If Connection A is used for Tenant X and returned to the pool, the next HTTP request assigned to Connection A might leak Tenant X data to Tenant Y.

To prevent connection context leakage, you must use transaction-local configuration parameters with SET LOCAL. The SET LOCAL command restricts parameter validity exclusively to the active BEGIN ... COMMIT transaction block. Once the transaction terminates, PostgreSQL resets app.current_tenant_id automatically.

If you are authenticating user requests via API tokens, make sure your security token workflow is configured properly. Reviewing how to implement Implementing JWT Refresh Token Rotation In Express offers great insight into securely extracting authenticated tenant claims before reaching your database layer.

Below is a complete, production-ready Express.js server in Node.js demonstrating tenant-isolated database context wrapping using the pg driver:

const express = require('express');
const { Pool } = require('pg');

const app = express();
app.use(express.json());

const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432', 10),
  database: process.env.DB_NAME || 'multitenant_db',
  user: 'app_user',
  password: 'SecureAppPassword123!',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

async function withTenantContext(tenantId, callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(`SET LOCAL app.current_tenant_id = $1`, [tenantId]);
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

app.use((req, res, next) => {
  const tenantId = req.headers['x-tenant-id'];
  if (!tenantId && req.path !== '/health') {
    return res.status(400).json({ error: 'Missing required X-Tenant-ID header' });
  }
  req.tenantId = tenantId;
  next();
});

app.get('/projects', async (req, res) => {
  try {
    const projects = await withTenantContext(req.tenantId, async (client) => {
      const response = await client.query(
        'SELECT id, name, description, created_at FROM projects ORDER BY created_at DESC'
      );
      return response.rows;
    });
    res.json({ data: projects });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

app.post('/projects', async (req, res) => {
  const { name, description } = req.body;
  try {
    const newProject = await withTenantContext(req.tenantId, async (client) => {
      const response = await client.query(
        `INSERT INTO projects (tenant_id, name, description) 
         VALUES ($1, $2, $3) 
         RETURNING id, name, description, created_at`,
        [req.tenantId, name, description]
      );
      return response.rows[0];
    });
    res.status(201).json({ data: newProject });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

app.get('/projects/:projectId/tasks', async (req, res) => {
  const { projectId } = req.params;
  try {
    const tasks = await withTenantContext(req.tenantId, async (client) => {
      const response = await client.query(
        `SELECT t.id, t.title, t.completed, t.created_at 
         FROM tasks t 
         JOIN projects p ON t.project_id = p.id 
         WHERE t.project_id = $1`,
        [projectId]
      );
      return response.rows;
    });
    res.json({ data: tasks });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Multi-tenant RLS server listening on port ${PORT}`);
});

Every database call inside withTenantContext runs inside a transactional isolation block. SET LOCAL assigns the tenant ID to PostgreSQL for the exact lifespan of that single client request, guaranteeing that connection pooling cannot accidentally share tenant permissions across threads or events.

Testing Tenant Isolation and Auditing Security

To confirm that your RLS engine works properly, you should write automated database boundary tests. You can execute this raw SQL testing script directly inside PostgreSQL to simulate multi-tenant attempts:

TRUNCATE TABLE tenants CASCADE;

INSERT INTO tenants (id, name, slug) VALUES 
('11111111-1111-1111-1111-111111111111', 'Acme Corp', 'acme'),
('22222222-2222-2222-2222-222222222222', 'Stark Ind', 'stark');

SET ROLE app_user;

SET LOCAL app.current_tenant_id = '11111111-1111-1111-1111-111111111111';

INSERT INTO projects (id, tenant_id, name, description) VALUES
('a1111111-1111-1111-1111-111111111111', '11111111-1111-1111-1111-111111111111', 'Acme Website', 'Redesign project');

SET LOCAL app.current_tenant_id = '22222222-2222-2222-2222-222222222222';

INSERT INTO projects (id, tenant_id, name, description) VALUES
('b2222222-2222-2222-2222-222222222222', '22222222-2222-2222-2222-222222222222', 'Iron Suit v2', 'R&D project');

SET LOCAL app.current_tenant_id = '11111111-1111-1111-1111-111111111111';

SELECT * FROM projects;

SELECT * FROM projects WHERE id = 'b2222222-2222-2222-2222-222222222222';

RESET ROLE;

When running SELECT * FROM projects; as Tenant 1, PostgreSQL returns only Acme Corp’s project. When requesting Stark Ind’s project ID explicitly, PostgreSQL returns 0 rows instead of leaking data.

For monitoring data security and tracking tenant access in real time, setting up Building Centralized Log Management with Grafana Loki allows you to collect structured database log events and detect unauthorized access attempts across your stack. Additionally, if you run automated batch processing or tenant data cleanup jobs, learn How to Self Host Kestra on Cloud VPS to manage cron tasks that safely initialize tenant contexts before executing database workflows.

Getting Started

Ready to implement Row Level Security in your infrastructure? Secure your platform using reliable cloud infrastructure:

• Deploy high-performance database servers on a Hetzner VPS or DigitalOcean instance.
• Register production domain names and configure SSL certificates using Namecheap.
• Automate database maintenance workflows with n8n Cloud.

Outsource Your Automation

Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.