공부 기록 남기기

지금까지 RDBMS 라고는 MYSQL과 그 사촌 MariaDB만 사용하다가, 이번에 PostgreSQL을 사용해 봐야겠다는 생각이 들어서 무턱 대고 설치해봤다. 일단 데이터베이스 생성과 유저 생성, 권한 관리는 MySQL이랑 대충 비슷하겠지 생각하고 검색해봤는데 웬걸, 생각보다 복잡했다.

일단 MySQL에선

• DB 생성
• User 생성
• DB에 대한 User 권한 부여

의 순서로 진행해왔고, 외부 접속 및 권한 관리 등을 한번에 할 수 있었다.

그러나 PostgreSQL에선, 일단 스키마...라는 것이 등장했다. 처음에는 테이블 스키마를 말하는 것인가? 생각했는데, 그것보단 오히려 MySQL에서 논리 DB에 가깝다고 한다. 의미적으로 서로 관련있는 테이블을 모아놓는 역할을 하는데, 일종의 namespace 라고 생각할 수도 있다. 한편 PostgreSQL이 제공하는 Multi-user environment에서 많은 유저들이 하나의 Database에 접근할 때, 상호 간섭을 막는 역할도 한다. 그에 더해, 스키마를 그대로 복사하여 생성할 수도 있는데 백업이나 테스트 환경을 구성할 때 도움이 되기도 한다.

PostgreSQL DBMS > Database > Schema > Table & View & Procedure

이런 구조인 셈이다.

기본 Database와 Schema

모든 Database는 하나 이상의 Schema를 갖는다. 새로 생성된 Database에는 기본적으로 Public이라는 Schema가 생성된다.

PostgreSQL을 제일 먼저 설치하면, 기본적으로 postgres라는 database가 존재하는데 여기에도 Public Schema가 존재한다.

postgres=> \l /* 데이터베이스 목록 */
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | 
 template0 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=> \dn+ /* 스키마 목록 */
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 
(1 row)

이제 관리자 유저(usr_mng)를 하나 만들어 테이블 하나를 생성해보자. 현재 Database (postgres)의 기본 Schema인 public schema에 생성된다.

postgres=# SELECT session_user; /* 현재 세션 유저 확인 */
 session_user 
--------------
 postgres
(1 row)

postgres=# CREATE USER usr_mng WITH PASSWORD 'mng'; /* 유저 생성 */
CREATE ROLE

postgres=# SET ROLE usr_mng; /* 세션 유저 변경 */
SET

postgres=> CREATE TABLE tb_temp(); /* 테이블 생성 */
CREATE TABLE

postgres=> \dt /* 현재 Database 내 테이블 목록 */
         List of relations
 Schema |  Name   | Type  |  Owner  
--------+---------+-------+---------
 public | tb_temp | table | usr_mng
(1 row)

public Schema는 누구나 접근 할 수 있기 때문에, 별 다른 권한이 없어도 테이블을 생성할 수 있었다. 그러나 이런 방식은 보안에 허점을 낳는다.

Schema 권한 관리

Table, Database 처럼 Schema 또한 REVOKE 를 이용하여 권한을 회수 할 수 있다. 이를 통해 Public 스키마에 대한 권한을 회수함으로서 접근을 막는다.

postgres=> set role postgres; /* 세션을 슈퍼 유저로 변경 */
SET

postgres=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC; /* public 에 대한 권한 전체를 모든 유저로부터 회수 */
REVOKE

postgres=# set role usr_mng;
SET

postgres=> CREATE TABLE tb_temp2(); /* public 스키마에 접근 불가능 */
ERROR:  no schema has been selected to create in
LINE 1: CREATE TABLE tb_temp2();

데이터베이스 생성 및 유저 권한 관리 시나리오

다음은 데이터베이스와 스키마를 생성하고 유저 별로 권한을 설정하는 예시이다. cool_market이라는 온라인 쇼핑몰을 구현하다고 해보자.

데이터베이스 생성

postgres=> set role postgres;
SET

postgres=# CREATE DATABASE cool_market; /* 데이터베이스 생성 */
CREATE DATABASE

postgres=# ALTER DATABASE cool_market OWNER TO usr_mng; /* usr_mng 유저를 오너로 지정 */
ALTER DATABASE

스키마 생성

postgres=> \c cool_market usr_mng /* cool_market 에 usr_mng 유저로 접속 */
You are now connected to database "cool_market" as user "usr_mng".

cool_market=> CREATE SCHEMA product; /* 상품 스키마 생성 */
CREATE SCHEMA

cool_market=> CREATE SCHEMA purchase; /* 결제 스키마 생성 */
CREATE SCHEMA

cool_market=> CREATE SCHEMA review; /* 리뷰 스키마 생성 */
CREATE SCHEMA

cool_market=> \dn+ /* 생성된 스키마 확인 */ 
                           List of schemas
   Name   |  Owner   |  Access privileges   |      Description       
----------+----------+----------------------+------------------------
 product  | usr_mng  |                      | 
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         | 
 purchase | usr_mng  |                      | 
 review   | usr_mng  |                      | 
(4 rows)

역할별 유저 생성 및 권한 부여

다음의 유저를 새로 생성하고, 적절한 권한을 부여한다.

• prd_mng : 상품 도메인 관리자, 상품 스키마에 DDL 권한과 DML (CRUD) 권한이 필요하다.
• prd_dev : 상품 도메인 서버의 접속, 상품 스키마에 대한 DML (CRUD) 권한만 필요하다.
• usr_anl : 분석가, 전체 스키마에 대한 조회 권한이 필요하다.

cool_market=> \c postgres postgres /* 슈퍼 유저로 세션 변경 */
You are now connected to database "postgres" as user "postgres".

/* 유저 생성 */
postgres=# CREATE USER prd_mng WITH PASSWORD 'pm';
CREATE ROLE
postgres=# CREATE USER prd_dev WITH PASSWORD 'pd';
CREATE ROLE
postgres=# CREATE USER usr_anl WITH PASSWORD 'ua';
CREATE ROLE

/* cool_market 데이터베이스에 접속 권한 부여 */
postgres=# GRANT CONNECT ON DATABASE cool_market TO prd_mng, prd_dev, usr_anl;
GRANT

/* cool_market에서 권한 부여를 위해 슈퍼 유저로 변경 */
postgres=# \c cool_market postgres
You are now connected to database "cool_market" as user "postgres".

/******************************************************/
/* (1) prd_mng에개 product 스키마에 대한 DDL, DML 권한 부여 */
/******************************************************/
cool_market=# GRANT CREATE ON SCHEMA product TO prd_mng;
GRANT

cool_market=# set role prd_mng;
SET

cool_market=> CREATE TABLE tb_product(); /* 테이블 생성 */
CREATE TABLE

cool_market=> SELECT * FROM tb_product; /* 테이블 조회 */
--
(0 rows)

cool_market=> DROP TABLE tb_product; /* 테이블 삭제 */
DROP TABLE

/**************************************************/
/* (2) prd_dev에게 product 스키마에 대한 DML 권한 부여 */
/**************************************************/
cool_market=> SET ROLE postgres;
SET

cool_market=# GRANT USAGE ON SCHEMA product TO prd_dev; /* 스키마 접근 권한 부여*/
GRANT

cool_market=# GRANT SELECT,INSERT,DELETE,UPDATE  ON ALL TABLES IN SCHEMA product TO prd_dev; /* 스키마 CRUD 권한 부여*/
GRANT

cool_market=# SET ROLE prd_dev;
SET

cool_market=> SELECT * FROM product.tb_product;
--
(0 rows)


/**************************************************/
/* (3) usr_anl에게 전체 스키마에 대한 DML 권한 부여 */
/**************************************************/
cool_market=> set role  postgres;
SET

cool_market=# GRANT USAGE ON SCHEMA product, purchase, review TO usr_anl;
GRANT

cool_market=# GRANT SELECT ON ALL TABLES IN SCHEMA product, purchase, review TO usr_anl;
GRANT

cool_market=# set role usr_anl;
SET

cool_market=> SELECT * FROM product.tb_product;
--
(0 rows)

/*