Row-Level Security for Multi-Tenancy

PostgreSQL row-level security filters and checks rows according to policies for the active role and command. It can provide defense in depth for shared-table tenancy, but table owners, superusers, BYPASSRLS roles, unsafe definer functions, missing tenant context, connection-pool leakage, and incomplete policies can defeat the intended boundary.

What will you be able to do?

Enable RLS and distinguish USING from WITH CHECK.
Pass transaction-local tenant context.
Test owner/bypass behavior and FORCE ROW LEVEL SECURITY.
Apply policies consistently to retrieval and writes.

Prerequisites and mental model

RLS behaves like an implicit predicate plus write check. If enabled with no applicable policy, normal access is default-deny. Owners normally bypass unless forced; privileged roles can bypass.

How can tenant context work?

An application authenticates a user, authorizes tenant membership, begins a transaction, and sets a transaction-local validated tenant ID:

sql

SELECT set_config('app.organization_id', $1, true);

Policy concept:

sql

ALTER TABLE signaldesk.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE signaldesk.tickets FORCE ROW LEVEL SECURITY;

CREATE POLICY tickets_tenant_policy
ON signaldesk.tickets
USING (
  organization_id = current_setting('app.organization_id', true)::bigint
)
WITH CHECK (
  organization_id = current_setting('app.organization_id', true)::bigint
);

The application must validate the setting value before SQL, set it locally inside every transaction, and fail closed when absent. Design error behavior to avoid cast surprises. Production policies often join membership/permission data or use safer helper functions after careful privilege review.

Why does this matter in AI-native systems?

The same RLS-protected relation should supply candidate chunks and tool context. Do not retrieve globally then filter in application/model code. Evaluation jobs and support tooling need explicit privileged paths and audit, not blanket bypass.

Prove it worked

As the actual runtime role, test two tenants: allowed select/insert/update/delete, wrong-tenant direct ID, wrong-tenant foreign key, missing context, invalid context, pooled reuse, owner behavior, and definer functions. A test run as superuser proves nothing about runtime RLS.

Production judgment

Policies compose as permissive or restrictive according to definitions; review logic carefully.
RLS can influence plans; index policy predicates and measure.
Background jobs must establish scope per transaction.
Referential checks and side channels require security review beyond row visibility.

Common mistakes

All rows visible in test: test used owner/superuser → use runtime role and force behavior deliberately.
Tenant leaks after pool reuse: session setting persisted → use transaction-local setting and missing-context tests.
Inserts cross tenant: only USING defined → add WITH CHECK.

AI Pair-Programmer Prompt

text

Audit PostgreSQL RLS as an attacker. Identify runtime role, owners, BYPASSRLS, definer
functions, tenant-context source/lifetime, USING/WITH CHECK policies for every command,
policy composition, pooled reuse, jobs, retrieval, admin paths, and side channels.
Generate positive and negative tests executed as the real role.

Hands-on exercise

Implement RLS on a disposable copy of tickets for two tenants and run it through a transaction-pooled simulation.

Acceptance criterion: missing/stale context returns no unauthorized data, writes cannot change tenant, and owner/bypass behavior is documented.

Knowledge check

What do USING and WITH CHECK control?
Who can bypass RLS?
Why set tenant context transaction-locally?

Answers

Existing-row visibility/eligibility and proposed new-row validity.
Superusers, BYPASSRLS roles, and generally owners unless forced, subject to PostgreSQL rules.
To prevent context leaking across pooled transactions and bound it to one unit of work.

Completion checklist

Policies cover every command.
Tests run as real runtime roles.
Missing and reused context fail closed.
Retrieval and jobs use the same isolation boundary.

Primary references

Row-Level Security for Multi-Tenancy

How can tenant context work?

An application authenticates a user, authorizes tenant membership, begins a transaction, and sets a transaction-local validated tenant ID:

sql

SELECT set_config('app.organization_id', $1, true);

Policy concept:

sql

ALTER TABLE signaldesk.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE signaldesk.tickets FORCE ROW LEVEL SECURITY;

CREATE POLICY tickets_tenant_policy
ON signaldesk.tickets
USING (
  organization_id = current_setting('app.organization_id', true)::bigint
)
WITH CHECK (
  organization_id = current_setting('app.organization_id', true)::bigint
);

AI Pair-Programmer Prompt

text

Audit PostgreSQL RLS as an attacker. Identify runtime role, owners, BYPASSRLS, definer
functions, tenant-context source/lifetime, USING/WITH CHECK policies for every command,
policy composition, pooled reuse, jobs, retrieval, admin paths, and side channels.
Generate positive and negative tests executed as the real role.

Knowledge check

What do USING and WITH CHECK control?

Who can bypass RLS?

Why set tenant context transaction-locally?

Answers

Existing-row visibility/eligibility and proposed new-row validity.

Superusers, BYPASSRLS roles, and generally owners unless forced, subject to PostgreSQL rules.

To prevent context leaking across pooled transactions and bound it to one unit of work.