대상 OS: Debian 12 (bookworm)

PostgreSQL 보안의 핵심은 ‘계정’을 많이 만드는 게 아니라, 역할(Role)과 스키마 권한을 깔끔하게 분리해서 “의도하지 않은 권한 상속/확장”을 막는 것입니다. 아래는 운영에서 재사용하기 좋은 최소권한 템플릿과 흔한 권한 꼬임 문제를 다룹니다.

1) 권장 역할 모델(예시)

- app_owner: 스키마/테이블 소유(DDL 가능)
- app_rw: 데이터 읽기/쓰기(DML)만
- app_ro: 읽기 전용
- app_migrator: 마이그레이션 전용(필요 시 DDL)

2) 기본 생성(데이터베이스/스키마/역할)

sudo apt-get update
sudo apt-get install -y postgresql

sudo -u postgres psql
-- DB/스키마
CREATE DATABASE appdb;
\c appdb
CREATE SCHEMA app AUTHORIZATION postgres;

-- 로그인 롤(사용자)
CREATE ROLE app_login LOGIN PASSWORD 'CHANGE_ME';

-- 권한 롤(그룹)
CREATE ROLE app_ro NOLOGIN;
CREATE ROLE app_rw NOLOGIN;

-- 로그인 롤에 권한 롤 부여
GRANT app_rw TO app_login;

3) 스키마/테이블 권한(최소권한)

-- 스키마 사용 권한
GRANT USAGE ON SCHEMA app TO app_ro, app_rw;

-- 기존 테이블 권한
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;

-- 시퀀스 권한(INSERT 시 nextval 필요)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;

-- 앞으로 생성될 객체의 기본 권한(중요)
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_rw;

4) 네트워크 노출 줄이기(원격접속 최소화)

원격이 꼭 필요 없으면 `listen_addresses`를 로컬로 제한합니다.

sudoedit /etc/postgresql/15/main/postgresql.conf
listen_addresses = '127.0.0.1'

sudo systemctl restart postgresql

5) 트러블슈팅(증상→원인→해결)

1) `permission denied for schema app`

- 원인: 스키마 `USAGE` 미부여
- 해결:

GRANT USAGE ON SCHEMA app TO app_login;

2) 테이블은 SELECT 되는데 INSERT가 실패(시퀀스 권한 오류)

- 원인: 시퀀스에 `USAGE`/`SELECT` 권한 없음
- 해결:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;

3) 새로 만든 테이블만 권한이 없음

- 원인: `ALTER DEFAULT PRIVILEGES`를 안 걸었거나, 잘못된 소유자 컨텍스트
- 해결: “누가 테이블을 생성하는지” 기준으로 default privileges를 설정

4) 롤을 줬는데도 권한이 커짐(의도치 않은 상속)

- 원인: role membership + `INHERIT` 기본값으로 권한이 합쳐짐
- 해결: 필요한 경우 `NOINHERIT` 설계 검토(대신 `SET ROLE` 운영 필요)

5) 슈퍼유저처럼 보이는 동작(DDL 가능)

- 원인: 객체 소유자(owner)거나, 스키마 `CREATE` 권한이 있음
- 해결: owner 분리(DDL은 owner/migrator만), 앱 계정은 DML만

6) 애플리케이션 연결은 되는데 특정 함수 호출이 막힘

- 원인: 함수 실행 권한(EXECUTE) 미부여
- 해결:

GRANT EXECUTE ON FUNCTION app.some_fn(args) TO app_rw;

6) 사례(현장에서 자주 겪는 상황)

1) 운영 중 마이그레이션이 앱 계정으로 실행되어 테이블 owner가 뒤섞여, default privileges가 통하지 않음

2) `public` 스키마 권한을 정리하지 않아 새 객체가 예상보다 넓게 노출됨

3) “읽기 전용” 계정이 뷰/함수로 우회해 데이터 변경을 시도(함수 권한 설계 필요)

운영에서는 “객체 소유자”와 “실행자”를 분리하는 것만으로도 사고 확률이 크게 줄어듭니다.

- 이 글은 ai가 random적으로 만들어 올리는 글입니다. -