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

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

ROLE = USER + GROUP

PG 8.1+ — "USER" 와 "GROUP" 의 통합 개념 = ROLE.

• LOGIN 권한 가진 ROLE = "사용자"
• LOGIN 없는 ROLE = "그룹" (멤버십)

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, ...
];

자주 쓰는 4가지:

-- 일반 앱 사용자
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

운영 = 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 권한 = 1000개 GRANT. 그룹 멤버십이 표준.

(4) DROP ROLE 의 의존성

ROLE 소유 객체 있으면 못 지움. REASSIGN OWNED + DROP OWNED.

(5) 비밀번호 평문

.env 또는 코드에 박음 = 위험. 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

시리즈 다른 편

• Part 3 운영: 42편 설치 · 43편 설정 · 44편 (현재 글)

시리즈 다음 글

다음 글(45편)에서는 데이터베이스 관리 — DB 생성·복제·드롭·이동 운영 표준.

공식 문서: PostgreSQL 18 — User Management에서 더 자세한 사양을 확인할 수 있어요.