rls-reference

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, 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 skill is a pure reference consumed by the tenant-leak test authors and reviewers. Postgres is the primary engine; MySQL / MariaDB, CockroachDB, Vitess, and SQL Server are covered in references/other-engines.md. For the broader model context see the isolation-models reference in cross-tenant-data-leak-tests.

When to use

• Designing tenant isolation on a Postgres-backed pool / bridge model (for MySQL / MariaDB, CockroachDB, Vitess, or SQL Server see references/other-engines.md).
• Auditing existing RLS policies for correctness.
• Writing tests that verify RLS denies cross-tenant access (per cross-tenant-data-leak-tests).
• 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 references/tenant-context-patterns.md and confirm the app sets it per transaction (SET LOCAL) from an authenticated session, never from request input.
3. Write the tenant policy using references/create-policy-syntax.md and a shape from references/policy-and-bypass-patterns.md - 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: references/create-policy-syntax.md.

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: references/tenant-context-patterns.md.

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): references/policy-and-bypass-patterns.md.

Performance discipline

Per Supabase RLS performance docs:

Practice	Effect
Wrap auth function calls in SELECT	Postgres optimiser caches result per statement (initPlan); 94%+ improvement on large tables
Specify TO role_name	Avoids policy evaluation for unrelated roles
Index on policy-referenced columns	99%+ improvement for tenant_id-filtered queries
Include explicit filter in query	Helps query planner even when RLS adds implicit filter

Bad:

USING ( auth.uid() = user_id )

Good (initPlan-cached):

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

Anti-patterns

Anti-pattern	Why it fails	Fix
RLS enabled but no policy	Default-deny means no rows return - broken silently	Always create at least one policy after enabling
Policy on table without FORCE ROW LEVEL SECURITY	Table owner bypasses; app role often is the owner	Add FORCE ROW LEVEL SECURITY
current_user for tenant_id without per-tenant roles	Tenants share a role; no isolation	Use SET LOCAL or JWT claim
tenant_id from request header -> SET LOCAL	Spoofable; never derive from request input	Derive from authenticated session/JWT only
auth.uid() not wrapped in SELECT	Per-row evaluation - major perf hit at scale	(SELECT auth.uid()) for initPlan caching
No index on tenant_id	Sequential scan after policy filter	btree index on tenant_id always
Permissive policy + missing TO clause	Applies to all roles including superusers	Always specify TO app_user
Tests use superuser to verify policies	Bypasses RLS - test passes but prod leaks	Tests must connect as a non-superuser without BYPASSRLS
Trusting raw_user_meta_data in Supabase policies	User-modifiable per Supabase docs	Use raw_app_meta_data (server-set)
Single policy mixing tenant + per-row ACL with OR	Permissive OR widens access; one bad clause leaks tenant	Split into permissive (tenant) + restrictive (ACL)

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 cross-tenant-data-leak-tests 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.
• CREATE POLICY syntax: postgresql.org/docs/current/sql-createpolicy.html.
• Supabase RLS guide: supabase.com/docs/guides/database/postgres/row-level-security.
• references/create-policy-syntax.md - full CREATE POLICY grammar and clause semantics.
• references/tenant-context-patterns.md - four tenant context source patterns.
• references/policy-and-bypass-patterns.md - tenant policy shapes and RLS bypass rules.
• references/other-engines.md - 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: the isolation-models reference in cross-tenant-data-leak-tests.
• Consumed by: cross-tenant-data-leak-tests (planning section + runtime gate).