MySQL Bugs: #114856: Odd errors after starting mysqld with ssl

Bug #114856	Odd errors after starting mysqld with ssl_mode=disabled in my.cnf
Submitted:	2 May 2024 16:32	Modified:	22 May 2024 7:06
Reporter:	Mark Callaghan	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Encryption	Severity:	S2 (Serious)
Version:	8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I stumbled onto this because I have using these options with MySQL 8.0 and they are no longer supported in 8.4
ssl=0
default_authentication_plugin=mysql_native_password

So I comment them out and then am able to initialize and start MySQL 8.4.0 but I don't do anything to setup SSL.

When I connect via UNIX socket (bin/mysql -u... -p...) I don't get SSL, which is what I want, and see this when I type '\s'
SSL: Not in use

When I connect via TCP (bin/mysql -u... -p... -h127.0.0.1) I do get SSL, which is what I don't want and see this when I type '\s'
SSL: Cipher in use is TLS_AES_128_GCM_SHA256

When I connect via TCP and add --ssl-mode disabled I don't get SSL, which is what I want.

So I added this to my.cnf:
[client]
ssl_mode=disabled

And restarting mysqld, my connections attempts that use TCP fail and I get error messages like this:
$ bin/mysql -uroot -ppw -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

$ bin/mysql -uroot -ppw -h127.0.0.1 --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

But if I then connect one time via a UNIX socket, after that I can connect via TCP without the errors above:
$ bin/mysql -uroot -ppw
...

And even better, my connections via TPC don't use SSL which is what I want:

$ bin/mysql -uroot -ppw -h127.0.0.1

mysql> \s
--------------
bin/mysql Ver 8.4.0 for Linux on x86_64 (Source distribution)

Current user: root@localhost
SSL: Not in use
Server version: 8.4.0 Source distribution
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
TCP port: 3306

But I fear there is a bug and/or odd behavior here and my guess is that this comes from whatever "caching" is implied by caching_sha2_password

How to repeat:
1) Add this to my.cnf
[client]
ssl_mode=disabled

2) Restart mysqld

3) Connect via TCP -> bin/mysql -u... -p... -h127.0.0.1
And get errors

4) Connect via UNIX socket -> bin/mysql -u... -p...
And this works

5) Connect via TCP -> bin/mysql -u... -p... -h127.0.0.1
And this works

Suggested fix:
Document or fix the current behavior.

I then add this to my.cnf:
---
[mysqld]
mysql_native_password=ON

[client]
ssl_mode=disabled
default_auth=mysql_native_password

But that does not change the behavior when I try to connect via TCP via either of:
bin/mysql -uroot -ppw -h127.0.0.1
bin/mysql -uroot -ppw -h127.0.0.1 --default-auth=mysql_native_password

Hi Mark! Out of curiosity, does adding the --get-server-public-key option consistently fix the non-SSL situation for you? https://dev.mysql.com/doc/refman/8.4/en/connection-options.html#option_general_get-server-...

I was having some similar issues yesterday using 8.4.0 client with --ssl-mode=disabled when native auth was disabled, and --get-server-public-key seemed to fix it. If I understand correctly, when using caching_sha2_password, you must have one of the following:

a) secure connection, or
b) client provides a specific intended server public key (--server-public-key-path), or
c) client explicitly requests the server public key (--get-server-public-key)

That said, it doesn't explain the caching oddity you're seeing. fwiw I am also seeing some other weird auth randomness in 8.4. I just reported https://bugs.mysql.com/bug.php?id=114876 which could also be a caching problem.

Evan - I don't know much about caching_sha2_password because with MySQL 8.0 I have been using the following after some changes with 8.0 broke my workflow for 5.6 and 5.7:
default_authentication_plugin=mysql_native_password

Hello Mark,

Thank you for the report and feedback.
I was able to reproduce this odd behavior only once in the below sequence and subsequent attempts(12-13 attempts) just works(even same instance, wiped and re-attempted). I'm verifying for now so that Dev's can clarify further on this.

** Connect via TCP -> bin/mysql -u... -p... -h127.0.0.1
And get errors

** Connect via UNIX socket -> bin/mysql -u... -p...
And this works

** Connect via TCP -> bin/mysql -u... -p... -h127.0.0.1
And this works

regards,
Umesh

Hi Mark! It looks like root account is created using caching_sha2_password and if so, the behaviour is expected.

Please refer to: https://dev.mysql.com/doc/refman/8.4/en/caching-sha2-pluggable-authentication.html

An account that uses caching_sha2_password requires secure connection on first attempt at connection after account creation (and subsequently after  password change, RENAME USER or FLUSH PRIVILEGES).

TCP without SSL is not a secure connection and that's why the first connection attempt fails.
Socket is considered secure and hence connection succeeds.

After first such connection, subsequent connection can operate on simple TCP connection because authentication flow now uses challenge-response based mechanism. That's the reason why subsequent connection using --protocol=TCP succeeds in your repro.

In nutshell, 
- Either use SSL or RSA keys as documented above if account is using caching_sha2_password
- OR create an account using mysql_native_password as authentication plugin. See: https://dev.mysql.com/doc/refman/8.4/en/create-user.html#create-user-authentication