Cloud SQL for MySQL8.0にリードオンリーユーザーを作成する

こんにちは、エンジニアの加藤(@tomo_k09)です。PharmaXの薬局DX事業部で主にバックエンドの開発を担当をしています。

CloudSQLで使っているMySQL8.0にリードオンリーユーザーを作成する際に少しハマりどころがあったので、この記事ではリードオンリーユーザーの作成方法についてまとめました。

CloudSQLのコンソール画面からではリードオンリーユーザーを作れない

結論、CloudSQLのMySQL8.0データベース内に読み取り権限のみを持ったユーザーを作成するには、cloudsqlsuperuserロールを持つユーザーでデータベースにログインした上で、データベース内に読み取り権限のみを持ったユーザーを作成する必要があります。

CloudSQLのドキュメントを読むと、CloudSQLでMySQL8.0を利用している場合、コンソール画面からデータベースユーザーを作ってしまうと、clousqlsuperuserという何でもできてしまうユーザーが強制的に作られてしまうとのことでした。（ちなみにMySQL5.6 or 5.7であれば、コンソールからでも権限の調整をできるようです）

MySQL 8.0 をサポートする Cloud SQL では、新しいユーザーを作成すると、自動的に cloudsqlsuperuser ロールが付与されます。cloudsqlsuperuser ロールは、数多くの MySQL 権限が含まれる Cloud SQL ロールです。このロールは、SUPER と FILE を除く、すべての MySQL 静的権限をユーザーに付与します。
https://cloud.google.com/sql/docs/mysql/users?hl=ja

また、PharmaXではインフラをTerraformで管理しているので、Terraformで何かしらのパラメータを渡すことによってリードオンリーユーザーを作れると嬉しいなぁとも考えたのですが、(Terraformのリファレンス)を読む限り、それも難しそうです。

冒頭にも書いた通り、CloudSQLのAPIを使用してユーザーを作成する場合は、問答無用で多くの権限を持つcloudsqluserというロールを持ったユーザーが出てきてしまうので、cloudsqlsuperuserロールを持つユーザーでデータベースにログインした上で、データベース内に読み取り権限のみを持ったユーザーを作成する必要があります。

CloudSQL MySQL8.0にリードオンリーユーザーを作成する手順

では具体的なリードオンリーユーザーの作成方法について紹介します。

ステップ1:CloudSQLに読み取り権限を与えるユーザーと読み取り権限を与えられるユーザーを作成する

まず、『読み取り権限を与えるユーザー』と『読み取り権限を与えられるユーザー』の2つを用意します。

PharmaXではTerraformで管理しているので、以下のようにユーザーを作りました。

# 読み取り権限を与えるためのslq_user
resource "google_sql_user" "this" {
  name     = 'sql_user'
  instance = 'hogehoge'
  password = 'sql_user'
}

# 読み取り権限が与えられるslq_user
resource "google_sql_user" "readonly" {
  name     = 'readonly_sql_user'
  instance = 'hogehoge'
  password = 'readonly_sql_user'
}

CloudSQLのコンソールからユーザーを作成していただいても問題ありません。

CloudSQLのコンソール画面からsql_userを作る場合とTerraformからsql_userで作る場合で、sql_userのnameに使える文字が異なるので注意してください。

具体的には、特定の記号をnameに使っていると、CloudSQLのコンソール画面からであればsql_userを作成できますが、Terraformからだと作成できないことがあります。

詳しくは以下のスクラップをご参照ください。
https://zenn.dev/tomoyuki_kato/scraps/9d7e6b1633e921

ステップ2：MySQLにログイン

次にMySQLにログインします。
この時のログインは読み取り権限を与えるユーザーでログインしてください。

PharmaXでは踏み台サーバー経由でSequel Aceを使ってMySQLにログインしているので、Sequel Aceで権限付与の作業を行います。

踏み台サーバー経由でSequel Aceを使ってMySQLにログインする方法としては以下の記事を参照ください。

https://zenn.dev/masarufuruya/articles/sequql_ace_fumidai_ssh

ステップ3:権限の付与

Sequel AceでMySQLにログインしたら、クエリ画面に移動して、先ほど作ったユーザーがちゃんと表示されているかを確認します。以下のSQLでuserとホスト名を確認してください。

SELECT user, host FROM mysql.user

次に読み取り権限を与えられるユーザーからcloudsqlsuperuserというロールを剥奪し、閲覧権限だけを付与します。以下のSQLを実行してください。

REVOKE 'cloudsqlsuperuser' FROM '読み取り権限を与えられるユーザー名'@'ホスト名'
GRANT SELECT ON データベース名.* TO '読み取り権限を与えられるユーザー名'@'ホスト名';

最後にSELECTの権限だけ与えられているかを確認します。

SHOW GRANTS FOR '読み取り権限を与えられるユーザー名'@'ホスト名';

SELECTの権限だけが付与されていたら成功です。
以上でリードオンリーユーザーを作成できました。

まとめ

今回はCloudSQLのMySQL8.0にリードオンリーユーザーを作る方法を紹介しました。
参考になれば幸いです。

PharmaXでは、絶賛エンジニア採用中です！
もし興味をお持ちの場合は、私のXアカウント（@tomo_k09）までメッセージいただけますと幸いです。まずはカジュアルにお話しましょう！