Amazon Redshift에서 ‘user cannot be dropped’ 오류를 해결하려면 어떻게 해야 합니까?

간략한 설명

Amazon Redshift에서 사용자를 삭제하려고 할 때, 다음 오류 메시지 중 하나가 표시될 수 있습니다.

• ERROR: user "username" cannot be dropped because some objects depend on it
• ERROR: user "username" cannot be dropped because the user has a privilege on some object
• ERROR: user "username" cannot be dropped because the user owns some object

다음 유형의 사용자를 삭제하려고 할 때 이러한 오류가 발생할 수 있습니다.

• 기본 권한을 가진 소유자 또는 대상 사용자.
• 모든 개체(예: 데이터베이스, 스키마, 테이블, 뷰, 프로시저 및 라이브러리)의 소유자.
• 개체에 대한 사용 권한이 있는 사용자.

이러한 오류 메시지를 해결하려면, 먼저 모든 사용자 권한을 제거합니다. 그런 다음, 개체 소유권을 이전하거나 개체의 그룹 소유권을 제거합니다.

중요: Amazon Redshift 클러스터의 모든 데이터베이스에서 사용자 및 그룹 권한을 취소해야 합니다. 클러스터의 데이터베이스 목록을 보려면 SHOW DATABASES 명령을 실행합니다. 로그인한 데이터베이스의 이름을 보려면 CURRENT_DATABASE 명령을 실행합니다.

해결 방법

스크립트를 설치하여 뷰 생성

수퍼유저로 GitHub 웹 사이트의 AWS Labs 리포지토리에서 v_generate_user_grant_revoke_ddl.sql 및 v_find_dropuser_objs.sql 스크립트를 다운로드하여 설치하십시오. v_generate_user_grant_revoke_ddl.sql 및 v_find_dropuser_objs.sql 스크립트의 뷰는 정의에 admin 스키마를 사용합니다. Amazon Redshift 클러스터에 admin 스키마가 없는 경우 기존 스키마에서 정의를 수정하십시오. 또는 admin 스키마를 생성하십시오.

뷰 정의에서 열을 변경한 경우 새 뷰와 정의를 생성하기 전에 기존 뷰를 삭제하십시오. 기존 뷰를 삭제하기 전에 새 뷰를 만들면 not valid table 오류가 발생합니다.

사용자 삭제

다음 단계를 완료하십시오.

1. 쿼리 편집기 v2를 엽니다. 그런 다음, 삭제하려는 사용자를 검색하여 부여된 사용자 권한을 확인합니다.

2. 다른 사용자에게 권한을 다시 부여하려면 다음 명령을 실행합니다.

   select regexp_replace(ddl,grantor,'superuser') from v_generate_user_grant_revoke_ddl where grantor='username' and ddltype='grant' and objtype <>'default acl' order by objseq,grantseq;

   참고: 권한을 다시 부여하는 사용자는 개체에 대한 권한이 있는 사용자여야 합니다. 또한 다른 사용자에게 권한 부여가 가능해야 합니다. 권한 부여 권한이 없는 경우 수퍼유저로 권한을 다시 부여할 수 있습니다.

3. 다른 사용자에게 권한을 재부여한 후 다음 명령을 실행하여 해당 사용자의 권한을 취소합니다.

   select ddl from v_generate_user_grant_revoke_ddl where ddltype='revoke' and (grantee='username' or grantor='username') order by objseq, grantseq desc;

   참고: grantor를 권한을 부여한 사용자의 이름으로 바꾸십시오. grantee를 권한을 받는 사용자의 이름으로 바꾸십시오.
   쿼리에서 레코드가 반환되지 않거나 DROP USER 명령이 실패하면 다음 명령을 실행합니다.

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and ddl ilike '%user-to-be-dropped%' order by objseq, grantseq desc;

   위 쿼리에는 사용자로부터 취소해야 하는 추가 권한이 나열되어 있습니다. 이러한 권한을 취소하십시오.

4. 빈 액세스 제어 목록(ACL)이 있는지 확인하려면 다음 쿼리를 실행합니다.

   select * from pg_user where username = 'username-to-be-dropped';   
   select * from pg_default_acl where defacluser= user-id;
   select pg_get_iam_role_by_user('user-name');

   참고: username-to-be-dropped 및 user-name을 해당 값으로 바꾸십시오. 사용자 이름과 사용자 ID를 검색하려면 PG_USER_INFO 테이블에서 username 및 usesysid 열 항목을 찾으십시오. PG_DEFAULT_ACL 테이블에 사용자 항목이 있는 경우 사용자를 삭제할 수 없습니다.

5. 사용자에게 여전히 개체에 대한 권한이 있는 경우, 사용자에게 assumerole 권한이 있는지 확인합니다.

   select pg_get_iam_role_by_user('user-name');

   사용자에게 assumerole 권한이 있는 경우 먼저 전체 사용자로부터 assumerole을 취소하십시오. 그리고 나서 다음 명령을 실행하여 사용자명을 취소합니다.

   revoke assumerole on all from public for all;  
   revoke assumerole on all from user-name for all;

6. (선택 사항) 사용자에게 여전히 일부 개체에 대한 권한이 있는 경우, 사용자가 다른 그룹에 속해 있는지 확인합니다. 사용자에게 해당 그룹에서 부여된 권한이 있을 수 있습니다. 또는 사용자에게 PUBLIC 그룹에 부여된 권한이 있을 수 있습니다.
   사용자에게 아직 남아 있는 권한을 확인하려면 다음 쿼리를 실행합니다.

   select * from pg_user where username = 'username-to-be-dropped';  
   select * from pg_group;

   pg_group 쿼리의 출력에서 grolist 열에 사용자를 나열하는 항목이 있는지 확인합니다. 사용자가 다른 그룹에 속해 있는 경우 grolist 열에 사용자 ID가 표시됩니다. 그룹의 권한을 확인하려면 다음 명령을 실행합니다.

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group group-name' ;
   select * from admin.v_generate_user_grant_revoke_ddl where objname='timecards' and schemaname='postgres' and grantee='PUBLIC' and ddltype='revoke';

   참고: objname 및 schemaname을 테이블 및 스키마로 바꾸십시오. 기본적으로 Amazon Redshift는 PUBLIC 그룹에 부여된 권한을 모든 사용자에게 부여합니다.

7. 사용자가 소유한 모든 개체를 찾은 다음, 소유권을 다른 사용자 또는 관리자에게 이전합니다.

   select ddl||'newuser;' as ddl from admin.v_find_dropuser_objs where objowner = 'username-to-be-dropped';

   위 명령의 출력에는 소유권을 새 사용자에게 이전하는 데 사용해야 하는 명령이 나열되어 있습니다.

8. Amazon Redshift 클러스터의 각 데이터베이스에서 2~7단계를 반복합니다.
   참고: 데이터베이스를 나열하려면 SHOW DATABASES 명령을 실행하십시오.

9. 데이터베이스에서 사용자를 제거하려면 다음 명령을 실행합니다.

   drop user username-to-be-dropped;

그룹 삭제

참고: 그룹을 삭제하기 전에 해당 그룹이 개체에 대해 가지고 있는 모든 권한을 취소해야 합니다.

다음 단계를 완료하십시오.

1. 그룹에 부여된 모든 권한을 찾은 다음, 권한을 취소합니다.

   select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and grantee= 'group group-name';

2. Amazon Redshift 클러스터의 각 데이터베이스에서 1단계를 반복하여 모든 데이터베이스에서 그룹 권한을 취소합니다.

3. 사용자 그룹을 제거하려면 DROP GROUP 명령을 실행합니다.