Support caching_sha2_password authentication method

[fulghum]

MySQL 8.4 (LTS) has deprecated, and MySQL 9.0 (innovation) has removed, support for the mysql_native_password authentication method. This is currently the only authentication mode supported by Dolt.

Customers on MySQL 8.4 can start mysqld with the --mysql-native-password=ON option to opt-in to re-enabling native password authentication, but it will not be available in future major releases of MySQL beyond 9.0.

The first affected use case is replication – customers have to opt-in to native password support on the current LTS of MySQL for Dolt to be able to connect to it as a replication source (or vice versa, for MySQL to connect to Dolt as the replication source).

It seems likely that the mysql client, as well as MySQL connector libraries, will continue supporting the deprecated native password auth process for a while, but eventually they will follow and may be unable to connect to Dolt sql servers depending on how they are updated.

[fanyang01]

+1

We're building MyDuck Server using the excellent go-mysql-server library. I recently discovered that if users install the MySQL client via brew install mysql-client, they will receive a version 9.* client, which lacks support for mysql_native_password. Additionally, setting --default-auth=mysql_native_password does not resolve the issue.

[timsehn]

This bit a new user who was using the latest MySQL from Homebrew. I think we need to do it very soon.

[fulghum]

We made some initial progress on this one this week. The first step was applying a refactoring to our vitess fork so that we could cleanly support multiple authentication methods. Those changes have been merged in, so now we can start testing the caching_sha2_password implementation from vitess. We'll update again as we get that piece working.

[earayu]

I noticed this issue while working on a similar challenge. We also forked Vitess for our project WeScale and had to implement the caching_sha2_password authentication method.

While Vitess provides an excellent framework for MySQL wire protocol authentication, we found that its caching_sha2_password implementation doesn't handle the complete FullAuth flow that MySQL requires. We ended up implementing the full authentication sequence in our fork.

If you're interested, you might want to take a look at how we approached this in WeScale.

• UserEntryWithFullAuth
• ScrambleSha2Password

Just thought I'd share our experience in case it helps with your implementation.

[fulghum]

Thank you @earayu! This is very helpful. I noticed some issues with auth negotiation in the Vitess implementation, but I hadn't debugged through them yet. Thanks for sharing your implementation – this looks like it'll be a big help! I'll be digging into this today.

[fulghum]

We're making steady progress on support for caching_sha2_password authentication. The latest PRs associated with this ticket have been approved and allow creating users using the caching_sha2_password auth plugin, and generating an authentication string that is compatible with MySQL. Many thanks to @earayu for pointing out the wesql/wescale implementation of the caching_sha2_password hashing code! That was a big help. 🙏

The next step is enabling the caching_sha2_password auth plugin, alongside the mysql_native_password auth plugin. I've got changes in my workspace and expect to open that PR on Monday. Once all those changes are released, customers will be able to create users for use with caching_sha2_password and then log in with those accounts. The default auth plugin will still be mysql_native_password for a bit longer though.

The initial support will be opt-in, meaning: 1) you'll need to explicitly reference the caching_sha2_password auth plugin when you create users, and 2) you'll need to run with a cert so that there is a secure, TLS connection (required by caching_sha2_password).

Stay tuned for more updates.

[fulghum]

We're excited to announce that starting in version 1.44.2 Dolt now supports caching_sha2_password authentication.

Support is initially opt-in, meaning customers can explicitly mention the caching_sha2_password auth plugin when creating user accounts to configure them for caching_sha2_password authentication. Our announcement blog post provides some examples of this. It's also worth noting that caching_sha2_password requires a secure connection, so you'll need to run Dolt with a TLS configuration to use it.

I'm going to resolve this issue, since Dolt now supports caching_sha2_password authentication, and we can track a few follow ups separately:

• Support and test coverage for MySQL-to-Dolt and Dolt-to-MySQL replication using caching_sha2_password authentication
• Promote caching_sha2_password to be the default auth method when creating users

Please feel free to open a new issue with us or swing by our Discord if you have any problems or questions about using caching_sha2_password authentication and we'll be happy to help.