RLS 고급 패턴과 보안 강화

입문편 7강에서 CREATE POLICY와 auth.uid()로 행 단위 접근 제어의 기초를 다졌습니다. 하지만 실서비스는 "본인 행만 읽기"보다 훨씬 복잡한 요구사항을 가집니다. 관리자·편집자·뷰어처럼 역할이 나뉘고, 여러 조직이 동일한 테이블을 공유하는 멀티테넌트 구조가 필요하며, 정책이 늘어날수록 쿼리 성능에 영향을 줍니다. 그리고 service_role 키를 잘못 사용하면 모든 정책이 한순간에 무력화됩니다.

이 레슨에서는 그 "한 단계 깊은" 보안 설계를 다룹니다. 정책이 왜 그렇게 작성되어야 하는지, 어떤 함정이 숨어 있는지, 실무에서 어떤 패턴이 검증되었는지에 집중합니다.

학습 목표

• auth.jwt()와 커스텀 클레임으로 역할 기반 접근 제어(RBAC)를 구현하는 원리를 이해한다.
• SECURITY DEFINER 헬퍼 함수로 정책 로직을 재사용하고 복잡도를 낮추는 패턴을 적용할 수 있다.
• 다중 테넌시(멀티테넌트) 시나리오에서 조직·팀 단위 데이터 격리 정책을 설계한다.
• USING / WITH CHECK 분리와 인덱스 전략으로 RLS 성능 저하를 예방한다.
• service_role 우회의 위험성과 안전한 서버 측 사용 방법을 파악하고, 정책 누락·권한 escalation 시나리오를 테스트한다.

auth.jwt()와 커스텀 클레임으로 RBAC 구현

JWT 클레임에 역할 정보 심기

auth.uid()는 사용자 ID만 반환하므로 역할(role) 판단을 위해서는 추가 정보가 필요합니다. Supabase는 JWT 페이로드에 임의의 키-값을 삽입할 수 있는 커스텀 클레임(custom claims) 기능을 제공합니다. 가장 일반적인 방법은 app_metadata 필드를 사용하는 것입니다. app_metadata는 서버(service_role)만 수정할 수 있어 클라이언트가 위조하지 못합니다.

-- service_role로 사용자에게 admin 역할 부여 (예: Edge Function 내부)
-- auth.users 테이블의 raw_app_meta_data를 직접 수정
update auth.users
set raw_app_meta_data = raw_app_meta_data || '{"role": "admin"}'::jsonb
where id = '사용자-UUID';

토큰이 재발급되면 JWT에 role: "admin"이 포함됩니다. 정책 안에서는 auth.jwt()로 이 값을 꺼낼 수 있습니다.

-- ✅ JWT app_metadata에서 role 클레임 읽기
create policy "관리자만 모든 게시글 조회"
on posts for select
using (
  (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
);

-- ✅ 일반 사용자는 본인 글만, 관리자는 전체 허용
create policy "본인 글 또는 관리자"
on posts for select
using (
  auth.uid() = author_id
  or (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin'
);

⚠️ 주의 — user_metadata는 사용자가 직접 수정할 수 있으므로 보안 판단에 사용하면 안 됩니다. 역할·권한 정보는 반드시 app_metadata에 저장하세요.

역할 테이블과 JWT 클레임 비교

방식	장점	단점
JWT 커스텀 클레임	정책에서 DB 조인 없이 빠름	토큰 만료 전까지 변경 즉시 반영 안 됨
별도 roles 테이블	실시간 반영 가능	모든 정책에서 서브쿼리 비용 발생
혼합(클레임 + 테이블)	정밀 제어 + 성능 균형	관리 복잡도 증가

역할 변경이 드물고 보안이 중요한 경우에는 JWT 클레임이, 권한이 실시간으로 자주 바뀌어야 하는 경우에는 roles 테이블이 적합합니다.

SECURITY DEFINER 헬퍼 함수로 정책 재사용

문제: 정책마다 동일한 서브쿼리 반복

테이블이 10개 이상이면 비슷한 권한 로직이 수십 개 정책에 복사됩니다. 로직이 바뀌면 모든 정책을 찾아 수정해야 합니다.

-- ❌ 동일한 역할 체크가 정책마다 반복됨
create policy "관리자 접근 posts"
on posts for select
using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin');

create policy "관리자 접근 comments"
on comments for select
using ((auth.jwt() -> 'app_metadata' ->> 'role') = 'admin');
-- ... 테이블마다 동일 패턴 반복

해결: SECURITY DEFINER 헬퍼 함수

SECURITY DEFINER로 정의된 함수는 호출자가 아닌 함수 소유자(보통 postgres)의 권한으로 실행됩니다. 정책 안에서 이 함수를 호출하면 복잡한 권한 로직을 한 곳에서 관리할 수 있습니다.

-- ✅ 역할 체크 헬퍼 함수 (한 번만 정의)
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin';
$$;

-- ✅ 조직 멤버 여부 체크 헬퍼
create or replace function public.is_org_member(org_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from org_members
    where organization_id = org_id
      and user_id = auth.uid()
  );
$$;

이제 정책은 깔끔한 단일 함수 호출로 단순해집니다.

-- ✅ 헬퍼 함수를 활용한 간결한 정책
create policy "관리자 또는 본인"
on posts for select
using (
  public.is_admin()
  or auth.uid() = author_id
);

create policy "조직 멤버만 문서 조회"
on documents for select
using (
  public.is_org_member(organization_id)
);

⚠️ 주의 — SECURITY DEFINER 함수는 반드시 set search_path = public (또는 명시적 스키마)을 선언해야 합니다. 그렇지 않으면 search_path 인젝션 취약점에 노출됩니다.

💡 TIP — 헬퍼 함수에 stable 속성을 붙이면 같은 쿼리 내에서 함수 결과를 캐시할 수 있어 성능에 유리합니다. 같은 트랜잭션 내에서 사용자 역할이 바뀌지 않는다면 stable이 적절합니다.

다중 테넌시 데이터 격리 설계

멀티테넌트 스키마 패턴

SaaS 제품에서 여러 팀(조직)이 동일한 테이블을 공유하면서도 서로의 데이터를 볼 수 없어야 합니다. 가장 흔한 패턴은 모든 테이블에 organization_id 컬럼을 두고, 멤버십 테이블로 사용자-조직 관계를 관리하는 것입니다.

-- 조직 및 멤버십 테이블
create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

create table org_members (
  organization_id uuid references organizations(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  role text not null check (role in ('owner', 'admin', 'member', 'viewer')),
  primary key (organization_id, user_id)
);

-- 비즈니스 데이터 테이블
create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid references organizations(id) on delete cascade,
  name text not null,
  created_by uuid references auth.users(id)
);

alter table projects enable row level security;

멤버십 기반 격리 정책

-- ✅ 소속 조직의 프로젝트만 조회
create policy "조직 멤버만 프로젝트 조회"
on projects for select
using (
  public.is_org_member(organization_id)
);

-- ✅ owner·admin만 프로젝트 생성 가능
create policy "관리자만 프로젝트 생성"
on projects for insert
with check (
  exists (
    select 1 from org_members
    where organization_id = projects.organization_id
      and user_id = auth.uid()
      and role in ('owner', 'admin')
  )
);

-- ✅ 생성자 또는 조직 관리자만 수정
create policy "생성자 또는 관리자 수정"
on projects for update
using (
  auth.uid() = created_by
  or exists (
    select 1 from org_members
    where organization_id = projects.organization_id
      and user_id = auth.uid()
      and role in ('owner', 'admin')
  )
)
with check (
  -- with check는 수정 후(NEW) 행 값만 볼 수 있으므로,
  -- NEW 값만으로 검증 가능한 조건만 여기에 둔다.
  -- 수정 후에도 변경 대상 조직의 멤버여야 함을 보장
  public.is_org_member(organization_id)
);

⚠️ 주의 — RLS의 with check / using 표현식은 수정 후(NEW) 행 값만 참조할 수 있고 수정 전(OLD) 값은 볼 수 없습니다. 따라서 "변경 전 organization_id와 같아야 한다"는 식의 불변성 검사는 정책만으로는 구현할 수 없습니다. organization_id 같은 불변 필드를 보호하려면 아래처럼 BEFORE UPDATE 트리거에서 NEW.organization_id <> OLD.organization_id를 비교해 예외를 던져야 합니다.

-- ✅ organization_id 변조(테넌트 이동)를 차단하는 트리거
create or replace function public.prevent_org_change()
returns trigger
language plpgsql
as $$
begin
  if new.organization_id is distinct from old.organization_id then
    raise exception '테넌트 이동(organization_id 변경)은 허용되지 않습니다';
  end if;
  return new;
end;
$$;

create trigger projects_prevent_org_change
before update on projects
for each row
execute function public.prevent_org_change();

초대 기반 접근 패턴

조직에 아직 가입하지 않은 사용자에게 토큰 기반 초대장을 보내는 경우, 초대 테이블에도 RLS가 필요합니다.

create table invitations (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid references organizations(id),
  email text not null,
  token text unique not null,
  invited_by uuid references auth.users(id),
  accepted_at timestamptz
);

alter table invitations enable row level security;

-- 초대를 보낸 관리자 또는 초대받은 이메일의 사용자만 조회
create policy "초대 조회"
on invitations for select
using (
  invited_by = auth.uid()
  or email = (select email from auth.users where id = auth.uid())
);

RLS 정책의 성능 영향과 최적화

정책이 쿼리 플랜에 미치는 영향

RLS 정책의 USING 조건은 실행 시 WHERE 절로 합성됩니다. 정책이 서브쿼리를 포함하면 원래 쿼리의 모든 행 접근마다 서브쿼리가 평가될 수 있습니다. EXPLAIN ANALYZE로 확인하는 습관이 중요합니다.

-- 정책이 적용된 쿼리의 실행 계획 확인
explain analyze
select * from projects where name ilike '%검색어%';

플랜에 Filter: (is_org_member(organization_id)) 같은 항목이 보이면 함수가 행마다 호출됩니다. 이를 줄이기 위한 두 가지 전략을 살펴봅니다.

인덱스 전략

정책의 USING 조건에 등장하는 컬럼에는 반드시 인덱스가 있어야 합니다.

-- ✅ 정책 조건 컬럼에 인덱스 추가
create index on projects (organization_id);
create index on org_members (organization_id, user_id);
create index on org_members (user_id);  -- user_id 단독 조회용

-- ✅ 복합 정책 조건 (user_id + role)에 부분 인덱스
create index on org_members (organization_id, user_id)
where role in ('owner', 'admin');

USING과 WITH CHECK 분리 최적화

UPDATE 정책에서 USING은 "수정 대상 행 필터"이고, WITH CHECK는 "수정 결과 검증"입니다. 이 둘을 명확히 분리하면 Postgres가 두 단계를 각각 최적화할 수 있습니다.

-- ❌ USING만 있는 UPDATE 정책 (with check 누락)
-- 수정 결과가 USING 조건을 벗어나도 차단 안 됨
create policy "본인 글 수정 (불완전)"
on posts for update
using (auth.uid() = author_id);

-- ✅ USING + WITH CHECK 모두 명시
create policy "본인 글 수정 (안전)"
on posts for update
using (auth.uid() = author_id)       -- 어떤 행을 수정할 수 있는지
with check (auth.uid() = author_id); -- 수정 후에도 조건을 유지해야 함

💡 TIP — WITH CHECK가 없는 UPDATE 정책에서 사용자가 author_id를 다른 사람의 ID로 바꾸면, USING은 통과하지만 변경 후 행은 자신에게 보이지 않게 됩니다. 이는 논리적 버그일 뿐 아니라 특정 시나리오에서 권한 escalation으로 이어질 수 있습니다.

정책 캐싱과 stable 함수 활용

Postgres는 STABLE 또는 IMMUTABLE로 표시된 함수를 단일 쿼리 내에서 한 번만 평가하도록 최적화할 수 있습니다. auth.uid()와 auth.jwt()는 이미 STABLE로 정의되어 있어 행마다 재호출되지 않습니다. 커스텀 헬퍼 함수도 동일하게 선언하세요.

-- ✅ stable로 선언된 헬퍼 — 같은 쿼리 내에서 결과 재사용
create or replace function public.current_user_role()
returns text
language sql
stable          -- 이 키워드가 캐싱 힌트
security definer
set search_path = public
as $$
  select coalesce(
    auth.jwt() -> 'app_metadata' ->> 'role',
    'authenticated'
  );
$$;

service_role 우회의 위험과 안전한 사용

service_role 키란 무엇인가

service_role 키로 생성된 Supabase 클라이언트는 RLS를 완전히 무시합니다. 모든 테이블의 모든 행에 읽기·쓰기가 가능합니다. 이 키는 서버 사이드(Edge Function, 백엔드 API)에서만 사용해야 하며, 절대로 클라이언트 코드나 버전 관리 시스템에 노출되어서는 안 됩니다.

// ❌ 클라이언트 코드에 service_role 사용 — 절대 금지
const supabase = createClient(URL, SERVICE_ROLE_KEY); // 이 키가 브라우저에 노출됨

// ✅ Edge Function (서버) 내부에서만 사용
// Deno.env.get() 으로 환경 변수에서 읽음
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

const adminClient = createClient(
  Deno.env.get('SUPABASE_URL')!,
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
);

// 관리 작업 — RLS 우회가 의도된 경우에만
const { data } = await adminClient
  .from('org_members')
  .insert({ organization_id: orgId, user_id: newUserId, role: 'member' });

꼭 필요한 우회는 명시적 주석으로

service_role 클라이언트를 사용하는 코드에는 "왜 RLS를 우회하는지"를 명시적으로 문서화하세요. 코드 리뷰 시 의도치 않은 우회를 조기에 발견하는 데 도움이 됩니다.

supabase-js v2 쿼리 빌더에는 .group() 메서드가 없습니다. 조직별 집계가 필요하다면 데이터베이스 뷰나 RPC 함수를 만들어 호출하는 방식이 안정적입니다.

-- ✅ 조직별 프로젝트 수를 집계하는 RPC 함수
create or replace function public.projects_count_by_org()
returns table (organization_id uuid, project_count bigint)
language sql
stable
as $$
  select organization_id, count(*) as project_count
  from projects
  group by organization_id;
$$;

// ✅ 의도적 우회는 이유를 명시
// NOTE: RLS bypass intentional — this function runs as a system job
// and needs to aggregate data across all organizations.
const { data: countsByOrg } = await adminClient
  .rpc('projects_count_by_org');
// 결과 예시: [{ organization_id: '...', project_count: 12 }, ...]

⚠️ 주의 — service_role 키가 유출되면 모든 RLS 정책이 무의미해집니다. 키가 노출되었다고 의심된다면 Supabase 대시보드 Settings → API에서 즉시 키를 재생성하세요.

보안 점검: 정책 누락 탐지와 권한 escalation 테스트

정책 없는 테이블 찾기

RLS가 켜졌지만 정책이 하나도 없는 테이블은 모든 접근이 차단된 상태입니다. 반대로 RLS 자체가 꺼진 테이블은 무방비로 열려 있습니다. 다음 쿼리로 두 가지를 동시에 점검합니다.

-- RLS가 꺼진 테이블 조회 (public 스키마)
select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public'
  and rowsecurity = false;

-- RLS는 켜졌으나 정책이 0개인 테이블
select t.schemaname, t.tablename
from pg_tables t
where t.schemaname = 'public'
  and t.rowsecurity = true
  and not exists (
    select 1 from pg_policies p
    where p.schemaname = t.schemaname
      and p.tablename = t.tablename
  );

-- 테이블별 정책 목록 전체 조회
select schemaname, tablename, policyname, cmd, roles, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

권한 escalation 시나리오 테스트

실제 사용자 토큰을 흉내 내어 정책이 의도대로 동작하는지 확인합니다. set local role + set local "request.jwt.claims"로 특정 사용자를 가장(impersonate)하는 테스트를 작성할 수 있습니다.

-- 테스트용 사용자 컨텍스트 설정 (트랜잭션 내부에서만 유효)
begin;

-- 일반 사용자 역할로 전환
set local role authenticated;
set local "request.jwt.claims" = '{"sub": "테스트-사용자-UUID", "role": "authenticated", "app_metadata": {"role": "member"}}';

-- 이 쿼리가 빈 결과를 반환해야 함 (다른 조직 데이터 접근 시도)
select * from projects where organization_id = '다른-조직-UUID';

-- 관리자 역할로 전환
set local "request.jwt.claims" = '{"sub": "테스트-사용자-UUID", "role": "authenticated", "app_metadata": {"role": "admin"}}';

-- 이 쿼리는 모든 행이 보여야 함
select count(*) from projects;

rollback; -- 항상 rollback으로 마무리

💡 TIP — CI 파이프라인에 위와 같은 RLS 테스트 스크립트를 포함하면, 스키마 변경 시 정책 회귀(regression)를 자동으로 감지할 수 있습니다. pgTAP 확장을 사용하면 SQL 단위 테스트를 체계적으로 관리할 수 있습니다.

공통 보안 점검 체크리스트

항목	확인 방법
모든 public 테이블에 RLS 활성화	pg_tables 조회
정책 없는 RLS 테이블 없음	pg_policies 조회
INSERT 정책에 WITH CHECK 존재	pg_policies.with_check 확인
UPDATE 정책에 USING + WITH CHECK 모두 존재	정책 DDL 검토
service_role 키가 클라이언트 코드에 없음	코드 및 환경 변수 감사
app_metadata로만 역할 판단	정책 코드 리뷰
헬퍼 함수에 set search_path 선언	함수 DDL 검토

요약

• JWT의 app_metadata에 역할 정보를 담고 auth.jwt()로 읽으면, DB 조인 없이 빠른 RBAC를 구현할 수 있습니다. user_metadata는 클라이언트가 수정 가능하므로 권한 판단에 사용하지 않습니다.
• SECURITY DEFINER 헬퍼 함수로 공통 권한 로직을 캡슐화하면 정책 유지보수 비용이 크게 줄어들며, 반드시 set search_path를 선언해 인젝션을 방지해야 합니다.
• 멀티테넌트 설계에서 organization_id 같은 불변 필드 보호는 정책의 WITH CHECK만으로는 불가능합니다(정책은 NEW 값만 보고 OLD 값을 참조할 수 없음). BEFORE UPDATE 트리거에서 NEW/OLD 값을 비교해 테넌트 이동을 차단하고, WITH CHECK에는 NEW 값만으로 검증 가능한 조건만 둡니다.
• 정책 조건에 등장하는 컬럼에 인덱스를 추가하고, 헬퍼 함수를 STABLE로 선언하면 RLS로 인한 성능 저하를 최소화할 수 있습니다.
• service_role 키는 절대 클라이언트에 노출하지 않으며, 사용 시 의도를 주석으로 명시합니다. 유출 시 즉시 재생성이 필요합니다.
• pg_tables와 pg_policies 쿼리로 정책 누락을 주기적으로 점검하고, 트랜잭션 내 역할 전환으로 escalation 시나리오를 자동화 테스트합니다.

연습문제

1. 다음 정책에는 보안 취약점이 있습니다. 무엇이 문제이며 어떻게 수정해야 합니까?

   create policy "멤버 역할 확인"
   on posts for select
   using (
     (auth.jwt() -> 'user_metadata' ->> 'role') = 'editor'
   );
   

2. documents 테이블에 organization_id uuid 컬럼이 있습니다. 조직 멤버만 문서를 읽고, owner·admin 역할만 삭제할 수 있는 두 개의 정책을 작성하세요. org_members(organization_id, user_id, role) 테이블을 활용하세요.

3. 아래 UPDATE 정책의 문제점을 설명하고, USING과 WITH CHECK를 모두 포함한 올바른 정책으로 수정하세요.

   create policy "본인 문서 수정"
   on documents for update
   using (created_by = auth.uid());
   

4. 현재 데이터베이스에서 RLS가 활성화되지 않은 public 스키마 테이블 목록을 반환하는 SQL을 작성하세요.

힌트 — 1번은 user_metadata와 app_metadata의 수정 권한 차이를 생각하세요. 3번은 organization_id 컬럼 변조 시나리오를 떠올리세요.