CockroachDB - native RLS

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).

CREATE POLICY syntax reference

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).

MySQL and MariaDB - view-based tenant isolation

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).

Row-level security on non-Postgres engines

Companion reference for rls-reference. The host skill 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

The host skill 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: the host rls-reference SKILL.md.
• Model context: the isolation-models reference in cross-tenant-data-leak-tests.
• Consumed by: cross-tenant-data-leak-tests (planning section + runtime gate).

Tenant policy patterns and RLS bypass

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).

SQL Server - CREATE SECURITY POLICY with predicate functions

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).

Tenant context patterns

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).

Vitess - keyspace sharding + vindexes

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).