PostgreSQL 17 환경 구축(2) : 기본 관리 - 권한, 롤, 사용자, 스키마, 익스텐션 등

안녕하세요! 갓대희 입니다.

오늘은 PostgreSQL의 기본 관리 개념을 정리하려 한다. 설치 후 실제 운영 환경에서 알아야 할 롤(Role), 권한(Privilege), 스키마(Schema), 익스텐션(Extension) 관리를 다룬다.

 

• 대상 버전: PostgreSQL 17.7 (2025년 11월 릴리즈)
• 사전 조건: PostgreSQL 설치 완료 (1장 참조)
• 공식문서 링크: Database Roles | Privileges | Schemas

목차

1. PostgreSQL 권한 체계 개요
2. 롤(Role)과 사용자 관리
3. 권한(Privileges) 관리
4. 스키마(Schema) 관리
5. 익스텐션(Extension) 관리
6. 실전 튜토리얼: 웹 애플리케이션 DB 환경 구축

 

1. PostgreSQL 권한 체계 개요

PostgreSQL은 롤(Role) 기반의 권한 관리 시스템을 사용한다. 다른 DBMS의 "사용자(User)"와 "그룹(Group)" 개념이 PostgreSQL에서는 모두 "롤"로 통합되어 있다.

 

1-1. USER vs ROLE 차이

PostgreSQL에서 CREATE USER와 CREATE ROLE은 거의 동일하다. 유일한 차이는 LOGIN 속성의 기본값이다.

-- CREATE USER = CREATE ROLE + LOGIN 속성
CREATE USER app_user; -- LOGIN 기본 포함
CREATE ROLE app_user LOGIN; -- 위와 동일

-- CREATE ROLE은 LOGIN 없음이 기본
CREATE ROLE dev_group; -- NOLOGIN 기본

(출처: PostgreSQL 17 - CREATE USER)

 

1-2. 권한 계층 구조

PostgreSQL 권한은 4단계 계층으로 구성된다. 상위 레벨의 권한이 있어도 하위 레벨 객체에 자동으로 접근할 수 있는 것은 아니다.

 

표 1-1. 권한 계층 구조

레벨	객체	주요 권한
서버(클러스터)	PostgreSQL 인스턴스	SUPERUSER, CREATEDB, CREATEROLE, REPLICATION
데이터베이스	DATABASE	CONNECT, CREATE, TEMPORARY
스키마	SCHEMA	USAGE, CREATE
객체	TABLE, FUNCTION, SEQUENCE 등	SELECT, INSERT, UPDATE, DELETE, EXECUTE 등

핵심 포인트
스키마 내 테이블에 접근하려면 먼저 해당 스키마에 USAGE 권한이 있어야 한다. 테이블 SELECT 권한만 있고 스키마 USAGE가 없으면 접근이 거부된다.

 

2. 롤(Role)과 사용자 관리

2-1. CREATE ROLE / CREATE USER

롤 생성의 기본 문법과 주요 옵션을 살펴본다.

-- 로그인 불가 그룹 롤 (권한 상속 용도)
CREATE ROLE dev_team NOLOGIN;

-- 로그인 가능 사용자
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_pass';

-- CREATE USER는 LOGIN이 기본 포함
CREATE USER developer WITH PASSWORD 'dev_pass';

-- 다양한 속성 지정
CREATE ROLE admin_user WITH
  LOGIN
  SUPERUSER
  CREATEDB
  CREATEROLE
  PASSWORD 'admin_pass';

 

2-2. Role Attributes (속성)

롤에 부여할 수 있는 9가지 주요 속성이다. 이 속성들은 서버 레벨에서 롤의 능력을 정의한다.

 

표 2-1. 주요 롤 속성

속성	설명	기본값
LOGIN	데이터베이스 접속 가능 여부	NOLOGIN
SUPERUSER	모든 권한 검사 우회 (최고 권한)	NOSUPERUSER
CREATEDB	데이터베이스 생성 권한	NOCREATEDB
CREATEROLE	롤 생성/수정 권한	NOCREATEROLE
REPLICATION	복제 연결 권한	NOREPLICATION
BYPASSRLS	Row-Level Security 우회	NOBYPASSRLS
INHERIT	그룹 롤의 권한 자동 상속	INHERIT (기본 상속)
CONNECTION LIMIT	최대 동시 연결 수	-1 (무제한)
PASSWORD	암호 인증용 비밀번호	NULL

(출처: PostgreSQL 17 - Role Attributes)

CREATEROLE 제한사항 (PostgreSQL 17)
CREATEROLE 권한을 가진 롤은 SUPERUSER나 REPLICATION 롤을 생성하거나 수정할 수 없다. 이는 권한 상승 공격을 방지하기 위한 보안 조치이다.

 

2-3. 롤 멤버십 (그룹 롤)

PostgreSQL에서는 롤이 다른 롤의 멤버가 될 수 있다. 이를 통해 권한을 그룹 단위로 관리할 수 있다.

-- 그룹 롤 생성
CREATE ROLE readonly_team NOLOGIN;

-- 멤버 롤 생성
CREATE ROLE analyst WITH LOGIN PASSWORD 'analyst_pass';

-- 그룹에 멤버 추가
GRANT readonly_team TO analyst;

-- 멤버십 확인 (psql)
\du analyst
List of roles
Role name | Member of
-----------+----------------
analyst | {readonly_team}

INHERIT vs SET ROLE

• INHERIT (기본값): 그룹 롤의 권한이 자동으로 적용됨
• NOINHERIT: SET ROLE group_role; 명령으로 명시적 전환 필요

 

GRANT 멤버십 WITH 옵션 (PostgreSQL 16+)

PostgreSQL 16부터 GRANT role TO member 시 세 가지 옵션으로 멤버십 동작을 세밀하게 제어할 수 있다.

옵션	설명
WITH INHERIT TRUE/FALSE	그룹 권한 자동 상속 여부 (멤버의 INHERIT 속성 오버라이드)
WITH SET TRUE/FALSE	SET ROLE로 그룹 롤 전환 가능 여부
WITH ADMIN TRUE/FALSE	다른 멤버에게 같은 그룹 멤버십을 부여/회수할 수 있는지

-- 예: 권한은 상속하지만 SET ROLE 금지
GRANT dev_team TO junior_dev WITH INHERIT TRUE, SET FALSE;

-- 예: 팀 리더에게 멤버 관리 권한 위임
GRANT dev_team TO team_lead WITH ADMIN TRUE;

-- team_lead가 다른 사람을 dev_team에 추가 가능
-- (team_lead로 접속 후)
GRANT dev_team TO new_member;

(출처: PostgreSQL 17 - Role Membership)

 

2-4. Predefined Roles (시스템 롤)

PostgreSQL 17은 15개의 사전 정의된 롤을 제공한다. SUPERUSER 권한 없이도 특정 관리 작업을 위임할 수 있다.

 

표 2-2. PostgreSQL 17 시스템 롤 (전체 15개)

롤	용도
pg_read_all_data	모든 테이블/뷰 SELECT 권한 (스키마 USAGE 포함)
pg_write_all_data	모든 테이블 INSERT/UPDATE/DELETE 권한
pg_read_all_settings	모든 설정값 조회 (pg_settings, pg_file_settings 등)
pg_read_all_stats	모든 pg_stat_* 뷰 조회 (다른 사용자 쿼리 포함)
pg_stat_scan_tables	시스템 카탈로그 직접 스캔 가능
pg_monitor	위 3개 롤 포함 (read_all_settings + read_all_stats + stat_scan_tables)
pg_database_owner	현재 DB 소유자를 대표하는 가상 롤
pg_signal_backend	다른 세션에 시그널 전송 (pg_cancel_backend, pg_terminate_backend)
pg_checkpoint	CHECKPOINT 명령 실행
pg_maintain	VACUUM, ANALYZE, REINDEX, CLUSTER 실행 (모든 DB 객체 대상)
pg_use_reserved_connections	reserved_connections 슬롯 사용 가능
pg_create_subscription	CREATE SUBSCRIPTION 명령 실행
pg_read_server_files ⚠️	서버 파일시스템 읽기 (COPY FROM, pg_read_file 등)
pg_write_server_files ⚠️	서버 파일시스템 쓰기 (COPY TO 절대경로 등)
pg_execute_server_program ⚠️	서버에서 OS 명령 실행 (COPY FROM/TO PROGRAM)

(출처: PostgreSQL 17 - Predefined Roles)

⚠️ 고위험 롤 주의
pg_read_server_files, pg_write_server_files, pg_execute_server_program은 SUPERUSER 수준의 권한 상승이 가능하다. 이 롤들은 DB 서버의 파일시스템에 직접 접근하거나 OS 명령을 실행할 수 있어, 신뢰할 수 있는 DBA에게만 부여해야 한다.

실용 예제: 모니터링 전용 계정

-- 모니터링 전용 계정 생성
CREATE ROLE monitor_user WITH LOGIN PASSWORD 'mon_pass';
GRANT pg_monitor TO monitor_user;

-- 이 계정으로 pg_stat_activity 조회 가능
SELECT * FROM pg_stat_activity;

 

2-5. ALTER ROLE / DROP ROLE

-- 속성 변경
ALTER ROLE app_user WITH CREATEDB;
ALTER ROLE app_user CONNECTION LIMIT 10;

-- 비밀번호 변경
ALTER ROLE app_user WITH PASSWORD 'new_password';

-- 롤별 기본 설정 (세션 시작 시 적용)
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET search_path = myschema, public;

-- 롤 삭제
DROP ROLE app_user;

롤 삭제 시 주의사항
롤이 소유한 객체나 권한이 있으면 삭제할 수 없다. 먼저 REASSIGN OWNED BY old_role TO new_role;로 소유권을 이전하거나, DROP OWNED BY old_role;로 소유 객체를 삭제해야 한다.

 

2-6. 롤 정보 조회 (시스템 카탈로그)

롤 정보는 pg_roles 뷰와 pg_auth_members 테이블에서 조회할 수 있다. psql 메타 명령어도 유용하다.

-- psql: 모든 롤 목록
\du

-- psql: 롤 상세 정보
\du+

-- SQL: 롤 속성 조회
SELECT rolname, rolsuper, rolinherit, rolcreaterole,
       rolcreatedb, rolcanlogin, rolconnlimit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';

-- SQL: 롤 멤버십 조회 (그룹 구조)
SELECT
  r.rolname AS role,
  m.rolname AS member,
  g.rolname AS grantor
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
JOIN pg_roles g ON g.oid = am.grantor;

실무 팁: 현재 세션 정보 확인
SELECT current_user; - 현재 롤
SELECT session_user; - 세션 시작 롤 (SET ROLE 전)
SELECT current_database(); - 현재 데이터베이스

 

3. 권한(Privileges) 관리

3-1. GRANT / REVOKE 기본

PostgreSQL에서 권한 부여/회수는 GRANT와 REVOKE 명령으로 수행한다.

표 3-1. 객체별 권한 종류

객체	가능한 권한	psql 확인
TABLE	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN (PG17)	\dp
SCHEMA	USAGE, CREATE	\dn+
SEQUENCE	USAGE, SELECT, UPDATE	\dp
FUNCTION	EXECUTE	\df+
DATABASE	CONNECT, CREATE, TEMPORARY	\l

(출처: PostgreSQL 17 - Privileges)

 

3-2. ACL 표기법 이해하기

psql에서 \dp 명령을 실행하면 권한이 ACL(Access Control List) 형식으로 표시된다. 이 표기법을 이해하면 권한 상태를 빠르게 파악할 수 있다.

 

ACL 표기법 형식

-- ACL 형식: grantee=privileges/grantor
-- 예시 해석:
app_user=arwd/postgres
│ │ └── 권한을 부여한 롤 (postgres)
│ └── 부여된 권한 (a=INSERT, r=SELECT, w=UPDATE, d=DELETE)
└── 권한을 받은 롤 (app_user)

 

표 3-2. 권한 약어 (ACL Abbreviations)

약어	권한	설명
r	SELECT ("read")	테이블/뷰에서 데이터 조회
a	INSERT ("append")	테이블에 새 행 삽입
w	UPDATE ("write")	기존 행 수정
d	DELETE	행 삭제
D	TRUNCATE	테이블 전체 비우기
x	REFERENCES	외래키 참조 생성
t	TRIGGER	트리거 생성
m	MAINTAIN (PG17 신규)	VACUUM, ANALYZE, REINDEX 등 유지보수 작업
X	EXECUTE	함수/프로시저 실행
U	USAGE	스키마/시퀀스 사용
C	CREATE	스키마/DB에 객체 생성
*	WITH GRANT OPTION	해당 권한을 다른 롤에 부여 가능

(출처: PostgreSQL 17 - ACL Privilege Abbreviations)

 

\dp 명령 실전 예제

-- 테이블 권한 확인
\dp orders

Access privileges
Schema | Name | Type | Access privileges
--------+--------+-------+-----------------------------
public | orders | table | postgres=arwdDxt/postgres +
| | | app_user=arwd/postgres +
| | | readonly=r/postgres

-- 해석:
-- postgres: 모든 권한 (소유자)
-- app_user: INSERT, SELECT, UPDATE, DELETE
-- readonly: SELECT만

 

3-3. MAINTAIN 권한 (PostgreSQL 17 신규)

MAINTAIN은 PostgreSQL 17에서 새로 추가된 권한이다. 데이터 접근 권한 없이 유지보수 작업만 위임할 수 있어, DBA 업무를 안전하게 분산할 수 있다.

MAINTAIN으로 허용되는 작업

• VACUUM - 불필요한 행 정리, 공간 회수
• ANALYZE - 통계 정보 갱신 (쿼리 플래너 최적화)
• REINDEX - 인덱스 재구축
• CLUSTER - 인덱스 기준 테이블 물리적 재정렬
• REFRESH MATERIALIZED VIEW - 구체화된 뷰 갱신
• LOCK TABLE - 명시적 테이블 잠금

-- DBA팀에게 유지보수 권한만 부여
CREATE ROLE dba_team NOLOGIN;
GRANT MAINTAIN ON ALL TABLES IN SCHEMA public TO dba_team;

-- 이제 dba_team 멤버는 SELECT/INSERT 없이도:
VACUUM ANALYZE public.orders; -- 성공!
REINDEX TABLE public.orders; -- 성공!
SELECT * FROM public.orders; -- 실패 (permission denied)

실무 활용: 운영 환경 분리
MAINTAIN 권한 덕분에 운영팀에게 VACUUM/ANALYZE 업무를 위임하면서도 민감한 데이터 접근은 차단할 수 있다. 이전에는 테이블 소유권이나 SUPERUSER가 필요했던 작업이다.
(출처: PostgreSQL 17 - Privileges)

 

3-4. 테이블 권한 예제

-- 기본 권한 부여
GRANT SELECT, INSERT ON orders TO app_user;

-- 모든 권한 부여
GRANT ALL ON accounts TO admin_user;

-- 특정 컬럼만 권한 부여
GRANT SELECT (id, name), UPDATE (status) ON users TO app_user;

-- 권한 회수
REVOKE INSERT ON orders FROM app_user;

-- 권한 확인 (psql)
\dp orders

 

3-5. WITH GRANT OPTION (권한 위임)

WITH GRANT OPTION을 사용하면 권한을 받은 롤이 다른 롤에게 해당 권한을 재부여할 수 있다. 권한 관리를 팀 리더에게 위임할 때 유용하다.

-- team_lead에게 SELECT 권한 + 위임 권한 부여
GRANT SELECT ON customers TO team_lead WITH GRANT OPTION;

-- 이제 team_lead가 다른 롤에게 SELECT 권한 부여 가능
-- (team_lead로 접속 후)
GRANT SELECT ON customers TO junior_dev;

-- ACL에서 * 표시로 확인 가능
\dp customers
-- team_lead=r*/postgres (r* = SELECT with grant option)

권한 위임 체인 주의
team_lead의 권한을 회수하면(REVOKE), team_lead가 부여한 junior_dev의 권한도 함께 회수(cascade)된다. 권한 위임 구조를 신중하게 설계해야 한다.
(출처: PostgreSQL 17 - GRANT)

REVOKE CASCADE / RESTRICT

WITH GRANT OPTION으로 부여된 권한을 회수할 때, 연쇄 회수 동작을 제어할 수 있다.

-- CASCADE: 연쇄적으로 부여된 권한도 모두 회수 (기본값)
REVOKE SELECT ON customers FROM team_lead CASCADE;
-- team_lead가 부여한 junior_dev의 SELECT도 함께 회수됨

-- RESTRICT: 연쇄 권한이 있으면 에러 발생 (안전 모드)
REVOKE SELECT ON customers FROM team_lead RESTRICT;
ERROR: dependent privileges exist
-- junior_dev의 권한을 먼저 회수해야 함

실무 권장
운영 환경에서는 RESTRICT를 먼저 시도하여 영향 범위를 확인한 후, 필요한 경우에만 CASCADE를 사용하는 것이 안전하다.
(출처: PostgreSQL 17 - REVOKE)

 

3-6. 스키마 권한

스키마 내 객체에 접근하려면 먼저 스키마 USAGE 권한이 필요하다. 이 단계를 빠뜨리면 테이블 SELECT 권한이 있어도 접근이 거부된다.

-- 스키마 접근 권한 (필수!)
GRANT USAGE ON SCHEMA api TO app_role;

-- 스키마 내 모든 기존 테이블에 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA api TO app_role;

-- 스키마에 객체 생성 권한 부여
GRANT CREATE ON SCHEMA api TO dev_role;

 

3-7. 기본 권한 설정 (ALTER DEFAULT PRIVILEGES)

GRANT ON ALL TABLES는 현재 존재하는 테이블에만 적용된다. 앞으로 생성될 테이블에 자동으로 권한을 부여하려면 ALTER DEFAULT PRIVILEGES를 사용한다.

-- 앞으로 생성되는 테이블에 자동 SELECT 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_role;

-- 특정 사용자가 생성하는 객체에만 적용
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA api
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- 시퀀스에도 자동 권한 부여 (SERIAL 컬럼용)
ALTER DEFAULT PRIVILEGES IN SCHEMA api
  GRANT USAGE ON SEQUENCES TO app_role;

실무 필수 패턴
새 스키마에 애플리케이션 계정 설정 시, GRANT USAGE, GRANT ON ALL TABLES, ALTER DEFAULT PRIVILEGES 세 가지를 모두 실행하는 것이 일반적이다.

 

3-8. PUBLIC 롤과 보안

PUBLIC은 모든 롤을 의미하는 특수 그룹이다. 기본적으로 몇 가지 권한이 PUBLIC에 부여되어 있다.

표 3-3. PUBLIC 기본 권한

객체	PUBLIC 기본 권한
DATABASE	CONNECT, TEMPORARY
public 스키마	USAGE (PostgreSQL 15+ CREATE 권한 제거됨)
FUNCTION	EXECUTE

PostgreSQL 15+ 보안 변경사항
PostgreSQL 15부터 public 스키마에 대한 CREATE 권한이 PUBLIC에서 제거되었다. 이전 버전에서는 모든 사용자가 public 스키마에 객체를 생성할 수 있었다.
(출처: PostgreSQL 17 - Schema Usage Patterns)

 

4. 스키마(Schema) 관리

4-1. 스키마 개념

스키마는 데이터베이스 내의 네임스페이스이다. 같은 이름의 테이블을 다른 스키마에 생성할 수 있어 객체 이름 충돌을 방지한다.

-- 같은 테이블 이름, 다른 스키마
sales.orders -- 판매 주문
warehouse.orders -- 창고 주문

-- 정규화된 이름 (fully qualified name)
SELECT * FROM sales.orders;
SELECT * FROM warehouse.orders;

 

4-2. 스키마 생성/삭제

-- 스키마 생성
CREATE SCHEMA app_schema;

-- 소유자 지정하여 생성
CREATE SCHEMA app_schema AUTHORIZATION app_owner;

-- 스키마와 내부 객체 모두 삭제
DROP SCHEMA app_schema CASCADE;

-- 빈 스키마만 삭제 (내부 객체 있으면 에러)
DROP SCHEMA app_schema RESTRICT; -- 기본값

 

4-3. search_path 설정

search_path는 스키마를 지정하지 않았을 때 객체를 검색하는 순서를 정의한다.

-- 현재 search_path 확인
SHOW search_path;
-- 기본: "$user", public

-- 세션 레벨 설정
SET search_path TO myschema, public;

-- 롤 레벨 영구 설정
ALTER ROLE myuser SET search_path = myschema, public;

-- 데이터베이스 레벨 설정
ALTER DATABASE mydb SET search_path = myschema, public;

"$user" 의미
$user는 현재 접속한 롤 이름과 동일한 스키마를 의미한다. 예를 들어 app_user로 접속하면 app_user 스키마를 먼저 검색한다. 해당 스키마가 없으면 건너뛴다.

⚠️ search_path 보안 경고
search_path에 스키마가 포함되어 있으면, 해당 스키마에 CREATE 권한을 가진 사용자가 동명의 악성 객체를 생성하여 공격할 수 있다. 예를 들어 public 스키마에 count() 함수를 만들어 기존 함수를 가로채는 방식이다.

보안 권장사항:

• 신뢰할 수 없는 사용자에게 스키마 CREATE 권한을 부여하지 않는다
• public 스키마 사용을 최소화하고, 전용 스키마를 생성한다
• 함수 호출 시 pg_catalog.function_name()처럼 명시적으로 스키마를 지정한다

(출처: PostgreSQL 17 - Schema Usage Patterns)

pg_catalog 자동 검색
pg_catalog 스키마는 search_path에 명시하지 않아도 항상 첫 번째로 검색된다. 내장 함수(count, sum 등)가 여기에 위치한다. search_path에 명시적으로 포함하면 해당 위치에서 검색된다.

 

4-4. 스키마 활용 패턴

패턴 1: 애플리케이션 계층 분리

-- 계층별 스키마 생성
CREATE SCHEMA api; -- 외부 노출 API용
CREATE SCHEMA internal; -- 내부 로직용
CREATE SCHEMA audit; -- 감사 로그용

-- 애플리케이션은 api 스키마만 접근
GRANT USAGE ON SCHEMA api TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA api TO app_role;

패턴 2: 멀티테넌트 (사용자별 스키마)

-- 테넌트별 스키마 생성
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;

-- $user를 활용한 자동 스키마 선택
-- tenant_a 롤로 접속 시 tenant_a 스키마가 기본 search_path
ALTER ROLE tenant_a SET search_path = "$user";

 

5. 익스텐션(Extension) 관리

5-1. 익스텐션 개념

익스텐션은 PostgreSQL의 기능을 확장하는 패키지이다. 함수, 데이터 타입, 연산자, 인덱스 방법 등을 추가할 수 있다.

Trusted vs Untrusted 익스텐션

• Trusted: 데이터베이스 소유자(CREATEDB 권한)가 설치 가능. 파일 시스템 접근 등 위험한 기능 없음
• Untrusted: SUPERUSER만 설치 가능. C 라이브러리 로드, 서버 프로그램 실행 등 포함

(출처: PostgreSQL 17 - Extensions)

 

5-2. 익스텐션 설치/관리

-- 익스텐션 설치
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 특정 스키마에 설치
CREATE EXTENSION hstore WITH SCHEMA extensions;

-- 익스텐션 업데이트
ALTER EXTENSION pgcrypto UPDATE;

-- 익스텐션 삭제 (의존 객체도 삭제)
DROP EXTENSION pgcrypto CASCADE;

-- 설치된 익스텐션 확인 (psql)
\dx

-- SQL로 확인
SELECT * FROM pg_extension;

 

5-3. 자주 사용하는 익스텐션

표 5-1. 실무 필수 익스텐션

익스텐션	용도	Trusted
pgcrypto	암호화/해시 함수 (bcrypt, AES 등)	Yes
uuid-ossp	UUID 생성 (v1, v4)	Yes
pg_stat_statements	SQL 실행 통계 수집 (성능 분석)	No
pg_trgm	유사 텍스트 검색 (트라이그램)	Yes
hstore	Key-Value 저장	Yes

(출처: PostgreSQL 17 - Additional Supplied Modules)

 

5-4. 실용 예제

pgcrypto - 비밀번호 해싱 (bcrypt)

-- 익스텐션 설치
CREATE EXTENSION pgcrypto;

-- 비밀번호 해싱 (bcrypt, cost=8)
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', crypt('mypassword', gen_salt('bf', 8)));

-- 비밀번호 검증
SELECT * FROM users
WHERE email = 'user@example.com'
  AND password_hash = crypt('input_password', password_hash);

uuid-ossp - UUID 기본키

-- 익스텐션 설치
CREATE EXTENSION "uuid-ossp";

-- UUID v4 (랜덤) 기본키 테이블
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 또는 PostgreSQL 13+ 내장 함수 사용
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

pg_stat_statements - 쿼리 성능 분석

-- 익스텐션 설치 (SUPERUSER 필요)
CREATE EXTENSION pg_stat_statements;

-- postgresql.conf 설정 필요
-- shared_preload_libraries = 'pg_stat_statements'

-- 가장 느린 쿼리 Top 10
SELECT
  substring(query, 1, 60) AS query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

pg_trgm - 유사 텍스트 검색

-- 익스텐션 설치
CREATE EXTENSION pg_trgm;

-- 유사도 검색용 GIN 인덱스
CREATE INDEX idx_products_name_trgm ON products
  USING GIN (name gin_trgm_ops);

-- 유사 검색 (오타 허용)
SELECT name, similarity(name, 'postgresql') AS sim
FROM products
WHERE name % 'postgresql' -- 유사도 임계값 초과
ORDER BY sim DESC;

 

6. 실전 튜토리얼: 웹 애플리케이션 DB 환경 구축

지금까지 배운 내용을 종합하여 웹 애플리케이션용 PostgreSQL 환경을 처음부터 끝까지 구축해 본다. 초보자도 따라할 수 있도록 단계별로 설명한다.

시나리오
- 프로젝트명: myapp
- 필요한 롤: 관리자(admin), 애플리케이션(app), 읽기전용(readonly)
- 스키마 구조: api(외부 노출), internal(내부 로직)

 

Step 1: 데이터베이스 생성

-- postgres 슈퍼유저로 접속
psql -U postgres

-- 데이터베이스 생성
CREATE DATABASE myapp;

-- myapp 데이터베이스로 전환
\c myapp

 

Step 2: 롤 생성

-- 1) 그룹 롤 생성 (로그인 불가, 권한 상속용)
CREATE ROLE myapp_readonly NOLOGIN; -- 읽기 전용 그룹
CREATE ROLE myapp_readwrite NOLOGIN; -- 읽기/쓰기 그룹

-- 2) 실제 로그인 롤 생성
CREATE ROLE myapp_admin WITH LOGIN PASSWORD 'admin_secure_pw';
CREATE ROLE myapp_app WITH LOGIN PASSWORD 'app_secure_pw';
CREATE ROLE myapp_report WITH LOGIN PASSWORD 'report_secure_pw';

-- 3) 그룹에 멤버 추가
GRANT myapp_readwrite TO myapp_admin; -- admin은 읽기/쓰기
GRANT myapp_readwrite TO myapp_app; -- app도 읽기/쓰기
GRANT myapp_readonly TO myapp_report; -- report는 읽기만

-- 4) 롤 확인
\du myapp*

 

Step 3: 스키마 생성

-- 스키마 생성
CREATE SCHEMA api; -- 외부 API용 테이블
CREATE SCHEMA internal; -- 내부 로직용 테이블

-- 스키마 확인
\dn

 

Step 4: 스키마 권한 설정

-- api 스키마: 모든 그룹이 USAGE 가능
GRANT USAGE ON SCHEMA api TO myapp_readonly;
GRANT USAGE ON SCHEMA api TO myapp_readwrite;

-- internal 스키마: readwrite 그룹만 접근
GRANT USAGE ON SCHEMA internal TO myapp_readwrite;

-- 권한 확인
\dn+

 

Step 5: 테이블 생성 및 권한 부여

-- 테이블 생성
CREATE TABLE api.users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE api.orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES api.users(id),
  total_amount NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE internal.audit_log (
  id SERIAL PRIMARY KEY,
  action VARCHAR(50),
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 기존 테이블 권한 부여
GRANT SELECT ON ALL TABLES IN SCHEMA api TO myapp_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA api TO myapp_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA internal TO myapp_readwrite;

-- 시퀀스 권한 (SERIAL 컬럼용)
GRANT USAGE ON ALL SEQUENCES IN SCHEMA api TO myapp_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA internal TO myapp_readwrite;

 

Step 6: 기본 권한 설정 (미래 객체용)

-- 앞으로 생성되는 테이블에 자동 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA api
  GRANT SELECT ON TABLES TO myapp_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA api
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA internal
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myapp_readwrite;

-- 앞으로 생성되는 시퀀스에 자동 권한
ALTER DEFAULT PRIVILEGES IN SCHEMA api
  GRANT USAGE ON SEQUENCES TO myapp_readwrite;

ALTER DEFAULT PRIVILEGES IN SCHEMA internal
  GRANT USAGE ON SEQUENCES TO myapp_readwrite;

Step 7: 익스텐션 설치

-- 실무에서 자주 사용하는 익스텐션
CREATE EXTENSION IF NOT EXISTS pgcrypto; -- 비밀번호 해싱
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- UUID 생성

-- 설치 확인
\dx

 

Step 8: 권한 검증 테스트

-- myapp_app 롤로 접속 테스트
psql -U myapp_app -d myapp

-- 데이터 삽입 테스트 (성공해야 함)
INSERT INTO api.users (email) VALUES ('test@example.com');
SELECT * FROM api.users;

-- internal 스키마 접근 테스트 (성공해야 함)
INSERT INTO internal.audit_log (action, details)
VALUES ('USER_CREATED', '{"email": "test@example.com"}');

-- myapp_report 롤로 접속 테스트
\c myapp myapp_report

-- SELECT만 가능, INSERT는 거부되어야 함
SELECT * FROM api.users; -- 성공
INSERT INTO api.users (email) VALUES ('fail@example.com'); -- 에러!
ERROR: permission denied for table users

튜토리얼 완료!
위 단계를 완료하면 다음과 같은 구조가 완성된다:

• myapp_admin: 모든 테이블 읽기/쓰기
• myapp_app: 모든 테이블 읽기/쓰기 (애플리케이션 연결용)
• myapp_report: api 스키마 읽기 전용 (리포팅/분석용)

새 테이블을 추가해도 ALTER DEFAULT PRIVILEGES 덕분에 권한이 자동으로 부여된다.

마무리

이번 글에서는 PostgreSQL의 기본 관리 개념을 다루었다.

핵심 요약:

• 롤: USER와 ROLE은 LOGIN 속성 기본값만 다름, 그룹 롤로 권한 상속 가능
• 권한: 스키마 USAGE → 테이블 SELECT 순서로 부여, ALTER DEFAULT PRIVILEGES 필수
• 스키마: 네임스페이스로 활용, search_path로 기본 스키마 설정
• 익스텐션: pgcrypto, pg_stat_statements, pg_trgm 등 실무 필수
• 실전 튜토리얼: 롤 → 스키마 → 권한 → 기본 권한 순서로 체계적 구축

다음 글에서는 성능 튜닝 Preset을 다룰 예정이다. 메모리, WAL, 연결 설정의 환경별 최적값을 소개한다.

궁금한 점은 댓글로 남겨주시면 아는 선에서 답변드리겠다!

 

참고 자료

공식 문서

• Database Roles (롤 관리)
• Role Attributes (롤 속성)
• Predefined Roles (시스템 롤)
• Privileges (권한)
• Schemas (스키마)
• Extensions (익스텐션)
• Additional Supplied Modules (contrib)

검증 정보

• 이 글의 명령어는 PostgreSQL 17 공식 문서(2026년 1월 기준)와 대조 검증됨
• 모든 SQL 예제는 PostgreSQL 17 환경에서 실행 가능