I’ve answered my own question, posting here to help anyone else affected as it took a LOT of work to get to what turned out to be a simple solution.
This applies to you if you are unable to connect to MySQL v8 or above because of the error “RSA public key is not available client side (option serverRsaPublicKeyFile not set)”.
The solution:
- change existing users to be authenticated by the mysql_native_password plugin;
- leave the server default plugin as caching_sha2_password UNLESS you will be creating new users, in which case change it so they inherit use of that plugin without having to be changed one by one during user creation;
- wait for LibreOffice to be told how to play with authentication via caching_sha2_password.
This solution should be acceptable where;
• client and server are both on localhost, so there is no imperative to use the most secure available authentication;
• the frequency of connections is low, so there is no loss of performance from giving up the authentication cache.
To change existing users who get the error above;
Find out who is authenticating with caching_sha2_password
select User,Host,plugin from mysql.user where plugin = 'caching_sha2_password';
Ignore the system users whose names begin with mysql.
Change the affected users so their authentication is handled by the mysql_native_password that was the default before MySQL server version 8 e.g.
ALTER USER user@host IDENTIFIED WITH mysql_native_password BY 'password';
Check the list of users again to make sure everyone has been changed;
select User,Host,plugin from mysql.user;
The next steps may not be necessary, but better certainty than confusion.
Tell the server to forget everything it knows about users (among other things, this clears the cache of known valid logins used by the caching_sha2_password plugin;
flush privileges;
Now close and restart LibreOffice so it also forgets anything it might have in RAM.
There is no need to adjust any database connection properties in LO Base. If you have the standard MySQL(JDBC) driver installed, everything should just work using the connection instructions you can easily find all over the web.
Finally (holding your breath) retry the connection from LO to MySQL.
If that fails, you’re in a world of pain from which I cannot rescue you.
Why was this problem ever a problem?
• connection from LO to MySQL was unencrypted (it was all on localhost, after all);
• the new caching_sha2_password authentication plugin expects an encrypted connection;
• on an unencrypted connection, the plugin expects the client to encrypt the password using a public key it can access, so that the server can decrypt using its matching private key;
• using the standard MySQL(JDBC) driver (the MariaDB driver), there is no way to tell LibreOffice where to find the public key, even when it has been fetched from the server (by a secure connection of course) & stored in an accessible file;
• likewise, there is no way to tell LibreOffice to demand the key suring logon even over an unencrypted connection, which is a last ditch option offered by the server;
• using the alternative JDBC driver (the MySQL driver) it is possible to edit a connection string that can include a parameter specifying the public key path or demanding the public key over unencrypted connection but the connection still fails for an unknown reason.
Hope this helps someone, cheers.