PostgreSQL 기초 강좌 #9 롤·권한·백업 기초: 운영의 최소 규율

인프라 PostgreSQL 데이터베이스
4 분 소요

기초의 마지막 편은 화려하지 않지만 사고를 막는 두 가지, 권한과 백업입니다. “개발 편의로 앱에 superuser 계정을 줬다가"와 “백업은 있는 줄 알았는데 복원이 안 됐다"는 DB 사고 수기의 양대 단골입니다. 둘 다 최소 규율만 세우면 피할 수 있습니다.

롤: 사용자도 그룹도 전부 role입니다 #

PostgreSQL의 권한 주체는 롤(role) 하나로 통일되어 있습니다. 로그인할 수 있는 롤을 관습적으로 “사용자"라 부르고, 권한 묶음으로 쓰는 롤을 “그룹"이라 부를 뿐 같은 물건입니다. 1편부터 써 온 postgres는 모든 것이 허용되는 superuser 롤이고, 학습에서는 편했지만 애플리케이션에 주는 것은 금물입니다. SQL 인젝션이든 코드 버그든, 뚫렸을 때의 피해가 “권한이 허용하는 만큼"으로 정해지기 때문입니다.

실전 순서: 최소 권한 계정 만들기 #

권한 묶음 롤과 로그인 롤을 나누는 것이 정석입니다.

최소 권한 롤 만들기 
-- 1) 권한 묶음(그룹) 롤: 로그인 불가
CREATE ROLE app_readwrite NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

-- 앞으로 만들 테이블에도 자동 적용
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 2) 로그인(사용자) 롤: 권한은 그룹에서 상속
CREATE ROLE app_server LOGIN PASSWORD 'strong-password' IN ROLE app_readwrite;

읽어 둘 포인트가 세 가지입니다. 첫째, GRANT ... ON ALL TABLES는 지금 있는 테이블에만 적용되므로, 미래의 테이블은 ALTER DEFAULT PRIVILEGES가 맡습니다(이것을 빼먹으면 배포 때마다 권한 에러를 만납니다). 둘째, IDENTITY 컬럼의 채번은 내부적으로 시퀀스를 쓰므로 ON ALL SEQUENCES의 USAGE가 필요합니다. 셋째, 애플리케이션용 외에 사람용 읽기 전용 롤(app_readonly, SELECT만)을 하나 더 두면 “조회하러 들어갔다가 실수로 UPDATE"를 구조적으로 막을 수 있습니다. 참고로 PostgreSQL 15부터는 public 스키마에 아무나 테이블을 만들 수 없게 기본값이 조여졌는데, 위처럼 명시적으로 GRANT하는 습관을 들이면 버전과 무관하게 안전합니다.

권한 확인은 psql 메타 명령 \du(롤 목록)와 \dp(테이블 권한)로 합니다. 1편의 관찰 도구 목록에 이 둘을 추가해 두세요.

백업 기초: pg_dump와 복원 리허설 #

백업의 기초는 논리 백업, 즉 “DB를 다시 만들 수 있는 SQL/아카이브를 뽑아 두는 것"입니다.

덤프와 복원 리허설 
# 커스텀 포맷으로 덤프(압축 + 선택적 복원 가능)
pg_dump -U postgres -Fc -f lab.dump lab

# 복원: 새 DB에 리허설
createdb -U postgres lab_restore
pg_restore -U postgres -d lab_restore lab.dump

-Fc(커스텀 포맷)를 기본으로 쓰는 이유는 압축이 되고, pg_restore로 특정 테이블만 골라 복원하는 것이 가능하기 때문입니다(평문 SQL 덤프는 전부 아니면 전무입니다). 그리고 백업에서 가장 중요한 문장은 이것입니다. 복원해 본 적 없는 백업은 백업이 아닙니다. 위처럼 별도 DB에 복원하는 리허설을 주기적으로 돌려야 “백업 파일은 쌓였는데 열리지 않는” 사고를 막을 수 있습니다.

pg_dump는 “덤프 시작 시점의 일관된 스냅샷"을 뜹니다(6편의 MVCC가 여기서도 일합니다). 다만 논리 백업의 한계도 분명합니다. 덤프 이후의 변경은 사라지고(어제 새벽 백업이면 오늘 데이터는 없습니다), 대용량 DB에서는 덤프·복원 시간이 길어집니다. “장애 직전 시점으로 돌아가기”(PITR)와 물리 백업은 실전 편 마지막 장의 주제입니다. 관리형 서비스(RDS 등)를 쓰면 이 층을 상당 부분 맡길 수 있다는 것은 RDS vs 자체 운영에서 다룬 대로입니다.

기초 9편의 회고, 그리고 실전 편 #

여기까지가 기초입니다. 설치와 psql(1편), 자료형과 제약(2편), 조인·집계(3편), 인덱스(4편), EXPLAIN(5편), 트랜잭션·MVCC(6편), JSONB(7편), 뷰·CTE·윈도 함수(8편), 그리고 권한·백업(이번 편)까지, “쿼리는 쓰는데 DB를 배운 적은 없는” 상태에서 출발해 DB를 관찰하고 판단하는 기본기를 갖췄습니다. 이어지는 실전 편은 무대를 “성장하는 서비스"로 옮깁니다. 무중단 스키마 변경, 커넥션 풀, 성능 진단 루틴, VACUUM, 잠금, 파티셔닝, 복제, 그리고 PITR까지, 운영에서 실제로 부딪히는 순서대로 다루겠습니다.

정리 #

  • 권한 주체는 롤 하나입니다. 로그인 가능 여부와 권한 상속의 조합으로 사용자와 그룹을 만듭니다.
  • 애플리케이션에 superuser는 금물입니다. 권한 묶음 롤 + 로그인 롤 구조로 최소 권한을 만듭니다.
  • ON ALL TABLES는 현재까지, 미래는 ALTER DEFAULT PRIVILEGES입니다. 시퀀스 USAGE도 잊기 쉬운 필수품입니다.
  • 백업 기초는 pg_dump 커스텀 포맷이고, 복원 리허설 없는 백업은 백업이 아닙니다. PITR은 실전 편에서 다룹니다.
  • 기초 9편이 끝났습니다. 실전 편에서는 성장하는 서비스의 운영 문제를 순서대로 부딪히겠습니다.
X