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

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 §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

-- 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 and looks like:

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

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

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