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

Technical Overview & Threat Model#

Understanding how attackers bypass PostgreSQL Row-Level Security (RLS) policies through security definer functions and view misconfigurations is essential for securing multi-tenant architectures and database isolation layers. While PostgreSQL provides enterprise-grade row filtering via ENABLE ROW LEVEL SECURITY, subtle flaws in query optimization, non-leakproof operator execution, and view security barriers frequently expose cross-tenant data to unauthorized queries.

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:

1. Table Owner Privilege Escalation: By default, table owners and superusers completely bypass RLS policies unless strict inheritance constraints are forced.
2. 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.
3. 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.
4. Security Definer View Blindness: Standard relational views execute with the permissions of the view creator rather than the querying caller, silently neutralizing row policies.

---

Common Postgres RLS Bypass Scenarios: Views Without Security Barrier#

A primary architectural vector for bypassing PostgreSQL RLS occurs when relational views are created on top of RLS-protected tables without the security_barrier = true attribute. By default, PostgreSQL views permit the cost-based query optimizer to reorder user-supplied WHERE clauses and push functions ahead of the view's qualification filters to optimize execution plans.

When an attacker submits a query containing an intentional error or a side-channel timing function (such as a custom function or non-leakproof operator), the optimizer may execute the user function against unauthorized rows before the RLS security policy evaluates. Setting WITH (security_barrier = true) enforces strict evaluation fencing, guaranteeing that row filtering occurs before any caller predicates are processed.

---

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 COMMIT or ROLLBACK:

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.

Frequently Asked Questions: Postgres RLS Security#

Can superusers bypass Postgres RLS?

Yes. By default, PostgreSQL database superusers (e.g., postgres) and roles with the BYPASSRLS attribute completely ignore all row-level security policies unless FORCE ROW LEVEL SECURITY is explicitly enabled on the table. However, even with FORCE ROW LEVEL SECURITY, superusers retain schema-level manipulation privileges that allow altering or disabling policies. Multi-tenant application connections should always connect using a dedicated, non-superuser role without the BYPASSRLS attribute.

How to test for RLS policy leaks?

To audit and test for PostgreSQL RLS policy leaks:

1. Impersonate Application Tenants: Test queries using SET ROLE tenant_role; SET request.jwt.claim.tenant_id = 'victim-tenant-id'; to verify zero cross-tenant row returns across SELECT, UPDATE, and DELETE.
2. Inspect Optimizer Execution Plans: Run EXPLAIN (ANALYZE, BUFFERS) on views and join queries to verify that security barrier qualifiers are evaluated before user-defined where clauses.
3. Audit Function Leakproof Flags: Query pg_proc WHERE proname = 'func_name' AND NOT proleakproof to confirm that functions used inside RLS policy expressions are marked LEAKPROOF (and that unprivileged users cannot define leakproof functions).
4. Verify View Security Barriers: Inspect pg_views and pg_class to confirm all views abstracting multi-tenant tables declare reloptions = {'security_barrier=true'}.

Database Hardening Defense: For enterprise connection pooling patterns, session variable isolation, and leakproof function audits, check our full guide on multi-tenant isolation architectures in our Database Security & Hardening Archive.

Authoritative Technical References#

• PostgreSQL Official Documentation: Row Security Policies
• PostgreSQL Documentation: CREATE FUNCTION and LEAKPROOF Attribute
• AWS Prescriptive Guidance: Multi-Tenant Data Isolation with PostgreSQL RLS
• MITRE CWE-200: Exposure of Sensitive Information to an Unauthorized Actor