# task-2991 로키(Loki) 레드팀 적대적 검증 보고서

- 대상: `/home/jay/projects/InsuRo/.worktrees/task-2991-dev3/supabase/migrations/20260820T000001_task2991_caller_binding.sql` (+ ROLLBACK 쌍)
- 방법: **프로덕션 미접촉.** 로컬 Docker `postgres:15` 컨테이너(`t2991-redteam`, 포트 55432)에 프로덕션 스키마를 축약 재현(`profiles`/`customers`/`customer_chat_tokens`/`conversations`/`conversation_messages`/`push_subscriptions`, RLS 활성, `anon`/`authenticated` 롤은 `NOLOGIN NOBYPASSRLS`로 생성해 실제 RLS 강제가 걸리도록 함)한 뒤, ① 프로덕션 실측 정책(백업 JSON) 그대로 BEFORE 상태 재현 → 취약 재현 확인 → ② 대상 마이그레이션 SQL을 **수정 없이 그대로** 적용 → ③ `SET ROLE anon`/`SET ROLE authenticated`로 실제 RLS 경계 안에서 공격 시도 → ④ ROLLBACK SQL 실행까지 검증. 작업 종료 후 컨테이너 삭제 완료(`docker rm -f t2991-redteam`).
- 원칙: "테스트 통과=안전"이 아니라 **직접 우회를 고안**했다. malformed 입력이 아니라 **형식이 올바른 위조값**(타인 토큰, NULL/빈문자열/공백, 포맷만 다른 전화번호, 실제 존재하지만 비활성인 토큰, UUID 형태 문자열 등)을 중심으로 시도했다.

## 공격 시나리오별 결과

### 1. SECURITY DEFINER 남용 / SQL Injection
- **방법**: 4개 함수(`chat_gate_info`, `chat_verify_and_open`, `chat_list_messages`, `chat_send_message`) 및 트리거 함수 전체 원문에서 `EXECUTE`/동적 SQL 존재 여부 확인(grep). 전부 정적 plpgsql `SELECT ... WHERE col = p_arg` 형태로 파라미터 바인딩됨.
- **결과**: **관통 실패(차단)**. 동적 SQL 자체가 없어 인젝션 표면이 없음.

### 2. 토큰 존재 여부 누출 (열거 공격)
- **방법**: `chat_gate_info`에 (a) 유효 토큰 (b) 존재하지만 `is_active=false`인 토큰 (c) 존재하지 않는 임의 토큰 (d) NULL (e) 빈 문자열 (f) 10,000자 토큰을 순서대로 호출, 반환 행 수/에러 형태 비교.
- **결과**: **관통 실패(차단)**. (a)만 1행, (b)~(f) 전부 동일하게 `0 rows`(에러 없음) — 무효/비활성/존재하지 않음을 응답 형태로 구분 불가. 실행 근거(발췌):
  ```
  1a valid → agent_display_name = 'Agent Alice' (1 row)
  1b inactive(존재함) → (0 rows)
  1c 존재하지 않는 토큰 → (0 rows)
  1d NULL / 1e '' / 1f 10000자 → 전부 (0 rows)
  ```
  (남은 잠재 채널: 타이밍 사이드채널 — §발견취약점 5 참조, Low)

### 3. 본인확인(name/phone) 우회 — ★ 지시된 핵심 공격
- **방법**: 유효 토큰 + (a) 오답 이름/전화 (b) NULL/NULL (c) 빈문자열/빈문자열 (d) 공백만 있는 이름 (e) 정답이지만 포맷이 다른 전화번호(하이픈 없음/공백/+82 국제표기/문자열 속에 숫자만 파묻힘) (f) **DB의 `customers.phone`이 NULL인 고객**에게 빈문자열/NULL/숫자없는 문자열/한자리 숫자 (g) 비활성 토큰 + 정답 이름/전화 (h) **정답 이름/전화를 다른 사람(타 고객)의 토큰과 결합**.
- **결과**: **전부 관통 실패(차단)**.
  - (a)(b)(c)(d)(g) → NULL (거부)
  - (e) 하이픈없음/공백 포맷 → **정상 통과**(정규화 설계 의도대로 동작, 취약 아님). `+82` 국제표기는 자릿수가 달라져 실패(보안결함 아닌 UX 이슈로만 기록)
  - (f) NULL-phone 고객: 4가지 위조 입력 전부 NULL 반환 — 함수 내부 가드(`IF v_input_phone_digits = '' THEN RETURN NULL`)가 "빈 문자열==빈 문자열" 식의 NULL/빈값 오통과를 원천 차단함. **이것이 지시서에서 "★ 중요"로 지목한 지점인데, 실제로는 안전하게 막혀 있음을 확인.**
  - (h) 타인 토큰 + 정답 신원: NULL — `chat_verify_and_open`은 **토큰이 가리키는 customer_id**로만 신원을 조회하므로, 공격자가 "누구인지"를 아무리 정확히 대도 토큰 자체가 그 사람 것이 아니면 무의미함.
  - 대조군(정답 토큰+정답 이름+정답 전화)만 유일하게 `conversation_id` 반환 확인.

### 4. 소유권 검사 누락 (IDOR) — `chat_list_messages` / `chat_send_message`
- **방법**: (a) 자신의 유효 토큰으로 메시지 목록/전송(대조군) (b) 타인의 유효 토큰(아직 대화 미생성 상태)으로 목록 조회 (c) 존재하지 않는 토큰으로 목록/전송 (d) 빈 콘텐츠/NULL 콘텐츠 전송 (e) 대화 `id`(UUID) 자체를 `token` 인자에 넣어 파라미터 혼동 유도.
- **결과**: **관통 실패(차단)**. 두 함수 모두 **client가 conversation_id를 직접 넘길 방법 자체가 없음** — 매 호출마다 `customer_chat_tokens t JOIN conversations conv ON conv.customer_id=t.customer_id AND conv.agent_id=t.agent_id WHERE t.token=p_token AND t.is_active=true`로 서버가 재확인. 위조 토큰으로 실제 삽입 시도 3건 모두 대화에 반영되지 않음(삽입 후 카운트 확인: 대화 1개에 메시지 3건만 존재 — 정상 2건 + 대조군 전송 1건, 위조 전송분 0건).

### 5. 트리거 악용
- **방법**: `chat_update_conversation_on_message()`을 anon 세션에서 **직접 SELECT 호출** 시도(트리거 우회 경로 탐색).
- **결과**: **관통 실패(차단)**, 단 이유가 REVOKE가 아니라 **Postgres 자체 제약**임을 확인 — `ERROR: trigger functions can only be called as triggers`. 즉 `RETURNS trigger` 타입 함수는 SQL에서 직접 호출 자체가 불가능해서 막힌 것이지, 이 마이그레이션이 의도적으로 막은 게 아님(§발견취약점 1 참조 — REVOKE 누락은 별도 결함으로 기록).
- 부가 확인: 트리거는 `NEW.conversation_id`만 갱신하며, 이 값은 트리거를 유발한 INSERT 자체가 **이미 인가를 통과한 행**(anon RPC 경로 또는 agent의 기존 authenticated INSERT 정책)이므로 트리거 자체가 별도의 권한 상승 경로를 만들지 않음 — 관통 실패.

### 6. search_path 하이재킹
- **방법**: 5개 함수(4 RPC + 1 트리거) 전체 `CREATE FUNCTION` 블록에서 `SET search_path` 존재 여부 원문 대조.
- **결과**: **관통 실패(차단)**. 5개 전부 `SET search_path = public, pg_temp` 명시됨(트리거 함수 포함).

### 7. 권한(REVOKE/GRANT) 감사
- **방법**: `pg_proc.proacl`로 5개 함수의 실제 ACL을 조회. `authenticated` 롤로 `chat_send_message` 직접 호출 시도.
- **결과**: **부분 관통** — 4개 RPC는 `REVOKE ALL FROM PUBLIC` + `GRANT ... TO anon`이 정확히 적용됨(`proacl = {postgres=X/postgres,anon=X/postgres}`, `authenticated` 호출 시 `permission denied` 확인). **그러나 트리거 함수 `chat_update_conversation_on_message()`만 `proacl`이 비어있음** = REVOKE가 누락되어 PUBLIC 기본 EXECUTE 권한이 그대로 남아있음. §5에서 확인했듯 Postgres 타입 제약으로 현재는 악용 불가하지만, 설계 원칙("전부 REVOKE FROM PUBLIC")과 불일치하는 **실제 코드 결함**으로 기록(§발견취약점 1).

### 8. 잔존 표면 — ★ 지시서가 명시한 `push_subscriptions`
- **방법**: 프로덕션 정책 백업 JSON에 있는 `push_subscriptions`의 실제 anon 정책 6건(`Anon can {read,insert,update} push subs`, `subscriber_type='customer'`만 검사)을 로컬에 동일하게 재현한 뒤, 마이그레이션이 이 테이블을 전혀 건드리지 않는다는 점을 이용해 (a) 임의 대화(victim)에 연결된 push 구독 행을 **토큰 없이** SELECT (b) 그 행의 `endpoint`/`keys`를 임의 값으로 **UPDATE**(하이재킹 시도).
- **결과**: **관통 성공(진짜 취약점, 이 마이그레이션 범위 밖에 잔존)**. 근거:
  ```sql
  -- anon, 토큰/인증 전혀 없이:
  SELECT id, conversation_id, endpoint, keys FROM public.push_subscriptions WHERE subscriber_type='customer';
  →  412e3e38-...  | c1111111-... | https://victim-real-endpoint.example/abc | {"p256dh": "victimkey"}
  UPDATE public.push_subscriptions SET endpoint='https://attacker-controlled.example/hijack', keys='{"p256dh":"attacker-key"}'
    WHERE subscriber_type='customer' RETURNING id, endpoint;
  →  412e3e38-... | https://attacker-controlled.example/hijack   (UPDATE 1)
  ```
  즉 `subscriber_type='customer'` 한 조건만 맞으면 **어떤 conversation_id에 속한 push 구독이든** anon이 전건 열람·변조 가능. task-2991이 막은 취약점(고객 채팅 anon `USING(true)`류)과 **완전히 동일한 패턴**이 옆 테이블에 그대로 남아있음. 설계안(§7.3)도 이를 인지하고 "범위 외, ANU 판단 필요"로 명시했으나, **적용 여부와 무관하게 실사용자 영향은 이미 존재**하므로 심각도를 명확히 해야 함(§발견취약점 2).

### 9. 롤백의 위험
- **방법**: 마이그레이션 적용 후 상태에서 ROLLBACK SQL을 **그대로 실행**, 이후 anon으로 직접 테이블 SELECT 재시도.
- **결과**: 롤백은 **완전하고 정확하게** 원래 취약 상태(5개 정책, `qual=true`)를 복원함(부분 실패 없음, `DROP ... IF EXISTS` 멱등성 정상 동작 확인). 즉 **롤백=설계대로 취약점 재오픈**이 확인됨 — 이는 버그가 아니라 "실패 시 즉시 원복" 설계 의도이나, **운영상 롤백은 임시조치일 뿐 방치되면 그 순간부터 다시 무방비 상태**라는 점을 배포 절차 문서에 명시할 것을 권고.

### 10. DoS/자원
- **방법**: `chat_list_messages`에 `p_after='infinity'`, `'-infinity'`, malformed 문자열 전달. `token`에 10,000자 문자열(§2 재사용). 실제 프로덕션 스키마의 인덱스 현황을 마이그레이션 히스토리에서 grep.
- **결과**:
  - `p_after` 극단값: **관통 실패(차단)** — `infinity`는 빈 결과, `-infinity`는 전체 이력(의도된 동작), malformed 문자열은 PostgREST/SQL 타입 캐스팅 단계에서 즉시 에러(정보 유출 없음).
  - **인덱스 부재 확인(신규 발견, Medium)**: `token` 컬럼은 `UNIQUE` 제약으로 자동 인덱스가 있어 토큰 조회는 안전하지만, `conversation_messages.conversation_id`(FK, Postgres가 FK에 자동 인덱스를 만들지 않음)와 `conversations(customer_id, agent_id)` 조합에는 인덱스가 없음. 인덱스를 추가하는 마이그레이션(`20260309160000_db-safety-improvements.sql`)이 저장소에 존재하지만 **투자 보고서(`task-2991-investigation-fe.md`)가 확인한 대로 프로덕션에 미적용 상태**. 이 마이그레이션은 고객 채팅을 realtime→**폴링**으로 전환하는 설계(design-proposal.md §2)를 전제하므로, 배포 시 이 4개 RPC에 대한 호출 빈도가 오히려 **증가**한다. 인덱스 없이 폴링 트래픽이 늘면 anon(무인증)이 값싸게 유발 가능한 증폭성 부하가 된다(§발견취약점 3).

## 발견 취약점

| # | 심각도 | 내용 | 재현 방법 | 권고 수정 |
|---|---|---|---|---|
| 1 | **Medium-High** | `push_subscriptions` 잔존 anon IDOR — 이번 마이그레이션 범위 밖이지만 실사용자에게 이미 노출 중인 동일 패턴 취약점. 토큰/인증 없이 임의 고객의 push 구독 endpoint/keys 열람·변조(알림 하이재킹/DoS) 가능 | §8 SQL 그대로 재현 | 이 마이그레이션과 **같은 릴리스 또는 즉시 후속 PR**로 `push_subscriptions`도 caller-binding RPC로 전환(설계안이 이미 "범위 외"로 인지·§7.3에 명시 — ANU가 반드시 결정해야 함, 미루면 "채팅은 막고 옆문은 열어둠" 상태로 배포됨) |
| 2 | Low | 트리거 함수 `chat_update_conversation_on_message()`에 `REVOKE ALL ... FROM PUBLIC` 누락 (다른 4개 함수는 있음). 현재는 Postgres가 `RETURNS trigger` 함수의 직접 호출을 원천 차단해 악용 불가 확인(§5, §7) | `pg_proc.proacl`이 해당 함수만 비어있음 | 일관성/방어심층 차원에서 `REVOKE ALL ON FUNCTION public.chat_update_conversation_on_message() FROM PUBLIC;` 한 줄 추가 권고(선택, 배포 차단 사유는 아님) |
| 3 | Medium | `conversation_messages.conversation_id`, `conversations(customer_id,agent_id)` 인덱스 부재 + 설계가 폴링으로 전환 → 무인증 anon이 값싸게 유발 가능한 반복 스캔 부하(증폭 DoS 소지) | §10, 마이그레이션 히스토리 grep으로 미적용 확인 | 이미 작성된 `20260309160000_db-safety-improvements.sql`의 인덱스 3종(`idx_conversations_customer_id`, `idx_conversation_messages_conv_created`, `idx_customer_chat_tokens_customer_active`)을 이번 배포에 동봉 또는 즉시 후속 적용 권고 |
| 4 | Low | 전화번호 정규화가 지나치게 관대함(`regexp_replace(...,'[^0-9]','','g')`) — 숫자 외 임의 문자가 섞여도 숫자만 추출해 비교하므로 `'call-me-at-010-1234-5678-thanks'` 같은 입력도 통과. 단, 공격자가 정확한 숫자열 자체는 여전히 알아야 하므로 **인증 우회는 아님** | §3(e) | 필수는 아니나 입력 형식 검증(숫자/하이픈/공백/괄호만 허용) 추가하면 더 엄격 |
| 5 | Low(이론) | `chat_verify_and_open`은 이름+전화 둘 다 일치할 때만 추가로 `conversations` SELECT/INSERT를 수행 → 일치 여부에 따른 **거친 타이밍 차이**가 이론상 존재. 실측상 압도적으로 값이 작아(정규식/문자열 비교 vs 네트워크 지터) 실용적 착취 난이도는 높음 | 코드 경로 분석(실측 타이밍 공격은 미실시 — 네트워크 개입 없는 로컬 DB라 유의미한 측정 불가) | DB 레벨 수정 불필요. 애플리케이션/엣지 레벨 rate limiting으로 충분히 상쇄됨(아래 운영 권고와 동일) |
| 6 | Info(운영) | 4개 RPC 전부 DB 레벨 rate limiting/lockout 없음 — 토큰 브루트포스는 오직 256비트 엔트로피(`encode(gen_random_bytes(32),'hex')`)에만, 신원(이름/전화) 브루트포스는 오직 전화번호 자릿수 엔트로피에만 의존 | 설계 검토(로컬 재현 대상 아님) | Supabase/Cloudflare 엣지 또는 PostgREST 앞단에 IP/토큰 기준 rate limit 병행 권고(이 SQL의 배포 차단 사유는 아님) |

## 뚫지 못한 항목 (방어가 유효하다는 증거)

- **직접 테이블 접근**: 마이그레이션 적용 후 anon의 `customer_chat_tokens`/`conversations`/`conversation_messages` 직접 SELECT/UPDATE/INSERT 전부 0행/거부 — 정책 5건이 실제로 제거되었고 대체 anon 정책이 생성되지 않았음을 실측 확인.
- **토큰 존재 열거**: 무효/비활성/존재하지않음이 응답 형태로 구분되지 않음(전부 0 rows, 동일 에러 없음).
- **본인확인 우회 8종**(오답, NULL, 빈문자열, 공백, 비활성토큰+정답, 타인토큰+정답신원, DB phone=NULL 고객 대상 4변형) — 전부 차단. 특히 지시서가 "★ 중요"로 지목한 "고객 phone이 NULL일 때" 케이스는 함수 내부의 `v_input_phone_digits=''` 조기 반환 가드 덕에 안전함을 실측 확인.
- **IDOR via RPC 파라미터**: `chat_list_messages`/`chat_send_message`에 `conversation_id`를 직접 지정할 방법이 애초에 없고, 매 호출 토큰→대화 재조회가 이뤄짐. 위조 토큰으로 시도한 메시지 삽입은 전부 미반영(DB 실측 카운트로 확인).
- **SQL Injection**: 동적 SQL 부재로 인젝션 표면 자체 없음.
- **search_path 하이재킹**: 5개 함수 전부 `SET search_path = public, pg_temp` 명시.
- **authenticated 롤 권한 확산**: `chat_send_message`를 `authenticated`로 호출 시 `permission denied` — 새 RPC가 설계사(authenticated) 쪽에 의도치 않은 신규 경로를 열지 않음.
- **agent(설계사) 경로 무변경**: DROP된 정책 5건 모두 `roles={anon}`이고, `auth.uid()` 기반 정책은 마이그레이션 어디에도 등장하지 않음(원문 대조 확인).
- **롤백 완전성**: 부분 실패 없이 원래 5개 정책을 정확히 복원함(멱등 DROP IF EXISTS 정상 동작).

## 종합 판정

**이 SQL(`20260820T000001_task2991_caller_binding.sql`) 자체는 프로덕션에 적용해도 안전하다고 판단한다 — 단, 두 가지 조건이 충족되어야 "안전하다"는 판정이 실질적 의미를 가진다.**

근거: 지시서가 요구한 10개 공격 범주 중 핵심 위협(SECURITY DEFINER 남용, 본인확인 우회, IDOR, 인젝션, search_path 하이재킹, 권한 확산, 롤백 무결성)은 **형식이 올바른 위조값을 포함한 적극적 우회 시도**에도 전부 차단되었다. 특히 지시서가 "★ 중요"로 강조한 "고객 phone이 NULL인 경우"는 안전하게 처리됨을 실측으로 확인했다 — 이 부분은 우려와 달리 관통되지 않았다.

**조건**:
1. **[필수] `push_subscriptions` 후속 조치를 확정할 것** — 이 마이그레이션이 배포되는 순간 "고객 채팅은 안전, 그 옆 push 구독 테이블은 동일한 패턴으로 여전히 무방비"라는 상태가 실사용자에게 노출된다(§발견취약점 1, 관통 재현 완료). 설계안 §7.3이 이미 이 판단을 ANU에게 요청해 두었으나, 본 레드팀이 **실제로 뚫어서** 확인했으므로 승인 시점에 반드시 처리 시점(동시 배포 vs 즉시 후속 task)을 결정해야 한다.
2. **[권고] 인덱스 3종 동봉** — 폴링 전환과 무인증 접근이 만나는 지점이라 배포 후 트래픽 패턴 변화를 모니터링하거나, 가능하면 이번 배포에 인덱스를 함께 넣을 것(§발견취약점 3).
3. **[권고, 비차단]** 트리거 함수 REVOKE 추가(§발견취약점 2), rate limiting 운영 병행(§발견취약점 6) — 둘 다 배포를 막을 사유는 아님.