PostgreSQL Row-Level Security: Threat Modeling Multi-Tenant Bypasses and Optimizer Leaks

Technical Overview & Threat Model#

Enforcing multi-tenant data isolation inside application code introduces significant operational risk. When developers rely solely on application-level WHERE tenant_id = $1 filters across hundreds of SQL queries or ORM models, a single missed clause leads directly to cross-tenant data exposure. PostgreSQL Row-Level Security (RLS) offers an architectural alternative by pushing isolation down to the database engine itself.

When RLS is active on a table, PostgreSQL rewrites incoming queries at parse and rewrite time, wrapping user queries in a security barrier filter. However, treating RLS as an impenetrable perimeter without understanding its internal mechanics creates dangerous blind spots.

sequenceDiagram
    autonumber
    participant App as Web Application Worker
    participant Pooler as Connection Pooler (PgBouncer)
    participant Engine as PostgreSQL Query Engine
    participant Table as Multi-Tenant Table (RLS)

    App->>Pooler: Acquire Connection from Pool
    App->>Pooler: SET app.current_tenant = 'tenant_a'
    App->>Engine: SELECT * FROM documents WHERE title ILIKE '%budget%'
    Engine->>Table: Apply Security Barrier (tenant_id = 'tenant_a')
    Table-->>App: Return Tenant A Records Only
    Note over App,Pooler: Connection returned to pool without RESET
    App->>Pooler: Next Request (Tenant B) reuses un-cleared connection
    App->>Engine: SELECT * FROM documents (Session still retains 'tenant_a')
    Engine->>Table: Apply Stale Security Barrier (tenant_id = 'tenant_a')
    Table-->>App: Cross-Tenant Data Leaked to Tenant B

The threat model encompasses four primary failure modes:

Table Owner Privilege Escalation: By default, table owners and superusers completely bypass RLS policies unless strict inheritance constraints are forced.
Side-Channel Information Leakage via Non-Leakproof Functions: The PostgreSQL query optimizer prioritizes execution speed. Under specific conditions, user-supplied functions can execute against rows before RLS filters discard them.
Session State Contamination in Transaction Pools: Multi-tenant SaaS architectures sharing connection pools (such as PgBouncer in transaction mode) risk inheriting stale tenant identifiers across client requests.
Security Definer View Blindness: Standard relational views execute with the permissions of the view creator rather than the querying caller, silently neutralizing row policies.

Query Optimizer Evaluation Order & Non-Leakproof Functions#

A common misconception is that PostgreSQL applies RLS policies before evaluating any conditions in a user query's WHERE clause. In reality, the query planner assesses operator selectivity and cost to generate an optimal execution plan.

flowchart TD
    subgraph UserQuery [Client Query Execution]
        RawQuery["SELECT * FROM invoices WHERE check_flag(secret_token)"]
    end

    subgraph PlannerDecision [Planner Optimization Path]
        IsLeakproof{"Is check_flag() marked LEAKPROOF?"}
        PushDown["Push function down before Security Barrier (Risk)"]
        HoldBarrier["Enforce Security Barrier before function (Safe)"]
    end

    subgraph ExecutionEngine [Data Storage Engine]
        TableScan["Physical Table Scan (All Tenant Rows)"]
        RLSFilter["RLS Policy Check: tenant_id = current_setting()"]
        UserFunc["User-Defined Function: check_flag()"]
    end

    RawQuery --> TableScan
    TableScan --> IsLeakproof
    IsLeakproof -->|Yes - Trusted System Function| PushDown
    IsLeakproof -->|No - Non-Leakproof User Function| HoldBarrier
    HoldBarrier --> RLSFilter
    RLSFilter -->|Filtered Rows Only| UserFunc

PostgreSQL addresses this via the concept of LEAKPROOF functions. A function is only considered leakproof if it contains no side channels, raises no data-dependent exceptions, and leaks no information through runtime timing.

If a function is not leakproof, the optimizer must hold it behind the security barrier. However, an attacker who can define functions or leverage built-in error-raising functions can craft expressions that trigger exceptions conditionally based on whether a specific row value exists:

SQL

-- Conceptual side-channel exploit query:
-- If the optimizer evaluates the division before RLS filtering,
-- a division-by-zero error confirms that another tenant has salary > 200000.
SELECT id FROM employees 
WHERE 1 / (CASE WHEN salary > 200000 THEN 0 ELSE 1 END) = 1;

PostgreSQL implements strict security barrier qualifications for RLS tables to prevent operators from running before the policy filter. However, third-party extensions, custom operators, and views lacking the security_barrier attribute can still expose data through planner shortcuts.

Comparison of Multi-Tenant PostgreSQL Isolation Models#

Choosing where and how to enforce tenancy requires balancing development overhead, query performance, and the blast radius of misconfigurations:

Isolation Strategy	Enforcement Layer	Blast Radius of App Bug	Connection Pool Overhead	Performance Characteristics
App-Level Filtering (`WHERE tenant_id = $1`)	ORM / Query Layer	Severe (single omitted clause leaks all records)	Lowest (standard shared pool)	Fast index lookups; zero database overhead
Schema-per-Tenant (`search_path`)	PostgreSQL Namespaces	Moderate (accidental schema leakage via search_path)	High (schema migration overhead at scale)	Excellent isolation; heavy resource consumption at 1,000+ tenants
Standard RLS (`ENABLE ROW LEVEL SECURITY`)	Query Rewriter	High if connection uses table owner role	Low (single shared schema)	Minimal overhead on simple queries; planner joins on complex policies
Hardened RLS (`FORCE RLS` + `SET LOCAL`)	Engine + Session Scope	Low (engine strictly rejects cross-tenant access)	Low (requires transaction-scoped session configuration)	Fast with compound indexing on `(tenant_id, id)`

Hands-on Implementation: Building a Hardened RLS Schema#

The following SQL configuration establishes an isolated multi-tenant table, disables owner bypasses, binds runtime context strictly to the active transaction, and enforces a SECURITY INVOKER view:

SQL

-- 1. Create unprivileged application runtime role
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'app_user') THEN
        CREATE ROLE app_user WITH LOGIN PASSWORD 'StrictPassword2026!';
    END IF;
END
$$;

-- 2. Define multi-tenant schema
CREATE TABLE IF NOT EXISTS customer_invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    invoice_number TEXT NOT NULL,
    amount_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Crucial compound index for RLS lookup efficiency
CREATE INDEX IF NOT EXISTS idx_invoices_tenant_id 
ON customer_invoices (tenant_id, id);

-- 3. Enable Row-Level Security
ALTER TABLE customer_invoices ENABLE ROW LEVEL SECURITY;

-- MANDATORY DEFENSE: Prevent table owner from silently bypassing RLS
ALTER TABLE customer_invoices FORCE ROW LEVEL SECURITY;

-- 4. Create Tenant Isolation Policy using session configuration
DROP POLICY IF EXISTS tenant_isolation_policy ON customer_invoices;
CREATE POLICY tenant_isolation_policy ON customer_invoices
    AS RESTRICTIVE
    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);

-- 5. Grant minimal operational privileges to application role
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_invoices TO app_user;

-- 6. Create secure view with explicit security_invoker attribute
CREATE OR REPLACE VIEW active_invoices_view
WITH (security_invoker = true) AS
SELECT id, tenant_id, invoice_number, amount_cents, created_at
FROM customer_invoices
WHERE amount_cents > 0;

Application Connection Pattern#

When executing queries from application services, always set session parameters with transaction-level scope using SET LOCAL. This guarantees that when the connection returns to a pooler like PgBouncer, the tenant context terminates immediately upon COMMITorROLLBACK:

SQL

-- Run inside an explicit database transaction
BEGIN;

-- Scope tenant setting strictly to this single transaction
SET LOCAL app.current_tenant_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- Application query: Engine transparently filters to matching tenant
SELECT id, invoice_number, amount_cents 
FROM customer_invoices;

COMMIT;
-- Session variable app.current_tenant_id is now automatically cleared

Operational Takeaways & Hardening Checklist#

Deploying RLS in production environments requires specific verification steps:

Always Specify FORCE ROW LEVEL SECURITY: Without FORCE, migrations, background scripts, or ORM daemons connecting as the table owner silently read and write all tenant rows.
Never Mark Custom Functions LEAKPROOF Lightly: Tagging a function as LEAKPROOF informs the query planner that it may run before RLS checks. Only functions that cannot throw data-dependent errors and do not log input arguments should ever receive this attribute.
Compound Indexing on Policy Columns: Every table protected by RLS must index (tenant_id, ...) as the leading key. Without this, every query degenerates into a sequential scan wrapped in policy evaluations.
Audit Active Roles for BYPASSRLS: Regularly query SELECT rolname FROM pg_roles WHERE rolbypassrls = true; to ensure service accounts maintain least privilege.