[PostgreSQL] 앱 전용 롤에 SELECT·INSERT만 주고 나머지는 막는다

설치·접속: PostgreSQL 설치와 접속

부제: 애플리케이션이 슈퍼유저로 붙어 있는 걸 발견했을 때, 딱 필요한 테이블에 필요한 동작만 하는 최소권한 롤로 갈아탈 때

CREATE ROLE m9_app LOGIN PASSWORD 'secret';

-- 접속·스키마 사용 권한부터 (이게 없으면 테이블 접근 자체가 막힘)
GRANT CONNECT ON DATABASE shop TO m9_app;
GRANT USAGE ON SCHEMA public TO m9_app;
-- products 테이블에는 읽기·쓰기만
GRANT SELECT, INSERT ON products TO m9_app;

-- 부여 안 한 동작을 앱유저 흉내로 시도해 본다
SET ROLE m9_app;
SELECT count(*) FROM products;            -- 허용
UPDATE products SET price = 0 WHERE id=1;  -- 미부여 -> 거부

 count
-------
    20

ERROR:  permission denied for table products

PostgreSQL은 기본이 거부다. 롤을 만들어도 GRANT로 명시한 것 외엔 아무것도 못 한다. 순서가 중요한데, 테이블 권한을 주기 전에 CONNECT(DB 접속)와 스키마 USAGE가 먼저 있어야 한다 — 스키마 사용 권한이 없으면 그 안의 테이블에 아무리 SELECT를 줘도 접근이 막힌다. SET ROLE로 그 롤인 척 전환해 실제로 무엇이 되고 안 되는지 바로 검증할 수 있다. UPDATE 권한을 준 적이 없으니 즉시 permission denied가 뜬다. 앱은 이렇게 읽기·쓰기 딱 필요한 범위만 가진 롤로 붙여야 사고 반경이 줄어든다.

이렇게도 쓴다

권한이 어떻게 걸렸는지 표로 확인한다. r=SELECT, a=INSERT, w=UPDATE. (조합: \dp)

\dp products
-- m9_app=ar/postgres  (a=INSERT, r=SELECT 만 있음)

 

준 권한을 회수한다. (조합: REVOKE)

REVOKE INSERT ON products FROM m9_app;

 

앞으로 만들 테이블에도 자동으로 읽기 권한이 붙게 예약한다. (조합: ALTER DEFAULT PRIVILEGES)

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO m9_app;

 

권한을 롤 하나에 묶고, 사람/앱 롤을 그 그룹에 넣어 관리한다. (조합: 그룹 롤)

CREATE ROLE m9_readers NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO m9_readers;
GRANT m9_readers TO m9_app;   -- m9_app이 m9_readers 권한을 물려받음

 

특정 컬럼만 읽게 좁힌다. 민감 컬럼 제외. (조합: 컬럼 단위 GRANT)

GRANT SELECT (id, name) ON products TO m9_app;

 

시퀀스도 함께 줘야 INSERT 시 serial 채번이 된다. (조합: SEQUENCE 권한)

GRANT USAGE ON SEQUENCE products_id_seq TO m9_app;