[DB] Redshift 특정 테이블의 권한이 생성되지 않는 문제

Redshift 특정 테이블의 권한이 생성되지 않는 문제

Redshift에서 그룹에 대한 권한을 추가했지만, 특정 테이블에 대한 권한만 추가되지 않는 문제가 발생하였다. 결론적으로는 권한을 생성하는 사용자가 아닌 다른 사용자가 테이블을 생성했기 때문이다. 이 문제를 해결하는 과정과 방지하는 방법을 살펴보려고 한다.

• 이전에 Redshift 사용자를 생성했던 내용

인프라 구축기 (7) - Airflow 및 Redshift 사용자 생성, 권한 설정

문제 상황

문제 발생

• raw_data 스키마의 popup_stores 테이블의 count를 확인하려고 쿼리 수행
• 권한 부족으로 Error 발생

-- ERROR: permission denied for relation popup_stores
select distinct count(*) from raw_data.popup_stores ps limit 10

문제 파악

• 다른 테이블은 모두 정상적으로 수행하므로 GRANT 쿼리 자체의 문제는 없다고 판단
• 테이블을 확인해 보니 popup_stores만 'redshift'가 아닌 다른 USER가 Owner로 설정돼 있었음
• 그러나 권한을 조작하고 있는 것은 redshift 계정

-- 테이블의 DDL 생성시 나타나는 owner (redshift가 아님)
ALTER TABLE raw_data.popup_stores owner to "ex_user";

문제 확인

• Owner가 다르면 권한 해제나 그룹 삭제가 어려울 수 있음
• 관리자 계정은 모든 객체 접근 권한을 갖고 있지만, 객체의 소유권이 없다면 권한 부여에 제약 발생

문제 해결

문제 해결

• popup_stores의 OWNER를 redshift로 변경 후 다시 권한 설정 진행

-- Owner 변경
ALTER TABLE raw_data.popup_stores OWNER TO redshift;

-- 그룹에 대한 스키마 권한 부여
GRANT USAGE ON SCHEMA raw_data, analytics TO GROUP ETL_team;

-- 테이블 권한
GRANT ALL ON ALL TABLES IN SCHEMA raw_data, analytics TO GROUP ETL_Team;

-- 새로 생성된 테이블에 대한 권한 부여
ALTER DEFAULT PRIVILEGES IN SCHEMA raw_data GRANT ALL ON TABLES TO GROUP ETL_team;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT ALL ON TABLES TO GROUP ETL_team;

결과

• 테이블에 대한 권한 확인 결과 올바르게 popup_stores가 생성된 것을 확인

-- etl_team에게 부여된 테이블 권한 확인
SELECT *
FROM information_schema.table_privileges
WHERE grantee = 'etl_team';

쿼리 결과

문제 발생 방지

아래의 방식은 일시적으로 해결할 수 있는 방식이며, 근본적인 해결법은 아니다. 따라서 사용자가 테이블을 생성함에 따라 올바르게 그룹에 권한이 적용되게 하는 방법은 아래 링크에서 참고하면 된다.

[DB] Redshift 특정 테이블의 권한 생성 문제 근본적인 해결법

스키마 권한 재설정

• 문제가 발생한 원인은 관리자가 아닌 USER가 테이블을 생성했기 때문
• 스키마 권한은 CREATE, USAGE, ALTER가 존재하며, 모든 권한을 부여했었음
• USER의 스키마 원한을 USAGE로만 설정하여 테이블 조작할 수 없게 변경

-- etl_team의 create, alter 권한 제거
REVOKE CREATE, ALTER ON SCHEMA raw_data, analytics FROM GROUP ETL_team;

재설정 후 스키마 권한 확인

• 오직 USAGE 권한을 갖고 있는 것을 확인

-- etl_team에게 부여된 스키마 권한 확인
SELECT namespace_name, privilege_type
FROM SVV_SCHEMA_PRIVILEGES
WHERE namespace_name in ('analytics', 'raw_data') and identity_name = 'etl_team';

쿼리 결과

Reference

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_GRANT.html

https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_Privileges.html