postgresql.co.kr Logo
postgresql.co.kr
PostgreSQL
강의

#1 - 처음 만드는 PostgreSQL 프로젝트 DB 생성 및 실행문 까지

중년개발자
중년개발자

@loxo

29일 전

40

📘 처음 만드는 PostgreSQL 프로젝트 DB 생성

인프라부터 권한 구조까지, 실무 기준으로 이해하기

실제 서비스 정보(DB명, 계정명, 경로 등)는 모두 교육용 가명으로 대체되어 있습니다.

왜 프로젝트는 데이터베이스 설계부터 시작해야 하는가?

프로젝트 초기에 데이터베이스를 대충 만들어도 당장은 잘 동작합니다.
하지만 시간이 지나면 아래 문제는 반드시 발생합니다.

  • 권한이 뒤엉켜 보안 사고 위험 증가
  • 운영 중 DDL 실행으로 인한 서비스 장애
  • 성능 튜닝이 불가능한 구조
  • 백업·복구가 어려운 DB

이 강의는
👉 “처음부터 운영을 고려한 PostgreSQL DB 설계 방법”
을 초보자도 이해할 수 있도록 설명합니다.

STEP 1. 인프라 구축 (Superuser 단계)

  • postgres 계정으로 접속

🔑 Superuser란 무엇인가?

PostgreSQL에는 기본적으로 postgres 라는 Superuser 계정이 존재합니다.

Superuser(postgres)의 특징

  • 모든 데이터베이스 및 객체에 대한 절대 권한
  • 데이터베이스(DATABASE) / 역할(ROLE) / 테이블스페이스(TABLESPACE) 생성 가능
  • 확장(Extension) 설치 가능
  • 시스템 레벨 설정 변경 가능

⚠️ 애플리케이션은 절대 Superuser로 접속하면 안 됩니다.

왜 이 단계는 postgres 사용자로 실행해야 하는가?

작업이유
데이터베이스 생성일반 사용자 불가
테이블스페이스 생성OS 디렉토리 접근 필요
역할 생성보안 관리
확장 설치시스템 레벨 기능

DBA 관점
“Superuser는 설계자이지 사용자가 아니다.”

1-1. 데이터베이스 생성

sql
CREATE DATABASE project_db WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = icu ICU_LOCALE = 'ko';

왜 DB를 먼저 만드는가?

  • DB는 프로젝트의 최상위 경계
  • 백업·복구·마이그레이션 단위

1-2. 테이블스페이스 분리 (데이터 / 인덱스)

sql
CREATE TABLESPACE project_data_ts LOCATION '/path/to/data'; CREATE TABLESPACE project_index_ts LOCATION '/path/to/index';

왜 분리하는가?

  • 데이터와 인덱스는 I/O 패턴이 다름
  • 성능 튜닝과 확장 대비

튜닝 전문가 관점
“지금은 하나의 디스크라도 구조는 미래를 대비한다.”

1-3. 역할(Role) 분리

sql
CREATE USER project_owner WITH ENCRYPTED PASSWORD 'owner_password'; CREATE USER project_app WITH ENCRYPTED PASSWORD 'app_password';
계정역할
project_owner스키마·테이블·함수 생성 (DDL)
project_appCRUD 전용 - 실제 애플리케이션 계정 (Spring Boot)

핵심 의도

  • 최소 권한 원칙
  • 실수와 보안 사고 차단

1-3-1. search_path 기본 설정

sql
ALTER ROLE project_owner IN DATABASE project_db SET search_path = core, project_owner, public; ALTER ROLE project_app IN DATABASE project_db SET search_path = core, project_owner, public;

search_path란?

  • SQL 실행 시 스키마 탐색 순서

핵심 의도

  • core 스키마 중심 설계
  • 잘못된 스키마 생성 방지
  • core 스키마 이름은 도메인 중심의 이름
  • 스키마 이름은 기능이 아니라 역할을 말해야 한다.
  • auth, log, stat, batch 등 역할 중심의 이름을 사용하여 스키마를 분리
  • 하나의 Database = 하나의 업무 도메인, 스키마로 여러 업무를 하는 것은 비권장
text
project_db ├─ core (공통 핵심) ├─ billing (결제) ├─ auth (인증) ├─ log (로그) └─ stat (통계)

1-4. 핵심 스키마(core) 생성

sql
CREATE SCHEMA IF NOT EXISTS core AUTHORIZATION project_owner;

왜 core 스키마인가?

  • 핵심 비즈니스 테이블 집합
  • public 스키마 오염 방지

1-5. 데이터베이스 및 테이블스페이스 권한

sql
GRANT ALL PRIVILEGES ON DATABASE project_db TO project_owner; GRANT CREATE ON TABLESPACE project_data_ts TO project_owner; GRANT CREATE ON TABLESPACE project_index_ts TO project_owner;

핵심 의도

  • 소유권과 책임의 명확화

1-5-1. public 스키마 권한 (중요)

sql
GRANT USAGE, CREATE ON SCHEMA public TO project_owner; GRANT USAGE ON SCHEMA public TO project_app;

왜 public을 완전히 막지 않는가?

  • 확장(Extension), 함수 호환성
  • PostgreSQL 내부 구조 대응

1-6. 확장(Extension) 설치

sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA core; CREATE EXTENSION IF NOT EXISTS "pgcrypto" SCHEMA core;

왜 초기에 설치하는가?

  • ID 전략과 보안 전략은 초기 고정
  • 이후 변경 시 대규모 수정 발생

STEP 2. 운영 레벨 설정 (DB Owner)

  • project_owner 계정으로 접속
  • DBeaver 툴로 접속하여 작업해도 무관

2-1. 세션 search_path

sql
SET search_path TO core, project_owner, public;

2-2. 애플리케이션 사용자 접근 허용

sql
GRANT USAGE ON SCHEMA core TO project_app;

2-3. DEFAULT PRIVILEGES 설정 (가장 중요)

sql
ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA core GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA core GRANT USAGE, SELECT ON SEQUENCES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO project_app;

왜 중요한가?

  • 미래에 생성될 테이블에도 자동 적용
  • 운영 중 권한 누락 사고 방지
  • CI/CD 친화적 구조

DBA 관점
“DEFAULT PRIVILEGES는 운영 DB의 보험이다.”

📌 전체 실행 스크립트 (강의용 통합본)

sql
-- ========================================================= -- STEP 1: Superuser (postgres) -- ========================================================= CREATE DATABASE project_db; CREATE TABLESPACE project_data_ts LOCATION '/path/to/data'; CREATE TABLESPACE project_index_ts LOCATION '/path/to/index'; CREATE USER project_owner WITH ENCRYPTED PASSWORD 'owner_password'; CREATE USER project_app WITH ENCRYPTED PASSWORD 'app_password'; ALTER ROLE project_owner IN DATABASE project_db SET search_path = core, project_owner, public; ALTER ROLE project_app IN DATABASE project_db SET search_path = core, project_owner, public; \c project_db CREATE SCHEMA IF NOT EXISTS core AUTHORIZATION project_owner; GRANT ALL PRIVILEGES ON DATABASE project_db TO project_owner; GRANT CREATE ON TABLESPACE project_data_ts TO project_owner; GRANT CREATE ON TABLESPACE project_index_ts TO project_owner; GRANT USAGE, CREATE ON SCHEMA public TO project_owner; GRANT USAGE ON SCHEMA public TO project_app; CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA core; CREATE EXTENSION IF NOT EXISTS "pgcrypto" SCHEMA core; -- ========================================================= -- STEP 2: Database Owner (project_owner) -- ========================================================= SET search_path TO public, core, project_owner; GRANT USAGE ON SCHEMA core TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA core GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA core GRANT USAGE, SELECT ON SEQUENCES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO project_app; ALTER DEFAULT PRIVILEGES FOR ROLE project_owner IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO project_app;

🎯 강의 핵심 한 줄 요약

“DB는 데이터를 저장하는 곳이 아니라,
운영과 성장을 견디는 구조물이다.”

목차

#PostgreSQL#DB설계#데이터베이스#SQL#인프라구축

댓글 0

Ctrl + Enter를 눌러 등록할 수 있습니다
※ AI 다듬기는 내용을 정제하는 보조 기능이며, 최종 내용은 사용자가 확인해야 합니다.