# RLS 정책 설계 (v0)

## 현재 상태 요약

`supabase/migrations/20260416012416_enable_rls_and_fix_api_exposure.sql`:

- `workout_sessions`: `user_id = auth.uid()` 만 self read/write
- `exercise_entries / exercise_sets / reps / rom_estimates / raw_motion_samples`:
  각 정책이 `EXISTS (... ws.user_id = auth.uid())` 체인으로 session 소유자 확인
- `centers`: authenticated all read (placeholder)
- `center_memberships`: self-read만
- materialized view `user_daily_load`: anon + authenticated REVOKE

## 변경 원칙

1. 세션 정책만 3-way OR (`user_id = uid OR trainer_id = uid OR is_shop_staff(uid, center_id)`)으로 확장.
2. 파생 테이블은 session 정책을 EXISTS로 상속하여 중복 정의 최소화.
3. 세션 **INSERT/UPDATE**는 `self (본인)` 또는 `supervised (trainer 본인)` 만 허용. "같은 샵 staff면 아무나 끼어들기" 금지.
4. helper functions `is_shop_staff`, `shared_shop_with_member` 는 SECURITY DEFINER + STABLE + `search_path = public, pg_temp`.

## 마이그레이션 전체 SQL

파일: `supabase/migrations/20260419XXXXXX_b2b_shop_hierarchy.sql`
(`XXXXXX`은 실행 시각 타임스탬프)

```sql
-- =============================================================================
-- B2B Shop Hierarchy — v0
-- - membership role enum
-- - workout_sessions: trainer_id / center_id / session_type
-- - pending_invitations
-- - RLS: shop staff 경로 허용 + 파생 테이블 EXISTS 상속
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 0. citext (email UNIQUE를 위해)
-- -----------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS citext;

-- -----------------------------------------------------------------------------
-- 1. membership_role enum
-- -----------------------------------------------------------------------------
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'membership_role') THEN
    CREATE TYPE membership_role AS ENUM ('owner', 'trainer', 'member');
  END IF;
END$$;

-- existing center_memberships.role (text) → enum
ALTER TABLE center_memberships
  ALTER COLUMN role DROP DEFAULT,
  ALTER COLUMN role TYPE membership_role USING role::membership_role,
  ALTER COLUMN role SET DEFAULT 'member';

-- owner 는 센터당 1명
CREATE UNIQUE INDEX IF NOT EXISTS one_owner_per_center
  ON center_memberships(center_id) WHERE role = 'owner';

-- -----------------------------------------------------------------------------
-- 2. workout_sessions 확장
-- -----------------------------------------------------------------------------
ALTER TABLE workout_sessions
  ADD COLUMN IF NOT EXISTS trainer_id   uuid REFERENCES auth.users(id),
  ADD COLUMN IF NOT EXISTS center_id    uuid REFERENCES centers(id),
  ADD COLUMN IF NOT EXISTS session_type text NOT NULL DEFAULT 'self';

ALTER TABLE workout_sessions
  DROP CONSTRAINT IF EXISTS session_type_check,
  ADD CONSTRAINT session_type_check
    CHECK (session_type IN ('self', 'supervised'));

ALTER TABLE workout_sessions
  DROP CONSTRAINT IF EXISTS supervised_requires_trainer,
  ADD CONSTRAINT supervised_requires_trainer
    CHECK (session_type = 'self'
           OR (trainer_id IS NOT NULL AND center_id IS NOT NULL));

CREATE INDEX IF NOT EXISTS idx_sessions_trainer
  ON workout_sessions(trainer_id, started_at DESC)
  WHERE trainer_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_sessions_center
  ON workout_sessions(center_id, started_at DESC)
  WHERE center_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- 3. pending_invitations
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS pending_invitations (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  center_id       uuid NOT NULL REFERENCES centers(id) ON DELETE CASCADE,
  email           citext NOT NULL,
  role            membership_role NOT NULL,
  invited_by      uuid NOT NULL REFERENCES auth.users(id),
  shadow_user_id  uuid REFERENCES auth.users(id),
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE(center_id, email)
);

CREATE INDEX IF NOT EXISTS idx_invitations_center ON pending_invitations(center_id);
CREATE INDEX IF NOT EXISTS idx_invitations_shadow ON pending_invitations(shadow_user_id)
  WHERE shadow_user_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- 4. Helper functions
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION is_shop_staff(_user_id uuid, _center_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp AS $$
  SELECT EXISTS (
    SELECT 1 FROM center_memberships
    WHERE user_id = _user_id
      AND center_id = _center_id
      AND role IN ('owner','trainer')
  );
$$;

CREATE OR REPLACE FUNCTION shared_shop_with_member(_staff_id uuid, _member_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp AS $$
  SELECT EXISTS (
    SELECT 1
    FROM center_memberships sm
    JOIN center_memberships mm ON mm.center_id = sm.center_id
    WHERE sm.user_id = _staff_id
      AND sm.role IN ('owner','trainer')
      AND mm.user_id = _member_id
      AND mm.role = 'member'
  );
$$;

REVOKE ALL ON FUNCTION is_shop_staff(uuid, uuid) FROM PUBLIC;
REVOKE ALL ON FUNCTION shared_shop_with_member(uuid, uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION is_shop_staff(uuid, uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION shared_shop_with_member(uuid, uuid) TO authenticated;

-- -----------------------------------------------------------------------------
-- 5. RLS — workout_sessions
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS sessions_self ON workout_sessions;
DROP POLICY IF EXISTS sessions_read ON workout_sessions;
DROP POLICY IF EXISTS sessions_insert ON workout_sessions;
DROP POLICY IF EXISTS sessions_update ON workout_sessions;
DROP POLICY IF EXISTS sessions_delete ON workout_sessions;

CREATE POLICY sessions_read ON workout_sessions FOR SELECT TO authenticated
USING (
  user_id = auth.uid()
  OR trainer_id = auth.uid()
  OR (center_id IS NOT NULL AND is_shop_staff(auth.uid(), center_id))
);

CREATE POLICY sessions_insert ON workout_sessions FOR INSERT TO authenticated
WITH CHECK (
  (session_type = 'self'
     AND user_id = auth.uid()
     AND trainer_id IS NULL)
  OR
  (session_type = 'supervised'
     AND trainer_id = auth.uid()
     AND center_id IS NOT NULL
     AND is_shop_staff(auth.uid(), center_id)
     AND shared_shop_with_member(auth.uid(), user_id))
);

CREATE POLICY sessions_update ON workout_sessions FOR UPDATE TO authenticated
USING (user_id = auth.uid() OR trainer_id = auth.uid())
WITH CHECK (user_id = auth.uid() OR trainer_id = auth.uid());

CREATE POLICY sessions_delete ON workout_sessions FOR DELETE TO authenticated
USING (user_id = auth.uid());

-- -----------------------------------------------------------------------------
-- 6. RLS — exercise_entries / exercise_sets / reps / rom_estimates / raw_motion_samples
-- -----------------------------------------------------------------------------
-- exercise_entries (via session)
DROP POLICY IF EXISTS entries_via_session ON exercise_entries;
CREATE POLICY entries_via_session ON exercise_entries FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = exercise_entries.session_id
    AND (ws.user_id = auth.uid()
         OR ws.trainer_id = auth.uid()
         OR (ws.center_id IS NOT NULL AND is_shop_staff(auth.uid(), ws.center_id)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = exercise_entries.session_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- exercise_sets (via entry → session)
DROP POLICY IF EXISTS sets_via_entry ON exercise_sets;
CREATE POLICY sets_via_entry ON exercise_sets FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = exercise_sets.entry_id
    AND (ws.user_id = auth.uid()
         OR ws.trainer_id = auth.uid()
         OR (ws.center_id IS NOT NULL AND is_shop_staff(auth.uid(), ws.center_id)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = exercise_sets.entry_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- reps (via set → entry → session)
DROP POLICY IF EXISTS reps_via_set ON reps;
CREATE POLICY reps_via_set ON reps FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_sets es
  JOIN exercise_entries ee ON ee.id = es.entry_id
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE es.id = reps.set_id
    AND (ws.user_id = auth.uid()
         OR ws.trainer_id = auth.uid()
         OR (ws.center_id IS NOT NULL AND is_shop_staff(auth.uid(), ws.center_id)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_sets es
  JOIN exercise_entries ee ON ee.id = es.entry_id
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE es.id = reps.set_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- rom_estimates (via entry → session)
DROP POLICY IF EXISTS rom_via_entry ON rom_estimates;
CREATE POLICY rom_via_entry ON rom_estimates FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = rom_estimates.entry_id
    AND (ws.user_id = auth.uid()
         OR ws.trainer_id = auth.uid()
         OR (ws.center_id IS NOT NULL AND is_shop_staff(auth.uid(), ws.center_id)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM exercise_entries ee
  JOIN workout_sessions ws ON ws.id = ee.session_id
  WHERE ee.id = rom_estimates.entry_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- raw_motion_samples (via session)
DROP POLICY IF EXISTS raw_via_session ON raw_motion_samples;
CREATE POLICY raw_via_session ON raw_motion_samples FOR ALL TO authenticated
USING (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = raw_motion_samples.session_id
    AND (ws.user_id = auth.uid()
         OR ws.trainer_id = auth.uid()
         OR (ws.center_id IS NOT NULL AND is_shop_staff(auth.uid(), ws.center_id)))
))
WITH CHECK (EXISTS (
  SELECT 1 FROM workout_sessions ws
  WHERE ws.id = raw_motion_samples.session_id
    AND (ws.user_id = auth.uid() OR ws.trainer_id = auth.uid())
));

-- -----------------------------------------------------------------------------
-- 7. RLS — centers
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS centers_authenticated_read ON centers;

CREATE POLICY centers_member_read ON centers FOR SELECT TO authenticated
USING (EXISTS (
  SELECT 1 FROM center_memberships m
  WHERE m.center_id = centers.id AND m.user_id = auth.uid()
));

CREATE POLICY centers_owner_insert ON centers FOR INSERT TO authenticated
WITH CHECK (owner_id = auth.uid());

CREATE POLICY centers_owner_update ON centers FOR UPDATE TO authenticated
USING (owner_id = auth.uid()) WITH CHECK (owner_id = auth.uid());

CREATE POLICY centers_owner_delete ON centers FOR DELETE TO authenticated
USING (owner_id = auth.uid());

-- -----------------------------------------------------------------------------
-- 8. RLS — center_memberships
-- -----------------------------------------------------------------------------
DROP POLICY IF EXISTS center_memberships_self_read ON center_memberships;

CREATE POLICY memberships_read ON center_memberships FOR SELECT TO authenticated
USING (
  user_id = auth.uid()
  OR is_shop_staff(auth.uid(), center_id)
);

-- Owner만 membership insert/update/delete
CREATE POLICY memberships_owner_write ON center_memberships FOR INSERT TO authenticated
WITH CHECK (
  EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid())
  OR
  -- 샵 최초 생성 직후 owner membership 자체 insert
  (user_id = auth.uid() AND role = 'owner'
     AND EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()))
);

CREATE POLICY memberships_owner_update ON center_memberships FOR UPDATE TO authenticated
USING (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()))
WITH CHECK (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()));

CREATE POLICY memberships_owner_delete ON center_memberships FOR DELETE TO authenticated
USING (EXISTS (SELECT 1 FROM centers c WHERE c.id = center_id AND c.owner_id = auth.uid()));

-- -----------------------------------------------------------------------------
-- 9. RLS — pending_invitations
-- -----------------------------------------------------------------------------
ALTER TABLE pending_invitations ENABLE ROW LEVEL SECURITY;

CREATE POLICY invitations_staff_read ON pending_invitations FOR SELECT TO authenticated
USING (is_shop_staff(auth.uid(), center_id));

CREATE POLICY invitations_staff_insert ON pending_invitations FOR INSERT TO authenticated
WITH CHECK (is_shop_staff(auth.uid(), center_id) AND invited_by = auth.uid());

CREATE POLICY invitations_staff_update ON pending_invitations FOR UPDATE TO authenticated
USING (is_shop_staff(auth.uid(), center_id))
WITH CHECK (is_shop_staff(auth.uid(), center_id));

CREATE POLICY invitations_staff_delete ON pending_invitations FOR DELETE TO authenticated
USING (is_shop_staff(auth.uid(), center_id));

COMMIT;
```

## pgTAP 테스트 초안

파일: `supabase/tests/b2b_rls.test.sql`

```sql
BEGIN;
SELECT plan(14);

-- Fixtures
-- (실제로는 Supabase CLI가 제공하는 auth.users seed helpers 활용)
-- 여기서는 의사코드 수준으로 시나리오만 기술

-- 전제:
--   A: owner of centerC1
--   T1: trainer of C1
--   T2: trainer of centerC2
--   M1: member of C1
--   M2: member of C2
--   OUTSIDER: no shop

-- 시나리오 1~14
SELECT ok(
  (SELECT count(*) FROM workout_sessions WHERE user_id = :m1_id),
  'as A (owner), can read M1 sessions'
);

SELECT throws_ok(
  $$ INSERT INTO workout_sessions (user_id, trainer_id, center_id, session_type)
     VALUES (:m2_id, :t1_id, :c1_id, 'supervised') $$,
  NULL, 'as T1, cannot supervise M2 (different shop)'
);

SELECT lives_ok(
  $$ INSERT INTO workout_sessions (user_id, trainer_id, center_id, session_type)
     VALUES (:m1_id, :t1_id, :c1_id, 'supervised') $$,
  'as T1, can supervise M1 in same shop'
);

SELECT throws_ok(
  $$ INSERT INTO workout_sessions (user_id, session_type)
     VALUES (:m1_id, 'self') $$,
  NULL, 'as T1, cannot insert self-session as M1'
);

SELECT lives_ok(
  $$ INSERT INTO workout_sessions (user_id, session_type)
     VALUES (auth.uid(), 'self') $$,
  'as M1 self, can insert own self-session'
);

SELECT throws_ok(
  $$ INSERT INTO workout_sessions (user_id, trainer_id, center_id, session_type)
     VALUES (:m1_id, auth.uid(), :c1_id, 'supervised') $$,
  NULL, 'as OUTSIDER, cannot supervise anyone'
);

SELECT ok(
  (SELECT count(*) FROM workout_sessions WHERE trainer_id = :t1_id) > 0,
  'as T1, can read own supervised sessions'
);

SELECT is_empty(
  $$ SELECT 1 FROM workout_sessions ws
     WHERE ws.user_id = :m1_id AND ws.session_type = 'supervised'
     AND NOT EXISTS (
       SELECT 1 FROM center_memberships cm
       WHERE cm.user_id = auth.uid()
       AND cm.center_id = ws.center_id
     ) $$,
  'as OUTSIDER, cannot see supervised sessions in shops they do not belong to'
);

-- 추가 시나리오 (9~14):
--  9. as A (owner), can insert trainer membership into own center
-- 10. as T1 (trainer), cannot insert membership (only owner can)
-- 11. as OUTSIDER, cannot read any center row
-- 12. as A, can read pending_invitations for own center
-- 13. as T2 (other shop trainer), cannot read C1 pending_invitations
-- 14. as M1, cannot read pending_invitations (not staff)

SELECT * FROM finish();
ROLLBACK;
```