cross-tenant-data-leak-tests

OWASP WSTG-ATHZ-02 scenarios

Three primary authorization-bypass scenarios apply to every pool/bridge surface:

Tenant-isolation-specific patterns

Source: OWASP WSTG-ATHZ-02 Testing for Bypassing Authorization Schema owasp.org/www-project-web-security-testing-guide/v42/4-Web_Application_Security_Testing/05-Authorization_Testing/02-Testing_for_Bypassing_Authorization_Schema (opens in new window).

CockroachDB supports native row-level security that closely mirrors Postgres RLS.

Enabling RLS

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Force table owners to obey policies too:
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY

CREATE POLICY policy_name ON table AS [PERMISSIVE | RESTRICTIVE] FOR [SELECT | INSERT | UPDATE | DELETE | ALL] TO role USING (condition) [WITH CHECK (condition)]. Per the CockroachDB RLS docs: USING filters rows on reads and updates; WITH CHECK validates writes and defaults to USING when omitted; permissive policies combine with OR, restrictive with AND; access is denied by default once RLS is enabled and no policy applies.

Tenant context via application_name

CockroachDB has no Postgres-style SET LOCAL + current_setting transaction variable. The canonical pattern reads the tenant from application_name, a session variable every client sets at connection open:

SET application_name = 'tenant:<uuid>';

CREATE POLICY tenant_isolation ON documents
    FOR ALL
    TO app_role
    USING (
        tenant_id = split_part(current_setting('application_name'), ':', 2)::uuid
    )
    WITH CHECK (
        tenant_id = split_part(current_setting('application_name'), ':', 2)::uuid
    );

Bypass risks

Per the CockroachDB RLS docs, these paths bypass RLS and need separate controls:

Source: CockroachDB RLS cockroachlabs.com/docs/stable/row-level-security.html (opens in new window).

Full CREATE POLICY grammar plus the clause semantics a tenant policy relies on.

Grammar

CREATE POLICY policy_name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO role_name [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ];

USING vs WITH CHECK

If only USING is specified, it implicitly applies to both.

Per Postgres docs, the canonical own-data UPDATE policy uses both:

CREATE POLICY user_policy ON users
    FOR UPDATE
    USING (user_name = current_user)
    WITH CHECK (
        user_name = current_user AND
        shell IN ('/bin/bash', '/bin/sh', '/bin/dash')
    );

USING ensures the user can only update their own row; WITH CHECK ensures they can't update the row into a state that violates other invariants.

Per-command policies

For SELECT/UPDATE divergence (a common tenant pattern: all users see all rows, but only modify their own):

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);

CREATE POLICY user_mod_policy ON users
    FOR UPDATE
    USING (user_name = current_user);

Permissive vs restrictive policies

Restrictive policies layer additional constraints on top of permissive ones. Per Postgres docs:

-- Permissive: anyone in role 'admin' or whose row matches
-- Restrictive: only allow when from local network
CREATE POLICY admin_local_only ON passwd
    AS RESTRICTIVE TO admin
    USING (pg_catalog.inet_client_addr() IS NULL);

TO role_name

If omitted, the policy applies to all users (TO PUBLIC). For tenant isolation, scope policies to the application role:

CREATE POLICY tenant_isolation ON documents
    TO app_user
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

References

• CREATE POLICY syntax: postgresql.org/docs/current/sql-createpolicy.html (opens in new window).

pytest - full horizontal-escalation battery

import pytest

class TestDocumentsTenantIsolation:
    """Per OWASP WSTG-ATHZ-02 - horizontal escalation battery."""

    def test_tenant_a_cannot_read_tenant_b_document(
        self, client, tenant_a_user, tenant_b_resource
    ):
        # Authenticate as tenant A user
        client.force_login(tenant_a_user)
        # Attempt to access tenant B's resource by ID
        response = client.get(f"/api/documents/{tenant_b_resource.id}/")
        assert response.status_code == 404, "Must return 404, not 403, to avoid existence disclosure"

    def test_tenant_a_cannot_list_tenant_b_documents(
        self, client, tenant_a_user, tenant_b_resource
    ):
        client.force_login(tenant_a_user)
        response = client.get("/api/documents/")
        assert response.status_code == 200
        ids = {d["id"] for d in response.json()["results"]}
        assert tenant_b_resource.id not in ids

    def test_tenant_id_in_body_is_ignored(
        self, client, tenant_a_user, tenant_b
    ):
        client.force_login(tenant_a_user)
        # Attempt to create a document for tenant B by spoofing the body
        response = client.post(
            "/api/documents/",
            data={"tenant_id": str(tenant_b.id), "body": "leak"}
        )
        # Must be rejected (400) or silently scoped to A (201 with A's tenant_id)
        if response.status_code == 201:
            doc = response.json()
            assert doc["tenant_id"] != str(tenant_b.id)

    def test_jwt_signed_for_a_rejected_on_b_endpoint(
        self, client, tenant_a_user, tenant_b_resource
    ):
        # Sign a JWT for tenant A user, use it on a B-scoped endpoint
        token = sign_jwt_for(tenant_a_user)
        response = client.get(
            f"/api/documents/{tenant_b_resource.id}/",
            HTTP_AUTHORIZATION=f"Bearer {token}"
        )
        assert response.status_code in (401, 404)

Postgres RLS-direct (language-agnostic)

For surfaces relying on RLS per row-level-security.md (opens in new window), also test at the DB layer:

-- Connect as app_user (not superuser, not table owner)
BEGIN;
SET LOCAL app.tenant_id = '<tenant_a_uuid>';
-- Insert a row for tenant A
INSERT INTO documents (tenant_id, body) VALUES (current_setting('app.tenant_id')::uuid, 'a-doc');

-- Switch to tenant B
SET LOCAL app.tenant_id = '<tenant_b_uuid>';
SELECT count(*) FROM documents;  -- expect 0 (tenant A's row invisible)

-- Cross-tenant INSERT attempt
INSERT INTO documents (tenant_id, body) VALUES ('<tenant_a_uuid>', 'leak');
-- Expect: ERROR: new row violates row-level security policy for table "documents"
ROLLBACK;

Companion reference for cross-tenant-data-leak-tests. The isolation model in use (silo / pool / bridge / vertically-partitioned) decides which test surfaces the planning section must cover.

Overview

Tenant isolation is the foundational concern of every B2B SaaS architecture: the AWS Well-Architected SaaS Lens calls it essential and treats crossing a tenant boundary as a significant, potentially unrecoverable event for a SaaS business. Isolation is a continuum, not a binary - Microsoft's Azure Architecture Center frames it as a spectrum from shared-nothing to everything-shared, with architectures often picking different points per tier (UI shared, app shared, data isolated). This is a pure reference consumed by the leak-test planning section and the tenant-leak critic; it executes nothing.

When to use

• Designing the tenant-isolation model for a new B2B SaaS product or feature.
• Auditing an existing model - does the testing surface match the declared isolation level?
• Choosing what to test: each model creates a distinct set of failure modes the test suite must cover.
• PR review of architecture changes that move components along the isolation continuum.

Tenant vs deployment

A tenant is a logical customer boundary; a deployment (also called a stamp or supertenant) is a physical set of infrastructure. One deployment can host many tenants (shared model), or each tenant can have its own deployment (silo). The tenant-to-deployment mapping is durable state: a routing table must exist somewhere so requests reach the right deployment.

The four canonical models

Per-model when-to-choose guidance, the sourced Microsoft framing, and each model's test surface are in models.md (opens in new window).

Isolation enforcement primitives

Tenant isolation is implemented by combining:

• Identity context - tenant_id in JWT claims (auth.jwt() in Supabase per supabase.com/docs/guides/database/postgres/row-level-security (opens in new window)) or AWS Cognito ID token; the source of truth for "who is this request for".
• Authorisation policy - Postgres Row-Level Security per row-level-security.md (opens in new window), AWS IAM dynamic policies generated per tenant, application-level authorisation middleware.
• Resource ABAC tags - tag each tenant resource with tenant-id=<x>, then enforce via IAM condition keys.
• Network segmentation - per-tenant VPCs / subnets / security groups (silo only).
• Encryption keys - per-tenant KMS keys (silo / bridge); useful for crypto-shredding on tenant offboarding.

Anti-patterns

Test surface

The required test categories per model, plus the per-tier isolation mapping, are in test-surfaces.md (opens in new window). The cross-tenant data leak suite is the universal floor: even silo deployments share some surface (account-management APIs, billing, identity providers) where pool-like leaks are possible.

Limitations

• No model is leak-proof by construction. Silo defends against most cross-tenant leaks but inherits leak risk in any shared management surface (admin UI, billing). RLS defends the DB but not application caches.
• Cost vs isolation is a real trade-off. Per Microsoft, if a single tenant requires a given infrastructure cost, 100 tenants in pure silo require roughly 100 times that cost.
• Compliance scope. Some regulators (e.g., FedRAMP High, certain healthcare regimes) effectively mandate silo for certain data classifications. Check counsel-of-record before assuming pool is acceptable.
• Azure subscription / AWS account limits. Shared infrastructure reaches account-level scale limits faster than silo.

References

• AWS Well-Architected SaaS Lens - Tenant Isolation: docs.aws.amazon.com/wellarchitected/latest/saas-lens/tenant-isolation.html (opens in new window).
• Microsoft Azure Architecture Center - Tenancy Models: learn.microsoft.com/en-us/azure/architecture/guide/multitenant/considerations/tenancy-models (opens in new window).
• Microsoft Deployment Stamps pattern (related): learn.microsoft.com/en-us/azure/architecture/patterns/deployment-stamp (opens in new window).
• Supabase RLS guide (consumed in row-level-security.md (opens in new window)): supabase.com/docs/guides/database/postgres/row-level-security (opens in new window).
• Per-model detail: models.md (opens in new window); test surface: test-surfaces.md (opens in new window).
• Consumed by: cross-tenant-data-leak-tests (host skill).

Naming across frameworks: Microsoft's automated-single-tenant / fully-multitenant / horizontally-partitioned / vertically-partitioned; AWS Well-Architected's silo / pool / bridge. Deployments are also called stamps or supertenants.

1. Automated single-tenant (silo / fully-isolated)

Microsoft's framing: deploying a dedicated set of infrastructure per tenant isolates each tenant's data and reduces the risk of accidental leakage.

When to choose: regulated industries with strong isolation mandates (healthcare HIPAA, financial services, government); a small number of high-value enterprise customers; per-tenant configuration is part of the value proposition.

Test surface: deployment automation (the Deployment Stamps pattern); cross-deployment operations like reporting; tenant-to-deployment routing.

2. Fully multitenant (pool / fully-shared)

Microsoft's risk framing: separate each tenant's data and don't leak across tenants; a large tenant running a heavy query or operation might affect other tenants.

When to choose: a large number of low-margin customers; high operational efficiency required; tenants accept shared infrastructure.

Test surface: cross-tenant data leak (the canonical risk), tenant_id propagation through every code path, noisy-neighbor behaviour, resource quotas per tenant.

3. Horizontally partitioned (bridge)

Microsoft's framing: a single application tier with an individual database per tenant, which mitigates the noisy-neighbor problem in the data tier.

When to choose: data isolation matters for compliance, but shared compute is acceptable; data-tier noisy neighbors are the dominant failure mode (heavy queries, large indexes).

Test surface: correct database routing per tenant; connection-pool exhaustion under tenant concurrency; cross-DB query attempts must fail.

4. Vertically partitioned

Microsoft's framing: a combination of single-tenant and multitenant deployments - most customers' data and application tiers on multitenant infrastructure, with single-tenant infrastructure for customers who require higher performance or data isolation. Includes geographic partitioning (one deployment per region, tenants mapped to the nearest region).

When to choose: the majority of customers fit the shared model, but a minority need silo (enterprise tier); geographic data-residency requirements.

Test surface: every test from the shared model plus every test from the silo model; tenant migration between tiers; pricing tier enforcement.

Source: Microsoft Azure Architecture Center - Tenancy Models learn.microsoft.com/en-us/azure/architecture/guide/multitenant/considerations/tenancy-models (opens in new window).

MySQL 8.x and MariaDB have no native row-level security statement. Isolation combines a view defined SQL SECURITY INVOKER with an application role that holds no direct grants on the base table.

Why INVOKER, not DEFINER

The CREATE VIEW default is SQL SECURITY DEFINER: the view runs with the creator's privileges, so any user with SELECT on the view reads every row the creator can read, including other tenants'. With SQL SECURITY INVOKER the view runs with the caller's privileges, so the view's WHERE clause becomes the tenant boundary. The only clauses material to isolation are SQL SECURITY { DEFINER | INVOKER } and WITH [CASCADED | LOCAL] CHECK OPTION; the MySQL 8.4 CREATE VIEW reference has the full grammar.

Canonical per-tenant view

CREATE VIEW tenant_docs AS
    SELECT *
    FROM documents
    WHERE tenant_id = /* app sets via session var or stored function */ ...
SQL SECURITY INVOKER
WITH CASCADED CHECK OPTION;

WITH CHECK OPTION rejects inserts or updates that would create rows outside the view's WHERE clause.

App-layer enforcement (required complement)

Views do not block TRUNCATE, constraint checks, or direct table access when the role holds table-level grants. The application must:

1. Connect with a role that has NO SELECT/INSERT/UPDATE/DELETE on the base table (only on the view).
2. Set the tenant identity before each query (session variable or a JWT-derived stored-function result).
3. Treat the view WHERE clause as the sole row gate and re-validate it in every schema migration.

MariaDB shares this model. Its DEFINER clause also accepts role | CURRENT_ROLE, which makes a schema-per-tenant view owned by a per-tenant role viable at low tenant counts. Atomic DDL for CREATE VIEW landed in MariaDB 10.6.1.

Bypass risks

Sources: MySQL 8.4 CREATE VIEW dev.mysql.com/doc/refman/8.4/en/create-view.html (opens in new window); MariaDB CREATE VIEW mariadb.com/kb/en/create-view/ (opens in new window).

Companion reference for row-level-security.md (opens in new window). That file covers Postgres RLS in full; this file covers the four non-Postgres engines that appear most in multi-tenant B2B SaaS stacks: MySQL / MariaDB, CockroachDB, Vitess, and SQL Server.

Overview

That file covers Postgres RLS in full. This reference covers the four non-Postgres engines that appear most in multi-tenant B2B SaaS stacks. For each engine the spine below gives the isolation primitive, the tenant-context essence, and a minimal isolation test; the full context SQL, vindex / VSchema detail, and the per-engine bypass table live in the linked per-engine reference file. For the broader model-selection context see the isolation-models reference in cross-tenant-data-leak-tests.

When to use

• Designing tenant isolation on a MySQL, MariaDB, CockroachDB, Vitess, or SQL Server backed service.
• Auditing an existing isolation scheme for bypass risks.
• Writing cross-tenant leak tests for any of these engines (companion to cross-tenant-data-leak-tests).
• Translating a Postgres RLS design to one of these engines.

Engine cheat sheet

MySQL / MariaDB - views + app layer

No native RLS. A view defined SQL SECURITY INVOKER runs with the caller's privileges, so its WHERE clause is the tenant boundary; the default SQL SECURITY DEFINER runs as the creator and leaks other tenants' rows. WITH CASCADED CHECK OPTION blocks writes that fall outside the view. Isolation holds only if the app role has no direct grants on the base table. MariaDB shares this model, with role-owned per-tenant views as an option at low tenant counts.

-- Role has SELECT on the view only (not the base table)
SET @tenant_id = 'tenant-A';
SELECT COUNT(*) FROM tenant_docs;                             -- tenant-A rows only
SELECT COUNT(*) FROM documents WHERE tenant_id = 'tenant-B';  -- must be denied
INSERT INTO tenant_docs (tenant_id, body) VALUES ('tenant-B', 'leak');  -- CHECK OPTION failed

Full view pattern, app-layer checklist, and bypass table: mysql-mariadb.md (opens in new window).

CockroachDB - native RLS

Native RLS mirroring Postgres. ALTER TABLE ... ENABLE ROW LEVEL SECURITY plus CREATE POLICY ... USING (...) WITH CHECK (...); access is denied by default once RLS is enabled and no policy applies. There is no Postgres SET LOCAL + current_setting transaction variable, so the tenant is read from the application_name session variable.

-- app_role: no BYPASSRLS, not table owner
SET application_name = 'tenant:11111111-1111-1111-1111-111111111111';
SELECT COUNT(*) FROM documents;                          -- tenant-1 rows only
INSERT INTO documents (tenant_id, body)
VALUES ('22222222-2222-2222-2222-222222222222', 'leak');  -- violates WITH CHECK
SET row_security = off;
SELECT * FROM documents;                                 -- errors if a policy would filter rows
RESET row_security;

Enable/force syntax, the application_name policy, and the bypass table (FK, TRUNCATE, CDC, backup, replication): cockroachdb.md (opens in new window).

Vitess - keyspace sharding + vindexes

No policy layer. A primary vindex on tenant_id routes each tenant's rows to a dedicated shard range; a query without a tenant_id predicate scatters to all shards and returns every tenant's rows, so the application must include tenant_id on every query. tenant_id is the sharding key and must be immutable - Vitess cannot move a row between shards on update.

-- Via VTGate: confirm the plan is a unique route, not a scatter
EXPLAIN SELECT COUNT(*) FROM documents WHERE tenant_id = 'tenant-A';
-- plan_type must be "EqualUnique", not "Scatter"
UPDATE documents SET tenant_id = 'tenant-B' WHERE id = 1;  -- must be rejected at app layer

Vindex types, the VSchema fragment, and the bypass table: vitess.md (opens in new window).

SQL Server - security policy + predicate function

Native RLS since SQL Server 2016. CREATE SECURITY POLICY adds a FILTER predicate (silently filters SELECT/UPDATE/DELETE) and/or a BLOCK predicate (blocks violating writes); the predicate is an inline table-valued function created WITH SCHEMABINDING. For shared pools the tenant ID is carried in SESSION_CONTEXT, set with @read_only = 1 so a reused connection cannot carry a prior tenant's context.

EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key = N'TenantId', @value = 1, @read_only = 1;
SELECT COUNT(*) FROM dbo.Documents;                          -- tenant-1 rows only
INSERT INTO dbo.Documents (TenantId, Body) VALUES (2, 'leak');  -- BLOCK predicate fires
EXEC sp_set_session_context @key = N'TenantId', @value = 2;     -- fails: key is read-only
REVERT;

Predicate function + policy, the USER_NAME() alternative, and the bypass table (CDC, Change Tracking, indexed views, FILESTREAM): sqlserver.md (opens in new window).

Anti-patterns (all engines)

Limitations

• MySQL / MariaDB: no database-enforced row gate equivalent to Postgres or SQL Server RLS; view isolation depends entirely on the app role lacking base-table privileges. A misconfigured ORM connection string that uses a privileged user bypasses all isolation.
• CockroachDB: CDC, backup, and replication bypass RLS by design; these operational paths require separate data-governance controls.
• Vitess: isolation is routing-based, not policy-based, and only as strong as the app's discipline in including tenant_id on every query. Vitess provides no mechanism to reject a scatter query at the database tier.
• SQL Server: Change Data Capture leaks filtered rows to db_owner and the CDC gating role, and Change Tracking leaks primary keys, regardless of active security policies.

References

• Per-engine detail: mysql-mariadb.md (opens in new window), cockroachdb.md (opens in new window), vitess.md (opens in new window), sqlserver.md (opens in new window).
• MySQL 8.4 CREATE VIEW: dev.mysql.com/doc/refman/8.4/en/create-view.html (opens in new window).
• MariaDB CREATE VIEW: mariadb.com/kb/en/create-view/ (opens in new window).
• CockroachDB RLS: cockroachlabs.com/docs/stable/row-level-security.html (opens in new window).
• Vitess Vindexes: vitess.io/docs/21.0/reference/features/vindexes/ (opens in new window).
• Vitess Keyspaces: vitess.io/docs/21.0/concepts/keyspace/ (opens in new window).
• SQL Server RLS: learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security (opens in new window).
• SQL Server CREATE SECURITY POLICY: learn.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql (opens in new window).
• Postgres counterpart: row-level-security.md (opens in new window).
• Model context: the isolation-models reference in cross-tenant-data-leak-tests.
• Consumed by: cross-tenant-data-leak-tests (planning section + runtime gate).

Canonical tenant-isolation policy shapes, plus the ways RLS can be bypassed that a tenant deployment must lock down.

Common policy patterns for tenant isolation

Pattern 1: Strict tenant_id discriminator

CREATE POLICY tenant_isolation ON documents
    AS PERMISSIVE
    FOR ALL
    TO app_user
    USING (tenant_id = current_setting('app.tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Both USING and WITH CHECK use the same expression - a tenant can't read OR insert rows for another tenant.

Pattern 2: Tenant + per-row ACL

CREATE POLICY tenant_isolation ON documents
    AS PERMISSIVE
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY owner_or_shared ON documents
    AS RESTRICTIVE
    USING (
        owner_id = current_setting('app.user_id')::uuid
        OR id IN (SELECT document_id FROM document_shares
                  WHERE shared_with = current_setting('app.user_id')::uuid)
    );

Permissive policy enforces tenant boundary; restrictive policy layers on per-row ACL.

Pattern 3: Admin override

CREATE POLICY tenant_isolation ON documents
    USING (
        tenant_id = current_setting('app.tenant_id')::uuid
        OR current_setting('app.is_global_admin', true)::boolean = true
    );

Global admin claim bypasses the tenant filter. Audit usage - admin connections must be logged and short-lived.

Bypassing RLS

Three categories of bypass per Postgres docs:

For production tenant tables, the application connection role must NOT be a superuser, NOT have BYPASSRLS, and must NOT own the table (or, if it owns the table, FORCE ROW LEVEL SECURITY must be set).

Detecting RLS-bypassed sessions

To prevent accidentally-bypassed query patterns from masquerading as policy-respecting:

SET row_security = off;
-- Subsequent queries error if a policy would have filtered

Useful in audit / backup contexts to catch unintended row exclusion.

Operations that always bypass RLS

Per Postgres docs, these operations are not subject to RLS:

• Foreign key constraint checks
• Unique constraint checks
• TRUNCATE
• REFERENCES privilege checks

This creates side-channel leaks: an attacker can probe for tenant-other rows via INSERT collisions on unique constraints, or via foreign-key reference patterns. Compensating controls:

• Use UUID primary keys (not sequential ints) to avoid enumeration via FK
• Treat unique-constraint timing differences as a side channel worth testing per cross-tenant-data-leak-tests

References

• Postgres Row-Level Security: postgresql.org/docs/current/ddl-rowsecurity.html (opens in new window).

Overview

Postgres Row-Level Security (RLS) lets the database itself enforce per-tenant row visibility, independent of the application code. It is the canonical defence-in-depth layer for pool / horizontally-partitioned tenancy models - even if the application forgets to add a WHERE tenant_id = ? filter, the database refuses to return another tenant's rows.

Per postgresql.org/docs/current/ddl-rowsecurity.html (opens in new window), RLS "restricts which rows users can access based on per-user policies." Unlike standard SQL GRANT / REVOKE privileges which act on whole tables, RLS controls row visibility and modification per role.

This reference is used by tenant-leak test authors and reviewers. Postgres is the primary engine; MySQL / MariaDB, CockroachDB, Vitess, and SQL Server are covered in other-engines.md (opens in new window). For the broader model context see isolation-models.md (opens in new window).

When to read this

• Designing tenant isolation on a Postgres-backed pool / bridge model (for MySQL / MariaDB, CockroachDB, Vitess, or SQL Server see other-engines.md (opens in new window)).
• Auditing existing RLS policies for correctness.
• Writing tests that verify RLS denies cross-tenant access (per the main skill).
• Onboarding a new table to the tenant_id discriminator pattern.

How to use

1. Enable AND force RLS on every tenant-bearing table (ENABLE then FORCE ROW LEVEL SECURITY) so the table owner obeys policies too.
2. Pick a tenant context source from tenant-context-patterns.md (opens in new window) and confirm the app sets it per transaction (SET LOCAL) from an authenticated session, never from request input.
3. Write the tenant policy using create-policy-syntax.md (opens in new window) and a shape from policy-and-bypass-patterns.md (opens in new window) - scope USING + WITH CHECK to the tenant discriminator.
4. Confirm the app connection role is not a superuser, lacks BYPASSRLS, and either does not own the table or runs with FORCE ROW LEVEL SECURITY.
5. Apply the performance discipline below: wrap auth functions in SELECT and index every policy-referenced column.
6. Run the statement-level and application-level tests as a non-privileged role; assert cross-tenant reads and writes fail.
7. Cross-check the policy against the Anti-patterns table before sign-off.

Enabling RLS

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

Per Postgres docs: "Once enabled, a default-deny policy applies - no rows are visible or modifiable unless explicitly allowed by a policy."

To disable:

ALTER TABLE accounts DISABLE ROW LEVEL SECURITY;

Force RLS on table owner

By default, the table owner bypasses RLS. For tenant isolation this is dangerous - the application's connecting role is often the table owner. Force the owner to obey policies too:

ALTER TABLE accounts FORCE ROW LEVEL SECURITY;

Per Postgres docs, this is the production-safe default for multi-tenant tables.

CREATE POLICY syntax

The full grammar plus USING vs WITH CHECK, per-command policies, permissive vs restrictive combination, and TO role_name scoping: create-policy-syntax.md (opens in new window).

Tenant context patterns

The policy needs a source of truth for the current tenant. Four canonical patterns - current_setting() with SET LOCAL, current_user / session_user, Supabase auth.uid() / auth.jwt(), and in-policy JWT claim parsing: tenant-context-patterns.md (opens in new window).

Policy patterns and bypass

The three tenant-isolation policy shapes (strict discriminator, tenant + per-row ACL, admin override), the three RLS bypass categories (superuser / BYPASSRLS / table owner), and the operations that always bypass RLS (FK, unique, TRUNCATE, REFERENCES): policy-and-bypass-patterns.md (opens in new window).

Performance discipline

Per Supabase RLS performance docs:

Bad:

USING ( auth.uid() = user_id )

Good (initPlan-cached):

USING ( (SELECT auth.uid()) = user_id )

Anti-patterns

Testing RLS policies

Two levels:

Statement-level

-- Connect as app_user (no BYPASSRLS, not table owner)
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT count(*) FROM documents;  -- should match tenant 1's count only

SET LOCAL app.tenant_id = '22222222-2222-2222-2222-222222222222';
SELECT count(*) FROM documents;  -- should match tenant 2's count

-- Attempt cross-tenant
SET LOCAL app.tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO documents (tenant_id, body)
VALUES ('22222222-2222-2222-2222-222222222222', 'leak');
-- Must fail with: new row violates row-level security policy

Application-level

Run the existing test suite under a non-superuser, non-BYPASSRLS, non-table-owner role. If passing tests rely on RLS bypass, the suite is silently invalid for production. Per the main skill, the gate test must use a tenant-A session to attempt access to tenant-B-owned rows and assert 0 rows returned.

Worked example

Onboard a documents table to the strict tenant_id discriminator (Pattern 1). Enable and force RLS, then create the policy scoped to app_user:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
    AS PERMISSIVE FOR ALL TO app_user
    USING (tenant_id = current_setting('app.tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

The application runs SET LOCAL app.tenant_id = '<uuid>' from the authenticated session at the start of every transaction. Connected as app_user (no BYPASSRLS, not the table owner), the tester sets tenant 1 and runs SELECT count(*) FROM documents - it returns tenant 1's rows only. Setting tenant 1 and attempting INSERT INTO documents (tenant_id, body) VALUES ('<tenant-2-uuid>', 'leak') fails with new row violates row-level security policy, because WITH CHECK rejects the foreign tenant_id. The table is isolated for both reads and writes.

Limitations

• Constraint side channels. FK and UNIQUE checks bypass RLS; this creates timing/error-message side channels.
• RLS does not protect logs. A query that filters to 0 rows still appears in pg_stat_statements and slow-query logs with the same SQL text. Log sanitisation is a separate concern.
• No RLS on views by default. A view that does SELECT * FROM tenants doesn't inherit RLS unless WITH (security_invoker = true) is set on the view (Postgres 15+).
• Replication. Logical replication does not respect RLS by default; the subscriber sees all rows. Per-publication filters needed.
• Schema migrations. ALTER TABLE for new columns runs as table owner - review whether new columns need to be added to policies.

References

• Postgres Row-Level Security: postgresql.org/docs/current/ddl-rowsecurity.html (opens in new window).
• CREATE POLICY syntax: postgresql.org/docs/current/sql-createpolicy.html (opens in new window).
• Supabase RLS guide: supabase.com/docs/guides/database/postgres/row-level-security (opens in new window).
• create-policy-syntax.md (opens in new window) - full CREATE POLICY grammar and clause semantics.
• tenant-context-patterns.md (opens in new window) - four tenant context source patterns.
• policy-and-bypass-patterns.md (opens in new window) - tenant policy shapes and RLS bypass rules.
• other-engines.md (opens in new window) - row/tenant isolation on MySQL / MariaDB, CockroachDB, Vitess, and SQL Server (per-engine detail in mysql-mariadb.md, cockroachdb.md, vitess.md, sqlserver.md).
• Tenant isolation context: isolation-models.md (opens in new window).
• Used by the main skill's planning section and runtime gate.

SQL Server has native RLS since SQL Server 2016 (13.x), also available on Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric Warehouse.

Predicate types

The predicate is an inline table-valued function created WITH SCHEMABINDING. SCHEMABINDING = ON (the default) means users querying the target table need no permission on the predicate function or its helper tables. The clauses material to isolation are ADD [FILTER | BLOCK] PREDICATE tvf(cols) ON table and WITH (STATE = ON); the CREATE SECURITY POLICY reference has the full grammar.

Tenant context via SESSION_CONTEXT

For shared connection pools, carry the tenant ID per connection with SESSION_CONTEXT():

CREATE FUNCTION Security.fn_tenant_predicate(@TenantId int)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS result
    WHERE CAST(SESSION_CONTEXT(N'TenantId') AS int) = @TenantId;
GO

CREATE SECURITY POLICY Security.TenantFilter
    ADD FILTER PREDICATE Security.fn_tenant_predicate(TenantId) ON dbo.Documents,
    ADD BLOCK PREDICATE Security.fn_tenant_predicate(TenantId) ON dbo.Documents AFTER INSERT
    WITH (STATE = ON);
GO

EXEC sp_set_session_context @key = N'TenantId', @value = 42, @read_only = 1;

@read_only = 1 locks the SESSION_CONTEXT value until the connection returns to the pool - critical for pool reuse safety.

Alternative tenant context - USER_NAME()

For low-tenant-count deployments where each tenant maps to a SQL login:

CREATE FUNCTION Security.tvf_securitypredicate(@TenantRep AS nvarchar(50))
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS result
    WHERE @TenantRep = USER_NAME() OR USER_NAME() = 'GlobalAdmin';
GO

Bypass risks

Sources: SQL Server RLS learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security (opens in new window); CREATE SECURITY POLICY learn.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql (opens in new window).

The policy needs a source of truth for the current tenant. Four canonical patterns.

1. current_setting() with SET LOCAL

Set the tenant at session / transaction start, read it in the policy:

-- Application code (every transaction):
SET LOCAL app.tenant_id = '<uuid>';

-- Policy:
CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

SET LOCAL confines the value to the current transaction - critical when the connection is from a shared connection pool.

2. current_user / session_user

Map each tenant to a Postgres role. Suitable for low-tenant-count deployments (silo-leaning):

CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = (SELECT id FROM tenants WHERE name = current_user));

3. Supabase auth.uid() / auth.jwt()

For Supabase-backed apps, JWT claims are exposed through helper functions. Per supabase.com/docs/guides/database/postgres/row-level-security (opens in new window):

CREATE POLICY "User can see their own profile only."
ON profiles FOR SELECT
USING ( (SELECT auth.uid()) = user_id );

Organisation / team membership via app_metadata:

CREATE POLICY "User is in team"
ON my_table TO authenticated
USING ( team_id IN (SELECT auth.jwt() -> 'app_metadata' -> 'teams') );

Critical: Per Supabase docs, never trust raw_user_meta_data for authorisation - it's user-modifiable. Use raw_app_meta_data (server-set) or a separate server-side claim store.

4. JWT claim parsing in policy

For self-rolled auth, parse the JWT or token directly:

CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = (current_setting('request.jwt.claims', true)::jsonb->>'tenant_id')::uuid);

The true argument to current_setting makes it return NULL if the setting is absent (safer than erroring).

References

• Supabase RLS guide: supabase.com/docs/guides/database/postgres/row-level-security (opens in new window).

Isolation tier mapping

A common pattern is independent isolation per architecture tier:

The test surface depends on the lowest isolation level in the stack. A fully isolated UI but shared database still requires the full cross-tenant data-leak test battery against the database.

Required test categories per model

The cross-tenant data leak suite is the universal floor: even silo deployments share some surface (account-management APIs, billing, identity providers) where pool-like leaks are possible.

Vitess has no row-level security policy layer. Tenant isolation happens at the routing tier: a tenant's rows sit on a dedicated shard (or shard range), and vindexes route queries so cross-tenant access never reaches the wrong MySQL shard. A keyspace is a logical database; when sharded it maps to multiple MySQL databases across shards, and a vindex maps a column value to a keyspace ID.

Vindexes for tenant isolation

A primary vindex on tenant_id maps each tenant to a keyspace-ID range; Vitess routes every query carrying a tenant_id condition to the owning shard(s).

Example VSchema fragment:

{
  "sharded": true,
  "vindexes": { "tenant_hash": { "type": "xxhash" } },
  "tables": {
    "documents": {
      "columnVindexes": [ { "column": "tenant_id", "name": "tenant_hash" } ]
    }
  }
}

Any query without a tenant_id condition scatters to all shards. Isolation therefore depends on every query carrying a tenant_id predicate - an application-layer responsibility.

Bypass risks

Sources: Vitess Vindexes vitess.io/docs/21.0/reference/features/vindexes/ (opens in new window); Vitess Keyspaces vitess.io/docs/21.0/concepts/keyspace/ (opens in new window).