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

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

- 주소: https://copyandrun.com/blog/postgres-roles-least-privilege/
- 분야: DevSecOps
- 발행일: 2026-10-30
- 태그: PostgreSQL, 최소 권한, GRANT, REVOKE, DEFAULT PRIVILEGES

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

## 준비물

| 항목 | 확인한 값 |
|---|---|
| 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. 느슨한 상태

<!-- files: roles_demo.sh -->

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

- `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인 것을 확인합니다.](https://copyandrun.com/img/postgres-roles-least-privilege/01-느슨한-상태-검사.png)

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

## 3. 최소 권한으로 고치기

<!-- files: roles_demo.sh -->

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

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`를 만들어 검사했습니다.

## 4. 같은 표로 다시 검사

<!-- files: roles_demo.sh -->

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

## 5. 교차 확인

<!-- files: roles_demo.sh -->

실제로 문장을 실행해 본 결과가 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는 읽기, 추가, 수정인 것을 확인합니다.](https://copyandrun.com/img/postgres-roles-least-privilege/02-최소-권한-검사.png)

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

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

## 전체 코드

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

```bash
# 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 "## 끝"
```

## 자주 막히는 곳

| 증상 | 원인 | 해결 |
|---|---|---|
| `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 기준입니다.
