DROP ROLE コマンドでロールを削除できない場合の対応方法

PostgreSQL で DROP ROLE コマンドでロールを削除しようとした場合に、下記のようなエラーが発生することがあります。

postgres=# DROP ROLE test_role;
ERROR:  role "test_role" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema statsrepo
privileges for table statsrepo.snapshot

これは、削除対象のロールはその他のオブジェクトと依存関係があるため削除できません、という意味合いのエラーメッセージとなります。
DETAIL を確認すると、test_role に権限が付与されているされている旨が記載されています。その他、ロールが所有しているオブジェクトがあるなどの場合に同様のエラーが発生します。

ロールを削除するには、DETAIL に出力されたメッセージを元に REVOKE で権限の取り消しを行なったり、ALTER コマンドでロールが所有しているオブジェクトの所有者を変更することを行い、ロールに付与された権限およびロールが所有するオブジェクトがない状態にする必要があります。

DROP OWNED コマンドを実行して依存関係を解消

PostgreSQL では一括でロールに付与されている権限を除去する手段はありません。ただ DROP OWNED コマンドを実行することで、指定したロールが所有するオブジェクトと権限を削除できます。

postgres=# DROP OWNED BY test_user;
DROP OWNED
postgres=# DROP ROLE test_user;
DROP ROLE

上記のように DROP OWNED コマンドを実行したことで、ロールに依存するオブジェクトや権限が削除されたため、DROP ROLE が成功するようになります。

DROP OWNED コマンドの注意点

繰り返しとなりますが、データベースやテーブル空間以外のロールが所有しているオブジェクトは削除される点に注意してください。この DROP OWNED コマンドを実行すると、そのロールが所有するテーブルなども合わせて DROP されます。

DROP OWNED コマンドを実行する前には、該当のロールが所有者となっているオブジェクトの存在を確認し、削除されても問題ないかどうかを確認することが推奨となります。

所有者を一括で変更したい場合は REASSIGN OWNED コマンドで実現できます。
下記に test_role が所有者となっている test_table を利用して REASSIGN OWNED の挙動を確認した結果を記載します。

-- test_table の所有者が test_role であることを確認
postgres=# SELECT * FROM pg_tables WHERE tablename = 'test_table';
 schemaname | tablename  | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+------------+------------+------------+------------+----------+-------------+-------------
 public     | test_table | test_role  |            | f          | f        | f           | f
(1 row)

-- test_role が所有するオブジェクトの所有者を test_role2 に変更
postgres=# REASSIGN OWNED BY test_role TO test_role2;
REASSIGN OWNED

-- test_table の所有者が test_role2 に変更されていることを確認
postgres=# SELECT * FROM pg_tables WHERE tablename = 'test_table';
 schemaname | tablename  | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+------------+------------+------------+------------+----------+-------------+-------------
 public     | test_table | test_role2 |            | f          | f        | f           | f
(1 row)

上記の通り、REASSIGN OWNED コマンドで、test_role が所有者となっているオブジェクトの所有者を test_role2 に変更されたことを確認できました。
これで test_role に対して DROP OWNED を実行しても、削除されるテーブルなどがない状態（ test_role を削除できる状態）にできました。

まとめ

本記事では DROP ROLE コマンド時に発生するエラーと、その対応方法について検証用の SQL を実行して挙動を確認しました。
記事内でも記載しました通り、DROP OWNED は取り扱い注意なところもありますので、実行する際には想定外のオブジェクトが削除されないかを確認した上で実行するのが推奨となります。