Rls Policy Authoring

---
name: rls-policy-authoring
description: Author Postgres Row Level Security policies (force-on, CI-deployed, denial-tested) for multi-tenant dashboards. Encodes the closeness-to-data invariant — tenant isolation at the closest-to-data layer the viewer's token cannot influence. Covers Postgres RLS + semantic-layer enforcement (Cube `securityContext`, Power BI DAX roles, Fabric, Snowflake row-access). Invoked by `ravenclaude-core/security-reviewer`.
invoked_by: ravenclaude-core/security-reviewer
---

# Skill: rls-policy-authoring

> **Invoked by:** `ravenclaude-core/security-reviewer` (mandatory for any multi-tenant change). Generated by `database-setup-guide` when scaffolding a new schema.
>
> **When to invoke:** authoring or reviewing Postgres RLS policies; verifying a multi-tenant schema; investigating a cross-tenant leak suspicion. **Also consulted when the engagement is semantic-layer-fronted** (Cube, Power BI) — to confirm the layer-of-enforcement decision is correct per the closeness-to-data invariant.
>
> **Output:** verified RLS policies (or verified semantic-layer scope rules), cross-boundary denial test, CI deployment evidence.

## The closeness-to-data invariant (the foundational rule)

**Tenant isolation is enforced at the closest-to-data layer the viewer's token cannot influence — and never at the rendering layer.**

Per the data-platform house opinion #3 (see [`../../CLAUDE.md`](../../CLAUDE.md) §3 #3), the load-bearing tenant control lives:
- **Raw-Postgres-backed dashboards** (Metabase/Superset against the DB): in **Postgres RLS**, force-on, CI-deployed, denial test
- **Semantic-layer-fronted** (Cube, dbt-semantic, Power BI/Fabric): in **the semantic layer's scope rule**; DB connection account is intentionally tenant-blind
- **Defense-in-depth:** where the semantic layer connects to Postgres with a tenant-aware role, *also* enable RLS as backstop
- **App-code tenant filters are never the load-bearing control** on a viewer-facing read path
- **Single-tenant deliverables:** no tenant axis = no tenant policy — document the assumption

## Postgres RLS — the canonical pattern

```sql
-- 1. Enable + force RLS on the table
ALTER TABLE fact_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE fact_orders FORCE ROW LEVEL SECURITY;

-- 2. Policy that scopes reads to the session's tenant_id
CREATE POLICY tenant_isolation_read ON fact_orders
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- 3. Policy that scopes writes (USING + WITH CHECK both required)
CREATE POLICY tenant_isolation_write ON fact_orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- 4. Set the tenant_id per request (in app code / connection initialization)
SET LOCAL app.tenant_id = '<jwt-claim-tenant_id>';
```

### Common Postgres RLS footguns

1. **`USING` without matching `WITH CHECK`** — read-policy enforced; write-policy isn't (or vice versa). Both required for `FOR ALL`.
2. **Missing `FORCE ROW LEVEL SECURITY`** — table owners bypass RLS by default. Without `FORCE`, the policy doesn't apply when the table owner connects (which is common in ELT pipelines).
3. **`SET app.tenant_id` instead of `SET LOCAL`** — without `LOCAL`, the setting persists across the connection (connection pooling = leak).
4. **`bypassrls` on the connection role** — superusers and roles with `BYPASSRLS` skip RLS entirely. ELT roles intentionally have `BYPASSRLS`; viewer-facing roles never should.
5. **Missing index on `tenant_id`** — RLS-filtered queries scan whole tables without it. Production cost surprise.
6. **Forgetting to add the policy to a new table** — the schema-deployment hook should detect tables with `tenant_id` but no `ENABLE ROW LEVEL SECURITY`.

## Cross-boundary denial test (ships alongside every RLS schema)

The test fails the build if RLS is misconfigured. The template lives at [`../../templates/rls-cross-tenant-test.sql`](../../templates/rls-cross-tenant-test.sql) and looks like:

```sql
-- 1. Insert disjoint data for two tenants (as a bypassrls role)
INSERT INTO fact_orders (tenant_id, amount) VALUES
  ('tenant-A-uuid', 100.00),
  ('tenant-B-uuid', 200.00);

-- 2. Switch to a viewer role with RLS enforced
SET ROLE viewer_role;
SET LOCAL app.tenant_id = 'tenant-A-uuid';

-- 3. Attempt to read tenant B's data
SELECT count(*) FROM fact_orders WHERE tenant_id = 'tenant-B-uuid';

-- 4. EXPECTED: zero rows. Test fails if non-zero.
```

The CI step runs this against a fresh test DB on every PR. **No test passing = no merge.**

## Semantic-layer enforcement (when RLS is NOT the right layer)

### Cube `securityContext`

```yaml
cubes:
  - name: orders
    sql: SELECT * FROM fact_orders
    measures: ...
    dimensions: ...
    access_policy:
      - role: viewer
        conditions:
          - filter:
              member: orders.tenant_id
              operator: equals
              values:
                - "{ securityContext.tenant_id }"
```

The Cube query planner injects this filter *before* SQL is generated. The DB sees a pre-scoped query from Cube's service account — DB RLS would either duplicate the filter (fine, defense-in-depth) or block Cube's account (broken).

### Power BI DAX roles

```dax
[Sales Order Lines RLS] =
  CALCULATETABLE(
    'Sales Order Lines',
    'Sales Order Lines'[TenantID] = USERNAME()
  )
```

Applied via Workspace → Manage Roles → Add Role → DAX filter. The embed token (Azure AD via MSAL) carries `EffectiveIdentity` that includes the role name and username. **Service principal connecting the model bypasses any underlying-DB RLS by necessity** — the model needs all tenants' rows to slice them per-viewer.

**Exception:** Power BI DirectQuery + EffectiveIdentity *can* pass user identity through to the source. In that narrow mode, DB-level RLS *does* participate. Surface this if the engagement uses DirectQuery rather than Import.

### Fabric OneLake

Fabric workspace roles + OneSecurity row-level — newer than the F-SKU pattern. Check [Microsoft Learn](https://learn.microsoft.com/fabric/) for current state before relying on this skill's framing.

### Snowflake / Databricks

- **Snowflake:** row-access policies + dynamic data masking — apply at the table level, evaluate `CURRENT_ROLE()` or session-context variables
- **Databricks:** Unity Catalog row-filters + column masks
- These are **warehouse-native row-policy mechanisms** — the equivalent layer to Postgres RLS for those warehouses

## App-code tenant filters — acceptable patterns

App-code filters are **never the load-bearing control** on a viewer-facing read path. They are acceptable as:

1. **Redundant layer above an enforced one** — ORM `where tenant_id = :ctx` *in addition to* RLS. Belt-and-suspenders.
2. **Back-end ELT / job code** — Airbyte loading raw, dbt running marts, nightly aggregations. Tenant routing is the application/job concern; RLS isn't the right tool for these layers because raw landing tables aren't viewer-facing.

The anti-pattern is app-code filter *instead of* enforced layer.

## Defense-in-depth

| Stack | Primary enforcement | Backstop |
|---|---|---|
| Raw Postgres + Metabase/Superset | Postgres RLS | n/a (the DB is the closest-to-data layer) |
| Cube → Postgres | Cube `securityContext` | Postgres RLS as backstop (Cube connects with a tenant-aware role) |
| Cube → Snowflake/Databricks | Cube `securityContext` | Warehouse row-access policy |
| Power BI Embedded (Import) | DAX role + RBAC | Role-coverage tests + deny-by-default workspace |
| Power BI Embedded (DirectQuery + EffectiveIdentity) | DAX role | Source DB RLS if same identity passes through |

## Anti-patterns this skill flags

- `tenant_id` column without `ENABLE` + `FORCE ROW LEVEL SECURITY`
- `USING` policy without matching `WITH CHECK` on `FOR ALL`
- `SET app.tenant_id` instead of `SET LOCAL app.tenant_id` (connection-pool leak)
- Viewer-facing role with `BYPASSRLS` attribute
- Missing index on `tenant_id` (RLS performance cliff)
- Cross-boundary denial test not present in the engagement's CI
- A new table added to a multi-tenant schema without RLS policies
- Power BI Embedded engagement where the agent insists on Postgres RLS instead of DAX roles (closeness-to-data violation in reverse)
- Cube engagement where the agent ships `cubes/` without `access_policy` or `securityContext`
- App-code-only tenant filter as the load-bearing control on a viewer-facing read path
- Single-tenant deliverable where the "no tenant axis" assumption is undocumented (silent foot-gun for future multi-tenant pivot)

## References

- Template: [`../../templates/database-schema-starter.sql`](../../templates/database-schema-starter.sql) — multi-tenant schema with RLS already applied
- Template: [`../../templates/rls-cross-tenant-test.sql`](../../templates/rls-cross-tenant-test.sql) — the denial test contract
- Knowledge: [`../../knowledge/multi-tenant-rls-patterns.md`](../../knowledge/multi-tenant-rls-patterns.md) — cross-stack patterns + footguns + the closeness-to-data invariant
- Skill: [`../jwt-embed-issuance/SKILL.md`](../jwt-embed-issuance/SKILL.md) — where the `tenant_id` claim originates
- Skill: [`../cube-schema-scaffolding/SKILL.md`](../cube-schema-scaffolding/SKILL.md) — Cube semantic-layer scope rules
- Upstream review rubric: [`../../../ravenclaude-core/agents/security-reviewer.md`](../../../ravenclaude-core/agents/security-reviewer.md) §5 (Database)