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 クラスター内にあるすべてのデータベースからユーザーとグループの権限を取り消す必要があります。

解決策

スクリプトをインストールしてビューを作成する

AWS ラボの GitHub リポジトリから 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. 削除する必要があるユーザーごとに付与されたすべてのユーザー権限を見つけます。次に、それらの権限を別のユーザーとして再付与します。これには superuser が推奨されます。

   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 として権限を再付与できます。

2. ユーザーに付与されたすべての権限を見つけて、それらを取り消します。

   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;

   前述のクエリは、ユーザーを削除する前にユーザーから取り消す必要がある権限を一覧表示します。次のステップに進む前に、これらの権限を取り消してください。

3. 空のアクセス制御リスト (ACL) をチェックするには、次のクエリを実行します。

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

   ユーザー名とユーザー ID を取得するには、PG_USER_INFO テーブルの username および usesysid 列のエントリを見つけます。
   注:PG\ _DEFAULT\ _ACL テーブルにユーザーエントリがある場合、そのユーザーを削除することはできません。

4. 削除するユーザーがまだ一部のオブジェクトに対する権限を持っている場合は、そのユーザーに 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;

5. (オプション) 削除されるユーザーが一部のオブジェクトに対する権限を引き続き保持している場合は、そのユーザーが別のグループに属しているかどうかを確認します。ユーザーは、そのグループから付与された権限がある場合があります。または、ユーザーは PUBLIC グループに付与された権限を持っている可能性があります。
   ユーザーに引き続き付与されている権限を確認するには、次のクエリを実行します。

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

   pg\ _group クエリの出力で、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 をそれぞれのテーブルとスキーマに置き換えてください。デフォルトでは、PUBLIC グループに付与された権限はすべてのユーザーに付与されます。

6. ユーザーが所有するすべてのオブジェクトを見つけて、その所有権を別のユーザーまたは admin に譲渡します。

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

   前述のコマンドの出力には、所有権を新しいユーザーに譲渡するために使用する必要があるコマンドが一覧表示されます。

7. Amazon Redshift クラスターの各データベースでステップ 2～7 を繰り返します。

8. データベースからユーザーを削除するには、次のコマンドを実行します。

   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 クラスターにある各データベースでステップ 2 を繰り返し、すべてのデータベースでグループの権限が取り消されたことを確認します。

3. DROP GROUP コマンドを実行して、データベースからユーザーを削除します。