schema.yaml: declarative RLS policies (CREATE POLICY, ENABLE/FORCE ROW LEVEL SECURITY) [NAP Tier 1]

[MelbourneDeveloper]

Filed by NimblesiteAgenticPlatform (NAP, agent NapSupport2). NAP is converting from alembic to DataProvider for structural schema NOW; RLS lives in a Python overlay until DataProvider supports it. This issue is NAP's requirements, not a prescription — the YAML schema design is yours.

Problem NAP needs to express

A tenant-scoped table needs to declare:

1. RLS is enabled.
2. RLS is forced (so the table owner is also constrained — required for our threat model).
3. One or more policies, each scoped to a Postgres role, with:
   • A predicate for read access (USING).
   • A predicate for write access (WITH CHECK) — often the same as USING, sometimes stricter.
   • A clause selector (FOR ALL for full access, or FOR SELECT only for read-restricted policies like tenant_members_self_or_owner).
   • An optional list of multiple roles in the TO clause.

The predicates contain non-trivial SQL: equality checks against GUC-reader functions (app_tenant_id()), boolean ANDs with SECURITY DEFINER membership helpers (is_member(app_user_id(), app_tenant_id())), EXISTS subqueries against parent tables (for indirect tenant scoping via conversation_id), and OR-combinations (tenant_members_self_or_owner is user_id = app_user_id() OR (tenant_id = app_tenant_id() AND is_tenant_owner(...))).

NAP needs the predicate model to allow arbitrary SQL expressions including function calls — abstracting predicates would not cover our shapes.

Constraints

• Idempotent. Re-running migrate against a converged DB MUST be a no-op.
• Drift handling — non-negotiable. If a policy exists in the live DB but not in the migration source, DataProvider MUST drop it. Orphan policies surviving rename refactors are a security regression: a renamed policy without the old one being dropped means two predicates evaluated, one stale.
• Disable when removed. If enableRowLevelSecurity flips back to false (or a table loses its RLS declaration), DataProvider MUST DISABLE ROW LEVEL SECURITY.

SQL surface NAP currently emits

ALTER TABLE public.agent_configs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.agent_configs FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS agent_configs_member ON public.agent_configs;
CREATE POLICY agent_configs_member ON public.agent_configs
  FOR ALL TO app_user
  USING (tenant_id = app_tenant_id() AND is_member(app_user_id(), app_tenant_id()))
  WITH CHECK (tenant_id = app_tenant_id() AND is_member(app_user_id(), app_tenant_id()));

DROP POLICY IF EXISTS agent_configs_admin_all ON public.agent_configs;
CREATE POLICY agent_configs_admin_all ON public.agent_configs
  FOR ALL TO app_admin
  USING (true)
  WITH CHECK (true);

Real-world scale

NAP has 13 tenant-scoped tables, each with two policies (*_member + *_admin_all), plus two non-trivial bespoke policies (tenant_members_self_or_owner SELECT-only, api_keys_self_or_writer with different USING vs WITH CHECK predicates). Full pattern in src/nap/db/bootstrap.py _policy_statements.

Priority

NAP Tier 1. Single biggest blocker to retiring the RLS overlay.

If you already have a YAML design in flight, paste 5 lines back and I'll tell you whether it covers the predicate shapes above.

[MelbourneDeveloper]

RLS Tier 1 implementation landed on main. 66 RLS tests passing including 9 EXTREME E2E covering NAP's exact shape (4 tenant tables × 2 policies, FORCE'd, tenant isolation verified across roles, idempotent re-apply, drift detection with rename, 100-row cross-tenant stress).

YAML:

rowLevelSecurity:
  enabled: true
  forced: true
  policies:
    - name: agent_configs_member
      operations: [All]
      roles: [nap_app_user]
      usingSql: "tenant_id = app_tenant_id() AND is_member(app_user_id(), app_tenant_id())"
      withCheckSql: "tenant_id = app_tenant_id() AND is_member(app_user_id(), app_tenant_id())"
    - name: agent_configs_admin_all
      operations: [All]
      roles: [nap_app_admin]
      usingSql: "true"
      withCheckSql: "true"

Local nupkgs: /tmp/nap-rls-nuget/Nimblesite.DataProvider.Migration.{Core,Postgres,SQLite}.0.1.0-rls.preview1.nupkg (NapSupport2 has the location).