Copy & Run

PostgreSQL 역할 최소 권한 실습: PUBLIC 회수, 기본 권한

앱 역할이 표 주인이고 읽기 역할에 ALL이 주어진 느슨한 PostgreSQL을 최소 권한으로 고칩니다. 같은 12칸짜리 허용/거부 표로 전후를 검사해 어긋난 칸이 6개에서 0개가 되는 것을 확인하고, 나중에 만든 표에 권한이 자동으로 붙는 기본 권한(DEFAULT PRIVILEGES)과 has_table_privilege 교차 확인까지 다룹니다.

실습 노트 · 2026-10-30 발행 · 11분 · 2026-10 확인
실습 코드 내려받기: 전체 받기(zip)GitHubroles_demo.sh

데이터베이스 권한은 "된다"를 확인하기는 쉬워도 "안 된다"를 확인하기는 어렵습니다. 앱이 잘 돌아가면 권한이 너무 넓은지 알 수 없고, 넓은 권한은 사고가 나야 드러납니다. 이 글은 일부러 느슨하게 만든 PostgreSQL 16을 최소 권한으로 고치면서, 역할별 허용/거부 기대표를 먼저 정하고 전후에 같은 표로 검사합니다. 어긋난 칸의 개수를 종료 코드로 바꿔 두면 권한을 바꿀 때마다 같은 검사를 다시 돌릴 수 있습니다. 같은 컨테이너 방식의 앞선 글로 행 수준 보안(RLS)이 있고, 이 글은 그보다 한 단계 앞인 표와 스키마 단위의 권한을 다룹니다.

준비물

항목 확인한 값
Docker 29.5.3 (Windows 11, Git Bash에서 실행)
PostgreSQL 16 (postgres:16 이미지)
역할 app_rw(읽고 쓰는 앱), app_ro(읽기 전용 보고서), intruder(아무 권한도 주지 않은 계정), app_owner(표 소유자, 로그인 불가)
표 orders(처음부터 있음), invoices(설정이 끝난 뒤에 만들어 검사)

컨테이너 하나(osk-fb-pgr)를 띄우고 사용자별 접속은 -h 127.0.0.1에 비밀번호(lab)로 했습니다. 시험이 끝나면 컨테이너를 지웁니다.

1. 기대하는 허용과 거부 표

최소 권한 기준으로 12칸을 먼저 적었습니다.

역할 문장 기대
app_rw SELECT, INSERT (orders) 허용
app_rw DELETE, DROP TABLE, CREATE TABLE 거부
app_ro SELECT (orders) 허용
app_ro INSERT, UPDATE (orders) 거부
app_ro 나중에 생긴 invoices를 SELECT 허용
app_rw 나중에 생긴 invoices에 INSERT 허용
intruder SELECT, CREATE TABLE 거부

각 문장은 BEGIN; ...; ROLLBACK; 안에서 실행해서 검사가 자료를 바꾸지 않게 했습니다. 결과가 permission denied, must be owner, CONNECT privilege가 들어간 오류면 거부, 아니면 허용으로 읽었습니다. 처음에는 permission denied만 거부로 읽었더니 DROP TABLE이 "must be owner of table"이라는 다른 문구로 막혔는데도 허용으로 잘못 판정되었습니다. 권한 오류의 문구가 문장 종류마다 다르다는 점에 주의합니다.

2. 느슨한 상태

느슨한 설정은 흔히 보는 모양으로 만들었습니다.

  • orders의 소유자가 앱 역할 app_rw입니다(앱이 표를 만들면 자연스럽게 그렇게 됩니다).
  • 보고서 역할 app_ro에게 GRANT ALL ON ALL TABLES를 주었습니다.
  • GRANT ALL ON SCHEMA public TO PUBLIC으로 모든 역할이 스키마에 표를 만들 수 있습니다(과거 버전의 기본 동작과 같은 상태입니다).

같은 12칸을 검사한 결과 6칸이 기대와 어긋났고 게이트는 종료 코드 1이었습니다.

어긋난 칸 실제 이유
app_rw의 DELETE 허용 소유자는 모든 권한을 가짐
app_rw의 DROP TABLE 허용 소유자만 지울 수 있는데 앱이 소유자
app_rw의 CREATE TABLE 허용 스키마가 PUBLIC에 열려 있음
app_ro의 UPDATE 허용 ALL에 UPDATE 포함
app_ro의 invoices SELECT 거부 ALL ON ALL TABLES는 지금 있는 표에만 적용됨
intruder의 CREATE TABLE 허용 PUBLIC에 스키마 권한이 열려 있음

느슨한 상태의 12칸 검사에서 app_rw의 DELETE, DROP TABLE, CREATE TABLE이 허용되고 app_ro의 UPDATE가 허용되며 invoices SELECT가 거부되고 intruder의 CREATE TABLE이 허용되어 6칸이 어긋나고 게이트 종료 코드가 1인 것을 확인합니다.
느슨한 상태의 12칸 검사에서 app_rw의 DELETE, DROP TABLE, CREATE TABLE이 허용되고 app_ro의 UPDATE가 허용되며 invoices SELECT가 거부되고 intruder의 CREATE TABLE이 허용되어 6칸이 어긋나고 게이트 종료 코드가 1인 것을 확인합니다.

한 가지 짚을 점이 있습니다. 같은 상태에서 app_ro의 INSERT는 기대대로 거부되었습니다. 표에는 ALL을 줬지만 serial 열이 쓰는 시퀀스에는 권한을 주지 않아서 시퀀스 권한 오류로 막힌 것입니다. 즉 의도한 보호가 아니라 우연히 막힌 것이므로, 검사표에서 "거부"가 나와도 이유까지 봐야 합니다.

이 단계의 파일 내려받기:roles_demo.sh

3. 최소 권한으로 고치기

고치는 명령은 네 묶음입니다.

  1. 소유자를 분리합니다. 로그인할 수 없는 app_owner를 만들고 표와 시퀀스의 소유자를 옮깁니다. 이렇게 하면 앱 계정이 표를 지우거나 구조를 바꿀 수 없습니다.
  2. 넓은 권한을 회수합니다. REVOKE ALL ON ALL TABLES ... FROM app_ro, app_rw, REVOKE ALL ON SCHEMA public FROM PUBLIC, REVOKE CONNECT ON DATABASE lab FROM PUBLIC을 실행합니다.
  3. 필요한 만큼만 다시 줍니다. 데이터베이스 CONNECT와 스키마 USAGE는 두 앱 역할에, orders의 SELECT는 app_ro에, SELECT, INSERT, UPDATE와 시퀀스 USAGE는 app_rw에 줍니다.
  4. 기본 권한을 정합니다. ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_ro와 같은 명령으로, app_owner가 앞으로 만드는 표에 권한이 자동으로 붙게 했습니다.

ALTER DEFAULT PRIVILEGES는 "누가 만드는 표"에 적용될지를 FOR ROLE로 정하므로, 표를 만드는 역할과 이 설정의 역할이 같아야 효과가 있습니다. 이 글에서는 설정이 끝난 뒤 app_owner로 invoices를 만들어 검사했습니다.

이 단계의 파일 내려받기:roles_demo.sh

4. 같은 표로 다시 검사

12칸 모두 기대와 같아서 어긋난 칸은 0개, 게이트 종료 코드는 0이었습니다. invoices는 설정 뒤에 만들었는데도 app_ro가 읽고 app_rw가 쓸 수 있었으니 기본 권한이 작동한 것입니다. intruder의 두 칸은 이제 데이터베이스 접속 단계에서 막혀 CONNECT privilege 오류로 거부되었습니다(느슨한 상태에서는 접속은 되고 표 쪽에서 막혔습니다).

이 단계의 파일 내려받기:roles_demo.sh

5. 교차 확인

실제로 문장을 실행해 본 결과가 PostgreSQL이 말하는 권한과 같은지 내장 함수 has_table_privilege로도 확인했습니다.

역할 SELECT INSERT DELETE
app_ro t f f
app_rw t t f

실행해 본 결과와 함수의 값이 일치했습니다. \dp orders로 본 권한 목록은 app_owner=arwdDxt/app_owner, app_ro=r/app_owner, app_rw=arw/app_owner였습니다. 글자는 r이 읽기(SELECT), a가 추가(INSERT), w가 수정(UPDATE), d가 삭제(DELETE)입니다.

최소 권한으로 고친 뒤 12칸이 모두 기대와 같아 어긋난 칸 0, 종료 코드 0이고, has_table_privilege 값과 표의 권한 목록이 app_ro는 읽기만 app_rw는 읽기, 추가, 수정인 것을 확인합니다.
최소 권한으로 고친 뒤 12칸이 모두 기대와 같아 어긋난 칸 0, 종료 코드 0이고, has_table_privilege 값과 표의 권한 목록이 app_ro는 읽기만 app_rw는 읽기, 추가, 수정인 것을 확인합니다.

이 단계의 파일 내려받기:roles_demo.sh

6. 운영에 옮길 때 확인할 것

  • 앱은 app_owner로 접속하지 않습니다. 소유자는 마이그레이션 도구가 쓰는 역할로 따로 두고, 앱에는 읽고 쓰는 역할만 줍니다.
  • 새 표를 만드는 역할마다 기본 권한을 정합니다. FOR ROLE이 빠지면 현재 접속한 역할에만 적용됩니다.
  • PUBLIC을 먼저 회수합니다. 개별 역할에 권한을 주고 빼기 전에 모두에게 열린 권한부터 닫아야 합니다. 이 글의 느슨한 상태는 GRANT ALL ON SCHEMA public TO PUBLIC으로 일부러 열어서 만들었고, 기본 설정에서 PUBLIC에 스키마 쓰기가 열려 있는지는 확인하지 않았습니다.
  • 검사표를 저장소에 둡니다. 권한을 바꾸는 변경마다 같은 검사를 돌려 종료 코드로 막습니다.

전체 코드

실험 전체를 한 번에 돌리는 스크립트입니다. 같은 폴더에서 실행합니다.

# roles_demo.sh
#!/usr/bin/env bash
# PostgreSQL 역할 권한을 "느슨한 상태"와 "최소 권한 상태"로 나눠 같은 허용/거부 표로 검사하고, 어긋난 칸 수를 종료 코드로 낸다.
# 준비: Docker (postgres:16 이미지). 시험용 컨테이너라 비밀번호는 모두 lab 이다.
# 실행(Git Bash): bash roles_demo.sh   (약 40초)
export MSYS_NO_PATHCONV=1
N=osk-fb-pgr
docker rm -f $N >/dev/null 2>&1
docker run -d --name $N -e POSTGRES_PASSWORD=lab postgres:16 >/dev/null
for i in $(seq 1 30); do docker exec $N pg_isready -U postgres >/dev/null 2>&1 && break; sleep 1; done
sleep 2
SU() { docker exec -i $N psql -U postgres -d lab -At "$@" 2>&1; }
AS() { local u=$1; shift; docker exec -i -e PGPASSWORD=lab $N psql -h 127.0.0.1 -U "$u" -d lab -At "$@" 2>&1; }
docker exec $N psql -U postgres -c 'CREATE DATABASE lab' >/dev/null

# 기대하는 표(최소 권한 기준): 역할 | 실행할 문장 | 기대값
MATRIX=(
  "app_rw|SELECT count(*) FROM orders|ALLOW"
  "app_rw|INSERT INTO orders(item) VALUES ('x')|ALLOW"
  "app_rw|DELETE FROM orders|DENY"
  "app_rw|DROP TABLE orders|DENY"
  "app_rw|CREATE TABLE evil(a int)|DENY"
  "app_ro|SELECT count(*) FROM orders|ALLOW"
  "app_ro|INSERT INTO orders(item) VALUES ('x')|DENY"
  "app_ro|UPDATE orders SET item='y'|DENY"
  "app_ro|SELECT count(*) FROM invoices|ALLOW"
  "app_rw|INSERT INTO invoices(amount) VALUES (1)|ALLOW"
  "intruder|SELECT count(*) FROM orders|DENY"
  "intruder|CREATE TABLE evil(a int)|DENY"
)
try() {   # try <역할> <문장> : 권한 문제(permission denied, must be owner, CONNECT)로 막히면 DENY, 아니면 ALLOW (문장은 되돌린다)
  local out; out=$(echo "BEGIN; $2; ROLLBACK;" | AS "$1")
  if echo "$out" | grep -qE 'permission denied|must be owner|CONNECT privilege'; then echo DENY; else echo ALLOW; fi
}
check_all() {
  local bad=0 row role sql want got
  SU -c "SET ROLE $OWNER; CREATE TABLE invoices (id serial PRIMARY KEY, amount int); " >/dev/null   # 설정 뒤에 새로 생긴 표
  for row in "${MATRIX[@]}"; do
    IFS='|' read -r role sql want <<<"$row"
    got=$(try "$role" "$sql")
    mark="  "; [ "$got" != "$want" ] && { mark="X "; bad=$((bad+1)); }
    printf "  %s%-8s %-45s 기대 %-5s 실제 %s\n" "$mark" "$role" "$sql" "$want" "$got"
  done
  SU -c "SET ROLE $OWNER; DROP TABLE invoices" >/dev/null
  echo "  어긋난 칸: $bad / ${#MATRIX[@]}"
  return $((bad>0))
}

echo "## 1. 느슨한 상태(앱 역할이 표 주인, 읽기 역할에 ALL, 스키마를 PUBLIC 에 열어 둠)"
SU <<'SQL' >/dev/null
CREATE ROLE app_rw LOGIN PASSWORD 'lab';
CREATE ROLE app_ro LOGIN PASSWORD 'lab';
CREATE ROLE intruder LOGIN PASSWORD 'lab';
GRANT ALL ON SCHEMA public TO PUBLIC;
SET ROLE app_rw;
CREATE TABLE orders (id serial PRIMARY KEY, item text);
INSERT INTO orders(item) VALUES ('a'),('b');
RESET ROLE;
GRANT ALL ON ALL TABLES IN SCHEMA public TO app_ro;
SQL
OWNER=app_rw
check_all; echo "  게이트 종료 코드 $?"

echo "## 2. 최소 권한으로 고치기"
SU <<'SQL' >/dev/null
CREATE ROLE app_owner NOLOGIN;
ALTER TABLE orders OWNER TO app_owner;
ALTER SEQUENCE orders_id_seq OWNER TO app_owner;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_ro, app_rw;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE CONNECT ON DATABASE lab FROM PUBLIC;
GRANT CONNECT ON DATABASE lab TO app_rw, app_ro;
GRANT USAGE ON SCHEMA public TO app_rw, app_ro;
GRANT SELECT ON orders TO app_ro;
GRANT SELECT, INSERT, UPDATE ON orders TO app_rw;
GRANT USAGE ON SEQUENCE orders_id_seq TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT ON TABLES TO app_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_rw;
SQL
OWNER=app_owner
echo "## 3. 같은 표로 다시 검사"
check_all; echo "  게이트 종료 코드 $?"

echo "## 4. 교차 확인: 내장 함수 has_table_privilege 와 실제 실행 결과"
SU -c "SELECT r, p, has_table_privilege(r, 'orders', p) FROM (VALUES ('app_rw'),('app_ro')) a(r), (VALUES ('SELECT'),('INSERT'),('DELETE')) b(p) ORDER BY 1,2" | tr '|' ' ' | sed 's/^/  /'
echo "## 5. 표의 권한 목록(\dp orders)"
docker exec $N psql -U postgres -d lab -At -c '\dp orders' | sed 's/^/  /'
docker rm -f $N >/dev/null 2>&1
echo "## 끝"
이 단계의 파일 내려받기:roles_demo.sh

자주 막히는 곳

증상 원인 해결
DROP TABLE이 거부되었는데 검사는 허용으로 나옴 오류 문구가 must be owner라 permission denied만 찾으면 놓침 거부 판정 문구를 함께 검사
나중에 만든 표를 읽기 역할이 못 읽음 GRANT ... ON ALL TABLES는 지금 있는 표에만 적용 ALTER DEFAULT PRIVILEGES FOR ROLE 소유자
기본 권한을 정했는데 새 표에 안 붙음 표를 만든 역할과 FOR ROLE이 다름 표를 만드는 역할을 FOR ROLE에 지정
INSERT가 permission denied for sequence 표에는 권한이 있고 시퀀스에는 없음 시퀀스 USAGE 부여, 기본 권한도 ON SEQUENCES로
접속 자체가 안 됨 CONNECT를 PUBLIC에서 회수한 뒤 개별 부여를 잊음 GRANT CONNECT ON DATABASE ... TO 역할

확인 범위

  • 해 본 것: 2026-10-30, Windows 11 · Docker 29.5.3 · PostgreSQL 16에서 느슨한 상태의 12칸 검사(어긋난 6칸, 종료 코드 1), 최소 권한으로 고친 뒤의 검사(어긋난 0칸, 종료 코드 0), 설정 뒤에 만든 표의 기본 권한, has_table_privilege와 \dp 교차 확인을 실행했습니다. 전체 스크립트를 세 번 실행해 출력이 완전히 같았고, 끝에 컨테이너를 지웠습니다.
  • 해 보지 않은 것: 열 단위 권한(GRANT SELECT (열)), pg_hba.conf 접속 제한, 역할 상속(INHERIT)과 멤버십 구조, SECURITY DEFINER 함수, 스키마를 여러 개 쓰는 구성, 다른 PostgreSQL 버전, Linux와 macOS.
  • 읽을 때 주의: 비밀번호 lab과 로컬 접속은 시험용입니다. 12칸은 이 실습에 맞춰 정한 기대표이며 모든 위험을 다루지는 않습니다. 오류 문구는 PostgreSQL 16 기준입니다.

이 글의 다음 단계

  • MySQL 8.0 사용자와 권한 감사: 빈 비밀번호, 와일드카드MySQL 8.0 컨테이너에 일부러 느슨한 계정을 만들고, mysql.user와 information_schema 질의로 빈 비밀번호, % 호스트, GRANT OPTION 전역 권한을 찾는 감사 스크립트를 돌립니다. HIGH 발견 4건을 최소 권한으로 고쳐 0건으로 만들고, 실제 접속 시험 8칸과 SHOW GRANTS로 교차 확인한 뒤 종료 코드로 판정합니다.
  • NoSQL과 SQL 차이: 같은 상품 데이터를 두 방식으로 다뤄 보기같은 상품 데이터를 PostgreSQL 표와 MongoDB 컬렉션에 각각 넣고 같은 질문을 던집니다. 조건 검색 결과는 같지만, 새 필드를 추가할 때 PostgreSQL은 ALTER TABLE이 필요하고 MongoDB는 문서에 바로 넣을 수 있는 차이를 실행 결과로 확인합니다.

이 내용을 팀 교육으로 진행하고 싶으신가요?

인원, 수준, 일정에 맞춰 실습 환경까지 준비해 진행합니다. 대상과 기간을 알려 주시면 구성을 제안해 드립니다.

교육 문의하기

교육 진행 순서와 자주 묻는 질문 보기

팀이나 교육 담당자에게 이 글 제안하기

메일로 보내실 수도 있습니다: fankh111@gmail.com

같은 카테고리의 글