RDS PostgreSQLで参照専用のユーザを作成する（複数のスキーマがあっても一発で）

RDS PostgreSQLで参照専用のユーザを作成するための手順です。ドキュメント自体は "AWS Document - Managing PostgreSQL users and roles - Read-only role" に丁寧に書かれていましたが、スキーマがたくさんある場合に大変だったのでそれを補った手順です。

環境

以下の環境で試しました。少し古いかもしれませんが基本的に上位互換です。

• RDS PostgreSQL v9.6.11
• psql v11.5

手順

psqlでDBへ接続します。この時、RDS作成時に指定した権限の強いユーザを利用しました。

$ psql -h hogehoge.fugafuga.ap-northeast-1.rds.amazonaws.com -U morihaya -d morihaya

以下のSQLクエリで参照ユーザを作成します。ポイントとしては

• ON DATABASE で指定するDBは各自のDBを指定する
• パスワードも各自のを指定
• FOR sch IN SELECT nspname FROM pg_namespace WHERE nspname not like ... は場合によってはエラーが出るスキーマもあるかもしれないため、適宜追加
• ここで作成しているのは readonly ロールと readonly_user です。詳細は上述したAWSさんのドキュメントが詳しくてわかりやすいです

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE morihaya TO readonly;
DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace WHERE nspname not like 'pg_%' and nspname not like 'information_schema' and nspname not like 'hogehoge'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO readonly $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO readonly $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO readonly $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL sequences IN SCHEMA %I TO readonly $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON sequences TO readonly $$, sch);
    END LOOP;
END;
$do$;
CREATE USER readonly_user WITH PASSWORD 'hogehoge';
GRANT readonly TO readonly_user;

参考

• AWS Document - Managing PostgreSQL users and roles - Read-only role
  • 上にも書きましたが、基本的にこのドキュメントの通りです
• stack overflow - Give all the permissions to a user on a DB
  • 上述のAWSの手順ではSchema単位で GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite; を行う必要があり、弊社の環境では少なくないスキーマが存在していて一括方法を探してたどり着いたのがこれ