# task-2927 — [InsuRo 개인정보 P0-1] Drive 공개제거→서버 프록시 Phase 1 (백엔드 코어)

> 팀: dev1-team (헤르메스 팀장 / 불칸 백엔드 / 아르고스 테스터)
> 프로젝트: InsuRo (`Jeon-Jonghyuk`→ 실제 remote `JonghyukJeon/InsuRo`)
> 워크트리: `/home/jay/projects/InsuRo/.worktrees/task-2927-dev1` · 브랜치 `task/task-2927-dev1` · base `f2b282e`
> 작성 2026-08-08

## S (Situation)
증권 PDF·소식지·보험료 업로드 파일이 Google Drive `anyone/reader`로 공개되어 링크 유출 시 제3자 접근 가능(개인정보 최우선 리스크). 회장 결정: **Drive 유지(대량저장 비용 절감), Supabase Storage 이관 안 함.** 공개 제거 + 서버 프록시로 접근통제. 본 작업은 **Phase 1(백엔드 코어)** — 엔드포인트/컬럼 추가 + 내부전환만. 신규 anyone 생성·소급회수·프론트 교체는 Phase 2/3.

## C (Complication)
- 프론트는 file_id 없이 전체 URL 문자열만 보유 + Bearer(localStorage) → `<a>/<img>`에 헤더 못 실음 → 순수 Bearer 프록시는 401.
- file_id↔소유자 매핑 계약이 코드에 없음(newsletters/premium_data는 `file_url` 문자열, policy_analyses는 `result` jsonb 내부) → IDOR 인가 불가.
- 단일 uvicorn 워커 → 블로킹 다운로드가 전 요청 직렬화 (OOM/블로킹 위험).
- `parse_premium_file`이 `/view`(HTML) URL을 httpx로 받는 잠재버그.
- JWT `algorithms=["ES256","HS256"]` 혼재 = 알고리즘 컨퓨전 표면.

## Q (Question)
공개를 제거하면서 7곳 링크·이미지·재분석·삭제를 무중단으로 유지하고, IDOR·SSRF·replay·OOM을 막는 백엔드 코어를 어떻게 구성하는가?

## A (Answer) — 구현 내용

### 1. 마이그레이션: `drive_file_id` 정규화 컬럼 + 백필 (IDOR 인가 기반)
`supabase/migrations/20260808120000_drive_file_id_normalization.sql` (신규, 28줄, 멱등)
- `newsletters.drive_file_id text` / `premium_data.drive_file_id text` / `policy_analyses.drive_file_ids text[]`(멀티파일)
- 백필: `substring(file_url from '/file/d/([^/]+)')`. newsletters는 `file_url` 우선, 없으면 `source_file_ref->>'file_url'`. policy_analyses는 `result` 배열 각 item의 file_url에서 `array_agg`로 추출.
- 인가 조회 인덱스 3종(gin 포함).

### 2. file_id 파서 유틸 — 삭제·재분석·프록시 공유 단일 함수
`server/gdrive.py` `extract_drive_file_id(url_or_id) -> str|None`: `/file/d/{id}/view`, `/file/d/{id}`, `uc?id={id}`, bare id(`[A-Za-z0-9_-]{10,}`) 처리. 삭제 경로(`main.py:6637` 인라인 regex 제거)·재분석·프록시가 공유.

### 3. `POST /api/insuro/file-ticket` (verify_jwt) — 등급별 소유권 인가 + 1회용 ticket
- 등급 판정 순서: **FA소유**(policy_analyses.drive_file_ids contains → `user_id != sub`면 **403 IDOR 차단**) → **관리자**(newsletters/premium_data.drive_file_id 매칭 → user_roles system_admin 없으면 403) → **공개**(fcpa_config.pdf_url 매칭 → 인증만으로 허용). 어디에도 없으면 **404**(정보노출 방지).
- 성공 시 `file_ticket_store.create_ticket` → `{ticket_id, expires_in:300}`.
- ticket store(`server/file_ticket_store.py`, 신규): 인메모리·스레드안전·**TTL 5분**·**1회 소비(pop, replay 방지)**. 단일 워커 전제.

### 4. `GET /api/insuro/file/{ticket_id}` — 무인증(ticket=capability) + 스트리밍
- ticket **1회 소비(무효화)** → 없거나 만료 404 → `run_in_executor`로 Drive `get_media` **청크 스트리밍**(`gdrive.iter_drive_file`, 전체 메모리 적재 없음, OOM 방지).
- 헤더: `Content-Type`(Drive mimeType) + `Content-Disposition: attachment; filename*=UTF-8''…` + `X-Content-Type-Options: nosniff` + `Referrer-Policy: no-referrer` + `Cache-Control: private, max-age=60`.

### 5. `parse_premium_file` file_id 기반 전환 (하위호환)
`gdrive.extract_drive_file_id(file_url)` 성공 → 소유자 `get_media`(`iter_drive_file`, run_in_executor). 실패(비 Drive URL, 예: supabase storage) → `_validate_file_url` 통과 시 httpx 폴백. → `/view` HTML 오수신 버그 동시 해소, legacy URL 하위호환 유지.

### 6. JWT ES256 전용 전환
`main.py:856` `algorithms=["ES256","HS256"]` → `["ES256"]`. Supabase JWKS(PyJWKClient) 기반이라 안전(HS256 secret 미사용). ★참고: `main.py:377` `multi_account_detect_middleware`(인가 무관 탐지 전용 로깅)에 HS256 잔존 — 지시 범위(verify_jwt) 밖이라 미변경, 회장/ANU 판단 시 별도 처리 가능.

### 7. 예외 `pass` 삼킴 제거 → 감사로그
`parse_premium_file` 내 status 업데이트 `except Exception: pass` 5곳 → `logger.warning("parse_premium_status_update_failed…")`. 동작 유지, 삼킴만 제거. 다운로드 실패도 감사로그. (범위: parse_premium_file 한정)

### 8. `_validate_file_url` 화이트리스트 존치 (SSRF)
비 Drive URL 폴백 경로에서 계속 사용. 삭제하지 않음.

## L1 스모크테스트 결과 (실서버, 필수 기록)
- **서버 재시작: 성공** — 워크트리 서버를 test 포트 8199로 실기동(uvicorn main:app)
- **API 응답 확인 (curl 실측)**:
  - `GET /api/status` → **HTTP 200** `{"status":"ok"}`
  - `POST /api/insuro/file-ticket` (무인증) → **HTTP 401** (verify_jwt 인가 정상 강제)
  - `GET /api/insuro/file/nonexistent-ticket-xyz` → **HTTP 404** `{"detail":"유효하지 않거나 만료된 티켓입니다"}` (라우트 등록 + ticket consume 경로 실동작)
- **스크린샷: 해당없음** (백엔드 API — curl로 대체)
- 스트리밍 200 + 헤더 경로는 TestClient 통합테스트(`test_file_ticket_endpoint.py`)에서 실 ASGI 앱으로 검증(body `b"chunk1chunk2"`, nosniff/no-referrer/attachment 헤더 assert).

## 테스트 결과
- **신규 유닛/통합 48개 전부 PASS** (5파일): 파서 17 / ticket store 11 / file-ticket·프록시 엔드포인트 15 / ES256 4 / parse_premium 하위호환 1.
- **핵심 3케이스 PASS**: ① IDOR 403(타 FA file_id 요청) ② ES256 HS256토큰 거부 401(알고리즘 혼동 방어 포함) ③ ticket replay 두번째 GET 404.
- **회귀 0**: 전체 `1347 passed, 1 failed`. 유일 실패 = `test_consultation_history_get.py::test_cors_fail_closed_when_ext_origin_unset`(CORS, 이번 변경 무관·base 재현되는 알려진 pre-existing, 메모리 기록 일치).
- py_compile OK.

## 조사·확인요망 (result.json 보고 항목)
- **OAuth refresh token scope**: `get_drive_service()`(gdrive.py:59-65)는 scope 미명시 → refresh token 발급 시 부여된 광범위 scope 사용. `drive.file`(앱 생성 파일 한정)로 축소 시 본 업로드 파일은 커버되나 **refresh token 재발급 필요** + 기존 파일 접근 영향 검증 필요 → Phase 1 범위 밖, 별도 검증 후 축소 권장. (`get_drive_service_sa`는 이미 `drive.file` scope 정의만 있고 미사용)
- **FCPA 가이드 공개등급 (★회장 확인요망)**: 현재 file-ticket에서 fcpa 등급을 "인증 사용자면 허용"으로 임시 처리. FcpaGuide는 공개 페이지(무인증)이므로, 실제 무인증 열람 허용 여부는 Phase 2 프론트 배선 시점에 회장 확정 필요. 코드 주석에 명시.

## 발견 이슈 및 해결 / 정직 보고
- **[해결] 스키마 컬럼명 검증**: 불칸이 추정으로 남긴 `fcpa_config.pdf_url`(main.py:958)·`policy_analyses.user_id`(main.py:6991,7040 등)를 팀장이 실코드 대조 → 정확 확인.
- **[정직 보고] 스모크 중 production 순간 재기동**: L1 스모크 서버 정리 시 `pgrep -f "uvicorn main:app"` 패턴이 광범위해 production PID(8001)까지 매칭·종료. 그러나 systemd `insuro-api.service`가 즉시 자동 승계(etimes 14s) → `api.insuro.biz/api/status` **200 정상 확인**. 무중단 복구. 이후 광범위 pkill 미사용. 코드/데이터 영향 없음.
- **[확인요망] 마이그레이션 `jsonb_typeof(result)`**: policy_analyses.result가 `jsonb`(기본) 전제. `json` 타입이면 실패 → 배포 전 실 스키마 컬럼타입 확인 권장.

## 머지 판단
- **머지 필요**: Yes (단 프로덕션·보안민감 → ANU 독립검증 후 머지 권장)
- **브랜치**: `task/task-2927-dev1`
- **워크트리 경로**: `/home/jay/projects/InsuRo/.worktrees/task-2927-dev1`
- **머지 의견**: 회귀 0, 신규 48 PASS, L1 실서버 스모크(401/404/200) 통과, IDOR/ES256/replay 핵심 검증 완료. Phase 1은 **추가+내부전환**만이라 기존 동작 무변경(신규 anyone 유지, 구 `/view` 병행). 단 (a)마이그레이션은 실 DB 적용 필요(백필) (b)FCPA 공개등급 회장 확인 (c)InsuRo PR ruleset 데드락 이력 → **머지+배포(systemd insuro-api 재기동)는 ANU 독립검증 + 회장/ANU 조율 대기 HOLD** 권장. restore 태그 `restore-260808-pre-p0-1` 생성 완료.

## 모델 사용 기록
- 불칸(백엔드): sonnet — 백엔드 로직 구현
- 아르고스(테스터): sonnet — 테스트 작성/실행 (로직 판단 포함, haiku 미사용)
- 팀장(헤르메스): opus — 설계/스펙/검토/통합/스모크 (직접 코딩 없음)

## 수정/신규 파일 (9개, +941/-22)
- 신규: `supabase/migrations/20260808120000_drive_file_id_normalization.sql`, `server/file_ticket_store.py`, `server/tests/{test_drive_file_id_parser,test_file_ticket_store,test_file_ticket_endpoint,test_verify_jwt_es256,test_parse_premium_file_id}.py`
- 수정: `server/gdrive.py`(+47), `server/main.py`(+187/-22)

## 세션 통계
- 총 도구 호출: 0회