백엔드 데이터 인프라 44편 — 사용자·역할 관리 CREATE ROLE GRANT

스마트라이프onLeave a Comment on 백엔드 데이터 인프라 44편 — 사용자·역할 관리 CREATE ROLE GRANT
2026-05-17•백엔드 데이터 인프라

백엔드 데이터 인프라 44편. PostgreSQL 사용자·역할 관리 — CREATE ROLE·GRANT·REVOKE·기본 권한·운영 표준 풀어쓴 학습 노트.

📚 백엔드 데이터 인프라 · 44편 — 사용자·역할 관리 CREATE ROLE GRANT

이 글은 백엔드 데이터 인프라 시리즈 70편 중 44편이에요. PG(PostgreSQL 약칭) 보안의 토대 — ROLE 관리.

ROLE = USER + GROUP

PG 8.1+ 부터 "USER" 와 "GROUP" 이 ROLE 하나로 통합됐다. LOGIN 권한이 있으면 "사용자", 없으면 "그룹" (멤버십) 으로 동작한다.

CREATE ROLE alice LOGIN PASSWORD 'strongpass';   -- 사용자
CREATE ROLE app_readonly NOLOGIN;                  -- 그룹

GRANT app_readonly TO alice;                       -- alice 가 그룹에 가입

CREATE ROLE 옵션

CREATE ROLE name [
    LOGIN | NOLOGIN
    SUPERUSER | NOSUPERUSER
    CREATEDB | NOCREATEDB
    CREATEROLE | NOCREATEROLE
    INHERIT | NOINHERIT
    REPLICATION | NOREPLICATION
    BYPASSRLS | NOBYPASSRLS
    CONNECTION LIMIT n
    PASSWORD 'value'
    VALID UNTIL 'timestamp'
    IN ROLE other_role, ...
];

실무에서 자주 쓰는 네 가지 형태.

-- 일반 앱 사용자
CREATE ROLE app_user LOGIN PASSWORD 'pass' CONNECTION LIMIT 50;

-- 읽기 전용
CREATE ROLE analyst LOGIN PASSWORD 'pass';

-- 관리자
CREATE ROLE admin LOGIN PASSWORD 'pass' SUPERUSER;

-- 임시 (만료)
CREATE ROLE temp_dev LOGIN PASSWORD 'pass' VALID UNTIL '2026-12-31';

GRANT·REVOKE

테이블 권한

GRANT SELECT ON users TO analyst;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_user;

REVOKE INSERT ON users FROM analyst;

스키마 권한

GRANT USAGE ON SCHEMA public TO analyst;     -- 스키마 안 객체 보기
GRANT CREATE ON SCHEMA public TO app_user;   -- 새 객체 만들기

데이터베이스 권한

GRANT CONNECT ON DATABASE mydb TO analyst;
GRANT CREATE ON DATABASE mydb TO app_user;   -- 새 스키마

컬럼 권한 — 세밀

GRANT SELECT (id, name, email) ON users TO analyst;
-- email 만 보고 password 컬럼 안 보임

DEFAULT PRIVILEGES — 새 객체 자동 권한

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

이렇게 걸어두면 이후 생기는 모든 테이블에 analyst 가 자동으로 SELECT 를 받는다. 운영 표준.

ALTER DEFAULT PRIVILEGES IN SCHEMA public FOR ROLE app_user
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

FOR ROLE 을 붙이면 그 ROLE 이 만드는 객체로 범위가 좁혀진다.

그룹 멤버십

-- 그룹 만들기
CREATE ROLE readonly NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- 사용자 추가
GRANT readonly TO alice;
GRANT readonly TO bob;
GRANT readonly TO charlie;

그룹에 권한을 박아두면 사용자가 자동으로 상속한다. 권한 관리 표준.

INHERIT vs NOINHERIT

CREATE ROLE alice LOGIN INHERIT;          -- 기본
CREATE ROLE bob LOGIN NOINHERIT;

GRANT readonly TO alice;
GRANT readonly TO bob;

INHERIT 면 readonly 권한이 자동 적용되고, NOINHERIT 면 SET ROLE readonly 로 명시적으로 전환해야 한다. 거의 항상 기본값 INHERIT.

ALTER ROLE

ALTER ROLE alice PASSWORD 'newpass';
ALTER ROLE alice VALID UNTIL '2026-12-31';
ALTER ROLE alice CONNECTION LIMIT 100;
ALTER ROLE alice NOLOGIN;                  -- 로그인 막기
ALTER ROLE alice RENAME TO new_name;

DROP ROLE

DROP ROLE alice;
-- ERROR: role "alice" cannot be dropped because some objects depend on it

ROLE 이 "소유한 객체" 가 있으면 못 지운다. 소유권을 옮긴 뒤 지워야 한다.

REASSIGN OWNED BY alice TO new_owner;
DROP OWNED BY alice;                       -- alice 의 권한·소유 제거
DROP ROLE alice;

운영 권한 패턴 — 4 ROLE 모델

-- 1. 슈퍼유저 (DBA)
CREATE ROLE dba SUPERUSER;

-- 2. 읽기 전용 (분석가)
CREATE ROLE readonly NOLOGIN;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- 3. 앱 사용자 (CRUD)
CREATE ROLE app_user NOLOGIN;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- 4. 마이그레이션 (DDL)
CREATE ROLE migrator NOLOGIN;
GRANT app_user TO migrator;
GRANT ALL ON SCHEMA public TO migrator;

-- 개별 사용자 = 그룹 멤버십
CREATE ROLE alice LOGIN PASSWORD 'pass';
GRANT app_user TO alice;

CREATE ROLE bob LOGIN PASSWORD 'pass';
GRANT readonly TO bob;

각 환경(개발·운영) 의 표준 ROLE 셋. 신규 사용자는 그룹에 가입만 시킨다.

Row-Level Security (RLS)

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_owner ON orders
    FOR ALL
    TO PUBLIC
    USING (user_id = current_setting('app.user_id')::BIGINT);

SET app.user_id = '1';
SELECT * FROM orders;     -- user_id = 1 인 행만 자동 필터

PG 9.5+ 에서 도입된 "행 단위 권한". 멀티 테넌트 시스템에 강력하다.

비밀번호 모범 사례

-- scram-sha-256 강제 (postgresql.conf)
password_encryption = scram-sha-256

-- 만료 정책
CREATE ROLE alice LOGIN PASSWORD 'pass' VALID UNTIL NOW() + INTERVAL '90 days';

-- 강한 비밀번호
SELECT * FROM gen_random_uuid();   -- 또는 외부 secret manager

scram-sha-256(암호 해시 표준) 을 강제하고, 운영에서는 AWS Secrets Manager·HashiCorp Vault 같은 비밀 관리 서비스를 쓴다.

Spring 백엔드 — 환경별 사용자 분리

# application-prod.yml
spring:
  datasource:
    url: jdbc:postgresql://prod.example.com/mydb
    username: app_user_prod
    password: ${DB_PASSWORD}

# application-readonly.yml (보고서·분석 서비스)
spring:
  datasource:
    url: jdbc:postgresql://replica.example.com/mydb
    username: readonly_prod
    password: ${RO_DB_PASSWORD}

읽기 전용 사용자로 Read Replica(읽기 전용 복제본) 에 접근시켜 운영 사용자와 분리한다.

권한 확인

-- 사용자 권한
\dp users                              -- psql 메타
\dn+ public                            -- 스키마 권한

-- 시스템 카탈로그
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'analyst';

-- 멤버십
SELECT * FROM pg_roles WHERE rolname IN ('alice', 'app_user');
\du+                                    -- 모든 ROLE

함정 5가지

(1) 앱이 슈퍼유저로 접속

DB 가 통째로 위험해진다. 앱은 무조건 전용 사용자.

(2) DEFAULT PRIVILEGES 안 박음

새 테이블마다 GRANT 를 박아야 하는데 잊기 쉽다. DEFAULT 를 표준으로.

(3) 모든 사용자에게 직접 GRANT

10명 직원에 100 권한이면 GRANT 1000개. 그룹 멤버십이 표준.

(4) DROP ROLE 의 의존성

ROLE 이 소유한 객체가 있으면 못 지운다. REASSIGN OWNED 와 DROP OWNED 로 정리.

(5) 비밀번호 평문

.env 나 코드에 박아두면 위험. Secrets Manager 로 옮긴다.

🎯 운영 ROLE 모델

4 그룹 + 개별 사용자. dba(SUPERUSER) · readonly · app_user · migrator. 사용자는 그룹에 가입만. DEFAULT PRIVILEGES 로 새 테이블 자동. 비밀번호 = Secrets Manager.

한 줄 정리 — PG ROLE = USER + GROUP 통합. LOGIN 가진 ROLE = 사용자. GRANT·REVOKE·DEFAULT PRIVILEGES. 4 그룹 모델 (dba·readonly·app·migrator) + 사용자는 그룹 가입. scram-sha-256·Secrets Manager·RLS 운영 표준.

시험 직전 한 번 더 — ROLE 입문자가 매번 헷갈리는 것

  • ROLE = USER + GROUP 통합
  • LOGIN ROLE = 사용자
  • NOLOGIN ROLE = 그룹
  • CREATE ROLE name LOGIN PASSWORD '...' CONNECTION LIMIT N
  • 옵션 = SUPERUSER·CREATEDB·CREATEROLE·INHERIT·REPLICATION·BYPASSRLS
  • VALID UNTIL = 만료
  • GRANT 권한 ON 객체 TO ROLE
  • 권한 = SELECT·INSERT·UPDATE·DELETE·USAGE·CREATE·ALL
  • 객체 = TABLE·SCHEMA·DATABASE·FUNCTION·COLUMN
  • DEFAULT PRIVILEGES = 새 객체 자동 권한
  • ALTER DEFAULT PRIVILEGES IN SCHEMA ...
  • FOR ROLE = 그 ROLE 가 만드는 객체
  • 그룹 멤버십 = GRANT group_role TO user_role
  • INHERIT (기본) = 자동 상속
  • NOINHERIT = SET ROLE 명시
  • 4 그룹 모델 = dba·readonly·app_user·migrator
  • ALTER ROLE = 변경
  • DROP ROLE = 의존성 정리 (REASSIGN·DROP OWNED)
  • Row-Level Security = 행 단위 권한
  • CREATE POLICY 사용 = 멀티 테넌트
  • scram-sha-256 = PG 10+ 표준
  • password_encryption 설정
  • 운영 비밀번호 = Secrets Manager
  • 앱 슈퍼유저 X
  • \du = ROLE 목록
  • \dp = 테이블 권한
  • information_schema 권한 뷰
  • 한국 운영 = 그룹 + DEFAULT PRIVILEGES + Secrets Manager

시리즈 다른 편 (앞뒤 글 모음)

이전 글:

다음 글:

※ 이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

답글 남기기

error: Content is protected !!