Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
2
Help us understand the problem. What is going on with this article?
@Morihaya

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

More than 1 year has passed since last update.

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;

参考

2
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
oisix
こだわりの有機野菜や無添加の自然食品など「体に良くて美味しい食品」を販売するECサイト「Oisix(おいしっくす)」を開発・運営しています。

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
2
Help us understand the problem. What is going on with this article?