Amazon Redshift の「ユーザーを削除できません」というエラーを解決する方法を教えてください。

簡単な説明

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;

   注: 権限を再付与するユーザーは、オブジェクトに対する権限を有しているユーザーである必要があります。このユーザーは、別のユーザーに権限を付与する必要があります。アクセス許可を付与できない場合は、superuser としてアクセス許可を再付与します。

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 を取り消します。次に、次のコマンドを実行して username を取り消します。

   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. ユーザーが所有するすべてのオブジェクトを特定してから、その所有権を別のユーザーまたは admin に譲渡します。

   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 コマンドを実行し、ユーザーグループを削除します。