마스킹된 프로덕션 DB로 E2E 테스트 돌리기 — Playwright + Neon 브랜칭 파이프라인 구축기

SECURITY LABEL FOR anon ON COLUMN users.name
  IS 'MASKED WITH FUNCTION anon.fake_last_name()';
 
SECURITY LABEL FOR anon ON COLUMN users.email
  IS 'MASKED WITH FUNCTION anon.fake_email()';
 
SECURITY LABEL FOR anon ON COLUMN users.phone
  IS 'MASKED WITH FUNCTION anon.partial(phone, 3, $$***$$, 0)';

name: E2E Tests on Masked Preview DB
on:
  pull_request:
    types: [opened, synchronize, reopened, closed]
 
jobs:
  e2e-test:
    if: github.event.action != 'closed'
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
 
      # 1단계: Neon 브랜치 생성 (CoW로 프로덕션 DB 복제)
      - name: Create Neon Branch
        id: create-branch
        uses: neondatabase/create-branch-action@v5
        with:
          project_id: ${{ vars.NEON_PROJECT_ID }}
          api_key: ${{ secrets.NEON_API_KEY }}
          branch_name: preview/pr-${{ github.event.number }}
 
      # 2단계: 마스킹 규칙 적용 후 익명화 실행
      - name: Anonymize PII
        run: |
          psql ${{ steps.create-branch.outputs.db_url }} -f ./sql/masking-rules.sql
          psql ${{ steps.create-branch.outputs.db_url }} -c "SELECT anon.anonymize_database();"
 
      # 3단계: 스키마 마이그레이션 (현재 브랜치의 변경사항 반영)
      - name: Apply Migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ steps.create-branch.outputs.db_url }}
 
      # 4단계: Preview 환경 배포
      - name: Deploy Preview
        id: deploy-preview
        uses: amondnet/vercel-action@v25
        with:
          vercel-token: ${{ secrets.VERCEL_TOKEN }}
          vercel-org-id: ${{ secrets.VERCEL_ORG_ID }}
          vercel-project-id: ${{ secrets.VERCEL_PROJECT_ID }}
          scope: ${{ secrets.VERCEL_ORG_ID }}
        env:
          DATABASE_URL: ${{ steps.create-branch.outputs.db_url }}
 
      # 5단계: Playwright 설치 및 E2E 테스트 실행
      - name: Install Playwright
        run: npx playwright install --with-deps chromium
 
      - name: Run E2E Tests
        run: npx playwright test
        env:
          DATABASE_URL: ${{ steps.create-branch.outputs.db_url }}
          BASE_URL: ${{ steps.deploy-preview.outputs.preview-url }}
 
      # 6단계: 스키마 diff를 PR 코멘트로 남기기
      - name: Schema Diff
        if: always()
        uses: neondatabase/schema-diff-action@v1
        with:
          project_id: ${{ vars.NEON_PROJECT_ID }}
          api_key: ${{ secrets.NEON_API_KEY }}
 
      - uses: actions/upload-artifact@v4
        if: failure()
        with:
          name: playwright-report
          path: playwright-report/
 
  # PR이 닫히면 브랜치 자동 정리
  cleanup:
    if: github.event.action == 'closed'
    runs-on: ubuntu-latest
    steps:
      - uses: neondatabase/delete-branch-action@v3
        with:
          project_id: ${{ vars.NEON_PROJECT_ID }}
          api_key: ${{ secrets.NEON_API_KEY }}
          branch: preview/pr-${{ github.event.number }}

-- sql/masking-rules.sql
CREATE EXTENSION IF NOT EXISTS anon CASCADE;
SELECT anon.init();
 
-- users 테이블
SECURITY LABEL FOR anon ON COLUMN users.name
  IS 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL FOR anon ON COLUMN users.email
  IS 'MASKED WITH FUNCTION anon.fake_email()';
SECURITY LABEL FOR anon ON COLUMN users.phone
  IS 'MASKED WITH FUNCTION anon.partial(phone, 3, $$***$$, 0)';
 
-- orders 테이블 — 배송지 주소 마스킹
SECURITY LABEL FOR anon ON COLUMN orders.shipping_address
  IS 'MASKED WITH FUNCTION anon.fake_city() || $$ $$ || anon.fake_last_name() || $$ Street$$';
 
-- payments 테이블 — 카드번호 부분 마스킹
SECURITY LABEL FOR anon ON COLUMN payments.card_number
  IS 'MASKED WITH FUNCTION anon.partial(card_number, 0, $$****-****-****-$$, 4)';

name: E2E Tests with Greenmask
on:
  pull_request:
    types: [opened, synchronize, reopened]
 
jobs:
  e2e-test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_db
          POSTGRES_USER: test_user
          POSTGRES_PASSWORD: test_pass
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
 
    steps:
      - uses: actions/checkout@v4
 
      # 0단계: Greenmask 설치
      - name: Install Greenmask
        run: |
          curl -sSL https://github.com/GreenmaskIO/greenmask/releases/latest/download/greenmask-linux-amd64 \
            -o /usr/local/bin/greenmask
          chmod +x /usr/local/bin/greenmask
 
      # 1단계: Greenmask로 프로덕션 read replica에서 덤프 + 마스킹 동시 수행
      - name: Dump & Mask Production Data
        run: |
          greenmask dump \
            --source "host=${{ secrets.PROD_REPLICA_HOST }} dbname=prod_db user=readonly" \
            --config greenmask-config.yml \
            --output masked-dump.sql
 
      # 2단계: 마스킹된 데이터를 테스트 컨테이너에 복원
      - name: Restore Masked Data
        run: |
          psql postgresql://test_user:test_pass@localhost:5432/test_db \
            < masked-dump.sql
 
      # 3단계: 마이그레이션 + 테스트
      - name: Apply Migrations & Run Tests
        run: |
          npx prisma migrate deploy
          npx playwright install --with-deps chromium
          npx playwright test
        env:
          DATABASE_URL: postgresql://test_user:test_pass@localhost:5432/test_db

# greenmask-config.yml
tables:
  - name: users
    columns:
      - name: name
        transformer:
          name: RandomPerson
          params:
            seed: 42
      - name: email
        transformer:
          name: RandomEmail
          params:
            seed: 42
      - name: phone
        transformer:
          name: Mask
          params:
            characters_to_reveal: 3
            mask_char: "*"
 
  - name: orders
    columns:
      - name: shipping_address
        transformer:
          name: RandomAddress
          params:
            seed: 42

// playwright/e2e/user-list.spec.ts
import { test, expect } from '@playwright/test';
 
test.describe('사용자 목록 페이지', () => {
  test('프로덕션 규모의 데이터가 올바르게 페이지네이션 되는지', async ({ page }) => {
    await page.goto('/admin/users');
 
    const totalCount = page.locator('[data-testid="total-count"]');
    await expect(totalCount).toHaveText(/\d{1,3}(,\d{3})*/);
 
    // 이메일 컬럼이 마스킹되어도 이메일 형식은 유지되는지 확인
    const firstEmail = page.locator('table tbody tr:first-child td.email');
    await expect(firstEmail).toContainText('@');
 
    // 페이지네이션이 대규모 데이터에서 정상 동작하는지
    await page.click('[data-testid="next-page"]');
    await expect(page.locator('table tbody tr')).toHaveCount(20);
  });
});

test('검색 기능이 마스킹된 데이터에서도 동작하는지', async ({ page }) => {
  await page.goto('/admin/users');
 
  // 결정론적 마스킹이므로 시드 42 기준 원본 '홍길동' → 'Smith'로 항상 변환됨
  // 이 값은 마스킹 시드와 원본 데이터가 고정되어 있으므로 안정적
  await page.fill('[data-testid="search-input"]', 'Smith');
  await page.click('[data-testid="search-button"]');
 
  const results = page.locator('table tbody tr');
  await expect(results).not.toHaveCount(0);
});