PostgreSQL 권한 및 스키마에 대한 이해

---

🔑 핵심 개념 요약

1. PostgreSQL은 "데이터베이스"와 "스키마"를 분리해서 관리합니다

• 데이터베이스: 전체 컨테이너 (접속 단위)
• 스키마: 데이터베이스 안의 네임스페이스(예: 폴더 같은 것)
  • 테이블, 뷰, 시퀀스 등의 객체는 스키마 안에 존재합니다.
• public 스키마는 모든 PostgreSQL 데이터베이스에 기본으로 하나 생성됩니다.

---

2. 데이터베이스 소유자 ≠ 스키마 권한 보유자

• CREATE DATABASE mydb OWNER myuser로 생성해도,
  • 그 myuser는 그 데이터베이스에 접속할 수는 있지만
  • public 스키마에서 객체(테이블 등)를 자동으로 만들 수는 없음

즉, 데이터베이스 접근 권한과 스키마 조작 권한은 별개입니다.

---

🔐 예시로 설명

1. 다음처럼 사용자와 데이터베이스를 만들었다고 가정:

CREATE USER myuser WITH PASSWORD 'mypass';
CREATE DATABASE mydb OWNER myuser;

1. myuser는 mydb에 로그인할 수는 있지만:

-- 실패
psql -U myuser -d mydb
CREATE TABLE test (...);
-- → ERROR: permission denied for schema public

1. 왜냐면, public 스키마는 여전히 postgres (DB 생성자)가 소유하고 있고,
   myuser는 public 스키마에 대해 USAGE와 CREATE 권한이 없음.

---

✅ 그래서 권한을 별도로 줘야 함

GRANT USAGE ON SCHEMA public TO myuser;
GRANT CREATE ON SCHEMA public TO myuser;

• USAGE: 스키마 안의 객체들을 참조 가능
• CREATE: 테이블, 뷰, 시퀀스 등 생성 가능

---

🧠 정리

구분 권한 종류 필요한 이유

데이터베이스	CONNECT / 소유	DB에 접속할 수 있도록
스키마 (public)	USAGE, CREATE	객체를 만들거나 접근하려면 별도 권한 필요

---

📌 권장: 별도 스키마 만들기 (보안 측면)

운영 환경에서는 public 대신 사용자 전용 스키마를 만드는 것이 더 안전합니다:

CREATE SCHEMA my_schema AUTHORIZATION myuser;
ALTER USER myuser SET search_path TO my_schema;

이렇게 하면 불필요한 public 접근 없이 독립된 공간을 제공합니다.

---

🧩 PostgreSQL 권한 구조 개요

┌─────────────────────────────┐
│         DATABASE            │
│       (mydb, etc.)          │
│                             │
│  ┌───────────────────────┐  │
│  │        SCHEMA         │  │  ← 기본: public
│  │    (네임스페이스)     │  │
│  │                       │  │
│  │  ┌─────────────────┐  │  │
│  │  │   TABLE, VIEW   │  │  │
│  │  │   FUNCTION, ... │  │  │
│  │  └─────────────────┘  │  │
│  └───────────────────────┘  │
└─────────────────────────────┘

---

🛡 권한 흐름 및 필요 권한

계층 예시 필요한 권한 설명

Database	mydb	CONNECT, OWNER	DB에 접속할 수 있어야 함
Schema	public, my_schema	USAGE, CREATE	스키마 안의 객체 접근/생성
Object	reddit_posts 테이블	SELECT, INSERT, UPDATE, ...	실제 데이터 작업 권한

---

🔑 권한 흐름 예시

1. DB 생성
2. CREATE DATABASE mydb OWNER postgres;
3. 사용자 생성
4. CREATE USER alice WITH PASSWORD 'pass';
5. 접속 권한 부여
6. GRANT CONNECT ON DATABASE mydb TO alice;
7. 스키마 권한 부여
8. GRANT USAGE, CREATE ON SCHEMA public TO alice;
9. (선택) 테이블 생성 후 작업 권한 부여
10. GRANT SELECT, INSERT ON TABLE reddit_posts TO alice;

---

🎯 권장 구조 (보안/유지보수 측면)

DATABASE: mydb
├── SCHEMA: alice_schema (소유자: alice)
│   ├── TABLE: reddit_posts
│   ├── VIEW: reddit_summary
│   └── ...

CREATE SCHEMA alice_schema AUTHORIZATION alice;
ALTER USER alice SET search_path TO alice_schema;

이렇게 하면 public 스키마 권한 없이도 안전하게 격리된 공간에서 작업 가능

---