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

6557 ワード
PostgreSQL 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;

参考

【python暗号解読】pythonの暗号解読モジュール使用
php array_valuesは配列のすべての値の詳細とインスタンスを返します.