🐘 PostgreSQL! - 2

Schema 에 관한 개념정리가 부족한 것 같아 구글링 도중 좋은 글을 찾았다. 이 글을 오늘 TIL에 참고 자료로 쓰려고 한다.

👨🏻‍💻 PostgreSQL의 Schema의 의미 및 권한관리!

• Table 은 행(rows)과 열(columns)로 데이터로 저장하고 있으며, Schema에 포함 되어있다.

• Schema 는 논리적으로 테이블을 정의해놓은 것으로서, 여러개의 Table / procedure / Function / Index / View 등을 포합하고 있다.

• DATABASE 는 데이터를 한 곳에 관리할 수 있도록 모아둔 것으로서, 여러개의 Schema를 가질 수 있다.

PostgreSQL Schema!

MySQL에서는 논리 DB를 Schema 와 같은 의미로 사용한다.

반면 PostgreSQL에서 DB와 Schema 두 가지 개념 모두 사용되며 DB는 Schema의 상위 개념이라 할 수 있습니다.

Table의 집합을 schema 라고 표현하며 이 schema는 하나의 DB를 논리적으로 나누는 개념입니다.

즉, MySQL에서의 논리 DB는 PostgreSQL에서의 Schema라고 할 수 있습니다.

이런 차이점 때문에 PostgreSQL에서는 하나의 DB instance에 있다해도 서로 다른 DB에 있는 테이블 간에는 서로 JOIN 연산을 할 수 없습니다.

대신 서로 다른 Schema의 테이블 간에 JOIN 연산이 가능합니다.

DB 생성과 접근을 막아보자!

다른 유저가 DB를 생성하고 접근 할 수 있게되면 큰 문제가 생긴다!

-- 먼저 db를 생성합니다. (public schema가 생성되겠죠.)
postgres=# create database svcdb with owner svcuser;
CREATE DATABASE

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 svcdb     | svcuser  | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

-- 다른 서비스 유저을 생성합니다.
postgres=# create user anotheruser with password 'secret';
CREATE ROLE

-- 다른 서비스유저로도 접근이 잘되고 테이블도 잘 만들어집니다.
postgres=# \c svcdb anotheruser
You are now connected to database "svcdb" as user "anotheruser".

-- postgres 유저로 권한 회수
svcdb=> \c svcdb postgres
You are now connected to database "svcdb" as user "postgres".

--그리고 소유자가 아닌 유저들은 DB 접근 권한을 revoke 합니다.
svcdb=# REVOKE ALL ON DATABASE svcdb FROM PUBLIC;
REVOKE

svcdb=# \c svcdb anotheruser
FATAL:  permission denied for database "svcdb"
DETAIL:  User does not have CONNECT privilege.
Previous connection kept

svcdb=# \c svcdb svcuser    
You are now connected to database "svcdb" as user "svcuser".

🤖 정리

기본적으로 설치된 Postgres DB에서

-- postgres db 에 다른 유저 접근 차단 및 public schema 사용 금지
psql -d postgres -U postgres -c "REVOKE ALL ON SCHEMA public FROM PUBLIC"
psql -d postgres -U postgres -c "REVOKE ALL ON DATABASE postgres FROM PUBLIC"

서비스에서 사용할 유저 생성 및 DB 생성후 권한 정리는 다음과 같이하면 됩니다.

특히 DDL 권한을 제거한 어플리케이션용 서비스 계정과
DDL 권한을 가진 어플리케이션용 서비스 계정을 분리하고 하나의 스키마를 사용하고 싶은 경우 다음과 같이 설정하면 됩니다.

APPOWNER=newappo
APPUSER=newsvc
DBNAME=newdb

# psql -d postgres -U postgres -c "drop database ${DBNAME}"
# psql -d postgres -U postgres -c "drop user ${APPOWNER}"
# psql -d postgres -U postgres -c "drop user ${APPUSER}"

-- 신규 유저 생성
psql -d postgres -U postgres -c "create user ${APPOWNER} with password 'secret'"
psql -d postgres -U postgres -c "create user ${APPUSER} with password 'secret'"

-- 신규 DB 생성
psql -d postgres -U postgres -c "create database ${DBNAME} with OWNER=${APPOWNER}"

-- db 접근 권한 및 public schema 접근 권한 revoke
psql -d ${DBNAME} -U ${APPOWNER} -c "revoke all on database ${DBNAME} from public"
psql -d ${DBNAME} -U postgres    -c "revoke all on schema public from public"

-- 신규db에 schema 생성 
psql -d ${DBNAME} -U ${APPOWNER} -c "create schema ${APPOWNER} authorization ${APPOWNER}"

-- 서비스용 유저에 최소한의 권한만 부여
psql -d ${DBNAME} -U ${APPOWNER} -c "grant connect,TEMPORARY on database ${DBNAME} to ${APPUSER}"
psql -d ${DBNAME} -U ${APPOWNER} -c "grant usage on schema ${APPOWNER} to ${APPUSER}"
psql -d ${DBNAME} -U ${APPUSER}  -c "alter role ${APPUSER} set search_path to ${APPOWNER}"
psql -d ${DBNAME} -U ${APPOWNER}  -c "grant select, insert, update, delete on all tables in schema ${APPOWNER} to ${APPUSER}"
psql -d ${DBNAME} -U ${APPOWNER}  -c "alter default privileges in schema ${APPOWNER} grant select, insert, update, delete on tables to ${APPUSER}"
psql -d ${DBNAME} -U ${APPOWNER}  -c "grant usage on all sequences in schema ${APPOWNER} to ${APPUSER}"
psql -d ${DBNAME} -U ${APPOWNER}  -c "alter default privileges in schema ${APPOWNER} grant usage on sequences to ${APPUSER}"

참고 자료-1
참고 자료-2