피드로 돌아가기
Dev.toDatabase
원문 읽기
Postgres 최적화 기반의 8개 테이블 구성 Auth 시스템 설계
The 8 tables behind a real auth system (Postgres schema, explained column by column)
AI 요약
Context
인증 시스템 구축 시 발생하는 데이터 무결성 저해와 보안 취약점 및 성능 저하 문제를 해결하기 위한 DB 스키마 설계 분석. 특히 Case-insensitive 이메일 처리와 세션 토큰 유출 시의 리스크 관리 및 인덱스 효율성 확보에 집중함.
Technical Solution
- citext 확장 모듈 도입을 통한 이메일 대소문자 구분 없는 Unique 제약 조건 강제 및 로그인 버그 방지
- bigserial PK 채택으로 UUID 대비 인덱스 크기 최적화 및 B-tree Bloat 현상 억제
- 세션 및 토큰 저장 시 원문이 아닌 SHA-256 해시값을 저장하는 token_hash 구조를 통한 DB 유출 시 보안성 확보
- inet 타입을 활용한 IPv4/IPv6 통합 관리 및 텍스트 대비 저장 공간 최적화
- users 테이블의 password_hash를 Nullable로 설계하여 OAuth 전용 계정의 불필요한 Sentinel Value 제거
- ON DELETE CASCADE 설정을 통한 사용자 삭제 시 연관 데이터의 자동 제거 및 Orphan 데이터 방지
실천 포인트
1. 이메일 필드에 citext 적용 여부 검토
2. 세션/인증 토큰 저장 시 Plain Text가 아닌 Hash 값 저장 여부 확인
3. PK 선정 시 UUID와 BigInt의 인덱스 성능 및 스토리지 비용 비교
4. IP 주소 저장 시 text 대신 inet 타입 사용 고려
5. 사용자 삭제 시 데이터 정리를 위한 외래키 Cascade 설정 확인