MySQL Bugs: #91482: db mysql > table user > column plugin default caching_sha2

Bug #91482	db mysql > table user > column plugin default caching_sha2_password missing/wron
Submitted:	29 Jun 2018 1:46	Modified:	29 Jun 2018 4:15
Reporter:	Sarah Schieffer Riehl	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	8.0.11	OS:	MacOS (High Sierra 10.13.5)
Assigned to:		CPU Architecture:	Any

Description:
When trying to connect to mysql root user on localhost through Sequel Pro or a local WordPress installation, I get the error Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found.

I can connect to the mysql shell through the terminal without issues.

(I ran mysql_secure_installation and set a root password. The first time, I declined to setup the VALIDATE PASSWORD PLUGIN, and got this error. The second time, I uninstalled mysql and wiped all my data. I ran mysql_secure_installation and did setup the VALIDATE PASSWORD plugin with password validation policy level 1, MEDIUM Length >= 8, numeric, mixed case, and special characters.)

Upon further inspection, there's a contradiction between the plugins provided and the default plugin setting for new entries in the DB `mysql` > TABLE `user` > COLUMN `plugin`.

The default value for plugin remains "caching_sha2_password", but this .so no longer exists in lib/plugin. I noticed this by querying the shell, and then verified that the raw SQL source file in the installation does contain this entry at share/mysql_system_tables.sql, line 109.

I observed the absence of the plugin and the default at line 109 in both the mysql-8.0.11-macos10.13-x86_64.tar.gz dl from https://dev.mysql.com/downloads/mysql/ and the homebrew version

How to repeat:
Stop mysql, uninstall, and remove /usr/local/var/mysql. Install a fresh 8.0.11 via homebrew or dev.mysql.com. Start the server and run mysql_secure_installation. You can either configure VALIDATE PASSWORD PLUGIN or not. Then try to connect to mysql through Sequel Pro, a local WordPress installation, etc.

Suggested fix:
Change the default value at mysql > user > plugin to mysql_native_password or whatever the new intended standard is, and make sure the .so file is in lib/plugin. Or, include caching_sha2_password.so in lib/plugin .

When I actually did the workaround, it turns out it doesn't work to change the plugin AFTER the user exists unless you can do some voodoo to change the password at the same time (I guess because with a different auth plugin the hash is wrong).

So the workaround that worked was from the clean install, BEFORE starting mysql or running mysql_secure_installation, go to /usr/local/etc/my.cnf, & under [mysqld], add default_authentication_plugin=mysql_native_password.

Interestingly, this doesn't change the plugin column default in the actual user table.  I guess it sets the type in the added record on its way into the table.

Looks like you actually need to make that change to /usr/local/etc/my.cnf BEFORE installing.  My file looks like this

# Default Homebrew MySQL server config
[mysqld]
# overwrite plugin error
default_authentication_plugin=mysql_native_password
# Only allow connections from localhost
bind-address = 127.0.0.1

Hello Sarah,

Thank you for the report.
Imho this is not a bug but expected behavior. 
Quoting from our manual - "If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading to MySQL 8.0 or higher, the simplest way to address those issues and restore pre-8.0 compatibility is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_password
That setting enables pre-8.0 clients to connect to 8.0 servers until such time as the clients and connectors in use at your installation are upgraded to know about caching_sha2_password. However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security provided by caching_sha2_password." more details here - https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-cachin...

Thanks,
Umesh

With original MySQL 8 dmg or wuth brew package best way for use mysql_native_password is edit launchDaemon file adding `<string>--default_authentication_plugin=mysql_native_password</string>`

 /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
or file installed

<key>ProgramArguments</key>
	<array>
		<string>/usr/local/mysql/bin/mysqld</string>
		<string>--user=_mysql</string>
		<string>--basedir=/usr/local/mysql</string>
		<string>--datadir=/usr/local/mysql/data</string>
		<string>--plugin-dir=/usr/local/mysql/lib/plugin</string>
		<string>--log-error=/usr/local/mysql/data/mysqld.local.err</string>
		<string>--pid-file=/usr/local/mysql/data/mysqld.local.pid</string>
		<string>--keyring-file-data=/usr/local/mysql/keyring/keyring</string>
		<string>--early-plugin-load=keyring_file=keyring_file.so</string>
		<string>--default_authentication_plugin=mysql_native_password</string>