はじめに
こんにちは、MA部SREブロックの片桐です。MA部ではメルマガやLINE、アプリプッシュ通知を配信するためのマーケティングオートメーションシステムを開発・運用しています。
MA部ではDBとして主にCloud SQL for MySQLを利用しており、調査や不具合対応のために開発メンバーがDBにログインして各種SQLを実行する場面があります。
このとき、共用の特権DBユーザーとパスワード認証を利用していました。しかし、この方式ではパスワード管理が必要になるほか、DB上のログイン主体も個人に紐づけにくい状態でした。
これらの課題を解決するために、人間によるDBへのログイン方式を、共用の特権DBユーザーとパスワード認証から個人のGoogle Cloudアカウントを使ったIAM認証へ移行しました。
あわせて、IAM認証でログインする各ユーザーには通常時は参照権限のみを付与し、書込系権限が必要な場合だけGitHub Actionsの承認付きワークフローから一時付与する運用にしました。
本記事では、共用DBユーザーによる運用から個人のIAM認証を使った運用へ移行した背景と、MySQLロールの一時付与を実現するための構成例を紹介します。
目次
- はじめに
- 目次
- 従来の運用の課題
- 目指した状態
- 全体構成
- IAM認証で個人ログインにする
- MySQLロールでDB内権限を分ける
- GitHub Actionsを承認ゲートにして書込系ロールを一時付与する
- 一時付与した書込系ロールを剥奪する
- 運用上の注意点と今後の改善
- おわりに
従来の運用の課題
従来の構成ではデータベース操作用の共用特権DBユーザーを作成し、パスワード認証でCloud SQL for MySQLへログインしていました。
構成としては次のとおりです。
ここで利用しているCloud SQL Studioは、Google Cloudコンソール上からCloud SQLへ接続してSQLを実行できるWebベースの画面です。
この運用では、複数人が同じDBユーザーを使ってログインします。そのため、主に次のような課題がありました。
強い権限が常時使える
日常運用におけるデータ調査であれば、多くの場合はSELECTを実行できれば十分です。
しかし、共用の特権DBユーザーを使うと、参照だけで済む作業時でも特権によりデータ変更やDDL操作まで実行できてしまいます。
強い権限を持った状態でSQLを実行すると、誤操作時に本来不要だったデータ更新やスキーマ変更まで起きてしまう可能性があります。そのため、通常時は参照のみを許可し、必要なときだけ書込系権限を一時的に付与する運用にしたいと考えました。
個人単位で追跡できない
共用ユーザーでログインするため、データベースから見ると誰が操作しても同じユーザーに見えます。
そのため、DB上のログイン主体を開発メンバー個人のGoogle Cloudアカウントと結びつけにくい状態でした。
人間のDBログインを個人のIAM認証に寄せることで、少なくともDBへのログイン主体は個人単位で扱えるようになります。
目指した状態
共用特権DBユーザーの課題を踏まえ、今回の移行では次の状態を目指しました。
- 人間によるDBへのログインを、共用ユーザーではなく個人のGoogle Cloudアカウントに紐づける
- 通常時は参照権限のみを付与する
- 書込系権限は常時付与せず、必要なときだけ一時的に付与する
- 書込系権限の付与申請を簡単に行えるようにする
- 書込系権限の付与には、申請者以外の承認を必須にする
- 付与した書込系権限は、作業後または定期実行で剥奪する
今回の構成では、Cloud SQLへのログインにはIAM認証を利用します。一方で、ログイン後にどのSQLを実行できるかはMySQL側の権限で制御します。
さらに、書込系権限は常時付与せず、必要なときだけ承認付きで一時付与して、作業後または定期実行で権限を剥奪します。
そのため、今回の構成ではログイン可否、DB内権限、権限の一時付与、付与後の剥奪を次のように分けて考えました。
| 項目 | 役割 | 利用する仕組み |
|---|---|---|
| 認証 | 誰がCloud SQLへログインできるかを制御する | Cloud SQL IAM認証 |
| 認可 | ログイン後に何を実行できるかを制御する | MySQLロール |
| 一時付与 | 必要時だけ書込系権限を付与する | GitHub Actionsの承認付きワークフロー |
| 剥奪 | 一時付与した権限を戻す | 手動または定期実行のREVOKEワークフロー |
この構成により、通常時は参照権限のみを使い、書込系権限が必要な場合だけ承認付きで一時的に付与する運用にしました。
全体構成
今回構築した仕組みは、通常時のログイン経路、必要時における書込系権限の一時付与フロー、一時付与した権限の剥奪フローに分かれます。
通常時は、開発メンバーがCloud SQL Studioから自分のGoogle CloudアカウントでCloud SQL for MySQLへログインします。
本記事ではCloud SQL Studioから接続する例で説明しますが、接続元はこれに限りません。IAM認証に対応した接続方式であれば、同じ考え方を適用できます。
Cloud SQLへのログイン可否はIAMで制御し、ログイン後に実行できるSQLはMySQLロールで制御します。通常時は、開発メンバーに参照用のMySQLロールのみを付与します。
IAM認証へ移行した後の通常時の構成は次のとおりです。
この状態では、開発メンバーはCloud SQL StudioからSELECTを実行できます。一方で、書込系権限は通常時には付与しません。
データ修正などで書込系権限が必要な場合は、GitHub Actionsの手動ワークフローを実行します。ワークフローはGitHub Environmentの承認待ちになり、申請者以外のメンバーが承認すると、対象ユーザーに書込系のMySQLロールを一時的に付与します。
書込系権限を一時付与する流れは次のとおりです。
一時付与した書込系ロールは、作業後の手動実行または定期実行で剥奪します。剥奪の流れは次のとおりです。
剥奪は権限を戻す操作であるため、今回の例では付与時のような承認ゲートは設けていません。手動実行または定期実行でGitHub ActionsからSQL実行基盤を起動し、MySQLロールのREVOKEを実行します。
以降のコード例では、次のプレースホルダーを使います。
| プレースホルダー | 意味 |
|---|---|
YOUR_PROJECT_ID |
Google CloudプロジェクトID |
YOUR_PROJECT_NUMBER |
Google Cloudプロジェクト番号 |
YOUR_MEMBER_NAME |
開発メンバーのメールアドレスの@より前の部分 |
YOUR_MEMBER_DOMAIN |
開発メンバーのメールアドレスのドメイン |
YOUR_SA_NAME |
権限操作用サービスアカウント名 |
YOUR_DB_NAME |
対象のデータベース名 |
YOUR_TABLE_NAME |
対象のテーブル名 |
YOUR_COLUMN_NAME |
対象のカラム名 |
YOUR_WIF_POOL |
Workload Identity Pool名 |
YOUR_WIF_PROVIDER |
Workload Identity Provider名 |
YOUR_GITHUB_ENVIRONMENT_NAME |
承認ゲートとして利用するGitHubのEnvironment名 |
IAM認証で個人ログインにする
まず、個人のGoogle CloudアカウントでCloud SQL for MySQLへログインできる状態を作ります。
Cloud SQL for MySQLでIAM認証を利用するため、主に次の項目を設定しました。
- Cloud SQLインスタンスでIAM認証を有効化する
- 開発メンバーごとのIAMデータベースユーザーを作成する
- Cloud SQLへログインするためのIAMロールを付与する
- Cloud SQL Studioを利用するためのIAMロールを付与する
これらの設定は、Google Cloudコンソール、gcloud CLI、Terraformなどで行えます。MA部ではインフラ設定をTerraformで管理しているため、以降ではTerraformでの設定例を示します。
IAM認証の有効化
Cloud SQL for MySQLでIAM認証を有効化するには、インスタンスのデータベースフラグcloudsql_iam_authenticationを有効にします。
resource "google_sql_database_instance" "main" { # name, database_version, region などは省略しています settings { database_flags { name = "cloudsql_iam_authentication" value = "on" } } }
IAMデータベースユーザーの作成
次に、開発メンバーをIAMデータベースユーザーとして作成します。
人間のGoogle CloudアカウントをIAMデータベースユーザーとして作成する場合は、typeにCLOUD_IAM_USERを指定します。
resource "google_sql_user" "member" { project = "YOUR_PROJECT_ID" name = "YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" instance = google_sql_database_instance.main.name type = "CLOUD_IAM_USER" }
この例では、IAMデータベースユーザーをYOUR_MEMBER_NAME@YOUR_MEMBER_DOMAINとして作成しています。
Cloud SQL for MySQLでは、IAMデータベースユーザーのメールアドレスの@より前の部分をMySQL上のユーザー名として扱います。そのため、後続のGRANTではYOUR_MEMBER_NAMEを指定します。
Cloud SQLへのログイン権限の付与
IAM認証でCloud SQLへログインするには、cloudsql.instances.login権限が必要です。この権限は、事前定義ロールのroles/cloudsql.instanceUserに含まれています。
resource "google_project_iam_member" "cloudsql_login" { project = "YOUR_PROJECT_ID" role = "roles/cloudsql.instanceUser" member = "user:YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" }
また、本記事では開発メンバーがCloud SQL Studioから接続する前提のため、Cloud SQL Studioを利用するためのIAMロールも付与します。
resource "google_project_iam_member" "cloudsql_studio" { project = "YOUR_PROJECT_ID" role = "roles/cloudsql.studioUser" member = "user:YOUR_MEMBER_NAME@YOUR_MEMBER_DOMAIN" }
ここまでで、開発メンバーが自分のGoogle Cloudアカウントを使ってCloud SQLへログインするための準備が整います。
ただし、IAM認証はCloud SQLへログインする主体を制御する仕組みです。ログイン後にどのSQLを実行できるかはMySQL側の権限で制御します。
MySQLロールでDB内権限を分ける
Cloud SQLへのログイン可否はIAMで制御しますが、ログイン後にどのデータベースやテーブルに対して、どのSQLを実行できるかはMySQL側の権限で制御します。
MySQLにおけるロールは、複数の権限をまとめて管理してユーザーへ付与するための仕組みです。本記事では、Cloud SQL for MySQLのMySQL 8.0系でロールを利用する前提で説明します。
通常時と一時付与用のロール
今回は例として、次の2種類のMySQLロールを用意します。
| ロール | 用途 | 権限 |
|---|---|---|
viewer |
通常時の参照用ロール | SELECT |
editor |
承認後に一時付与する書込系ロール | SELECT, INSERT, UPDATE, DELETE |
通常時は、開発メンバーにviewerのみを付与します。これにより、Cloud SQL Studioへログインした直後は参照のみ実行できる状態です。
一方、editorは通常時には付与しません。データ修正などで書込系権限が必要になった場合だけ、後述するGitHub Actionsの承認付きワークフローから一時的に付与します。
ロールの分け方、付与する権限、対象範囲は、実際の運用や対象データによって調整が必要です。本記事では通常時の参照権限と、承認後に一時付与する書込系権限とで2つに分ける例として説明します。
ロールの作成
MySQL上にロールを作成して、参照や更新に必要な権限を付与します。
この例では、YOUR_DB_NAME.*に対して権限を付与しています。実際の運用では必要以上に広い範囲へ権限を付与しないよう、対象データや作業内容に応じて、データベース単位、テーブル単位、権限種別を調整してください。
CREATE ROLE 'viewer', 'editor'; GRANT SELECT ON YOUR_DB_NAME.* TO 'viewer'; GRANT SELECT, INSERT, UPDATE, DELETE ON YOUR_DB_NAME.* TO 'editor';
参照用ロールの付与
開発メンバーには、通常時の権限としてviewerロールを付与します。
また、データベースへのIAMログイン直後から標準で有効になるように、viewerをデフォルトロールとして設定します。
MySQLユーザーは'user'@'host'の形式で扱われます。本記事のサンプルでは'YOUR_MEMBER_NAME'@'%'としており、%は任意の接続元を表すhost部です。
実際の運用では、Cloud SQLへの到達経路やネットワーク制御に応じてhost部を調整してください。
GRANT 'viewer' TO 'YOUR_MEMBER_NAME'@'%'; SET DEFAULT ROLE 'viewer' TO 'YOUR_MEMBER_NAME'@'%';
GitHub Actionsを承認ゲートにして書込系ロールを一時付与する
editorロールが必要な場合は、GitHub Actionsの手動ワークフローから申請するようにしました。
この仕組みにおけるGitHub Actionsの役割は、Cloud SQLへの接続経路そのものではなく、書込系ロールを一時付与するための承認ゲートです。実際にGRANTを実行する処理はCloud SQLへ接続できる実行環境で行います。本記事ではCloud Buildを利用した例として説明します。
GitHub Environmentで申請者以外の承認を必須にする
今回利用するGitHub Actionsのワークフローの中では、GitHub Environmentを承認ゲートとして利用します。
EnvironmentにはRequired reviewersを設定し、Prevent self-reviewを有効にします。ワークフローのjobで対象Environmentを指定すると、そのEnvironment上での実行前に承認を要求できます。
これにより、申請者以外のメンバーによる承認を挟んでから後続の処理を実行できるようになります。今回の例では、この承認ゲートを使って承認後にeditorロールを一時付与するようにしました。
権限操作用サービスアカウントの作成
承認後にGRANTを実行するため、権限操作用サービスアカウントを用意します。
権限操作用サービスアカウントは、GitHub ActionsからWorkload Identity Federation経由で利用します。承認後は、SQL実行基盤がこのサービスアカウントでMySQLへ接続し、対象ユーザーへeditorロールを付与します。
このサービスアカウントもCloud SQLへIAM認証でログインできるようにするため、IAMデータベースユーザーとして作成しておきます。
resource "google_sql_user" "grant_sa" { project = "YOUR_PROJECT_ID" name = "YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com" instance = google_sql_database_instance.main.name type = "CLOUD_IAM_SERVICE_ACCOUNT" }
この例では、権限操作用サービスアカウントをYOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.comとして作成しています。
サービスアカウントの場合も、MySQL上では@YOUR_PROJECT_ID.iam.gserviceaccount.comを除いた部分をユーザー名として扱います。そのため、後続のGRANTではYOUR_SA_NAMEを指定します。
権限操作用サービスアカウントがMySQL上でeditorロールを他ユーザーへ付与できるように、MySQL側ではWITH ADMIN OPTION付きでeditorロールを付与しておきます。
GRANT 'editor' TO 'YOUR_SA_NAME'@'%' WITH ADMIN OPTION;
WITH ADMIN OPTIONを付与されたユーザーは、そのロールを他のユーザーへ付与できます。つまり、この権限操作用サービスアカウントは書込系ロールの付与経路です。
そのため、Workload Identity Federationの条件やサービスアカウントの利用権限を絞る必要があります。想定したGitHubリポジトリ、ブランチ、Environment以外から利用されないようにしておきます。
承認後に実行するSQLは、最終的には次のような形です。
GRANT 'editor' TO 'YOUR_MEMBER_NAME'@'%';
ワークフロー設定例
GitHub ActionsからGoogle Cloudへの認証には、Workload Identity Federationを利用します。
サービスアカウントキーをGitHub Secretsに保存せず、GitHub ActionsのOIDCトークンを使ってGoogle Cloudのサービスアカウントを利用できます。
GitHub Actionsのワークフロー構文を使った設定ファイルの例は次のとおりです。
name: grant-cloudsql-db-role on: workflow_dispatch: inputs: target_user: description: 対象ユーザーのIAMアカウントメールアドレス type: string required: true role: description: 付与するMySQLロール type: choice options: - editor required: true jobs: grant: runs-on: ubuntu-latest # このEnvironmentにRequired reviewersとPrevent self-reviewを設定する environment: YOUR_GITHUB_ENVIRONMENT_NAME permissions: contents: read # OIDCトークンを発行するために必要 id-token: write steps: - uses: actions/checkout@v4 # Workload Identity FederationでGoogle Cloudへ認証する - uses: google-github-actions/auth@v2 with: workload_identity_provider: projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account: YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name: Grant DB role run: gcloud builds submit --config=grant.yaml --substitutions="_TARGET_USER=${{ inputs.target_user }},_ROLE=${{ inputs.role }}"
ここでは、Cloud SQLがPublic IPを持たず、プライベート経路でのみ到達できる構成として、Cloud Build上で権限操作SQLを実行します。
今回の構成では、Cloud SQLへ到達できるVPC内に中継用Compute Engineインスタンスを配置しました。Cloud Buildからは、そのインスタンス上のCloud SQL Auth Proxyを経由してCloud SQLへ接続します。
なお、SQLの実行経路は環境に依存します。Cloud SQLへの到達方式や既存の実行基盤によっては、GitHub Actionsのself-hosted runnerやCloud Run jobsなども選択肢になります。
一時付与した書込系ロールを剥奪する
editorロールは一時的な付与を前提としているため、作業後には剥奪できるようにします。
editorロールの付与は権限を強める操作のため、GitHub Environmentによる承認を必須にしています。一方、editorロールの剥奪は一時付与した権限を戻す操作のため、今回の例では承認ゲートを設けていません。
ロール剥奪の方法には、手動実行と定期実行の2つを用意しました。
- 作業後に任意のタイミングで剥奪するための手動ワークフロー
- 戻し忘れを抑止するための定期実行ワークフロー
いずれの場合も、GitHub ActionsからCloud Buildへ処理を渡し、権限操作用サービスアカウントでMySQLへ接続してREVOKEを実行します。
手動でロールを剥奪する
手動剥奪では、対象ユーザーを入力として受け取り、次のようなSQLを実行します。
REVOKE 'editor' FROM 'YOUR_MEMBER_NAME'@'%';
GitHub Actionsのワークフロー設定ファイルの例は次のとおりです。
name: revoke-cloudsql-db-role on: workflow_dispatch: inputs: target_user: description: 対象ユーザーのIAMアカウントメールアドレス type: string required: true jobs: revoke: runs-on: ubuntu-latest permissions: contents: read id-token: write steps: - uses: actions/checkout@v4 - uses: google-github-actions/auth@v2 with: workload_identity_provider: projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account: YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name: Revoke DB role run: gcloud builds submit --config=revoke.yaml --substitutions="_TARGET_USER=${{ inputs.target_user }}"
定期実行でロールを剥奪する
手動での剥奪漏れを防ぐため、editorロールを定期的にも剥奪するように設定しておきます。
定期剥奪では、MySQL上の現在のロール付与状態を確認し、editorロールを保持しているユーザーを対象にREVOKEを実行します。
GitHub Actionsのワークフロー設定ファイルの例は次のとおりです。
name: revoke-cloudsql-db-role-scheduled on: workflow_dispatch: schedule: # 毎日 00:00 JST に実行する # GitHub Actions の cron は UTC 基準のため、15:00 UTC を指定する - cron: "0 15 * * *" jobs: revoke: runs-on: ubuntu-latest permissions: contents: read id-token: write steps: - uses: actions/checkout@v4 - uses: google-github-actions/auth@v2 with: workload_identity_provider: projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/YOUR_WIF_POOL/providers/YOUR_WIF_PROVIDER service_account: YOUR_SA_NAME@YOUR_PROJECT_ID.iam.gserviceaccount.com - name: Revoke all temporary DB roles run: gcloud builds submit --config=revoke-all.yaml
この例でCloud Buildに渡しているrevoke-all.yamlでは、MySQL上でeditorロールを保持しているユーザーを取得します。そのうえで、権限操作用サービスアカウントのDBユーザーを剥奪対象から除外し、残ったユーザーに対して順にREVOKEを実行します。
権限操作用サービスアカウントからeditorロールを剥奪すると、以降のGRANTやREVOKEを実行できなくなるためです。
運用上の注意点と今後の改善
今回の構成により、人間によるCloud SQL for MySQLへのログインを個人のIAM認証に寄せ、通常時に書込系権限を持たない運用にできました。
一方で、この仕組みをより安全に、かつ便利に運用するには、権限付与後のロールの使い方、ワークフロー入力値の扱い、ロール粒度などを継続的に見直す必要があります。
ここでは、今回の構成における運用上の注意点と、今後の改善余地を整理します。
一時付与したロールを使うときの注意
今回の例では、editorロールはMySQLユーザーに対するデフォルトロールとして設定していません。そのため、Cloud SQL Studioなどから一時付与された権限を使う場合は、実行するSQLと同じセッション内でSET ROLEを実行します。
特に、実行単位によってセッションが変わりうる接続方式では、SET ROLEと対象SQLを同じ実行単位にまとめます。
SET ROLE 'editor'; UPDATE YOUR_TABLE_NAME SET YOUR_COLUMN_NAME = 'XXXXX' WHERE id = XXX;
なお、Cloud SQL for MySQLではactivate_all_roles_on_loginフラグを有効にすると、ログイン時に付与済みのロールを自動的に有効化できます。ただし、その場合は一時付与した書込系ロールもログイン時に自動で有効化されるため、通常時に有効化したいロールと一時付与するロールの扱いを踏まえて設計する必要があります。
ワークフロー入力値の検証
本記事では、GitHub ActionsからCloud Buildへtarget_userやroleを渡し、SQL実行基盤側でGRANTやREVOKEを実行する構成を例にしています。
ワークフロー入力値をもとにSQLを組み立てる場合、想定外のユーザー名やロール名がSQLに含まれる可能性があります。
そのため、GitHub Actions側で入力形式を絞るだけでなく、SQL実行基盤側でも許可したユーザー名やロール名だけを扱うように制御する必要があります。
SQL本文のレビューと監査
今回の構成では、GitHub Actionsから対象ユーザーへのeditorロールの一時付与を申請し、承認を挟むようにしています。
ただし、承認対象はeditorロールの一時付与です。承認後に実行されるSQL本文そのものは、今回の仕組みではレビュー対象にしていません。
SQL本文まで事前に確認する場合は、申請時に実行予定のSQLや作業内容を添付し、承認者による確認を挟む運用も考えられます。
実行後の追跡性を高めるには監査ログやDB監査機能を組み合わせ、誰がいつ、どの操作をしたかを確認できる状態にしておくことも重要です。
一時付与した権限の失効タイミングの厳密化
今回の例では、手動剥奪と毎日0時の定期剥奪でeditorロールを戻す構成にしています。
より厳密に制御したい場合は、付与時刻や申請IDを記録してユーザー単位で失効時刻を管理する設計が必要になります。
MySQLロールの粒度
本記事でのMySQLロールの例としては、書込系権限をeditorロールにまとめました。
ただし、INSERT、UPDATE、DELETE、各種DDLでは影響範囲が異なります。特にDELETE、DROP、ALTERのような操作は対象データや運用ルールによっては別ロールに分けるほうが安全です。
例えば次のように、MySQLのロールを細分化する余地があります。
| ロール | 権限 | 用途 |
|---|---|---|
viewer |
SELECT |
通常調査 |
data_writer |
INSERT, UPDATE など |
手動データ補正 |
data_deleter |
DELETE |
削除が必要な例外対応 |
schema_editor |
CREATE, ALTER など |
スキーマ変更 |
schema_dropper |
DROP |
破壊的DDL |
ただし、ロールを細かく分けるほど、申請フローや承認基準も複雑になります。そのため、対象データ、作業頻度、レビュー体制に応じてロール粒度を調整していく必要があります。
おわりに
本記事では、人間によるCloud SQL for MySQLへのアクセスを、共用の特権DBユーザーとパスワード認証から、IAM認証とMySQLロールを使った運用へ移行した例を紹介しました。
今回の構成では、Cloud SQLへのログインは個人のGoogle Cloudアカウントに寄せ、DB内の権限はMySQLロールで制御しています。通常時は参照用のviewerロールのみを利用し、書込系権限が必要な場合だけGitHub Actionsの承認付きワークフローからeditorロールを一時付与する形にしました。
これにより、共用特権DBユーザーに依存した人間によるアクセスをやめ、強い権限が常時使える状態を避けられるようになりました。
SQL本文のレビューや監査、ロール粒度の細分化、失効タイミングの厳密化などは、引き続き改善の余地があります。
今回の対応を足がかりとして、運用負荷と安全性のバランスを見ながら改善に取り組んでいきます。
ZOZOでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください。
