mita2 database life

主にMySQLに関するメモです

MySQL ruby ドライバの caching_sha2_password 平文接続の挙動がバラバラだった件

MySQL

caching_sha2_password は 少なくとも１回はセキュアな経路を必要とする

caching_sha2_password 形式のパスワードを使った、平文の接続 をする場合、その過程で、少なくとも一度は事前に、暗号化されたセキュアな経路を通して認証成功させる必要があります。 サーバがキャッシュ（パスワードのハッシュ）を生成するのに、平文のパスワードを要求するためです。

この「少なくとも一度は〜」の背景は、MySQL 運用・管理 実践入門で詳しく解説されていました。気になる人は読むと良いでしょう

MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ

MySQL運用・管理［実践］入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ

Amazon

「少なくとも一度は〜」と書きましたが、厳密に言うと「１度」だけ済まない可能性があります。 キャッシュはメモリ上にしか保持されないため、サーバの再起動で消えます。また、FLUSH PRIVILEGE コマンドでもクリアされます。

キャッシュが未生成だとエラーになるドライバがある

キャッシュ未生成時、サーバがセキュアな経路を要求してきたときの挙動はドライバ依存です。 go-sql-driver/mysql や php_mysqlnd では、セキュアの経路を自動的にフォールバックし、何事もなく接続できます。コードの変更も不要です。

rubyのドライバの対応状況を確認してみると、挙動の違いがあって、面白かったです。

ドライバ名 caching_sha2 平文接続のサポート caching_sha2 キャッシュ未作成時
ruby-mysql ⚪︎ ⚪︎ エラーなし (get_server_public_key=trueが必要)
mysql2 ⚪︎ × エラー
trilogy × サポートされていない -

なお、この仕様を意識する必要があるのは平文接続の場合のみです。常にセキュアな通信をする SSL/TLS 接続やUnix Domain Socket では意識する必要ありません。

ruby-mysql

オプションに get_server_public_key=true を付けておけば、キャッシュ未作成時もエラーなく接続可能でした。mysql コマンドにあるオプションと同じですね。 サーバの公開鍵を利用して、自動的に「初回のセキュアな経路」を作成します。

my = Mysql.connect(URI::Parser.new.escape("mysql://#{username}:#{password}@#{hostname}:#{port}/?ssl_mode=disabled&get_server_public_key=true"))

mysql2

サーバサイドにキャッシュが未作成だとエラーになります。回避するためのオプション等は提供されていなそうでした。 ワークアラウンドとして、このエラーをトラップして、何らか別手段 (rubyで書く必要もない) で一度認証を成功させてキャッシュを作ることになりそうです。

Mysql2::Error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

一旦、キャッシュが作成されしまえば、以降は問題なく接続可能です。

trilogy

trilogy は caching_sha2_password 形式の認証は SSL か Unix Domain Socket のみサポートです。平文は利用できません。 サーバにキャッシュが作成済みであろうと、なかろうと、caching_sha2_password と 平文接続を組み合わせた時点で、エラーになります。

Trilogy::BaseConnectionError: trilogy_auth_recv: caching_sha2_password requires either TCP with TLS or a unix socket: TRILOGY_UNSUPPORTED

PR にも明確に書かれていました。

chosen on purpose to only implement the path where TLS or a unix socket is used. We will not be implementing the non-TLS/non-unix socket path. https://github.com/trilogy-libraries/trilogy/pull/165

trilogyで平文接続を利用している場合は、caching_sha2_password 導入とあわせて、SSL/TLS接続への変更が必要になります。

ruby-mysql や mysql2 では、キャッシュが生成済みであれば、エラーは出ません。 これらのドライバでは「初回のみセキュアな経路を必要とする」仕様を見逃しやすく、サーバ再起動などでキャッシュがなくなったときに、ある日突然、エラーに直面するリスクがあるように感じます。

trilogy はサーバサイドのキャッシュの有無に依存せず、常にエラーになるため、事前に問題に気づきやすく、一番、安全側に倒した選択とも言えそうです。

試したバージョン

$ gem list | egrep 'mysql|trilogy'
mysql2 (0.5.6)
ruby-mysql (4.1.0)
trilogy (2.8.1)

$ dpkg -l | grep mysql
ii  libmysqlclient-dev     8.0.39-0ubuntu0.24.04.1  amd64 MySQL database development files
ii  libmysqlclient21:amd64 8.0.39-0ubuntu0.24.04.1  amd64 MySQL database client library
ii  mysql-client-core-8.0  8.0.39-0ubuntu0.24.04.1  amd64 MySQL database core client binaries
ii  mysql-common           5.8+1.1.0build1          all   MySQL database common files, e.g. /etc/mysql/my.cnf

検証用のコード

require 'uri'

require 'mysql'
require 'mysql2'
require 'trilogy'

port     = 3307
hostname = '192.168.103.123'
username = 'app'
password = 'Password'
q        = 'show status like "Ssl_cipher"'

# mysql2
begin
    my2 = Mysql2::Client.new(:host => hostname, :port => port, :username => username, :password => password, :ssl_mode => :disabled)

    my2.query(q).each do |row|
      p row
    end
    # clear sha256 cache if successfuly loggin
    my2.query("FLUSH PRIVILEGES")
rescue => e
  p e
end

# ruby-mysql
begin
    my = Mysql.connect(URI::Parser.new.escape("mysql://#{username}:#{password}@#{hostname}:#{port}/?ssl_mode=disabled&get_server_public_key=true"))
    my.query(q).each do |row|
      p row
    end
    # clear sha256 cache if successfuly loggin
    my.query("FLUSH PRIVILEGES")
rescue => e
  p e
end


# trilogy
begin
    tri = Trilogy.new(host: hostname, port: port, username: username, password: password)
    tri.query(q).each_hash do |row|
     p row
    end
    # clear sha256 cache if successfuly loggin
    tri.query("FLUSH PRIVILEGES")
rescue => e
  p e
end
$ ruby test.rb
#<Mysql2::Error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.>
["Ssl_cipher", ""]
#<Trilogy::BaseConnectionError: trilogy_auth_recv: caching_sha2_password requires either TCP with TLS or a unix socket: TRILOGY_UNSUPPORTED>