LoginSignup
10
5

More than 3 years have passed since last update.

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

Posted at

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;

参考

10
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
10
5