PostgreSQL Best Practice

PostgreSQL 권한 부여: 특정 데이터베이스에만 안전하게 GRANT 적용하는 방법 (완전한 SQL 예제)

읽는 시간: 약 5분

서버 애플리케이션 전용 계정을 만들 때, 불필요하게 모든 데이터베이스에 권한을 열어두면 보안 리스크가 커집니다.
아래 예제는 특정 데이터베이스(mydb)의 특정 스키마(public)에만 최소 권한을 안전하게 부여하는 표준 절차입니다.
기존 객체(테이블·시퀀스)와 향후 생성될 객체에 대한 기본 권한(ALTER DEFAULT PRIVILEGES)까지 한번에 설정하세요.

1) 사전 준비

• 슈퍼유저 또는 해당 객체 소유자 권한
• 대상 DB: mydb (변경 가능)
• 앱 계정: common (변경 가능)

2) 완전한 GRANT 스크립트

다음 스크립트는 오직 mydb 내 public 스키마에만 적용됩니다. 다른 DB에는 영향이 없습니다.

-- (옵션) 앱 전용 사용자 생성
-- CREATE ROLE common LOGIN PASSWORD '강력한_비밀번호';

-- 1) 특정 데이터베이스 접속 허용
GRANT CONNECT ON DATABASE mydb TO common;

-- 2) 이후 명령은 mydb에 접속한 상태에서 실행
-- psql 기준: \c mydb

-- 3) public 스키마 사용 권한 부여
GRANT USAGE ON SCHEMA public TO common;

-- 4) 기존 테이블에 대한 DML 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO common;

-- 5) 기존 시퀀스 권한 (시퀀스로부터 nextval 등)
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO common;

-- 6) 앞으로 생성될 테이블의 기본 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO common;

-- 7) 앞으로 생성될 시퀀스의 기본 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO common;

3) 왜 ‘특정 DB 한정’인가?

GRANT CONNECT ON DATABASE mydb는 데이터베이스 레벨 접근만 허용합니다.
그 외 명령들은 모두 해당 DB의 스키마 범위에서만 동작하므로, 다른 데이터베이스에는 일절 영향이 없습니다.
워크로드 격리, 최소 권한 원칙(Principle of Least Privilege)을 만족합니다.

4) 여러 스키마를 쓰는 경우

여러 스키마를 운용한다면, 필요한 스키마마다 동일 패턴으로 권한을 부여하세요.

-- 예: app, audit 스키마 추가
GRANT USAGE ON SCHEMA app, audit TO common;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO common;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA app TO common;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO common;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO common;

-- audit 스키마는 읽기 전용으로 제한하고 싶다면:
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA audit FROM common;
GRANT SELECT ON ALL TABLES IN SCHEMA audit TO common;

5) 함수/프로시저 권한 (옵션)

앱이 스키마 내 함수/프로시저를 호출한다면 다음도 고려하세요.

-- 기존 모든 함수 실행 권한
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO common;

-- 앞으로 생성될 함수에 대한 기본 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO common;

6) 권한 검증 방법

-- common 계정으로 접속 후
-- 간단한 DML 시도
SELECT * FROM public.some_table LIMIT 1;
INSERT INTO public.some_table(col1) VALUES ('ok');

-- 시퀀스 nextval 테스트
SELECT nextval('public.some_table_id_seq');

-- 스키마 접근 확인
SET search_path TO public;

7) 롤백/권한 회수

-- 기존 객체 권한 회수
REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM common;
REVOKE USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public FROM common;

-- 기본 권한 회수
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM common;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE USAGE, SELECT, UPDATE ON SEQUENCES FROM common;

-- 스키마/DB 접근 회수
REVOKE USAGE ON SCHEMA public FROM common;
REVOKE CONNECT ON DATABASE mydb FROM common;

FAQ

Q1. 이 스크립트가 다른 데이터베이스에도 영향이 있나요?

아니요. 없습니다. GRANT 대상 DB(mydb)와 그 안의 스키마(public)에만 적용됩니다.

Q2. 앞으로 생성될 테이블 권한이 적용되지 않는 경우가 있어요.

ALTER DEFAULT PRIVILEGES는 명령을 실행한 “소유자”가 추후 생성하는 객체에만 영향을 줍니다. 테이블을 생성하는 계정(또는 역할)에서 해당 명령을 실행했는지 확인하세요.

Q3. 읽기 전용 계정은 어떻게 만드나요?

테이블 권한에서 SELECT만 부여하고, INSERT/UPDATE/DELETE는 부여하지 않으면 됩니다.

Q4. 다른 스키마를 추가하면 어떻게 되나요?

해당 스키마마다 동일한 패턴으로 USAGE와 테이블/시퀀스 권한, 기본 권한을 설정하세요.