　GO株式会社では、主にクラウドサービス上にあるマネージドデータベースのデータ参照ツールとして、ダッシュボードツール「Redash」を自前構築し社内で運用しています。

　以前、このSelf Hosted版 Redashの構築の記事の中で、ログイン方法としてSAML認証を採用していることをご紹介しましたが、この度、弊社の社名変更並びにメールアドレスのドメイン変更に伴い、Redashのデータベース内で管理しているSAML認証のログインで利用するメールアドレスについてもドメインの変更が必要となりました。

　この記事では、Self Hosted版Redashのデータベースにある、ユーザ情報やメールアドレスの変更方法をご紹介できればと思います。

はじめに

　SREグループ・ヒロチカです。GOでは、サービスのクラウドインフラの設計から構築・運用までを担当しています。

　以前の記事で、Self Hosted版 Redashの構築についてと、v8からv10へのバージョンアップ作業の内容についてご紹介致しましたが、今回はSAML認証をしているRedashのユーザログイン情報の変更方法をご紹介できればと思います。

　今回、SAML認証側の設定変更の他に、Redashのデータベース内で直接ユーザ情報を変更する作業を行なっています。多くの方は、直接データベースに更新をかける状況は発生しないかとは思いますが、本記事がそんな作業が必要になった方の参考になれば幸いです。

Redash https://redash.io/

以前の記事

変更の経緯

　株式会社Mobility Technologiesから、新たにGO株式会社へと社名変更されることに伴い、弊社で利用するメールアドレスのドメインも変更されることとなりました。

　弊社では、Redashを含めた複数のアプリケーションで会社のAzure ADを利用したSAML認証によるSSO設定を行なっており、今回のドメイン変更に付随したAzure ADの設定変更のような、会社全体の設定に関わる変更が発生する場合には、まず全体での設定変更方針を定めた上で、それぞれのアプリケーションのケースごとに設定変更の対応がとられます。今回のAzure ADの設定変更における社内での方向としては、さまざまなリスク回避等の理由から、SAML認証設定で変更が必要となる大元のUserID(Azure ADにおけるUser Principal Name)をいきなり新ドメイン仕様に変更するのではなく、まず先に既にAzure AD上で新ドメインに変更され全体反映されているメールアドレス(Azure ADにおけるmail)を用いて、各アプリケーション単位でUserIDを新ドメイン仕様に変更した後、全体のUserID自体の変更を新ドメイン仕様に変更するという流れとなりました。

　SAML認証では一般的にUserIDとメールアドレスを一致させる運用が広く行われていますが、弊社のRedashでも同様の設定を行っています。今回弊社では、UserIDを新ドメイン仕様に変更する方法として、UserIDの値自体、またUserIDが使われている箇所を全て一時的にmailへと変更することで、その時点から新ドメインのメールアドレスがUserIDとして認識されるように変更を行いました。この設定は、全体のUesrIDの変更が行われた後、戻す想定です。

　この変更の際、Redash側で管理しているユーザ情報内のメールアドレスも変更しなくては、ログインした際にUserIDが異なるため別の新規ユーザとして扱われてしまい、Redash内の権限情報を引き継ぐことができなくなってしまいます。

　そのため、全社的にメールアドレスのドメインが変わる今回のタイミングにおいて、Redash側データベースで管理している、ユーザ情報(メールアドレス)変更が必要となりました。

変更手順

　Redashの持つ内部APIからユーザ情報変更も可能なのかも知れませんが、今回は数百名程度いる全てのユーザに対して変更が必要なことを踏まえ、直接データベース内のユーザ情報を書き換える方法を取りました。

　データベースのユーザ情報を変更後、自分を含めたどのユーザからもRedashへのアクセスがない状態を維持したままSAML認証側のUserIDを、新しいメールアドレスになるよう変更を行います。

　以降、具体的な手順です。

旧ドメイン: XXXX.com
新ドメイン: YYYY.jp

事前準備

データベースを直接触るため、念の為dumpなどを取っておく
該当時間にユーザからのアクセスがないことを確認

更新手順

psqlなどでデータベースにログインし、ユーザ情報を確認し変更

$ sudo -u postgres psql redash など

-- データベースのテーブル情報などの確認
redash => ¥dt 
redash => ¥du 
redash => ¥d users
                                                    Table "redash.users"
      Column       |           Type           | Collation | Nullable |              Default
-------------------+--------------------------+-----------+----------+-----------------------------------
 updated_at        | timestamp with time zone |           | not null |
 created_at        | timestamp with time zone |           | not null |
 id                | integer                  |           | not null | nextval('users_id_seq'::regclass)
 org_id            | integer                  |           | not null |
 name              | character varying(320)   |           | not null |
 email             | character varying(255)   |           | not null |
 profile_image_url | character varying(320)   |           |          |
 password_hash     | character varying(128)   |           |          |
 groups            | integer[]                |           |          |
 api_key           | character varying(40)    |           | not null |
 disabled_at       | timestamp with time zone |           |          |
 details           | json                     |           |          | '{}'::json
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_api_key_key" UNIQUE CONSTRAINT, btree (api_key)
    "users_org_id_email" UNIQUE, btree (org_id, email)
Foreign-key constraints:
    "users_org_id_fkey" FOREIGN KEY (org_id) REFERENCES organizations(id)
Referenced by:
    TABLE "access_permissions" CONSTRAINT "access_permissions_grantee_id_fkey" FOREIGN KEY (grantee_id) REFERENCES users(id)
    TABLE "access_permissions" CONSTRAINT "access_permissions_grantor_id_fkey" FOREIGN KEY (grantor_id) REFERENCES users(id)
    TABLE "alert_subscriptions" CONSTRAINT "alert_subscriptions_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "alerts" CONSTRAINT "alerts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "api_keys" CONSTRAINT "api_keys_created_by_id_fkey" FOREIGN KEY (created_by_id) REFERENCES users(id)
    TABLE "changes" CONSTRAINT "changes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "dashboards" CONSTRAINT "dashboards_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "events" CONSTRAINT "events_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "favorites" CONSTRAINT "favorites_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "notification_destinations" CONSTRAINT "notification_destinations_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "queries" CONSTRAINT "queries_last_modified_by_id_fkey" FOREIGN KEY (last_modified_by_id) REFERENCES users(id)
    TABLE "queries" CONSTRAINT "queries_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
    TABLE "query_snippets" CONSTRAINT "query_snippets_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

-- 変更対象レコードの数をいろんな角度から確認しておく
redash => select count(*) from users;
redash => select count(*) from users where email not like '%@XXXX.com';
redash => select count(*) from users where email like '%@YYYY.jp'; 
redash => select count(*) from users where email like '%@XXXX.com';
count
-------
   NNN
(1 row)

-- 対象レコードの変更
redash => update users set email = replace(email, '%@XXXX.com', '%@YYYY.jp') where email like '%@XXXX.com';
UPDATE NNN

　where句をつけているのはUPDATE に表示される件数が、変更されないレコードも含めた変更対象の全レコード数の値となってしまう(変更対象である%@XXXX.com ではないレコードも、変更されないが変更対象かどうか検索はかけられるため)ので、変更予定じゃないユーザも変更してしまったかも！などと焦らないためにも、対象となるものだけにしぼるようなwhere句をつけてupdateをかけるようにしたほうが精神的にも安全です。

　また、このデータベース側だけを変更したタイミングで、アクセスしてしまったユーザがいた場合には、UserIDとの差異が生じ、本来、権限が引き継がれた状態でログインできるようになるはずのユーザが新規ユーザとして認識され、古いユーザが権限が初期状態のユーザが作成されてしまいます。この間にどのユーザからもアクセスがないように気をつけてください。

　もし仮に誤ったユーザが残ってしまう、作成されてしまう状況となった場合、UI上からはユーザをdisableにするまでしかできないため、データベース上にあるデータの物理削除が必要となります。

　データベースからのユーザ削除の方法も、下記に記載しておきます。

-- 複数存在する状態になってしまったユーザを調査
-- ログインの日付やドメインなどなど他のcolumn情報も含め、消す対象のidを確認する
redash => select id,email,created_at from users where email like '%taro.yamada@%';
 id  |         email        |          　created_at
-----+----------------------+-------------------------------
 111 | taro.yamada@XXXX.com | 20XX-MM-DD HH:MM:SS.123456+00
 222 | taro.yamada@YYYY.jp  | 20YY-MM-DD HH:MM:SS.123456+00

-- 今回はid = 222を消す
-- 消すユーザが間違っていないか、users,eventsテーブルで再確認
-- eventsテーブルでは、idは、user_id
redash => select * from users where "id" = '222';
redash => select * from events where "user_id" = '222';

-- ユーザ削除
redash => delete from events where "user_id" = '222';
DELETE 15
redash => delete from users where "id" = '222';
DELETE 1

redash => select id,email,created_at from users where email like '%taro.yamada@%';

　一度ログインされたユーザにはeventsテーブルに残るイベント情報が紐づいてしまうため、ユーザを消す前にイベント情報のレコード削除が必要となります。

　データベース側のユーザ情報変更が完了しましたら、続けてSAML認証側の設定を変更します。GOのRedashでは、Azure ADとの連携でのSAML認証をおこなっています(基本設定は割愛)。

　変更箇所は、対象のエンタープライズアプリケーション内シングルサインオン項になります。属性とクレーム部分に、今回のUserIDとなるuserprincipalname関連の設定箇所があり、こちらを変更していきます。

　変更前(参考値)

　変更後(参考値)

　user.userprincipalname部分をすべてuser.mailにすることで、一旦のユーザ識別が新しいメールアドレスベースのものになります(FirstName/LastName等の値は参考まで)。

　このSAML認証の設定変更後にアクセスすると、新しいドメインのユーザでログインした場合でも、既存のユーザの権限情報が引き継がれた形でのログインが可能となります。また、SAML認証のログインがなされている状態で一連の作業後にアクセスすると、強制的に再度SAML認証のログイン画面が開かれる状態になっています。

変更作業に際しての注意点など

今回の手順はあくまで参考まででお願いします。実施する場合にはすべて自己責任にてお願いします。
認証部分の変更となるため、締め出しが発生する場合もないとは言い切れません。バックアップ等をあらかじめ取っておく、データの作業前への切り戻しが可能な状態にするなどの対応は事前にしておくほうが安全です。
SAML認証側の設定変更後、ユーザ状態や頃合いをみて、然るべき形に修正していく予定です。
Azure ADの属性マッピングのカスタマイズには、replace関数が使えるため、UserID(user.userprincipalname)のメールアドレスのドメイン部分を、 XXXX.comから YYYY.jp に変換する、もしくはその逆のパターンの変換などによって、SAML側設定に都合の良い形に合わせたUserIDに変化させることが可能です。これにより、複数のドメインを許容できるような状態を作ることができ、ユーザ側がドメインの変更を意識することなく安全に変更ができる環境をつくることも可能です。
データベースを直接操作したくないという場合であれば、SAML認証側の設定変更のみを行い、新しいドメインのメールアドレスのユーザは新規ユーザとして権限と共に再度作り直していく(旧ドメインのユーザはdisable状態)方法もあるかもしれません。