PostgreSQLでデータをバックアップファイルからリストアした時、オーナ情報を削除し忘れて、取得元のユーザを引き継いでしまったり、postgresユーザでインポート作業を行ってしまい、オーナが全部postgresになってしまうことがある。
そんな時、あわててデータベースのオーナを変更してもその下に紐付いているテーブルのオーナは変更前のままとなってしまう。
pgadminで変更を試みてもまとめて変更することはできないので、泣く泣く一旦DBを削除してインポート…なんてことは誰でもあるはず。
そんな時、テーブルのオーナを一括変更するコマンドを紹介する。
postgresユーザ以外のユーザから別のユーザへのテーブルオーナ一括変更
一番簡単なのがこのコマンド。コマンド一行で該当ユーザ（old_user）がオーナとなっているテーブルを一括でnew_userに変更する。
このコマンドは個別にテーブル指定を行うことができないので、一括して変更される。
古いユーザを削除して新しいユーザを作り直すときなどに便利。（また、間違えてオーナ情報を設定してリストアした場合も…）
コマンド
--下記のcurrent_userとnew_userをそれぞれ実行環境に応じて置き換える。TOの後ろのユーザ名はダブルコーテーションで囲う事。
REASSIGN OWNED BY current_user TO "new_user";
postgresユーザから別のユーザへのテーブルオーナ変更
先出のコマンドは、postgresユーザから別のユーザへのオーナ変更の際には利用できない。
理由は、一部のシステムテーブルはpostgresユーザからオーナを変更できないものがあり、その変換でエラーとなってしまうから。
そんなときは下記のコマンドで一括変更ができる。
ただし、この方法は一度オーナ変更を行うSQLを自動生成し、それを実行するという２段実行となる。この場合、自動生成されたSQLを自分で取捨選択できるため、オーナ変更を行うテーブルを選択できる。
コマンド
--下記のcurrent_userとnew_userをそれぞれ実行環境に応じて置き換える。
SELECT
'ALTER TABLE ' || schemaname
|| '.' || tablename ||
' OWNER TO new_user;' --ここでSQLに記載する新しいオーナを指定
FROM pg_tables
WHERE tableowner ='old_user' --ここで対象とするテーブルの現在のオーナを指定
上記のコマンドを実行することで、下記のSQLが結果に出力される。出力されたSQLを実行することで、オーナをnew_userに変更することができる。
"ALTER TABLE public.table_name1 OWNER TO new_user;"
"ALTER TABLE public.table_name2 OWNER TO new_user;"
"ALTER TABLE public.table_name3 OWNER TO new_user;"
"ALTER TABLE public.table_name4 OWNER TO new_user;"
"ALTER TABLE public.table_name5 OWNER TO new_user;"