개요
Redshift는 사용자와 권한을 관리하는 방법이 조금 복잡하여 권한 부여 및 삭제에서 삽질한(?) 내용을 공유해보겠습니다.
사용자와 그룹
Redshift에서는 Postgres와 조금 차이가 있는데 접근제어를 위해 role의 개념을 사용하지 않고 사용자와 그룹을 사용합니다. 그리고 아래의 규칙을 가집니다.
- 사용자만 그룹의 하위에 속할 수 있습니다. 즉, 그룹은 다른 그룹에 속할 수 없습니다.
- 그룹이 아닌 사용자가 관계를 소유합니다.
- 사용자와 그룹에 별도로 권한이 부여될 수 있습니다.
- 사용자는 자신이 속한 그룹의 모든 권한을 자동으로 상속합니다.
계정
계정생성
CREATE USER name [ [ WITH ] option [ ... ] ]
where option is
CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| SYSLOG ACCESS { RESTRICTED | UNRESTRICTED }
| PASSWORD { 'password' | 'md5hash' | DISABLE }
[ VALID UNTIL 'expiration_date' ]
| RENAME TO new_name |
| CONNECTION LIMIT { limit | UNLIMITED }
| SESSION TIMEOUT limit | RESET SESSION TIMEOUT
| SET parameter { TO | = } { value | DEFAULT }
| RESET parameter
| EXTERNALID external_id
CREATE USER {사용자가 요청한 계정 명} PASSWORD '패스워드' NOCREATEDB NOCREATEUSER SYSLOG ACCESS RESTRICTED [in group 그룹명];
CREATEDB | NOCREATEDB: Database 생성 권한 부여CREATEUSER | NOCREATEUSER : Create User를 포함한 모든 데이터베이스에 권한을 가진 슈퍼 유저 생성 가능SYSLOG ACCESS {RESTRICTED | UNRESTRICTED} : 시스템 테이블 및 뷰에 대해 사용자가 생성한 행만 볼 수 있음
- RESTRICTED : 사용자가 생성한 행만 볼 수 있음
- UNRESTRICTED : 다른 사용자가 생성한 행을 볼 수 있음
in group : 사용자를 그룹에 추가
참고
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_CREATE_USER.html
계정삭제
DROP USER name;
이 때 계정 삭제를 위해서는 모든 권한이 해제되어야 합니다.
그룹
그룹은 동일한 권한이 부여된 사용자들의 집합으로 그룹을 사용하여 역할을 기준으로 권한을 부여할 수 있습니다. Redshift에서는 사용자의 권한은 최대한 그룹으로 관리하도록 합시다. 사용자 계정을 생성했을 때 그룹으로 관리가 되지 않을 경우 Database가 여러개로 나눠져 있을 경우 모든 DB를 뒤져서 권한을 해제해야하는 번거로운 상황이 발생할 수 있습니다….
조회
SELECT * FROM PG_USER;
생성
create group serviceusers;
create group generalusers;
사용자 추가 및 제외
ALTER GROUP group_name ADD USER user_name [, ... ]
ALTER GROUP group_name DROP USER user_name [, ... ]
권한
데이터베이스 사용자 계정은 모든 클러스터에 전역적으로 존재합니다. 다만 권한은 데이터베이스에 종속적이기 때문에 데이터베이스 및 스키마를 지정하여 권한을 부여해야합니다.
조회
권한 조회시 각 데이터베이스에 접근해서 조회해야만합니다.
-- 1. 테이블별 권한 조회
SELECT * FROM information_schema.table_privileges WHERE grantee = 'omty'
-- 2. 스키마 및 테이블에 대한 권한 조회
SELECT usename
, schemaname
, tablename
, has_table_privilege(usename, schemaname || '.' || tablename, 'select') as select
, has_table_privilege(usename, schemaname || '.' || tablename, 'insert') as insert
, has_table_privilege(usename, schemaname || '.' || tablename, 'update') as update
, has_table_privilege(usename, schemaname || '.' || tablename, 'delete') as DELETE
, has_table_privilege(usename, schemaname || '.' || tablename, 'references') as references
, has_schema_privilege(usename, schemaname, 'create') as create
, has_schema_privilege(usename, schemaname, 'usage') as usage
FROM pg_tables, pg_user
WHERE schemaname <> 'pg_catalog'
AND usename in ('omty')
그룹 권한 조회
-- 1. 유저 그룹조회
SELECT usename AS user_name, groname AS group_name
FROM pg_user, pg_group
WHERE pg_user.usesysid = ANY(pg_group.grolist)
AND pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group);
-- 2. 그룹의 권한 조회
SELECT * FROM information_schema.table_privileges tp WHERE grantee = '그룹명'
권한 부여
grant usage on schema send_log to group serviceusers;
grant select,insert,delete,update on all tables in schema send_log to group serviceusers;
grant usage on schema send_log to group generalusers;
grant select on all tables in schema send_log to group generalusers;
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_GRANT.html
권한 회수
권한은 스키마, 테이블이 각각 부여됩니다. 위에서 얘기한 것처럼 하나의 계정에 다수의 DB 및 스키마의 권한이 부여되어 있다면 나중에 엄청나게 귀찮은 상황이 발생합니다. 계정 삭제가 필요할 경우 모든 DB, 스키마를 뒤져서 권한을 회수해야하며 계정의 모든 권한이 회수되기 전까지 계정은 삭제할 수 없습니다.
특히 테이블, 스키마, DB에 대한 권한을 각각 회수해야합니다. 스키마에 대한 권한을 회수해도 스키마 하위의 테이블에 대한 권한은 남아있습니다.
-- 1. 해당 스키마에 존재하는 모든 테이블 권한 회수
REVOKE ALL ON ALL TABLES IN SCHEMA 스키마명 FROM omty
-- 2. 스키마 권한 회수
REVOKE ALL ON SCHEMA 스키마명 FROM omty
-- 3. 데이터베이스 권한 회수
REVOKE ALL ON DATABASE 데이터베이스 FROM omty
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_REVOKE.html
Redshift는 계정을 관리하기 상당히 불편한 점이 존재합니다. 계정을 생성할 경우 DB별로 그룹을 생성하여 권한을 할당하고 각 사용자를 해당 그룹에 추가하는 방법이 가장 이상적이라고 생각됩니다. 무분별한 권한 부여로 권한을 제거했다고 생각했음에도 아래의 메시지가 계속해서 발생하는 쓴맛을 볼 수 있었습니다...
cannot be dropped because the user has a privilege on some object
참고
https://discourse.getdbt.com/t/the-difference-between-users-groups-and-roles-on-postgres-redshift-and-snowflake/429