앱 계정 권한 계약 — 표 33개·함수 26개에 남아 있던 앱 계정 권한에서 "권한은 정책·RPC 선언에서 따라온다" 규칙과 전수 검사까지 (2026-09-04)

• 기간: 2026-09-04 (세션 1개 — 98fe2f51 분석·Phase 0~3. 오너 결정 원문: "1. 전수 규칙 2. 응 삭제해줘 / go")
• 랜딩: PR 1268(Phase 0~2, e221490c, 마이그레이션 20260912010200_app_role_privileges_v1, CI 1회 초록) — Vercel 배포 없음(앱 코드 변경 없음). Production 적용은 릴리스 v0.17.0(#1227, 오너 승인 대기).
• 설계서: 없음 — 분석·계획·예상 효과는 이슈 #1258 계획 댓글(2026-09-04)
• 정본: docs/contracts/app-role-privileges.md(R1~R3·마이그레이션 작성 규칙) · pgTAP supabase/tests/database/app_role_privilege_rules_v1.test.sql · scripts/check-supabase-migrations.mjs APP_ROLE_GRANT_RULE_SINCE
• 도구: Management API 카탈로그 조회·마이그레이션 전문 드라이런(레포 밖, 세션 scratchpad prod_*.json·dryrun2.json)
• 게이트: pgTAP app_role_privilege_rules_v1(11 assert, 허용 목록 없음) · 앵커 tests/react/appRolePrivilegesContract.test.mjs(4) · 정적 검사 권한 선언 규칙 · 로컬 통과 기록: 검증: ci:local full · verify 통과 (5단계) · db reset(마이그레이션 전체 적용) 통과 · pgTAP 통과 105파일/1787 assert · e2e-local 통과 11/11 · e2e-empty 통과 7/7 · e2e-cardio 통과 6/6 · e2e-browser 통과 35/35 · e2e-viewport 통과 14/14 · 12분 23초 (2026-09-04 16:56 KST, main 합류 전 트리) · 합류·renumber 뒤 npm run check 통과 + db:preflight 105파일·1787 assert 통과(2026-09-04T08:00:34Z, 7a92fab7)
• 버그리포트: bug-report/bug-078-20260904.md
• 계약: docs/contracts/app-role-privileges.md(신설) · identity_passing_contract 내부 장부에 엔진 2개 추가

Phase 현황

Phase	내용	상태
Phase 0	계약 문서 + pgTAP 전수 검사(main 에서 9/11 빨간불로 실측과 일치)	✅ PR 1268
Phase 1	마이그레이션 — 기본 권한 닫기·규칙 회수·죽은 정책 25개 삭제·엔진 2개 service_role·자기 검증	✅ PR 1268
Phase 2	정적 검사(새 함수·drop 재생성 함수 권한 선언) + 앵커 테스트	✅ PR 1268
Phase 3	ci:local full → 랜딩 잠금·번호 재배정 → PR·CI 1회 → 릴리스 → 프로브 → 기록	✅ (e221490c) · 릴리스 v0.17.0 뒤 프로브 ✅ (2026-09-07)

1. 배경

이슈 #1252 가 schema.sql 을 실제 DB 덤프로 바꾸는 과정에서, 마이그레이션 텍스트에는 없는 GRANT ALL … TO "anon" 줄이 실제 DB 에 있는 것이 드러났다(#1258 함수 6개, #1259 표 5개). 이 세션이 Production 을 전수 조회하니 범위는 표 33개·함수 26개였고, 그중 하나는 앱 공개 키만으로 남의 데이터(PR 달성 횟수)를 읽을 수 있었다.

2. 문제 제기

기본 권한이 "새 객체는 앱 계정에 전부 준다"로 켜져 있었다

pg_default_acl 실측(소유자 postgres·스키마 public): 표 8권한·함수 실행·시퀀스 읽기/쓰기/사용이 anon·authenticated 에 자동 부여. 베이스라인 v2 머리(42~44행)가 끄지만 같은 파일 꼬리의 pg_dump 출력(31154행~)이 다시 켰다. 그래서 권한을 "빼는 것"이 작성자 몫이었고, 잊으면 조용히 전부 열렸다. drop 뒤 create 는 기본값으로 되돌아가고, revoke … from public 은 PUBLIC 만 회수한다.

의도와 실제를 대조하는 게이트가 없었다

기존 검사 4곳(internal_surface_grant_hygiene 트리거 3개·identity_passing_contract 등록부·schemaRls 함수 몇 개·service_role_readback)은 전부 이름 목록형 — 목록 밖 객체는 아무도 보지 않았다.

실측(Production 2026-09-04, v0.16.0 스키마)

앱 계정이 TRUNCATE·REFERENCES·TRIGGER·MAINTAIN 을 가진 표 33개(248건) · anon 표 권한 29개 표(anon 정책 0개) · 정책 없는 authenticated 쓰기 권한 10개 표 41건 · 죽은 정책 27개 · anon 실행 함수 26개 · 앱 계정이 부를 수 있는 트리거 함수 6개 · authenticated 실행 권한이 있는 SECURITY INVOKER 도우미 21개 · 시퀀스 1개. 앱 공개 키로 user_pr_event_count_v1(아무 uuid) → HTTP 200.

3. 해결 방안

원칙 (오너 결정 D1·D2, 2026-09-04)

• D1 "전수 규칙" — 이슈에 적힌 11개만 고치는 안(A)은 기각. 규칙 R1~R3 을 계약으로 두고 기계가 회수·검사한다.
• D2 "응 삭제해줘" — 정책은 있는데 권한이 없어 죽어 있던 정책 25개(main 기준)를 같은 마이그레이션에서 이름으로 삭제.

접근

안	판단
A. 표 5개·함수 6개만 revoke	기각 — 나머지 28개 표·20개 함수가 남고 다음 drop·create table 에서 재발
B. 규칙 + 카탈로그 순회 회수 + pgTAP 전수 검사	채택 — 새 객체는 닫힌 채 태어나고, 잊으면 42501 로 드러나며, 검사는 이름 목록 없이 전수
C. B + 정적 검사	채택(보조) — PR 전 조기 경고

규칙: R1 기본 권한에 앱 계정·PUBLIC 없음(service_role 은 종전대로) · R2 표 권한(열 단위 포함) ⇔ 그 역할·그 명령의 RLS 정책, TRUNCATE 류 절대 없음, 시퀀스 없음, 뷰는 security_invoker + SELECT 만, 죽은 정책 없음 · R3 anon·PUBLIC 실행 0, 트리거 함수 앱 실행 불가, authenticated 는 SECURITY DEFINER 또는 정책·제약·기본값·인덱스·뷰가 pg_depend 로 참조하는 함수만.

4. 적용한 내용

Phase 0 — 계약 + 전수 검사 (PR 1268)

docs/contracts/app-role-privileges.md · pgTAP app_role_privilege_rules_v1.test.sql(11 규칙, 각 단언이 위반 객체를 이름으로 나열). main 샌드박스에서 9/11 빨간불(anon 함수 29·TRUNCATE 류 229건·정책 없는 권한 130건·죽은 정책 25·시퀀스 2·뷰 14·트리거 함수 8·invoker 도우미 23).

Phase 1 — 마이그레이션 20260912010200_app_role_privileges_v1 (PR 1268)

① alter default privileges for role postgres in schema public revoke all on tables|sequences|functions from public, anon, authenticated ② DO 블록이 pg_catalog 를 돌며 R2·R3 대로 회수(이름 목록 없음, 권한을 새로 주는 문장 없음) ③ 엔진 user_pr_event_count_v1·apply_training_attendance_policy_v1 는 service_role 전용 ④ 죽은 정책 25개를 (표, 정책) 값 목록으로 삭제 — to_regclass 로 표가 없는 환경(#1215 개명 전)은 건너뜀 ⑤ 끝에 같은 규칙으로 자기 검증, 위반이 남으면 raise. 적용 뒤 검사 11/11 초록, pgTAP 105파일 1,787건 통과.

Phase 2 — 정적 검사 + 앵커 (PR 1268)

check-supabase-migrations.mjs: APP_ROLE_GRANT_RULE_SINCE 이후 파일에서 (a) 같은 파일에서 drop function 뒤 다시 만드는 함수 (b) 이전 파일 어디에도 없던 새 함수는 같은 파일에 grant|revoke … on function public.F( 가 있어야 통과(returns trigger 제외). 음성 시험: 새 함수·drop 재생성은 거부, 트리거·선언된 함수는 통과. 앵커 tests/react/appRolePrivilegesContract.test.mjs.

주요 결정과 그 근거

• 권한을 정책에서 유도: 권한과 정책이 따로 놀면 어느 쪽도 믿을 수 없다. 열 단위 권한(profiles 6개 열)도 같은 규칙으로 대조해야 죽은 정책으로 오판하지 않는다.
• SECURITY DEFINER 는 손대지 않음: R3 은 SECURITY DEFINER 를 RPC 문으로 보고 authenticated 실행을 허용한다 — "문 안에서 누구 것을 다루는가"는 #1033 신원 계약의 몫.
• 회수만, 부여 없음: 마이그레이션은 뺄 것만 뺀다. 필요한 권한이 빠져 있었다면 e2e·pgTAP 가 드러낸다(이번엔 0건).
• 죽은 정책은 이름으로: 자동 삭제 규칙은 "grant 를 잊은 새 정책"까지 지울 수 있어 사람이 읽고 목록으로 지운다. 검사는 앞으로의 죽은 정책을 잡아 사람에게 넘긴다.

작업 중 드러난 것

• 이슈의 6개 함수 중 3개(exercise_synonym_search_text_v1·validated_entry_*)는 "잔여"가 아니라 #1175 가 명시로 anon 에게 준 것 — 어느 쪽이든 규칙으로 회수.
• "char" 형 열(defaclobjtype·polcmd)은 ::text 없이 || 하면 42725.
• drop policy if exists 는 표가 없으면 42P01 — Production(#1215 개명 전) 드라이런에서 발견, to_regclass 가드로 수정.
• 과다 회수로 깨진 pgTAP 2파일: profile_handle_v1(도우미를 authenticated 로 직접 호출 → postgres 로)·session_hierarchy_layers_v1(층 표 정책 4개 → 읽기 1개). 앱 경로 회귀 0건.
• SECURITY DEFINER 이면서 authenticated 가 부를 수 있는데 본문에서 신원을 읽지 않는 함수 15개(plan_owner_json_v1·profile_feed_can_view_v1 등 정책 도우미 다수) — #1033 후속 검토 후보.
• CI 1회차 browser-journeys 샤드 1이 Playwright 18/18 통과를 찍고도 스텝이 실패로 끝남(로그에 오류 없음) — 재실행 초록. 로컬로 재현 불가(CI 환경 차이)로 분류.
• 랜딩 잠금 대기: 같은 시간대에 #1215 후속(#1266)·#1235(#1265)·#1252(#1263) 이 줄을 섰다.

5. 적용 결과

항목	결과
앱 공개 키로 user_pr_event_count_v1 호출	200 → 401 permission denied for function(Production, 2026-09-07)
anon·PUBLIC 실행 가능 함수	26 → 0 (드라이런 0, Production 프로브 0 — 2026-09-07)
앱 계정 TRUNCATE·REFERENCES·TRIGGER·MAINTAIN	33개 표 248건 → 0
정책 없는 앱 계정 권한 / 죽은 정책	41(+anon 106) / 27 → 0 / 0
트리거 함수 앱 실행 · invoker 도우미 authenticated	6 · 21 → 0 · 2(제약 참조)
기본 권한에 앱 계정	표·시퀀스·함수 → 없음
pgTAP · npm run check · ci:local	105파일 1,787 · 통과 · 검증: ci:local full · verify 통과 (5단계) · db reset(마이그레이션 전체 적용) 통과 · pgTAP 통과 105파일/1787 assert · e2e-local 통과 11/11 · e2e-empty 통과 7/7 · e2e-cardio 통과 6/6 · e2e-browser 통과 35/35 · e2e-viewport 통과 14/14 · 12분 23초 (2026-09-04 16:56 KST, main 합류 전 트리) · 합류·renumber 뒤 npm run check 통과 + db:preflight 105파일·1787 assert 통과(2026-09-04T08:00:34Z, 7a92fab7)
Production 적용(릴리스 v0.17.0, 2026-09-06 머지)	프로브 2026-09-07: anon 실행 가능 함수 0 · 앱 계정 TRUNCATE·REFERENCES·TRIGGER·MAINTAIN 0건 · 공개 키 호출 401

6. 이번 개선으로 향상된 것

로그인 없이 남의 데이터를 읽던 경로가 닫혔다

앱 공개 키로 부를 수 있는 함수가 0개가 된다. 신원 확인 없는 엔진은 서버 계정만 부른다.

새 표·함수가 닫힌 채 태어난다

기본 권한이 닫혀 "권한 선언을 잊으면 열린다"가 "잊으면 거부된다"로 뒤집혔다. 정적 검사가 PR 전에 알려 준다.

구조적으로 남는 것

계약 app-role-privileges.md, pgTAP 전수 검사(CI migration-smoke·ci:local·db:preflight), 정적 검사 규칙, identity_passing 내부 장부 2개 추가. #1252 실제 덤프와 합치면 "덤프가 보여주고 규칙이 판정한다".

남은 것

• SECURITY DEFINER + authenticated + 신원 미확인 문 15개 — #1033 계약 후속(별도 이슈 여부는 오너 판단).
• APP_ROLE_GRANT_RULE_SINCE 는 이 마이그레이션 번호와 같다 — 번호 재배정 시 함께 바뀌었는지 확인(이번 랜딩에서 확인: renumber 가 20260912010200 으로 함께 갱신함, 확인).