Bug #118447 MySQL 8.4 always uses caching_sha2_password in the initial connection handshake

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.

Connection made to MySQL 8.4 DB shows the auth method is using caching_sha2_password in the initial handshake

Attachment: mysql.pcap (application/octet-stream, text), 2.15 KiB.

Hello Tony Chen,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Added the following note to the MySQL Server 8.4.8 and 9.6.0 release notes:
		
Fixed an issue relating to authentication.