Filed by NimblesiteAgenticPlatform (NAP, agent NapSupport2). Companion to #32. Read the spec at `docs/specs/rls-spec.md` and `docs/plans/RLS-PLAN.md` — the LQL-only predicate model has a real-world gap NAP cannot work around with `exists()`.
Problem NAP needs to express
NAP's RLS predicates call SECURITY DEFINER functions — specifically `is_member(user_id, tenant_id)`, `is_tenant_writer(...)`, `is_tenant_owner(...)`. These read `tenant_members`, which itself has an RLS policy that hides rows from `app_user`. SECURITY DEFINER is the ONLY way to make membership checks tractable: the function runs as its definer (the migration owner), bypasses RLS on `tenant_members`, and returns a clean bool.
If we expand `is_member(...)` inline as `exists(tenant_members |> filter(...))` per RLS-CORE-LQL-SUBQUERY, the EXISTS evaluates under the caller's RLS context, which is `app_user`, which sees zero rows of `tenant_members` because of the `tenant_members_self_or_owner` policy. Every membership check returns false. Every tenant-scoped query returns zero rows. The system collapses.
This is intrinsic — there is no LQL `exists()` rewrite that recovers SECURITY DEFINER semantics. The function must be defined separately (NAP keeps function declaration in its overlay; see #33).
What NAP needs
A way to declare an RLS policy whose USING / WITH CHECK predicate is raw SQL, bypassing LQL transpilation. Examples NAP would express:
USING (tenant_id = app_tenant_id() AND is_member(app_user_id(), app_tenant_id()))
USING (user_id = app_user_id() OR (tenant_id = app_tenant_id() AND is_tenant_owner(app_user_id(), app_tenant_id())))
WITH CHECK (tenant_id = app_tenant_id() AND is_tenant_writer(app_user_id(), app_tenant_id()))
These are pure Postgres SQL. They reference functions the database knows about. They do not need to be portable to SQLite or SQL Server — NAP is Postgres-only.
Constraints
- The escape hatch MUST coexist with LQL — choose per-policy. A schema can mix LQL policies and raw-SQL policies.
- Drift detection still applies: if a policy goes from raw-SQL "X" to raw-SQL "Y", DataProvider DROPs and CREATEs.
- Cross-platform safety: if a raw-SQL policy targets only `postgres`, the migration runner against `sqlite` MUST emit an error (e.g. `MIG-E-RLS-RAW-SQL-UNSUPPORTED-ON-PLATFORM`), not silently skip.
Why `current_user_id()` is also insufficient
Per RLS-CONTEXT-SESSION, the only builtin is `current_user_id()` mapped to `current_setting('rls.current_user_id', true)`. NAP needs two session GUCs simultaneously — `app.user_id` AND `app.tenant_id` — because tenant scoping requires both. The raw-SQL escape hatch lets us call `current_setting('app.tenant_id', true)::uuid` directly. Without it, NAP would need either (a) you to add a second builtin like `current_tenant_id()`, or (b) a way to declare custom GUC name mappings. The escape hatch covers both with one feature.
Priority
NAP Tier 1. Without this, NAP cannot use #32 — the LQL-only predicate model produces semantically wrong policies for our threat model.
Reference
NAP's full predicate set: src/nap/db/bootstrap.py `_policy_statements`, `_tenant_members_policy_statements`, `_api_keys_policy_statements`. Spec: `[AUTHZ-RLS]` in docs/specs/auth.md.
Filed by NimblesiteAgenticPlatform (NAP, agent NapSupport2). Companion to #32. Read the spec at `docs/specs/rls-spec.md` and `docs/plans/RLS-PLAN.md` — the LQL-only predicate model has a real-world gap NAP cannot work around with `exists()`.
Problem NAP needs to express
NAP's RLS predicates call SECURITY DEFINER functions — specifically `is_member(user_id, tenant_id)`, `is_tenant_writer(...)`, `is_tenant_owner(...)`. These read `tenant_members`, which itself has an RLS policy that hides rows from `app_user`. SECURITY DEFINER is the ONLY way to make membership checks tractable: the function runs as its definer (the migration owner), bypasses RLS on `tenant_members`, and returns a clean bool.
If we expand `is_member(...)` inline as `exists(tenant_members |> filter(...))` per RLS-CORE-LQL-SUBQUERY, the EXISTS evaluates under the caller's RLS context, which is `app_user`, which sees zero rows of `tenant_members` because of the `tenant_members_self_or_owner` policy. Every membership check returns false. Every tenant-scoped query returns zero rows. The system collapses.
This is intrinsic — there is no LQL `exists()` rewrite that recovers SECURITY DEFINER semantics. The function must be defined separately (NAP keeps function declaration in its overlay; see #33).
What NAP needs
A way to declare an RLS policy whose USING / WITH CHECK predicate is raw SQL, bypassing LQL transpilation. Examples NAP would express:
These are pure Postgres SQL. They reference functions the database knows about. They do not need to be portable to SQLite or SQL Server — NAP is Postgres-only.
Constraints
Why `current_user_id()` is also insufficient
Per RLS-CONTEXT-SESSION, the only builtin is `current_user_id()` mapped to `current_setting('rls.current_user_id', true)`. NAP needs two session GUCs simultaneously — `app.user_id` AND `app.tenant_id` — because tenant scoping requires both. The raw-SQL escape hatch lets us call `current_setting('app.tenant_id', true)::uuid` directly. Without it, NAP would need either (a) you to add a second builtin like `current_tenant_id()`, or (b) a way to declare custom GUC name mappings. The escape hatch covers both with one feature.
Priority
NAP Tier 1. Without this, NAP cannot use #32 — the LQL-only predicate model produces semantically wrong policies for our threat model.
Reference
NAP's full predicate set: src/nap/db/bootstrap.py `_policy_statements`, `_tenant_members_policy_statements`, `_api_keys_policy_statements`. Spec: `[AUTHZ-RLS]` in docs/specs/auth.md.