[PostgreSQL 시리즈 10편] Oracle 권한 관리 그대로 했다가 막힌 썰 (PostgreSQL ROLE 완벽 정리)

 [PostgreSQL 시리즈 10편] Oracle 권한 관리 그대로 했다가 막힌 썰 (PostgreSQL ROLE 완벽 정리)

테스트 환경: PostgreSQL 17, Rocky Linux 9 / Oracle 19c

Oracle DBA에게 권한 관리는 기본 중의 기본입니다. CREATE USER, GRANT, ROLE... 눈 감고도 하죠. 그래서 저는 PostgreSQL에서도 똑같이 했습니다.

CREATE USER app_user IDENTIFIED BY 'password';

ERROR: syntax error at or near "IDENTIFIED"

"어? IDENTIFIED BY가 안 돼?"

그리고 테이블에 SELECT 권한을 줬는데도 사용자가 "권한이 없다"며 막히는 상황까지. Oracle 습관대로 했다가 곳곳에서 막혔습니다. 알고 보니 PostgreSQL의 권한 체계는 Oracle과 비슷하면서도 결정적으로 다른 지점들이 있었습니다.

이번 편은 실무 시리즈의 마지막 글입니다. 권한과 스키마 관리를 Oracle과 비교하며, 개념 이해를 돕는 그림과 함께 정리합니다.

• ★ 유저와 롤이 같다 (가장 큰 개념 전환)
• 권한 계층 구조 (여기서 많이 막힘)
• GRANT/REVOKE 실전
• 그룹 롤로 권한 관리하기
• 스키마와 검색 경로(search_path)

 

 

★ 첫 번째 충격: 유저와 롤이 같다

Oracle에서 유저(USER)와 롤(ROLE)은 명확히 다른 개념입니다. 유저는 로그인하는 계정, 롤은 권한 묶음이죠. 그런데 PostgreSQL에선 이 둘이 같습니다.

PostgreSQL에는 오직 ROLE이라는 하나의 개념만 있습니다. 그리고:

LOGIN 속성이 있는 ROLE  → 유저처럼 동작 (로그인 가능)
LOGIN 속성이 없는 ROLE  → 롤(그룹)처럼 동작 (권한 묶음)

즉, CREATE USER는 사실 "LOGIN 권한이 있는 ROLE을 만드는 것" 의 축약형입니다.

-- 이 두 문장은 사실상 같다
CREATE USER app_user PASSWORD 'secret';
CREATE ROLE app_user LOGIN PASSWORD 'secret';

-- 로그인 못 하는 그룹 롤
CREATE ROLE app_readonly;   -- LOGIN 없음 = 그룹용

Oracle과의 문법 차이:

Oracle:     CREATE USER u IDENTIFIED BY 'pw';
PostgreSQL: CREATE USER u PASSWORD 'pw';   -- IDENTIFIED BY 아님!

이 개념만 이해하면 PostgreSQL 권한의 절반은 끝납니다.

 

 

★ 두 번째 충격: 권한 계층 구조

제가 "테이블에 GRANT 했는데 왜 안 되지?"로 한참 헤맨 부분입니다. PostgreSQL 권한은 계층이 있습니다. 상위에서 막히면 하위는 접근조차 안 됩니다.

계층을 정리하면:

① CLUSTER (인스턴스) - 롤이 여기 소속
② DATABASE - CONNECT 권한 필요
③ SCHEMA - USAGE 권한 필요  ← 여기서 많이 막힘!
④ TABLE/객체 - SELECT 등 권한 필요

가장 흔한 함정: 스키마 USAGE

-- 테이블에 SELECT 권한을 줬는데...
GRANT SELECT ON employees TO app_user;

-- 여전히 "권한 없음" 에러가 날 수 있다!
-- → 스키마 USAGE 권한이 없기 때문

해결:

-- 스키마 사용 권한을 먼저 줘야 함
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON employees TO app_user;

핵심: Oracle은 스키마=유저라 이런 중간 단계가 없었는데, PostgreSQL은 스키마 USAGE라는 관문이 하나 더 있습니다. 이걸 놓치면 "분명 GRANT 했는데 안 되는" 상황이 생깁니다. 제가 막혔던 게 바로 이거였습니다.

 

 

GRANT/REVOKE 실전

기본 문법은 Oracle과 비슷합니다. 실무에서 자주 쓰는 패턴을 정리합니다.

기본 권한 부여

-- 테이블 권한
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;

-- 스키마 내 모든 테이블에 한 번에
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

-- 시퀀스 권한 (SERIAL 쓰면 필요)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

★ 앞으로 만들 테이블에도 자동 부여

Oracle에는 없는 편리한 기능입니다. 미래에 생성될 객체에도 권한을 미리 설정합니다.

-- 앞으로 public 스키마에 만들어질 테이블도 자동으로 SELECT 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO app_readonly;

이걸 설정해두면, 나중에 새 테이블을 만들어도 매번 GRANT할 필요가 없습니다. 운영에서 정말 유용합니다.

권한 회수와 확인

-- 권한 회수
REVOKE INSERT ON employees FROM app_user;

-- 권한 확인 (psql 메타 명령어! 4편 참고)
\dp employees        -- 테이블 권한 보기
\du                  -- 롤 목록과 속성 보기

 

 

★ 그룹 롤로 권한 관리하기 (실무 핵심)

실무에서는 유저마다 일일이 권한을 주지 않습니다. 그룹 롤에 권한을 모으고, 유저를 그룹에 소속시킵니다. Oracle의 롤 개념과 같습니다.

실전 패턴

-- 1. 그룹 롤 생성 (LOGIN 없음 = 그룹용)
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;

-- 2. 그룹에 권한 부여
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

GRANT app_readonly TO app_readwrite;  -- 롤 상속!
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

-- 3. 실제 유저 생성 (LOGIN 있음)
CREATE ROLE kim LOGIN PASSWORD 'secret1';
CREATE ROLE lee LOGIN PASSWORD 'secret2';

-- 4. 유저를 그룹에 소속시킴
GRANT app_readonly TO kim;      -- kim은 읽기 전용
GRANT app_readwrite TO lee;     -- lee는 읽기+쓰기

장점: 권한 정책이 바뀌면 그룹 롤 한 곳만 수정하면 됩니다. 소속된 모든 유저에게 자동 반영됩니다. 유저가 100명이어도 관리가 간단합니다.

롤 상속 주의점

-- INHERIT (기본): 그룹 권한을 자동으로 물려받음
CREATE ROLE kim LOGIN INHERIT;

-- NOINHERIT: SET ROLE로 명시적 전환해야 권한 사용
CREATE ROLE admin_kim LOGIN NOINHERIT;
-- 사용 시: SET ROLE app_admin;

기본은 INHERIT라 Oracle처럼 자동으로 그룹 권한이 적용됩니다.

 

 

스키마와 search_path

마지막으로 스키마 접근에 관한 개념입니다. Oracle DBA가 헷갈리는 부분이죠.

search_path란

Oracle에서 다른 유저의 객체는 유저명.테이블명으로 접근했습니다. PostgreSQL은 search_path로 스키마 검색 순서를 정합니다.

-- 현재 검색 경로 확인
SHOW search_path;
-- 기본값: "$user", public

-- 검색 경로 설정
SET search_path TO myschema, public;

-- 이제 myschema.table을 그냥 table로 접근 가능
SELECT * FROM employees;   -- myschema.employees를 찾음

Oracle: 스키마=유저, 다른 스키마는 소유자.테이블
PostgreSQL: search_path에 있으면 스키마명 생략 가능

롤별 기본 search_path

-- 특정 롤이 로그인하면 자동으로 이 경로 사용
ALTER ROLE kim SET search_path TO app_schema, public;

 

 

★ AI로 권한 설계하기

권한 구조가 복잡해지면 Claude에게 설계를 맡겨보세요.

프롬프트:
"PostgreSQL 17에서 다음 요구사항에 맞는 
롤/권한 구조를 설계해줘. Oracle DBA가 
이해하기 쉽게 설명과 함께.

요구사항:
- 읽기 전용 팀 (분석가 5명)
- 읽기+쓰기 팀 (개발자 3명)
- 관리자 2명
- 앞으로 만들 테이블도 자동 권한 적용
GRANT 문을 순서대로 만들어줘."

⚠️ 검증 필수: 권한은 보안과 직결됩니다. AI가 만든 GRANT 문은 반드시 검토하고, 특히 최소 권한 원칙(필요한 만큼만)을 지켰는지 확인하세요. 과도한 권한 부여는 보안 사고로 이어집니다.

 

 

Oracle DBA가 권한에서 자주 막히는 것 TOP 5

1. IDENTIFIED BY 문법

CREATE USER u IDENTIFIED BY 'pw';  -- ❌
CREATE USER u PASSWORD 'pw';        -- ✅

2. 스키마 USAGE 누락

테이블 GRANT만 하고 스키마 USAGE 안 줘서 막힘
→ GRANT USAGE ON SCHEMA ... 먼저!

3. 시퀀스 권한 누락

SERIAL 컬럼에 INSERT하는데 막힘
→ 시퀀스에도 USAGE 권한 필요

4. 미래 테이블 권한

새 테이블마다 GRANT 반복
→ ALTER DEFAULT PRIVILEGES로 한 번에

5. public 스키마 기본 권한

PostgreSQL 15부터 public 스키마 기본 CREATE 권한이 
제거됨 (보안 강화) → 명시적으로 부여해야 함

특히 5번은 PostgreSQL 15 이후 바뀐 부분이라, 예전 자료를 보고 따라 하면 막힙니다.

 

 

마무리

PostgreSQL 권한 관리의 핵심입니다.

1. 유저 = 롤 — LOGIN 속성 차이일 뿐
2. 권한은 계층 — Cluster→DB→Schema→Table
3. 스키마 USAGE 필수 — 가장 흔한 함정
4. 그룹 롤로 관리 — 권한은 그룹에, 유저는 소속
5. ALTER DEFAULT PRIVILEGES — 미래 테이블도 자동

Oracle의 권한 관리 경험은 PostgreSQL에서도 대부분 통합니다. 다만 "유저=롤" 개념과 "스키마 USAGE 관문"만 확실히 이해하면, 제가 겪은 것처럼 막히는 일은 없을 겁니다.

이번 편으로 실무 시리즈(5~9편)가 완결되었습니다. SQL 함수, 인덱스, PL/pgSQL, 킬러 기능, 그리고 권한까지. 이제 PostgreSQL로 실무를 할 수 있는 기반이 갖춰졌습니다. 다음 편부터는 운영 단계로 들어갑니다. 성능 튜닝, VACUUM, 장애 대응, 백업/복구 같은 진짜 운영자의 영역입니다.

여러분이 권한 관리에서 막혔던 경험이나 유용한 롤 설계 팁이 있다면 댓글로 공유해주세요.