Redshift 초기설정

Redshift 처음 구동 시 스키마, 그룹, 유저등을 생성해야한다.

• redshift의 스키마란 모든 관계형 데이터베이스는 테이블을 관리하기 위해서 이중구조로 놓는데, 스키마 혹은 db라고 부르며 폴더의 컨셉. 테이블을 구조적으로 관리하는 폴더로 보면 된다.
• 보통 테이블을 만들 때 CREATE TABLE 시 TABLE이름만 주는게 아니라 schema이름.table이름 그다음에 table의 definition을 준다. 그 때 스키마를 사용해서 테이블을 목적에 맞게 카테고리화해서 테이블을 저장하는 형태. redshift는 dev라는 이름의 db가 있고 그 아래에 별도 4개의 스키마를 만들 예정

• raw_data : 데이터 엔지니어들이 만드는 ETL을 통해서 외부에 있는 데이터소스의 데이터들이 테이블 형태로 여기에 저장.
• analytics : 데이터 분석가들이 ELT를 통해서 기존의 테이블을 조합(JOIN 등)을 통해 새로운 데이터를 만들어 여기에 저장.
• adhoc: 개발 용도 및 테스트 데이터
• pii : 데이터 활용이 고도화될 경우 우리 서비스 사용자들의 개인정보를 어떻게든 활용하는 경우가 있는데, 개인정보의 경우 잘못 사용되어 노출이 될 경우 엄청난 페널티가 있고 회사에도 손해가 가기 때문에 관리를 잘해야 한다. 왠만하면 사용안하는게 좋고 만약 꼭 사용해야할 경우 별도의 스키마를 만들고 그 밑에 개인정보와 관계된 테이블을 생성해놓고 이 테이블을 액세스할 수 있는 사람들을 최소화할 수 있도록 제약하고 이 테이블 액세스는 로깅을 모두 해서 나중에 감사할 수 있게 만드는게 일반적.

스키마는 결국 테이블관리를 카테고리화할 수 있도록 폴더를 만드는 것이다.

스키마 설정

• 모든 스키마 리스트하기 : select * from pg_namespace;

CREATE SCHEMA raw_data;
CREATE SCHEMA analytics;
CREATE SCHEMA adhoc;
CREATE SCHEMA pii;

이걸 실행하려면 실행하는 사용자에게 admin권한이 주어져야 한다.

사용자 생성

• 모든 사용자 리스트하기 : select * from pg_user;

CREATE USER yongjin PASSWORD '...';

그룹 생성/설정

사용자별로 table 액세스 권한을 줘야 한다. 테이블의 수가 100개 1000개 이럴 경우 이테이블은 누가 액세스할 수 있고 누가 액세스하면 안되고 하는걸 수많은 테이블과 수많은 사용자 사이에 일일이 지정하는 것은 불가능. 더 좋은 방법은 테이블별로 액세스 권한을 정하는 것이 아니라 스키마별로 액세스 권한을 정하고 사용자들도 사용자별로 액세스권한을 주는게 아니라 그룹이라는 템플릿을 만들어 그룹으로 관리하는게 일반적.
그룹을 만들어 놓고 사용자들을 집어넣어 그 그룹에 액세스 권한을 컨트롤하는 식으로 간다. 각 그룹별로 어떤 스키마의 테이블을 읽을 수 있는지, 쓸 수 있는지 이런식으로 관리. 각 사용자별로 어떤 테이블을 액세스할 수 있고 쓸 수 있고 그런 것들을 지정을 해줘야 하는데 테이블이 많아지고 사용자가 많아지면 이게 많이 힘들어지기 때문에 테이블을 스키마로, 사용자는 그룹으로 관리를 하여 그룹과 스키마간의 액세스 관계를 정의하는 형태로 접근권한을 관리한다.

• 한 사용자는 다수의 그룹에 속할 수 있다.
• 그룹의 문제는 계승이 안된다는 점.
  • 즉, 너무 많은 그룹을 만들게 되고 관리가 힘들어짐.
  • 그래서 나온게 역할,role. 계승이라는 컨셉이 있기 때문에 관리가 쉬워진다.
• 예를 들어 다음과 같은 그룹이 존재
  • 어드민을 위한 pii_users
  • 데이터 분석가를 위한 analytics_authors
  • 데이터 활용을 하는 개인을 위한 analytics_users
• 그룹 생성 : CREATE GROUP
• 그룹에 사용자 추가 : ALTER GROUP 그룹이름 ADD USER 사용자 이름
• 그룹에 스키마/테이블 접근 권한 설정 (나중에 설명)
• 모든 그룹을 리스트하기 : select * from pg_group;

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

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

GRANT를 통해 각 그룹에 권한을 지정할 수 있다.

역할(Role) 생성 / 설정

역할의 용도는 그룹과 같음. 차이라고 하면 계승구조를 사용가능.

• 역할은 그룹과 달리 계승 구조를 만들 수 있음.
• 역할은 사용자에게 부여될 수도 있고 다른 역할에 부여될 수도 있음.
• 한 사용자는 다수의 역할에 소속 가능함
• 모든 역할을 리스트 하기 : select * from SVV_ROLES;

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

GRANT ROLE staff TO yongjin;
GRANT ROLE staff TO ROLE manager;

manager라는 역할은 staff 역할이 갖고있는 것들을 계승한다. 그리고 manager에게 필요한 별도의 권한을 부여 가능.

Google Colab에서 실습

Schema 설정

4개의 스키마를 위와 같이 생성할 수 있다.

SChema 확인

위의 sql 쿼리를 통해 만들어진 모든 스키마를 확인할 수 있다. 방금 만든 것을 제외한 다른 스키마들은 시스템이 만든 것들이며, 만약 테이블 생성시 스키마를 지정하지 않을 경우 public 스키마에 테이블이 들어가게 된다.

User 설정

위와 같은 SQL 쿼리를 통해 유저를 생성할 수 있다. Password는 영어 대소문자 혼합에 숫자 및 특수문자를 넣어주는식으로 8자 이상의 패스워드를 부여하면 된다.
그리고 select * from pg_user; 쿼리를 통해 만들어진 유저를 확인할 수 있다.

Group 설정

위의 SQL 쿼리를 통해 group을 생성할 수 있다. 또한 만든 그룹에 원하는 USER를 추가할 수 있다.

Role 설정 및 확인

ROLE 생성하고 만든 role에 사용자를 추가할 수 있다. 또한 staff롤을 manager롤에 계승시켜 manager롤에 staff매니저의 권한들을 넣어줄 수 있다.
만들어진 role을 확인하는 sql문은 select * from SVV_ROLES;이다.