Post-Installation Wizard can't set database password for MariaDB 10.4

I've just installed VirtualMin on a server where I wanted to run the latest MariaDB from the off. So I added the MariaDB 10.4 RPM repo before installing VirtualMin. All went fine with the installation, but Step 5 of the Post-Installation Wizard offers to remove the test user and database, and forces you to set a root password for MySQL. That's a good thing. Except that, when you click "Next", you get update user set authentication_string = password('foo') where user = 'root' and host = 'localhost' failed : Column 'authentication_string' is not updatable This is because of the note about MariaDB 10.4 at https://mariadb.com/kb/en/library/mysqluser-table/ meaning this is no longer the way to set passwords.

To work around, I tried setting a password at the commandline, but Step 5 doesn't detect this and says "Currently the MySQL password is not set, which is highly insecure."

So I tried not setting a password, even though it's "highly insecure", and I still can't progress because it then tries to set the empty password rather than skip the step.

I realise that MariaDB 10.4 is probably not officially supported yet, but 6 weeks in I thought it worth filing a bug report so that this gets picked up in due order.

Thanks

Status: 
Active

Comments

Howdy -- thanks for contacting us!

Yeah we're currently seeing a few issues with MariaDB 10.3+.

This most recently came up with the release of Debian 10, which offers MariaDB 10.3 by default, but generates similar errors.

Jamie is working on all that, and we hope to have a fix soon!

OK, I've found the problem.

This is specific to MariaDB 10.4, not simply 10.3+. 10.2 introduced the option to use "set password", but updating authentication_string was kept deprecated for backwards compatibility. However 10.4 removes the option to set authentication_string to the desired password, which is why this method will now fail.

Here's the code that's causing the problem:

        if (($variant eq "mysql" && &compare_versions($ver, "8") >= 0 ||
             $variant eq "mariadb" && &compare_versions($ver, "10.2") >= 0) &&
            $plainpass) {
                # Use the plaintext password wherever possible
                $sql = "set password for '$user'\@'$host' = '".
                       &mysql_escape($plainpass)."'";
                }
        elsif (&compare_versions($ver, "5.7.6") >= 0) {
                $sql = "update user set authentication_string = $encpass ".
                       "where user = '$user' and host = '$host'";
                $flush++;
                }

On the face of it, this is designed to pick up cases like this. I've inserted debug code and can confirm that $variant is coming out at "mariadb" and $ver is 10.4.6, and the $compare_versions function is doing its job correctly. However, as I run through the post installation wizard, $plainpass is empty. Which means the code that's designed to pick up MariaDB 10.2 or later doesn't, and we still end up calling "update user set authentication_string = "

That's why it's failing. As to the technically correct and properly secure way to fix it - I'm afraid I don't have a patch to suggest!

Sorry - I should have said: This is in feature-mysql.pl

@Christos - pretty sure that won't work. In my use-case, you end up in the elsif (&compare_versions($ver, "5.7.6") >= 0) scenario, because $plainpass is empty. But your code then runs some SQL that includes sha1_hex(sha1($plainpass)), which will therefore store an empty password.

PS: It would help people to try out suggestions like this if you could include them in .patch format.

@Christos

See my comment above, #6, in reply to the first time you posted this.

Sorry for double post its my fault ...

Well JamesOakley the patch is tested MANY TIMES and working in my Server you can backup and try it ... If they dont work just put the backup ...

If you think $plainpass is empty :) check it directly from mysql.global_priv after create a site with mysql !!!!!!!! ;)

Sorry no time for make in .patch .. You can see the changes here >> @ https://pastebin.com/FuZ8aqfQ

https://mariadb.com/kb/en/library/set-password/

https://mariadb.com/kb/en/library/authentication-from-mariadb-104/

https://www.reddit.com/r/sysadmin/comments/avx1u6/how_to_change_the_root...

Please take care todo it the right way!

Cookbook

After installing MariaDB system-wide the first thing you’ve got used to doing is logging in into the unprotected root account and protecting it, that is, setting the root password:

$ sudo dnf install MariaDB-server
$ mysql -uroot
...
MariaDB> set password = password("XH4VmT3_jt");

This is not only unnecessary now, it will simply not work — there is no unprotected root account. To login as root use

$ sudo dnf install MariaDB-server
$ sudo mysql

Note that it implies you are connecting via the unix socket, not tcp. If you happen to have protocol=tcp in a system-wide /etc/my.cnf file, use sudo mysql --protocol=socket.

After installing MariaDB locally you’ve also used to connect to the unprotected root account using mysql -uroot. This will not work either, simply use mysql without specifying a username.

If you've forgotten your root password, no problem — you can still connect using sudo and change the password. And if you've also removed unix_socket authentication, to restore access do as follows:

Si important to socket!

Indeed, @jfro

Although, as I said in my OP, I managed to set a root password without any problem using the CLI. But the VirtualMin Post Installation Wizard doesn't detect that's been done, so it still gets stuck thinking it needs to set the root password yet is unable to do so.

See my edit root password setting password in old way isn't needed at all with 10.4 !

cookbook mariadb 10.4x quote if doing socket.

also only via SSH the CLI out of GUI doesn't work, it seems to work but doesn't!

Also you can read here how to revert to old way! https://mariadb.com/kb/en/library/authentication-from-mariadb-104/#rever...

Reverting to the Previous Authentication Method for root@localhost.

could be workaround for now, while post-Installation Wizard is s.cking for this. ;)