PostgreSQLでテーブルオーナを一括変更する方法 #PostgreSQL

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;"