【PostgreSQL】不要になったROLEを削除する〜エラー対応含む

環境

CentOS7
PostgreSQL 11.18

事前準備

ロールを作成

CREATE ROLE sample_role LOGIN PASSWORD 'sample1';

-- 作成されたか確認
¥du

テーブルを作成

CREATE TABLE tbl (
    id int PRIMARY KEY,
    name text NOT NULL
);

-- 作成されたテーブルを確認
¥dt

sample_roleにSELECT権限を付与する

GRANT SELECT ON tbl TO sample_role;

-- 付与した権限の確認
SELECT grantee,
    table_name,
    privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'sample_role';

ROLEの削除

sample_roleを削除する

DROP ROLE sample_role;

エラー

SELECT権限などがROLEについていると削除が出来ないためエラーが出る。

postgres=# drop role sample_role;
ERROR:  他のオブジェクトが依存していますのでロール"sample_role"を削除できません
DETAIL:  テーブルtblの権限

対応としては権限を取り消してから削除を実施する。

権限を取り消す

REVOKE SELECT ON tbl FROM sample_role;

今回は検証なので1件だけですが、運用しているDBなら複数件取り消さないといけないことが大半。
下記のSQLを実行して複数のREVOKE文を作成する。

select 'revoke ' || privilege_type || ' on ' || table_name || ' from ' || grantee || ';' FROM information_schema.role_table_grants WHERE grantee = 'sample_role';

revoke 権限 on tbl from sample_role;
revoke 権限 on tbl from sample_role;
~

再度ROLEを削除する

DROP ROLE sample_role;

-- ROLEを確認する
¥du