CloudSQL に Vercel から接続する

このスクラップについて

このスクラップでは CloudSQL の MySQL インスタンスに Vercel から接続できるようにするまでに過程を記録していく。

スクラップ作成の経緯

それまで PlanetScale を使っていたが無料プランがなくなってしまったので移行が必要になってしまった。

先人の知恵

https://zenn.dev/laiso/scraps/595d631fe19ef5

https://b.hatena.ne.jp/entry/s/zenn.dev/laiso/scraps/595d631fe19ef5

パフォーマンス観点の調査についても興味深いがこのスクラップでは本当に接続するまでの部分を対象にしよう。

GCP ドキュメント

https://cloud.google.com/sql/docs/mysql/configure-ip?hl=ja

https://cloud.google.com/sql/docs/mysql/authorize-networks?hl=ja

手っ取り早く行うには全ての IP アドレスを許可するのが良いがさすがにセキュリティ的にまずそう。

そもそも追加できるのか？

やってみた感じだとできそうだ。

数分程度かかるらしい。

代替手段

https://vercel.com/docs/storage/vercel-postgres

移行できるのであれば DB を Vercel Postgres を使うのも一つの手かもしれない。

ホスティング自体を Cloud Run に移行してしまうのも良さそうだ。

TLS を必須にする

さすがにこれくらいは設定しておいた方が良さそうだ。

動作確認には --ssl-mode DISABLED を使用する。

MySQL クライアントのインストール

# インストール実行
brew install mysql-client
# インストール確認
/opt/homebrew/opt/mysql-client/bin/mysql --version
# PATH に追加したい場合は下記を実行
echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
# ただし、将来 MySQL サーバーをインストールすることを想定して今はしない。

ローカルから接続確認

/opt/homebrew/opt/mysql-client/bin/mysql \
  -u {username} \
  -p \
  -h {IP address} \
  --ssl-mode REQUIRED

無事に成功した。

データベース作成

root でログインして下記を実行する。

CREATE DATABASE {dbname} CHARSET utf8mb4;
CREATE USER {username}@'%' IDENTIFIED BY '{password}';
GRANT ALL PRIVILEGES ON {dbname}.* TO {username}@'%';

作成したら mysql コマンドで接続できるかどうかを試すと良いかも。

Prisma から接続

DATABASE_URL='mysql://{username}:{password}@{IP address}/{dbname}'

dotenv -e .env.local -- npx prisma db push
dotenv -e .env.local -- npx prisma db seed

どうも接続できない。

初心にかえる

https://www.prisma.io/docs/getting-started/quickstart

https://www.prisma.io/docs/getting-started/setup-prisma/start-from-scratch/relational-databases/connect-your-database-typescript-mysql

mkdir hello-prisma
cd hello-prisma
npm init -y
npm install typescript ts-node @types/node --save-dev
npx tsc --init
npm install prisma --save-dev
npx prisma init --datasource-provider mysql

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

.env を変更してから下記を実行してみたがダメだった。

npx prisma db push

データベース名などを短くしてみる

CREATE DATABASE testdb CHARSET utf8mb4;
CREATE USER testuser@'%' IDENTIFIED BY 'testpass';
GRANT ALL PRIVILEGES ON testdb.* TO testuser@'%';

/opt/homebrew/opt/mysql-client/bin/mysql -u testuser -p -h xxx.xxx.xxx.xxx

DATABASE_URL='mysql://testuser:testpass@xxx.xxx.xxx.xxx:3306/testdb'

ダメだった。

いったん SSL を OFF にしてみる

いけた！！！

こんなことに小一時間くらい費やしてしまった。

でも SSL は ON にしたいから引き続き調べよう。

同じようなことに悩んでいる方を発見

https://kb.tableau.com/articles/Issue/unable-to-connect-to-google-cloud-sql-postgres-with-ssl-enabled?lang=ja

何やら気になるボタンがある

証明書をダウンロードしてみる。

そして気になる記述もある。

sslcert=<PATH>: Path to the server certificate. This is the root certificate used by the database server to sign the client certificate. You need to provide this if the certificate doesn't exist in the trusted certificate store of your system. For Google Cloud this likely is server-ca.pem. Certificate paths are resolved relative to the ./prisma folder

https://www.prisma.io/docs/orm/overview/databases/mysql#configuring-an-ssl-connection

もしかして server-ca.pem をダウンロードして prisma ディレクトリに入れて sslcert を URL に追加すればいける？

いけた！！！

DATABASE_URL='mysql://testuser:testpass@35.243.92.25:3306/testdb?sslcert=server-ca.pem'

テストデータベースの削除

REVOKE ALL PRIVILEGES ON testdb.* FROM testuser@'%';
DROP USER testuser@'%';
DROP DATABASE testdb;

Next.js の pre-render

Next.js ではビルド時にもデータベースからソースコードを取得しようとしてくるらしい。

Next.js 14 であれば route.ts に下記を設定することで無効化できるようだ。

export const dynamic = 'auto';

server-ca.pem をバージョン管理する

あまり良いやり方ではないが prisma ディレクトリに入れてバージョン管理する必要がある。

Next.js だと動かない

しかも少しエラーが異なる。

Can't reach database server

server-ca.pem が見つからないのかも知れない

ローカルで server-ca.pem を一時的に削除したら同じような動作が確認できた。

https://github.com/prisma/prisma/issues/1673#issuecomment-647107891

こういうことをすれば解決できるかも知れない。

なんと動く方法を見つけた

諦めかけてたが適当にやったら動いた。

https://stackoverflow.com/questions/77083613/how-to-use-ssl-with-prisma-on-a-vercel-deployment

https://davidparks.dev/blog/planetscale-deployment-with-prisma/

Vercel で DATABASE_URL の環境変数を下記のように設定すれば良い。

mysql://testuser:testpass@xxx.xxx.xxx.xxx:3306/testdb?sslcert=/etc/pki/tls/certs/ca-bundle.crt

もしかすると証明書ファイルは何でも良くて、間違えていても設定されていれば SSL を使うようになっているのかも知れない。

試しに server-ca.pem の内容を削除して prisma db push しても大丈夫だった。

セキュリティ上の懸念

本来であれば正しい鍵を確認できなければ接続できない方が望ましい。

sslaccept=strict を使えば良いのかな？

また接続できる IP アドレスを制限した方が良い。

今回はまずは接続できたので良しとしよう。

まとめ

下記を設定すればとりあえずは接続できるようだ。

• CloudSQL の設定で 0.0.0.0/0 からアクセスできるようにする。
• DATABASE_URL の末尾に &sslcert=/etc/pki/tls/certs/ca-bundle.crt を追加する。

こんな簡単なことを調べるのに 3 時間も使ってしまったが誰かの役に立てば幸いです。

恨み言

Prisma の接続エラーメッセージはわかりにくすぎる。

• Error: P1000: Authentication failed against database server → sslcert を指定していない。
• Error: P1001: Can't reach database server → sslcert のファイルが存在しない。

サーバー証明書の中身

openssl x509 -text -in prisma/server-ca.pem

Issuer と Subject がそれぞれ下記のようになっている。

dnQualifier=00000000-0000-0000-0000-000000000000, CN=Google Cloud SQL Server CA, O=Google, Inc, C=US

server-ca.pem の名前の通り認証局の証明書のようだ。

Prisma のドキュメントをよく読む

sslcert=<PATH>: Path to the server certificate. This is the root certificate used by the database server to sign the client certificate. You need to provide this if the certificate doesn't exist in the trusted certificate store of your system. For Google Cloud this likely is server-ca.pem. Certificate paths are resolved relative to the ./prisma folder

これはクライアント証明書に証明するためにデータベースサーバーによって使用されるルート証明書です、とある。

せっかくなので最後までやろう

クライアント証明書を設定するところまでやろう。

クライアント証明書の作成

「クライアント証明書を作成」ボタンを押す。

名前は「Vercel」とした。

• client-key.pem
• client-cert.pem
• server-ca.pem

上記 3 点の内容が表示されるのでそれぞれコピー＆ペースとして prisma ディレクトリ内に保存する。

client-key.pem だけは間違ってもバージョン管理しないように気をつけよう。

PKCS12 証明書の作成

openssl pkcs12 -export \
  -out prisma/client-identity.p12 \
  -inkey prisma/client-key.pem \
  -in prisma/client-cert.pem \
  -passout pass:

passout パラメーターについては下記を参考にしたが Enter を押すだけでも良いかも知れない。

https://stackoverflow.com/questions/27497723/export-a-pkcs12-file-without-an-export-password

.gitignore に *.p12 を追加しておく。

クライアント証明書の中身を見る

openssl x509 -text -in prisma/client-cert.pem

Issuer がルート証明書の Issuer / Subject であり、Subject が CN=Vercel, O=Google, Inc, C=US となっている。

接続チャレンジ

DATABASE_URL='mysql://testuser:testpass@xxx.xxx.xxx.xxx/testdb?sslcert=server-ca.pem&sslidentity=client-identity.p12&sslaccept=strict'

dotenv -e .env.local -- npx prisma db push

下記のエラーメッセージが表示された。

Error: P1011: Error opening a TLS connection: MAC verification failed during PKCS12 import (wrong password?)

もう一度アイデンティティファイルを作り直してみよう。

legacy パラメーターが必要？

https://stackoverflow.com/questions/70431528/mac-verification-failed-during-pkcs12-import-wrong-password-azure-devops

openssl pkcs12 -export \
  -legacy \
  -out prisma/client-identity.p12 \
  -inkey prisma/client-key.pem \
  -in prisma/client-cert.pem \
  -passout pass:

ダメだった。

OpenSSL 1.1 を使ってみる

/opt/homebrew/opt/openssl@1.1/bin/openssl pkcs12 -export \
  -out prisma/client-identity.p12 \
  -inkey prisma/client-key.pem \
  -in prisma/client-cert.pem \
  -passout pass:

ダメだと思ったが一度ファイルを削除して再作成したら大丈夫だった。

もしかして上書きされない？

どうやらパスワードが必須らしい

pass とかでも良いので設定しておく必要がある。

OpenSSL 1.1 は必須ではない様子だったが -legacy パラメーターは必要なようだ。

エラーメッセージが変化した

Error: P1011: Error opening a TLS connection: The certificate was not trusted.

Prisma ドキュメントには気になる記述がある。

strict: Any missing value in the certificate will lead to an error. For Google Cloud, especially if the database doesn't have a domain name, the certificate might miss the domain/IP address, causing an error when connecting

GCP の場合、特にもしデータベースにドメイン名がない場合、証明書はドメインや IP アドレスの情報を含んでいないかもしれず、接続時にエラーが発生する。

mysql コマンドで試す

mysql -u root -h xxx.xxx.xxx.xxx --ssl-ca=prisma/server-ca.pem --ssl-cert=prisma/client-cert.pem --ssl-key=prisma/client-key.pem

下記のエラーメッセージが表示されたら成功している。

ERROR 1045 (28000): Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: NO)

client-cert.pem などの内容を削除して接続しようとすると失敗するのでしっかり動作しているようだ。

一方、Prisma の方は内容を削除しても普通に接続できてしまうのでちゃんと設定されているかどうか不安だ。

CloudSQL でクライアント証明書を必須にする

この状態で mysql コマンドで接続できるかどうかを試す。

mysql -u root -p -h xxx.xxx.xxx.xxx

正しいパスワードを入力しても接続できなくなっている。

/opt/homebrew/opt/mysql-client/bin/mysql -u root -p -h xxx.xxx.xxx.xxx --ssl-ca=prisma/server-ca.pem --ssl-cert=prisma/client-cert.pem --ssl-key=prisma/client-key.pem

クライアント証明書を指定すると接続できるようになった。

Prisma の方はどうだろう？

sslcert を指定するだけではアクセスできなくなった。

相変わらず server-ca.pem の中身はどうでも良いようだ。

この状態で CloudSQL で証明書を削除してアクセスしてみる。

そうすると下記のエラーが表示されるようになった。

Error: P1011: Error opening a TLS connection: unknown Cert Authority

Vercel でクライアント証明書を使う方法

環境変数で証明書と秘密鍵の PEM 本文を設定し、起動時に tmp ディレクトリに client-identity.p12 を出力する必要がありそうだ。

興味が続いたら

Vercel でクライアント証明書を使う方法について調べてみよう。