kou-tech
← Blog一覧へ

パスワードのローテーションを設計するより、パスワードを無くすほうが早かった

#tech#Azure#Laravel#SQL Server

データベースへの接続はSQL認証で、利用者名とパスワードをApp Serviceのアプリケーション設定に置いていました。値はポータルからも az webapp config appsettings list からも平文で見えます。

漏れたときに効く手当てはパスワードを回すことだけです。回すにはSQL側で新しい値を発行し、アプリケーション設定を書き換え、再起動を待つまでが1セットで、これを環境の数だけ繰り返します。読める人と写しは放っておいても増えるのに、減らす側にだけ手順が要る。

置き場所を変える方向で調べ始めて、着いたのはパスワードそのものを無くす形でした。

前提

LaravelをAzure App Service (Linux) で動かしていて、接続先はAzure SQL Databaseです。接続には sqlsrv ドライバを使っていて、PHP側はPDO_SQLSRV、その下はMicrosoft ODBC Driver for SQL Serverという積み方になっています。

同じデータベースに他システムのスキーマが同居しています。このアプリが読み書きするのは自分のスキーマ (以下 app) で、他システムの持ち物 (以下 shared) には一覧表示のための読み取りだけが届きます。

Key Vaultに入れても、ローテーションの手順は減らない

最初に見たのは、接続文字列をKey Vaultに置いて、App Serviceのアプリケーション設定からKey Vault参照で引く形です。Key Vaultはシークレットを一元管理してアクセス履歴まで残せるAzureのサービスで、参照にしておけばアプリケーション設定には値そのものが載りません。バージョンを指定しなければ最新版を24時間以内に拾ってくれるので、ローテーションとの相性も良さそうに見えました。

引っかかったのはLaravel側です。config/database.php の 'url' => env('DATABASE_URL') に値を入れると ConfigurationUrlParser が個別の DB_* へ分解しますが、読めるのは parse_url() が解釈できるURL形式だけです。

sqlsrv://user:password@host.database.windows.net:1433/dbname

パスワードが他の項目と1本に編み込まれているので、回すたびにホスト名もデータベース名も含んだURL全体を書き直すことになります。パスワードだけを個別の DB_PASSWORD に切り出す手もありますが、それでも回す作業は残ります。

置き場所の案を書き出したところで、比べる軸が「どこに置くか」ではないことに気づきました。

案 パスワードの置き場所 回すときにやること
A Key Vault SQL側で新しいパスワードを発行し、Key Vaultに新バージョンを追加し、反映を待つ
B どこにも無い 何もしない

BはマネージドIDです。Azureのリソース自身にEntra ID (MicrosoftのIDサービス) 上の識別情報を持たせる仕組みで、アプリに持たせていた資格情報はプラットフォーム側へ移ります。採用理由はドキュメントの1文がそのままです。

Managed identities eliminate the need for developers to manage these credentials.

(マネージドIDは、開発者がこれらの資格情報を管理する必要をなくします)

— Microsoft Entra「Managed identities for Azure resources」

Key Vaultが不要になったわけではなく、今回の接続にはパスワードという守る対象が無くなった、という順序です。

実施できるかはODBCドライバの版で決まった

方針は決まりましたが、ODBCドライバが対応していなければ何も始まりません。

A new Microsoft Entra managed identity authentication method was added in ODBC Driver version 17.3.1.1 for both system-assigned and user-assigned managed identities.

(システム割り当てとユーザー割り当ての双方について、Microsoft EntraのマネージドIDによる認証方式がODBC Driverバージョン17.3.1.1で追加されました)

— SQL Docs「Using Microsoft Entra ID with the ODBC Driver」

指定は接続文字列のキーワード1つで、Authentication=ActiveDirectoryMsi です。Laravel側にもこれを載せる口はあります。SqlServerConnector::getSqlSrvDsn() の末尾がそこです。

if (isset($config['authentication'])) {
    $arguments['Authentication'] = $config['authentication'];
}

return $this->buildConnectString('sqlsrv', $arguments);

問題は、同じメソッドが積むキーに Driver が無いことでした。どのODBCドライバを使うかを名指しする手段がなく、ロードされるものはホストの状態で決まります。

App Serviceのイメージには17と18が両方入っていることがあり、古い側に結び付いていたら Authentication が未知のキーワードとして弾かれます。Laravelから版を指定できない以上、詰みだと思っていました。

動作環境のApp ServiceにSSHで入って数えた結果がこれです。

項目 実測
msodbcsql17 17.10.6.1-1
msodbcsql18 18.3.3.1-1 (dpkgの状態は hi = hold)
結び付き先 [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired → 17
pdo_sqlsrv 5.12.0

結び付き先は、わざと届かないホスト名で接続してエラーの発行元を見る形で判定しました。バージョン番号を並べるだけでは、どちらがロードされたかは分からないためです。

17に結び付いていましたが、17.10.6.1は17.3.1.1より新しいので条件を満たします。詰みだと思っていたのは併存そのもので、実際に効くのは古い側の版だけでした。ODBC DSNを定義して迂回する案も用意していましたが、要らなくなりました。

Encryptを明示しても暗号化は増えない

config/database.php の encrypt はコメントアウトされたままでした。17の既定は no なので平文で流れているのではないか、と思って確かめました。

外れました。Azure SQLはサーバー側で強制しています。

These services always enforce TLS encrypted connections to ensure all data is encrypted in transit between the client and server.

(これらのサービスは常にTLS暗号化された接続を強制し、クライアントとサーバーのあいだのすべてのデータが転送中に暗号化されることを保証します)

— Azure「Security Overview — Transport Layer Security (encryption-in-transit)」

Encrypt を書いていようがいまいが、通信はもともと暗号化されていました。明示して増えるのは証明書を検証するかどうかだけで、それも TrustServerCertificate が No のときに限ります。アプリケーション設定にはそれが true で入っています。

そのうえで encrypt は明示しました。既定値が認証方式とドライバの版で動くので、条件によって変わる側に置いておきたくないからです。trust_server_certificate を false にするのは、認証方式と同時に変えると切り分けができないので別の作業にしました。

config/database.phpはauthenticationを足すだけで切り替わる

 'sqlsrv' => [
     'driver' => 'sqlsrv',
     'url' => env('DB_URL'),
     'host' => env('DB_HOST', 'localhost'),
     'port' => env('DB_PORT', '1433'),
     'database' => env('DB_DATABASE', 'laravel'),
-    'username' => env('DB_USERNAME', 'forge'),
-    'password' => env('DB_PASSWORD', ''),
+    'username' => env('DB_USERNAME'),
+    'password' => env('DB_PASSWORD'),
     'charset' => env('DB_CHARSET', 'utf8'),
     'prefix' => '',
     'prefix_indexes' => true,
-    // 'encrypt' => env('DB_ENCRYPT', 'yes'),
+    'encrypt' => env('DB_ENCRYPT', 'yes'),
+    'authentication' => env('DB_AUTHENTICATION'),
 ],

authentication は未設定なら null になり、getSqlSrvDsn() の isset() が false になるのでDSNに載りません。既存の接続の振る舞いは変わりません。

ユーザー割り当てのマネージドIDを使う場合は、UID にクライアントIDが要ります。getSqlSrvDsn() は UID を組み立てませんが、username がPDO側から渡るので届きます。今回はシステム割り当てなので、どちらも未設定にしました。

DB_USERNAMEを消すとスケルトンのforgeが復活する

env('DB_USERNAME', 'forge') はスケルトンから残っている値で、アプリケーション設定から DB_USERNAME を消した瞬間に forge が復活します。Laravelは username と password をDSNではなくPDOのコンストラクタ引数に渡すので、DSNに Authentication=ActiveDirectoryMsi が載っていても、利用者名として forge が一緒に飛んでいきます。設定を消して切り替える手順とスケルトンの既定値は相性が悪い。

db_datareaderはスキーマの外まで読む

権限は先に決めておく必要がありました。データベースが同居構成だからです。

チュートリアルの類は ALTER ROLE db_datareader ADD MEMBER で済ませていますが、権限の一覧表を見るとこの固定データベースロールの実体は GRANT SELECT ON DATABASE::<database-name> です。db_datawriter も同じで、対象はデータベース全体になります。

スキーマで分けていても、与えた時点で同居している他システムのテーブルまで読めます。スキーマを境界として扱うなら、GRANT もスキーマ単位で書くしかありません。境界を越える必要があるものだけ、越える先を名指しで足します。

CREATE USER [webapp] FROM EXTERNAL PROVIDER;
ALTER USER [webapp] WITH DEFAULT_SCHEMA = app;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO [webapp];
GRANT SELECT ON SCHEMA::shared TO [webapp];

マイグレーションを流す接続には、これに CREATE TABLE と ALTER ON SCHEMA::app を足します。CREATE TABLE はデータベースレベルの権限なのでスキーマを限定できませんが、テーブルを作るには対象スキーマへの ALTER も併せて要るため、結果として app にしか作れません。

DEFAULT_SCHEMA は付け足しではなく必須です。config/database.php にスキーマを指定する項目はなく、モデルも protected $table = 'users' のように修飾していないので、見に行く先は利用者の既定スキーマだけで決まります。作り直すときにこの行を落とすと、全モデルが dbo を見て落ちます。

表示名が重複していて利用者を作れなかった

App Serviceのシステム割り当てマネージドIDを有効にして、Entra管理者でクエリエディタに入り、上のSQLを流したところで止まりました。

Msg 33131, Level 16, State 1, Line 4
Principal 'webapp' has a duplicate display name. Make the display name unique in Azure Active Directory and execute this statement again.

認証方式の話をしていたはずが、止まったのは名前でした。

Entra IDはサービスプリンシパルの表示名の重複を許しますが、データベース側は利用者名の一意を求めます。どこで増えたかは追っていません。作り直しても表示名が一意になるわけではなく、名前で解決する道は最初から無いためです。

回避策はDDLの拡張です。

CREATE USER [user_name] FROM EXTERNAL PROVIDER WITH OBJECT_ID = ‘objectid’

— SQL Docs「Microsoft Entra logins and users with nonunique display names」

このとき利用者名を表示名のままにはできず、元の名前にサフィックスを足した別名が要ります。同じ節がサフィックスにオブジェクトIDの先頭5文字までを使うことを勧めているので、それに合わせました。

CREATE USER [webapp-aaaaa] FROM EXTERNAL PROVIDER
  WITH OBJECT_ID = 'aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb';

この別名はデータベースの中だけの名前で、接続の設定には現れません。システム割り当てのIDによる接続はIDで照合されるので、DB_USERNAME には何も置きません。

作成後は sys.database_permissions を引いて、既存の利用者と同じ権限が付いていることを確認しました。

切り替えは設定の2操作で済んだ

配信のあと、切り替え前の状態で動くことを確認してから設定を入れ替えました。

az webapp config appsettings set --name webapp --resource-group app-rg \
  --settings DB_AUTHENTICATION=ActiveDirectoryMsi

az webapp config appsettings delete --name webapp --resource-group app-rg \
  --setting-names DB_USERNAME DB_PASSWORD

設定を変えるとApp Serviceが再起動し、起動スクリプトの php artisan optimize で設定のキャッシュも作り直されます。キャッシュが残っていると env() は古い値を返すので、ここが自動で揃うのは助かりました。

接続中の利用者を問い合わせて確認しました。

php artisan tinker --execute 'dump(DB::select("SELECT USER_NAME() AS u, SCHEMA_NAME() AS s"));'

webapp-aaaaa と app が返ってきて、マネージドIDでの接続と既定スキーマの両方が成立していることが分かりました。ただしこのクエリは権限をほとんど使わないので、接続できたことと権限が足りていることは別です。画面からの一覧表示と登録を通すまでが確認で、足りなければ GRANT を足せば済み、切り替えを戻す必要はありません。

まとめ

時間を使ったのはドライバの版と利用者の名前で、パスワードを無くすと決めるところではほとんど迷いませんでした。守り方を設計するより守る対象を減らすほうが早い場面は、まだ他にもある気がします。