대상 OS: Ubuntu Server 24.04 LTS
PostgreSQL 권한 설계가 느슨하면, 앱 계정 하나가 사실상 DBA가 된다.
운영에서 자주 쓰는 최소 권한(role 분리) 패턴과 실수 포인트를 정리한다. (작성 시각: 2026-02-25 13:08)
1) 역할을 목적별로 분리(읽기/쓰기/마이그레이션)
sudo -u postgres psql
CREATE ROLE app_read;
CREATE ROLE app_write;
CREATE ROLE app_migrate;
GRANT CONNECT ON DATABASE appdb TO app_read, app_write, app_migrate;2) 스키마/테이블 권한을 최소로 부여
-- 예시: public 스키마 사용 시(운영 정책에 맞게)
GRANT USAGE ON SCHEMA public TO app_read, app_write;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO app_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO app_write;3) 실제 로그인 계정은 role을 부여받게만
CREATE USER appuser WITH PASSWORD 'STRONG_PASSWORD';
GRANT app_read, app_write TO appuser;사례(현장에서 자주 겪는 상황)
- 앱 계정에 스키마 생성/DDL 권한을 줘서, 침해 시 구조 변경까지 가능해짐
- 신규 테이블이 생길 때마다 권한 누락으로 장애가 나고, 임시로 광권한을 부여함
- 운영/배치/마이그레이션 계정이 분리되지 않아 사고 분석이 어려움
트러블슈팅(증상→원인→해결)
- 증상: 신규 테이블에서 permission denied가 발생
원인: DEFAULT PRIVILEGES 설정 누락
해결: ALTER DEFAULT PRIVILEGES를 role 기준으로 고정하고 표준화
- 증상: 마이그레이션이 실패(DDL 권한 없음)
원인: 앱 계정과 마이그레이션 계정 분리 안 됨
해결: app_migrate 전용 계정을 만들고, 실행 위치/시간을 통제
- 증상: 권한을 줬는데도 안 되는 것처럼 보임
원인: 세션/트랜잭션에서 role 적용 방식 혼동
해결: 현재 사용자/role을 확인하고, 연결 풀 재시작 포함해 재검증
- 증상: 실수로 과도한 권한을 줬다
원인: GRANT ALL 남발
해결: 최소 권한 role로 재설계 후 REVOKE로 되돌리고, 변경 이력 남기기
- 증상: 침해 사고 시 영향 범위가 너무 큼
원인: 단일 계정이 너무 많은 권한을 보유
해결: 읽기/쓰기/관리 role 분리 + 네트워크 제한 + 감사 로그 강화