削除した Amazon Redshift ユーザーが pg_class テーブルに表示されますが、pg_user テーブルには表示されないのはなぜですか?
簡単な説明
削除されたユーザーがクラスター内の別のデータベースにオブジェクトを所有している場合、削除されたユーザーは pg_class テーブルに表示されます。DROP USER コマンドは、削除しようとしているユーザーが所有するオブジェクトについて、現在のデータベースだけをチェックします。ユーザーが別のデータベースにオブジェクトを所有している場合、エラーはスローされません。代わりに、削除されたユーザーはオブジェクトの所有権を保持し、usesysid は引き続き pg_class テーブルに表示されます。
解決方法
クラスターの各データベースで以下のコマンドを実行します。このコマンドは、削除されたユーザーが所有するオブジェクトをチェックします。
select distinct schemaname, tablename, tableowner from pg_tables where tableowner like '%unknown%';
削除されたユーザーがデータベースにまだオブジェクトを所有している場合、次のような出力が得られます。usename が保存されている pg_user テーブルから所有者が削除されたため、tableowner は「unknown」です。
demo_localdb=# select distinct schemaname, tablename, tableowner from pg_tables where tableowner like '%unknown%';
schemaname | tablename | tableowner
------------+-----------------+-------------------
demo_local | orders_nocomp_2 | unknown (UID=114)
demo_local | orders_nocomp_3 | unknown (UID=115)
(2 rows)
オブジェクトの所有権を移管するには、ALTER TABLE コマンドを使用します。次の例で、新しい所有者は userc です。
alter table demo_local.orders_nocomp_2 owner to userc;
防止
ユーザーを削除する前に、次のクエリを実行して、そのユーザーが所有しているオブジェクトを見つけます。(v_find_dropuser_objs ビューを使用してこれを行うこともできます。) 次の例では、ユーザーは labuser です。
SELECT decode(pgc.relkind,
'r','Table',
'v','View'
) ,
pgu.usename,
pgu.usesysid,
nc.nspname,
pgc.relname,
'alter table ' || QUOTE_IDENT(nc.nspname) || '.' || QUOTE_IDENT(pgc.relname) || ' owner to '
FROM pg_class pgc,
pg_user pgu,
pg_namespace nc
WHERE pgc.relnamespace = nc.oid
AND pgc.relkind IN ('r','v')
AND pgu.usesysid = pgc.relowner
AND nc.nspname NOT ILIKE 'pg\_temp\_%'
AND pgu.usename = 'labuser'
このクエリでは、次のようなコマンドで作成されたスキーマを所有するユーザーは検出されない場合があります。この例では、labuser がスキーマの所有者です。
create schema demo_local authorization labuser;
スキーマが新しい所有者に移管されない限り、スキーマレベルの承認を持つユーザーを削除することはできません。所有権を userc に移管する方法の例を次に示します。
alter table demo_local.orders_nocomp_2 owner to userc;
関連情報
Amazon Redshift で発生する「user cannot be dropped」(ユーザーを削除できません) というエラーを解決するにはどうすればよいですか?
カタログテーブルへのクエリの実行
データベースユーザーを表示する