`default_authentication_plugin` is not compatable with MySQL's

[CbcWestwolf]

Nowadays, default_authentication_plugin in tidb only affects the connection between servers and clients. It cannot control the default auth plugin when creating users without explicit auth plugin as in MySQL. Since caching_sha2_password is safer than the default mysql_native_password, it is better for tidb to support this compatibility with MySQL.

It determines which authentication plugin the server assigns to new accounts created by CREATE USER statements that do not explicitly specify an authentication plugin.

However, since currently using the caching_sha2_password in TiDB requires a SSL connection, it is not suitable for TiDB to make it default authentication plugin after bootstrapping a new TiDB cluster. It is better to make it default after implementing complete caching_sha2_password on TiDB.

[dveeden]

Nowadays, default_authentication_plugin in tidb only affects the connection between servers and clients. It cannot control the default auth plugin when creating users without explicit auth plugin as in MySQL. Since caching_sha2_password is safer than the default mysql_native_password, it is better for tidb to support this compatibility with MySQL.

It determines which authentication plugin the server assigns to new accounts created by CREATE USER statements that do not explicitly specify an authentication plugin.

In MySQL the default_authentication_plugin has been deprecated since MySQL 8.0.27 and has been removed in MySQL 8.4.0. MySQL recommends to use authentication_policy instead. As TiDB aims to be compatible with MySQL 8.0 and we don't yet support authentication_policy this is fine for now.

• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_authentication_policy
• https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html

However, since currently using the caching_sha2_password in TiDB requires a SSL connection, it is not suitable for TiDB to make it default authentication plugin after bootstrapping a new TiDB cluster. It is better to make it default after implementing complete caching_sha2_password on TiDB.

Minor nit: Both TiDB and MySQL never use SSL. Both use TLS. But this is a common mixup... Best to either use "TLS" or at least "SSL/TLS".

caching_sha2_password requires a secure connection on both MySQL and TiDB. This is not TiDB specific. A secure connection an be TLS or a UNIX socket. Using a socket isn't practical for TiDB.

I don't see why caching_sha2_password isn't suitable after bootstrapping a new cluster. Currently people might use the root account without a password over an insecure connection when setting up a cluster. A slightly better way is to use -initialize-secure which uses a password. However if you connect with password over a non-TLS connection then only the authentication part is secure, CREATE USER... IDENTIFIED BY... statements can be seen in the network traffic.

I think the most secure way is to make caching_sha2_password the default and enable TLS by default with auto-tls.

Note that in TiDB caching_sha2_password never does the "fast authentication", it always does the "complete authentication".

• https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html