PostgreSQL Row-Level Security: Multi-Tenant Access Control

Key Insights

PostgreSQL Row-Level Security enforces tenant isolation at the database layer, eliminating entire classes of data leakage bugs that plague application-level filtering approaches.
RLS policies use session variables or JWT claims to automatically filter queries based on tenant context, making multi-tenancy transparent to application code once properly configured.
Proper indexing on tenant_id columns and careful policy design are critical—poorly implemented RLS can create performance bottlenecks and security gaps that defeat its purpose.

Introduction to Multi-Tenancy Challenges

Multi-tenant applications face a fundamental security challenge: how do you safely share database tables across multiple customers while guaranteeing data isolation? The traditional approach involves adding WHERE tenant_id = ? clauses to every query in your application code. This works until it doesn’t—and when it fails, the consequences are catastrophic.

A single forgotten WHERE clause, a copy-pasted query missing the filter, or a junior developer’s oversight can expose Customer A’s data to Customer B. These aren’t theoretical risks. Data breaches from missing tenant filters have affected major SaaS companies, resulting in regulatory fines, customer churn, and reputational damage.

Application-level filtering is fundamentally flawed because it relies on developers never making mistakes. PostgreSQL Row-Level Security (RLS) takes a different approach: enforce tenant isolation at the database level, making it impossible to accidentally query another tenant’s data regardless of how your application code is written.

PostgreSQL Row-Level Security Fundamentals

Row-Level Security is a PostgreSQL feature that allows you to define policies controlling which rows users can access. When RLS is enabled on a table, all queries—SELECT, INSERT, UPDATE, and DELETE—are automatically filtered according to your policies.

Here’s the critical insight: RLS policies are enforced by PostgreSQL itself, not your application. Even if your application code is compromised or buggy, the database won’t return rows that violate your policies.

Enabling RLS is straightforward:

-- Create a sample multi-tenant table
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

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

-- Create a basic policy
CREATE POLICY tenant_isolation ON documents
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

This policy applies to all operations (SELECT, INSERT, UPDATE, DELETE) and only allows access to rows where tenant_id matches a session variable we’ll set from our application.

PostgreSQL supports two policy types: permissive (default) and restrictive. Permissive policies use OR logic—a row is visible if it matches any permissive policy. Restrictive policies use AND logic—a row must match all restrictive policies. For most multi-tenant scenarios, permissive policies are sufficient.

Implementing Tenant Isolation with RLS

The foundation of RLS-based multi-tenancy is storing the current tenant context in a session variable. PostgreSQL’s current_setting() function retrieves these variables, which your policies can reference.

Here’s a complete schema setup:

-- Users table with tenant association
CREATE TABLE tenants (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'member',
    created_at TIMESTAMP DEFAULT NOW()
);

-- Multi-tenant data table
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL REFERENCES tenants(id),
    owner_id INTEGER NOT NULL REFERENCES users(id),
    name TEXT NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Set tenant context (called by application)
CREATE OR REPLACE FUNCTION set_tenant_context(tenant_id INTEGER)
RETURNS VOID AS $$
BEGIN
    PERFORM set_config('app.current_tenant_id', tenant_id::TEXT, false);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Now create policies that use this context:

-- Users can only see other users in their tenant
CREATE POLICY tenant_users ON users
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

-- Projects are isolated by tenant
CREATE POLICY tenant_projects ON projects
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

The third parameter in set_config() determines scope: false means the setting persists for the entire session, true means it only lasts for the current transaction.

Advanced Policy Patterns

Real-world applications need more sophisticated policies. You might need role-based access, ownership checks, or special admin privileges.

Here’s a policy combining tenant isolation with ownership:

-- Users can SELECT all projects in their tenant
CREATE POLICY tenant_projects_select ON projects
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

-- Users can only UPDATE/DELETE projects they own
CREATE POLICY tenant_projects_modify ON projects
    FOR UPDATE
    USING (
        tenant_id = current_setting('app.current_tenant_id')::INTEGER
        AND owner_id = current_setting('app.current_user_id')::INTEGER
    );

CREATE POLICY tenant_projects_delete ON projects
    FOR DELETE
    USING (
        tenant_id = current_setting('app.current_tenant_id')::INTEGER
        AND owner_id = current_setting('app.current_user_id')::INTEGER
    );

For INSERT operations, use WITH CHECK to validate new rows:

CREATE POLICY tenant_projects_insert ON projects
    FOR INSERT
    WITH CHECK (
        tenant_id = current_setting('app.current_tenant_id')::INTEGER
        AND owner_id = current_setting('app.current_user_id')::INTEGER
    );

Super-admin access requires a bypass mechanism:

-- Create an admin role
CREATE ROLE app_admin;

-- Admins bypass RLS entirely
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- Create a policy for admins
CREATE POLICY admin_all_access ON projects
    FOR ALL
    TO app_admin
    USING (true);

The FORCE ROW LEVEL SECURITY clause ensures RLS applies even to table owners, preventing accidental admin queries from bypassing security.

Integration with Application Code

Setting tenant context from your application is critical. Here’s how to do it safely in Node.js with PostgreSQL:

// middleware/tenantContext.js
const setTenantContext = async (client, tenantId, userId) => {
    await client.query('SELECT set_config($1, $2, false)', [
        'app.current_tenant_id',
        tenantId.toString()
    ]);
    await client.query('SELECT set_config($1, $2, false)', [
        'app.current_user_id',
        userId.toString()
    ]);
};

// Express middleware
app.use(async (req, res, next) => {
    // Assume authentication middleware has set req.user
    if (req.user) {
        const client = await pool.connect();
        try {
            await setTenantContext(client, req.user.tenantId, req.user.id);
            req.dbClient = client;
            next();
        } catch (err) {
            client.release();
            next(err);
        }
    } else {
        next();
    }
});

// Cleanup middleware
app.use((req, res, next) => {
    res.on('finish', () => {
        if (req.dbClient) {
            req.dbClient.release();
        }
    });
    next();
});

Python with SQLAlchemy:

from sqlalchemy import event, text
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def set_tenant_context(dbapi_conn, connection_record):
    # Get tenant context from thread-local storage or request context
    tenant_id = get_current_tenant_id()
    user_id = get_current_user_id()
    
    cursor = dbapi_conn.cursor()
    cursor.execute(
        "SELECT set_config('app.current_tenant_id', %s, false)",
        (str(tenant_id),)
    )
    cursor.execute(
        "SELECT set_config('app.current_user_id', %s, false)",
        (str(user_id),)
    )
    cursor.close()

Critical consideration: Connection pooling can reuse connections with stale tenant context. Always set context at the start of each request or use transaction-scoped settings.

Performance Considerations and Testing

RLS policies add WHERE clauses to your queries. Without proper indexing, this kills performance:

-- Essential index for tenant filtering
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);

-- Composite index for ownership queries
CREATE INDEX idx_projects_tenant_owner ON projects(tenant_id, owner_id);

Analyze query plans to verify index usage:

SET app.current_tenant_id = '1';

EXPLAIN ANALYZE
SELECT * FROM projects WHERE name LIKE 'Test%';

You should see an Index Scan on idx_projects_tenant_id, not a Sequential Scan.

Testing RLS enforcement is non-negotiable:

-- Test isolation between tenants
BEGIN;
SET app.current_tenant_id = '1';
INSERT INTO projects (tenant_id, owner_id, name) 
VALUES (1, 1, 'Tenant 1 Project');

SET app.current_tenant_id = '2';
-- This should return 0 rows
SELECT COUNT(*) FROM projects WHERE name = 'Tenant 1 Project';
ROLLBACK;

-- Test missing context (should error or return no rows)
BEGIN;
RESET app.current_tenant_id;
SELECT * FROM projects; -- Should fail or return empty
ROLLBACK;

Common Pitfalls and Best Practices

Forgetting to set tenant context is the most common mistake. Always validate context is set:

-- Add a check function
CREATE OR REPLACE FUNCTION require_tenant_context()
RETURNS BOOLEAN AS $$
BEGIN
    IF current_setting('app.current_tenant_id', true) IS NULL THEN
        RAISE EXCEPTION 'Tenant context not set';
    END IF;
    RETURN true;
END;
$$ LANGUAGE plpgsql;

-- Use in policies
CREATE POLICY tenant_projects_safe ON projects
    FOR ALL
    USING (
        require_tenant_context()
        AND tenant_id = current_setting('app.current_tenant_id')::INTEGER
    );

Security checklist:

Enable FORCE ROW LEVEL SECURITY on all multi-tenant tables
Never grant table ownership to application roles
Test policies with multiple tenant contexts
Index all tenant_id columns
Log policy violations for security monitoring
Use transaction-scoped context for connection pools
Audit admin bypass mechanisms regularly

Migration strategy: Enable RLS on existing tables during low-traffic windows. Test thoroughly in staging with production data volumes. Consider enabling RLS in permissive mode first (allowing all access) before adding restrictive policies.

PostgreSQL Row-Level Security transforms multi-tenant data isolation from a developer responsibility into a database guarantee. When implemented correctly, it eliminates entire categories of security vulnerabilities while simplifying application code. The upfront investment in proper RLS configuration pays dividends in security, reliability, and peace of mind.