認証プロバイダーのパスワードカラムマイグレーション: MySQLからPostgreSQLへの移行の記録

2023年10月24日

田中湧大

URLをコピーしました！

こんにちは、開発本部でインターンをしている田中です。

今回は企業ユーザーの認証を担っている認証プロバイダーのMySQLから、PR TIMESのPostgreSQLにパスワードカラムをマイグレーションしたので紹介します。

経緯

PR TIMESでは企業ユーザーの認証を行う認証プロバイダー（auth.prtimes.jp）が独自のMySQLを持っており、一部のデータがMySQLに保存されていました。

この認証プロバイダーはいくつかの問題から廃止を予定しています。

なのでまず認証プロバイダーのMySQLからPR TIMESのPostgreSQLにデータをマイグレーションすることにしました。

認証プロバイダーは何をしているのか

MySQLのデータを移行するにあたって認証プロバイダーの機能とどのようなデータが保存されているかを理解する必要がありました。

結論としては、認証プロバイダーはOAuth2.0 （RFC6749）の認可コードフローを提供しています。

PR TIMESから認可エンドポイントへリクエストして、ユーザーはメールアドレスとパスワードによる認証を行います。認証が通ればPR TIMESへコールバックして、PR TIMESは認可コードもとにアクセストークン発行のリクエストを送る。といったフローです。

MySQLには基本的なユーザー情報とOAuth2.0に関連するデータしか保存されていないことがわかりました。

💡 OAuth2.0については以下の記事が参考になります。

OAuth 2.0 全フローの図解と動画 – Qiita

RFC 6749: The OAuth 2.0 Authorization Framework

情報として重要性の高いデータはユーザーテーブルのIDとパスワードのみでIDは既にPostgreSQL側で持っていました。その他は認証プロバイダーを廃止することができれば必然的に必要のないデータでした。

なので今回は、パスワードカラムのみをPostgreSQLへマイグレーションすることにしました。

パスワードカラムをマイグレーションするための準備

無停止でパスワードカラムをマイグレーションするためにいくつかの準備が必要でした。

そのことについて紹介したいと思います。

認証プロバイダーからPR TIMES PostgreSQLに接続できるようにする

認証プロバイダーからMySQLとPostgreSQLに対してパスワードカラムへのRead/Writeするために両DBに接続できる必要があります。

認証プロバイダーとPostgreSQLはどちらもAWS上のリソースとして存在していますが別VPCで稼働していました。このままだとPostgreSQLへ接続することができないのでVPCピアリングという機能を使って認証プロバイダーVPCとPostgreSQLのVPCをピアリングしました。

💡 VPC ピアリングとは？

What is VPC peering? – Amazon Virtual Private Cloud

PostgreSQLの企業ユーザーテーブルにパスワードカラムを追加する

当然、移行先のテーブルにパスワードカラムが必要なので追加しました。php.netによると password_hash の第2引数PASSWORD_DEFAULT でハッシュ化すると現時点のデフォルトのBCRYPTが使われ60文字になるようですが、これは今後変わる可能性があるので255文字あたりが推奨されています。今回のカラム幅も推奨値に従います。

💡 参考

PHP: password_hash – Manual

PR TIMESのパスワードの安全性を高める | PR TIMES 開発者ブログ

パスワード変更時などに２つのDBに対してdouble writeする

PR TIMES上では以下のトリガーによってパスワードのINSERT・UPDATEが行われます。

企業ユーザー登録申請時
パスワード変更時
パスワード再設定依頼
サブユーザー登録時

各アクション実行時にMySQLとPostgreSQLに対してINSERT or UPDATEを実行します。

このとき注意しなければならないことはデータの整合性です。

PostgreSQLのUPDATEは成功し、MySQLでは何らかの原因で失敗したにも関わらずパスワード変更に成功したとレスポンスを返してしまえばユーザーが新しいパスワードでログインを試みても認証できません。

このような自体を避けるべく更新する順番やレスポンスの扱いには注意する必要があります。

PostgreSQL側のパスワードはこの時点ではまだ参照していないのでこちらを先に更新した後にMySQL側のパスワードを更新します。PostgreSQLの更新が成功してMySQLの更新が失敗した場合は、参照先のMySQLは古いパスワードのままです。しかしこの場合はレスポンスは失敗扱いになることからユーザーはもう一度パスワードの変更を試みる可能性が高いです。また漏れがないようにエラーが出ていないかNew Relicで監視をしていました。

パスワードカラムをマイグレーションするスクリプトを作成する

MySQL側のパスワードカラムからPostgreSQL側のパスワードへマイグレーションします。

今回は次の３つのスクリプトを作成しました

MySQL→PostgreSQLへ逐次パスワードをマイグレーションするスクリプト
パスワードが同期されているかチェックするスクリプト
企業ユーザーIDのリストを受け取り、それに基づいてマイグレーションするスクリプト

1.はマイグレーションを実行するためのメインのスクリプトです。説明のためコードの一部を示します。

<?php
const BATCH_SIZE = 100;
const SLEEP_US = 100000;

$max_user_id = PostgreSQLUserRepo::getMaxUserId($postgresql_pdo);
$failed_update_ids = [];

for ($offset = 0; $offset < $max_user_id; $offset += BATCH_SIZE) {
    $end = $offset + BATCH_SIZE - 1;
    if ($end > $max_user_id) {
        $end = $max_user_id;
    }
    $users = MySQLUserRepo::getUsersByIdRange($mysql_pdo, $offset, $end);
    foreach ($users as $user) {
        if (!PostgreSQLUserRepo::updatePostgresqlPasswordByUserId($postgresql_pdo, $user['id'], $user['password'])) {
            $failed_update_ids[] = $user['id'];
        }
        usleep(SLEEP_US);
    }
}

ステージング環境や、本番データをリストアしたDBで検証した結果を基に100件ずつ更新するようにして、負荷軽減のため間にスリープを入れています。またUPDATEに失敗した場合はIDを記録して3.で再同期できるようにしています。

2.は1.を実行後に正しく同期されているかを確認するためのスクリプトです。こちらも同様に同期されていないIDを記録し、3.で再同期できるようにしています。

パスワードカラムを移行

実装したスクリプトを実行していきます。

MySQLとPostgreSQLの両方に接続できる認証プロバイダーのバッチコンテナにECS Execを使って中に入って作業します（このとき意気揚々と「さあ、実行するぞ！」とコンテナの中に入ろうとしたらExecが有効化されておらず弾かれてしまいました・・・）。

💡 ECS Execの有効化について

PR TIMESで使用しているECSのおすすめ設定 | PR TIMES 開発者ブログ

無事、ECS Execを有効化できたところで改めてコンテナの中に入り、スクリプトを実行します。

aws ecs execute-command --cluster prtimes-auth --container batch --interactive --command "/bin/sh" --task ****

今回はスクリプトの実行時間が長時間になることが予想されるので、コネクションが切断されても処理が続行されるように、screenコマンドを用いてセッションを保持しておくことにしました。

screenとは

screenは端末を多重化できるターミナルマルチプレクサと呼ばれるツールです。

💡 screenについて

Linux screenコマンド使い方 – Qiita

新しいセッションを作成

$ screen -S migrate

スクリプトを実行してデタッチする

$ php artisan command:migrate_mysql_to_postgresql
Ctrl+a d

午後のリリースで実行を開始して翌日の出勤時間には実行が完了していました。

再びコンテナの中に入り、実行完了後に吐き出されたログの確認とパスワード同期チェックのスクリプトを実行します。

$ screen -r migrate
$ php artisan command:check_password_sync

結果的に両者ともに問題はなさそうだったので3.のスクリプトは実行せずに終わりました。

PostgreSQLからパスワードを参照する

いよいよ認証プロバイダーのMySQLからPR TIMESのPostgreSQLへパスワードの参照先を切り替えます。

認証プロバイダーはLaravelで実装されています。私はLaravelをこれまで触れたことがあまりなく、まずLaravelのログイン周りの仕組みについて理解するところからスタートしました。

Laravelのログイン認証については以下の記事が非常に参考になりました。

💡 Laravelのログイン認証について

本気で詳細を理解したい人向けのLaravelログイン認証 | アールエフェクト

EloquentUserProvider::validateCredentialsでパスワードのバリデートを行っていることがわかったのでこのメソッドをオーバーライドしてしまえば良さそうです。

そのためEloquentUserProviderを継承したAuthUserProviderを実装し、このクラスが使われるようにサービスに登録します。

class AuthUserProvider extends EloquentUserProvider
{
    public function validateCredentials(UserContract $user, array $credentials)
    {
        $plain = $credentials['password'];

　　　　　// PostgreSQL側のパスワードを取得する
        $postgresql_hashed_password = Users::query()->firstWhere('user_id', $user->getAuthIdentifier())->password;

        return $this->hasher->check($plain, $postgresql_hashed_password);
    }
}

これで認証プロバイダーがPostgreSQL側のパスワードを参照することになります。