# Pattern 09 — Multi-tenant SELECT isolation

## What this is

The foundational multi-tenant RLS pattern. Every row has an `org_id`; users can only SELECT rows whose `org_id` matches their own. The building block every other multi-tenant pattern extends.

## Why the subquery, not a direct column compare

The naive single-user isolation pattern is:

```sql
using (user_id = auth.uid());
```

This only works if the row's owner *is* the isolating key. In a real multi-tenant system, `auth.uid()` is an individual user — not the org. You need a lookup:

```sql
using (
  org_id = (select org_id from users where id = auth.uid())
)
```

PostgreSQL treats this as an `InitPlan` — the subquery evaluates once per statement and is cached, not once per row. Cost is one extra query per request, not O(rows).

## The index requirement

Without an index on `documents(org_id)`, every SELECT filters by org via a sequential scan. At 100k rows, acceptable. At 10M rows, this times out and your users file support tickets.

```sql
create index on documents(org_id);
```

This is easy to forget because the policy *looks* correct and works fine in development with small datasets.

## Testing

```sql
-- Simulate as a specific user and verify isolation
set role authenticated;
set request.jwt.claims = '{"sub": "<user-id>"}';

-- Should return only this user's org's documents
select org_id, count(*) from documents group by org_id;
-- Expect: exactly one org_id row
```

## Common mistake

Not adding the index. The policy is correct; the query is slow at scale. The failure mode is a production performance incident, not a security incident — but it's avoidable with one line.