[AWS RedShift] 5. SQL로 Redshift 초기 설정

📌 Google Colab에서 SQL 쿼리를 통해 RedShift의 초기 설정을 해 보자.

1. 스키마 생성

• 먼저 DEV라는 Redshift의 데이터베이스 안에 네 개의 스키마를 생성한다. (스키마의 목적은 내부에 있는 테이블의 목적이 무엇인지를 속한 스키마만 보고도 파악할 수 있도록 해 주는 것)
  • RAW_DATA는 ETL의 결과가 들어가는 스키마
  • ANALYTICS는 ELT의 결과가 들어가는 스키마
  • ADHOC은 테스트용 테이블이 들어가는 스키마
  • PII는 개인 정보가 들어가는 스키마

✔ 이때 개인 정보는 잘못 사용해서 노출되면 패널티가 크기 때문에 관리를 철저하게 해야 한다. 웬만하면 사용하지 않는 것이 좋지만 꼭 해야 한다면 별도의 스키마를 두고, 그 밑에 테이블을 둔다. 이 테이블에 접근할 수 있는 인원은 꼭 개인 정보를 보아야 하는 인원으로만 구성해서 최소화하며 꼭 로그를 남긴다.

• 이제 이 구상한 스키마를 생성한다. 이때 CREATE SCHEMA를 통해 생성해 줄 수 있다.

CREATE SCHEMA RAW_DATA;
CREATE SCHEMA ANALYTICS;
CREATE SCHEMA ADHOC;
CREATE SCHEMA PII;

• 이렇게 생성된 스키마는 PG_NAMESPACE를 조회해 확인할 수 있다.

SELECT *
  FROM PG_NAMESPACE;

• 조회 결과를 보면 RAW_DATA, ALALYTICS, ADHOC, PII 이렇게 생성해 준 네 개의 스키마가 생성되어 조회되는 것을 확인할 수 있다.
  
  

---

2. 사용자 생성

• 테이블에 접근 권한을 부여해 줄 사용자를 생성한다.

• 사용자를 생성할 때는 CREATE USER를 통해 생성할 수 있다.

• 이때 PASSWORD 뒤에는 설정해 줄 비밀번호를 입력해 주면 되는데 사용자를 생성할 때 비밀번호에 대한 제약 조건이 존재한다.

• 제약 조건에 어긋나는 비밀번호를 입력하면 (psycopg2.errors.SyntaxError) password must contain an uppercase ASCII character 이런 오류를 겪게 된다.

• 나는 대문자를 입력하지 않아 오류가 발생했다. 제약 조건에 맞는 비밀번호를 입력하면 Done이라는 문구가 뜨게 된다.

CREATE USER song PASSWORD '...';

• 생성된 사용자를 보기 위해서는 PG_USER를 조회해 준다.

SELECT *
  FROM PG_USER;

• 조회 결과를 보면 다음과 같이 방금 생성한 사용자인 song이 등록된 것을 볼 수 있다.
  
  

---

3. 그룹 생성

• 그룹이 필요한 이유는 사용자가 많아지게 되면 사용자 개개인에게 일일이 권한을 부여하는 것이 어려워지기 때문이다.

• 이를 해결하기 위해 테이블별로 접근 권한을 정하는 것이 아니라 스키마별로 권한을 정하고, 사용자들도 사용자별로 권한을 정하는 것이 아니라 그룹별로 권한을 정한다.

• 대신 그룹은 계승이 되지 않는다. 예를 들어 B 그룹이 A 그룹의 권한 + 추가 권한이라고 해도 A 그룹의 권한을 가지고 올 수 없고 다시 설정해 주어야 한다. 그렇기 때문에 너무 많은 그룹이 생기면 관리가 어려워진다. 이 문제를 해결하기 위해 나온 게 역할(Role)이다.

• 먼저 다음과 같은 그룹을 생성해 보자.

  • 데이터 활용을 하는 개인을 위한 analytics_users
  • 데이터 분석가를 위한 analytics_authors
  • 개인 정보 스키마까지 접근할 수 있는 pii_users

• CREATE GROUP 구문을 통해 그룹을 생성할 수 있다.

CREATE GROUP analytics_users;
CREATE GROUP analytics_authors;
CREATE GROUP pii_users;

• 그리고 사용자를 각 그룹에 넣어 주어야 한다. 이때 ALTER GROUP groupname ADD USER username를 사용해 주며 특정 권한을 줄 때는 GRANT 명령을 통해 줄 수 있다.

ALTER GROUP analytics_users ADD USER song;
ALTER GROUP analytics_authors ADD USER song;
ALTER GROUP pii_users ADD USER song;

• 이와 같이 한 명의 사용자는 다수의 그룹에 소속이 가능하다
• 이후 그룹을 확인하려면 PG_GROUP을 조회하면 된다.

SELECT *
  FROM PG_GROUP;

• 그러면 다음과 같이 생성한 그룹들이 조회되는 것을 볼 수 있다.
  
  

---

4. 역할(Role) 생성 및 설정

• 앞서 말했듯 역할(Role)은 계승이 되지 않는 그룹(Group)의 문제를 해결해 줄 수 있다. 즉, 계승이 된다는 뜻이다.

• 역할은 사용자에게 부여될 수도 있고, 다른 역할에 부여될 수도 있다.

• 한 사용자는 그룹과 동일하게 다수의 역할에 소속이 가능하다.

• 역할을 생성할 때는 CREATE ROLE을 사용해 준다.

CREATE ROLE staff;
CREATE ROLE manager;
CREATE ROLE external;

• 개인과 역할에 각각 권한을 부여해 줄 수 있다.
• 개인에게 부여할 경우 GRANT ROLE rolename TO username, GRANT ROLE rolename TO ROLE otherrolename으로 쿼리문을 사용해 주면 된다.

-- staff 역할을 song이라는 사용자에게 부여한다.
GRANT ROLE staff TO song;
-- staff 역할을 manager 역할에게 부여한다
GRANT ROLE staff TO ROLE manager;

• 이때 생성된 역할들을 목록으로 보고 싶다면 SVV_ROLES를 조회하면 된다.

SELECT *
  FROM SVV_ROLES;

• 다음과 같이 staff, manager, external이라는 세 개의 역할(role)이 생성된 것을 볼 수 있다.