Description:
In MySQL 8.4, the initial handshake sent by the server always uses caching_sha2_password regardless of the default authentication plugin.
In MySQL 8.0, the default authentication plugin is configured through the "default_authentication_plugin" parameter. In MySQL 8.0, if default_authentication_plugin is configured to be `mysql_native_password` then the authentication method in the initial handshake sent by the server will use `mysql_native_password`.
In MySQL 8.4, the default authentication plugin is configured through the "authentication_policy" parameter. In MySQL 8.4, if authentication_policy is configured to be `*:mysql_native_password` then the authentication method in the initial handshake sent by the server will still use `caching_sha2_password`.
This conflicts with the documented behavior in https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_connection_phase.html.
```
Server uses its default authentication method defined by authentication_policy to produce initial authentication data payload and sends it to the client inside Protocol::Handshake, together with the name of the method used.
```
While this is not an issue for most clients as an Protocol::AuthSwitchRequest will be performed, we have observed some old clients that do not support caching_sha2_password nor Protocol::AuthSwitchRequest and so these clients are no longer able to connect to MySQL 8.4 even if the DB user they are connecting to is configured to authenticate with mysql_native_password.
This is the commit that hard-codes the auth method to caching_sha2_password in MySQL 8.4: https://github.com/mysql/mysql-server/commit/6f03eaa8ceb5bdfd9c2bccc8054a87f1b0a5dc6b#diff...
How to repeat:
Using a packet analyzer such as WireShark,
In MySQL 8.4 set authentication_policy to *:mysql_native_password.
Connect to the server and observe that caching_sha2_password is the auth method in the initial handshake packet.
In MySQL 8.0 set default_authentication_plugin to mysql_native_password.
Connect to the server and observe that mysql_native_password is the auth method in the initial handshake packet.
Suggested fix:
MySQL 8.4 should use the default authentication plugin as defined by the authentication_policy server system variable as the auth method in the initial handshake.
