Virtualmin inserts into mysql.user, causing issues with password hashing method

Rather than using an SQL CREATE USER query, Virtualmin uses insert into user (with the mysql database active) when creating a user for a Virtualmin-managed database for a virtual server. This bypasses the default_authentication_plugin setting in the MySQL 8 config, and therefore using the caching_sha2_password password hashing method instead of the server admin's mysql_native_password override. (Running CREATE USER manually uses the admin-specified password hashing method.)

The error that older clients (e.g. PHP prior to v7.4) get when attempting to connect to a Virtualmin-created database is "The server requested authentication method unknown to the client".

I'm running Virtualmin version 6.08.gpl on Ubuntu Linux 18.04.3 .

Status: 
Closed (fixed)

Comments

Ilia's picture
Submitted by Ilia on Wed, 06/03/2020 - 10:19

Hi,

Thanks for the heads up.

I'm running Virtualmin version 6.08.gpl

First of all, you must upgrade to Virtualmin 6.09-3.

Note: If you don't see an update, check your /etc/apt/sources.list and that it actually have correct links set for Virtualmin, which are software.virtualmin.com/vm/6/apt .

Additionally, you could apply the patch that fixes some other MySQL related issues by running the following commands:

 
curl https://raw.githubusercontent.com/virtualmin/virtualmin-gpl/master/feature-mysql.pl -o /usr/share/webmin/virtual-server/feature-mysql.pl
/etc/webmin/restart

Hello. Thanks for the speedy response. I am now running Virtualmin version 6.09.gpl on Ubuntu Linux 18.04.3 .

I've reviewed the patch that you sent and it doesn't contain a fix for this issue. Specifically, there were some unrelated fixes to do with MariaDB and for "remote mysql modules".

Specifically, unlike for MariaDB which uses the correct create user statement, all MySQL-related code (regardless of version) inserts into mysql.user. All that differs between MySQL versions is which columns are inserted. CREATE USER is available in MySQL v5.0.2 and later, so I wonder if there is a reason why it is not used given that it is standard?

I installed virtualmin and then uploaded packed-ftp files of my prestashop-based website and uploaded mysql database to the database. When I go to website, prestashop shows the following massage:

Link to database cannot be established:SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client at line 102 in file classes/db/DbPDO.php

I see the mentioned solution in the above comments. But I have no idea how to use this fix on my installed virtualmin!!!! I replaced the code written inside feature-mysql.pl with the one mentioned in https://github.com/unixnut/virtualmin-gpl/blob/d3574834bfb5f4b2120864a66.... But the problem is still the same with the same message.

If I changed php to new version like php7.4, I can access the website with many errors of incompatibilities and some images are not shown. This is because prestashop 1.6 is compatible with php 7.1 or less. If I changed to php 7.0 or php5.6, I will get "The server requested authentication method unknown to the client".

How to fix this?

Kind regards

The solution that works for me.

  1. Add code below to /etc/mysql/mysql.cnf

    [mysql]

    [mysqld]

    default_authentication_plugin=mysql_native_password

  2. Restart mysql

Status: Fixed ยป Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.