【備忘録】SQLAlchemy でMySQLにSSL接続できなくて苦労した話

1. プロローグ

Dockerコンテナ上で稼働するPythonアプリから、異なるDockerコンテナで稼働するMySQLへのSSL接続が2日かけてやっと成功したので、苦労を忘れぬうちに作業記録を残す。

2. 環境

アプリケーションコンテナ
・python3.12
・flask3.0.3
・flask-sqlalchemy3.1.1
・ymysql1.1.1
・cryptography42.0.8
ＤＢコンテナ
・mysql9.0.0

3. サーバ証明書/クライアント証明書

○ opensslを使用して作成
● MySQLが自動作成
上記の両方を試したが、最終的にMySQLが生成した証明書を利用することにした。

4. MySQLの起動

my.cnf に証明書をの所在を追加することでSSL接続が可能となる。

my.cnf

[mysqld]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

MySQLを立ち上げた際、/var/lib/mysql/配下に サーバ証明書、クライアント証明書等を作成し、置いてくれる。

5. MySQLにSSL接続用のユーザ追加

rootユーザは管理用とし、クライアントからSSL接続するユーザを追加する。

mysql -u ${DB_USER} -p${DB_PASSWORD} <<-END
    CREATE DATABASE ${DATABASE};
    CREATE USER '${CLIENT_USER}' IDENTIFIED BY '${CLIENT_PW}' REQUIRE SSL;
    GRANT ALL ON ${DATABASE}.* TO '${CLIENT_USER}';
END

6. SQLAlchemy MySQL接続定義

conf.py

class DevelopmentConfig:

    # SQLAlchemy
    SQLALCHEMY_DATABASE_URI = \
      'mysql+pymysql://{user}:{password}@{host}/{database}' \
      '?charset=utf8&ssl_ca={ca}&ssl_cert={cert}&ssl_key={key}&ssl_check_hostname=false'.format(
        **{
          'user': os.getenv('CLIENT_USER'),
          'password': os.getenv('CLIENT_PW'),
          'database': os.getenv('DATABASE'),
          'host': os.getenv('HOST'),
          'ca': os.getenv('CA_CERT'),
          'cert': os.getenv('CLIENT_CERT'),
          'key': os.getenv('CLIENT_KEY'),
        })
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ECHO = False
    PROPAGATE_EXCEPTIONS = True


Config = DevelopmentConfig

※ 環境変数 CA_CERT, CLIENT_CERT, CLIENT_KEY には、MySQLが自動生成したもののパスを指定している。

7. 何が苦労したか・・・

実装当初、SQLAlchemy接続定義に ssl_check_hostname=false を指定していなかった。そのため、HOST名指定だと、

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'dbms' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'dbms'. (_ssl.c:1000))")

IPアドレス指定にしても

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' ([SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: IP address mismatch, certificate is not valid for 'xxx.xxx.xxx.xxx'. (_ssl.c:1000))")

のエラーになる。
SQLAlchemyマニュアルを確り確認していなかったのが大きな過ちで、マニュアル（https://docs.sqlalchemy.org/en/20/dialects/mysql.html#pymysql-ssl ）には「ホスト名と一致しない自動生成された自己証明書を使用する場合はssl_check_hostname=falseを指定しろ」と確りと書いてあった。

8. エピローグ

ネット上にはいろんなサンプルが転がっており、そのままコピペである程度は動いてしまう。【教訓】うまくいかないときはサンプルコードを漁るのではなく今一度マニュアルに目を通そう。