[MDEV-25169] Secure installation with normal user fails to accept empty root password - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Minor
Resolution: Not a Bug
Affects Version/s: 10.5.9
Fix Version/s: 10.4.18
Component/s: Configuration, Server
Labels:
- installation
Environment:
macOS, Linux

Description

When installing MariaDB as a normal user, mysql_secure_installation fails when attempting to authenticate the root user (with an empty password).

This can be worked around by updating the make_config function to avoid writing the user and password lines if the provided root password is empty, as in the following alternate implementation (first if-statement added):

make_config() {

    echo "# mysql_secure_installation config file" >$config

    echo "[mysql]" >>$config

    if [ ! -z "$rootpass" ]; then

        echo "user=root" >>$config

        esc_pass=`basic_single_escape "$rootpass"`

        echo "password='$esc_pass'" >>$config

fi

    #sed 's,^,> ,' < $config  # Debugging

    if test -n "$defaults_file"

    then

        dfile=`parse_arg "$defaults_file"`

        cat "$dfile" >>$config

fi

I'm not sure if this is the proper fix, but hopefully it at least highlights where the conflict is happening.

Attachments

Issue Links

relates to

MDEV-22486 mysql_secure_installation cannot work without root user in the database

Open

Activity

Ascending order - Click to sort in descending order

Anel Husakovic added a comment - 2021-04-05 13:57

serg can you please review .
Analysis:

Unix-socket auth:

$./mysql

MariaDB [test]> select user,host,password from mysql.user;

+-------------+-----------+----------+

| User        | Host      | Password |

+-------------+-----------+----------+

| mariadb.sys | localhost |          |

| root        | localhost | invalid  |

| anel        | localhost | invalid  |

|             | localhost |          |

|             | anel      |          |

+-------------+-----------+----------+

5 rows in set (0.005 sec)

MariaDB [test]> show grants for current_user;

+-----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for anel@localhost                                                                                                               |

+-----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO `anel`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |

+-----------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.000 sec)

unix-socket patch (for report only) 4e4df7b46deac857b51b29ccbab153edcd67e694

Root user

$ ./client/mysql -uroot

ERROR 1698 (28000): Access denied for user 'root'@'localhost

$ sudo ./client/mysql -uroot

[sudo] password for anel: #password for user anel

MariaDB [(none)]> select current_user();

+----------------+

| current_user() |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.000 sec)

MariaDB [test]> show grants for root@localhost;

+-----------------------------------------------------------------------------------------------------------------------------------------+

| Grants for root@localhost                                                                                                               |

+-----------------------------------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |

| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |

+-----------------------------------------------------------------------------------------------------------------------------------------+

2 rows in set (0.000 sec)

Blog to read Authentication in mariadb 10.4

Run mariadb-secure-installation

$ ./mariadb-secure-installation --defaults-file=~/.my-10.4.cnf

Enter current password for root (enter for none):

cat: '~/.my-10.4.cnf': No such file or directory

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Note: problem in parsing CLI option -> new bug?

Run mariadb-secure-installation with sudo

$ sudo ./mariadb-secure-installation --defaults-file=~/.my-10.4.cnf

[sudo] password for anel:

Enter current password for root (enter for none):

cat: '~/.my-10.4.cnf': No such file or directory

OK, successfully used password, moving on...

Change root password from mysql to some password - works and see the result:

MariaDB [test]> select user,host,password from mysql.user;

+-------------+-----------+-------------------------------------------+

| User        | Host      | Password                                  |

+-------------+-----------+-------------------------------------------+

| mariadb.sys | localhost |                                           |

| root        | localhost | *279018759837D91FAB7BB5939852F3E940F3334B |

| anel        | localhost | invalid                                   |

+-------------+-----------+-------------------------------------------+

3 rows in set (0.002 sec)

$ ./client/mysql -uroot -pmelisa # works

Now run mariadb-secure-installation and try to change to empty password:

Change the root password? [Y/n] Y

New password:

Re-enter new password:

Sorry, you can't use an empty password here.

patch should fix this, since

mysql can change root password to empty:

## Set root password to empty from mysql

$ ./client/mysql -uroot -pmelisa

set password = password("");

MariaDB [test]> select user,host,password from mysql.user;

+-------------+-----------+----------+

| User        | Host      | Password |

+-------------+-----------+----------+

| mariadb.sys | localhost |          |

| root        | localhost |          |

| anel        | localhost | invalid  |

+-------------+-----------+----------+

3 rows in set (0.002 sec)

And it is possible to use and set empty password from mariadb-secure-installation.

Anel Husakovic added a comment - 2021-04-05 13:57 serg can you please review . Analysis: Unix-socket auth: $. /mysql MariaDB [test]> select user ,host, password from mysql. user ; + -------------+-----------+----------+ | User | Host | Password | + -------------+-----------+----------+ | mariadb.sys | localhost | | | root | localhost | invalid | | anel | localhost | invalid | | | localhost | | | | anel | | + -------------+-----------+----------+ 5 rows in set (0.005 sec) MariaDB [test]> show grants for current_user ; + -----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for anel@localhost | + -----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `anel`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION | + -----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) unix-socket patch (for report only) 4e4df7b46deac857b51b29ccbab153edcd67e694 Root user $ . /client/mysql -uroot ERROR 1698 (28000): Access denied for user 'root' @'localhost $ sudo . /client/mysql -uroot [ sudo ] password for anel: #password for user anel MariaDB [(none)]> select current_user (); + ----------------+ | current_user () | + ----------------+ | root@localhost | + ----------------+ 1 row in set (0.000 sec) MariaDB [test]> show grants for root@localhost; + -----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | + -----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON '' @ '%' TO 'root' @ 'localhost' WITH GRANT OPTION | + -----------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) Blog to read Authentication in mariadb 10.4 Run mariadb-secure-installation $ . /mariadb-secure-installation --defaults- file =~/.my-10.4.cnf Enter current password for root (enter for none): cat : '~/.my-10.4.cnf' : No such file or directory ERROR 1698 (28000): Access denied for user 'root' @ 'localhost' Note: problem in parsing CLI option -> new bug? Run mariadb-secure-installation with sudo $ sudo . /mariadb-secure-installation --defaults- file =~/.my-10.4.cnf [ sudo ] password for anel: Enter current password for root (enter for none): cat : '~/.my-10.4.cnf' : No such file or directory OK, successfully used password, moving on... Change root password from mysql to some password - works and see the result: MariaDB [test]> select user ,host, password from mysql. user ; + -------------+-----------+-------------------------------------------+ | User | Host | Password | + -------------+-----------+-------------------------------------------+ | mariadb.sys | localhost | | | root | localhost | *279018759837D91FAB7BB5939852F3E940F3334B | | anel | localhost | invalid | + -------------+-----------+-------------------------------------------+ 3 rows in set (0.002 sec) $ . /client/mysql -uroot -pmelisa # works Now run mariadb-secure-installation and try to change to empty password: Change the root password? [Y /n ] Y New password: Re-enter new password: Sorry, you can't use an empty password here. patch should fix this, since mysql can change root password to empty: ## Set root password to empty from mysql $ . /client/mysql -uroot -pmelisa set password = password ( "" ); MariaDB [test]> select user ,host, password from mysql. user ; + -------------+-----------+----------+ | User | Host | Password | + -------------+-----------+----------+ | mariadb.sys | localhost | | | root | localhost | | | anel | localhost | invalid | + -------------+-----------+----------+ 3 rows in set (0.002 sec) And it is possible to use and set empty password from mariadb-secure-installation .

Sergei Golubchik added a comment - 2021-04-05 14:41

Eh... The bug report was that

mysql_secure_installation fails when attempting to authenticate the root user (with an empty password).

anel, you "fix" the issue where mysql_secure_installation fails to set an empty root password. This is a very different thing. And that behavior is quite intentional, as you could see, mysql_secure_installation indeed is supposed to reject attempts to set an empty root password, that's what "secure" in the name of the script means.

Sergei Golubchik added a comment - 2021-04-05 14:41 Eh... The bug report was that mysql_secure_installation fails when attempting to authenticate the root user (with an empty password). anel , you "fix" the issue where mysql_secure_installation fails to set an empty root password. This is a very different thing. And that behavior is quite intentional, as you could see, mysql_secure_installation indeed is supposed to reject attempts to set an empty root password, that's what "secure" in the name of the script means.

Anel Husakovic added a comment - 2021-04-05 15:35 - edited

Well, I couldn't reproduce the bug.

$ ./mariadb-secure-installation --defaults-file=~/.my-10.4.cnf -uroot

works with empty password for root and I guess the reason is here.
Documentation of mysql_secure_installation doesn't say about empty root password, so I understood it should be allowed, but yes I understand that it has nothing to do with authentication in scope of this MDEV.
groot-verde can you please let me know steps you did?

Anel Husakovic added a comment - 2021-04-05 15:35 - edited Well, I couldn't reproduce the bug. $ . /mariadb-secure-installation --defaults- file =~/.my-10.4.cnf -uroot works with empty password for root and I guess the reason is here . Documentation of mysql_secure_installation doesn't say about empty root password, so I understood it should be allowed, but yes I understand that it has nothing to do with authentication in scope of this MDEV. groot-verde can you please let me know steps you did?

Andrew Groot added a comment - 2021-04-05 17:55 - edited

I ran the following commands to install/run MariaDB into a directory (./data/) alongside the MariaDB files (./base/):

./base/scripts/mysql_install_db --basedir=./base --datadir=./data/

./base/bin/mysqld --basedir=./base --datadir=./data/ --socket=./mariadb.sock --pid-file=mariadb

.pid

While MariaDB is running, I separately ran the following command to perform the secure installation:

./base/bin/mysql_secure_installation --basedir=./base --socket=./data/mariadb.sock

I get the following:

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Enter current password for root (enter for none):

With the code changes referenced in the issue, I get the following:

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]

Andrew Groot added a comment - 2021-04-05 17:55 - edited I ran the following commands to install/run MariaDB into a directory (./data/) alongside the MariaDB files (./base/): . /base/scripts/mysql_install_db --basedir=. /base --datadir=. /data/ . /base/bin/mysqld --basedir=. /base --datadir=. /data/ --socket=. /mariadb .sock --pid- file =mariadb .pid While MariaDB is running, I separately ran the following command to perform the secure installation: . /base/bin/mysql_secure_installation --basedir=. /base --socket=. /data/mariadb .sock I get the following: In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): ERROR 1698 (28000): Access denied for user 'root'@'localhost' Enter current password for root (enter for none): With the code changes referenced in the issue, I get the following: In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n]

Anel Husakovic added a comment - 2021-04-05 18:17

Basically the same procedure.
Can you show the output of select user,host,password from mysql.user ?

Anel Husakovic added a comment - 2021-04-05 18:17 Basically the same procedure. Can you show the output of select user,host,password from mysql.user ?

Andrew Groot added a comment - 2021-04-05 21:07

Here is the output I get (I substituted my personal username and computer name for <user> and <host>):

mysql> select user,host,password from mysql.user;

+-------------+--------------+----------+

| User        | Host         | Password |

+-------------+--------------+----------+

| mariadb.sys | localhost    |          |

| root        | localhost    | invalid  |

| <user>      | localhost    | invalid  |

|             | localhost    |          |

|             | <host>.local |          |

+-------------+--------------+----------+

5 rows in set (0.00 sec)

Anel Husakovic added a comment - 2021-04-06 11:46

Hi groot-verde, so looking from your example mysql.user table your root user doesn't have password, it is invalid.
You could authenticate with empty password if:

root has set empty password (see example above),
has operating system user root to use unix-socket (in your case <user>@localhost) (authentication plugin unix socket)
using sudo to invoke the script (where you need provide password
So I guess this is not the bug, and I'm closing this as such.

Anel Husakovic added a comment - 2021-04-06 11:46 Hi groot-verde , so looking from your example mysql.user table your root user doesn't have password, it is invalid . You could authenticate with empty password if: root has set empty password (see example above), has operating system user root to use unix-socket (in your case <user>@localhost ) ( authentication plugin unix socket ) using sudo to invoke the script (where you need provide password So I guess this is not the bug, and I'm closing this as such.

Andrew Groot added a comment - 2021-04-06 13:28

Hi Anel, I can appreciate that maybe what I'm trying to do isn't currently possible (or if I just described it poorly) but I'd be a bit surprised if it's considered unsupported. Immediately following installation, what is the expectation for the state of the root user's password? My impression was always that it was empty and that the password was then set with mysql_secure_installation (though I honestly prefer the idea that root's password is invalid).

In the example you gave, if I understand correctly, the configuration file specifies an empty root password and that allows you to continue with mysql_secure_installation, but that scripts tells you "If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here." This is the case I have, so it seems like either this message is misleading or there's an issue with the implementation.

I'm further pushed to this perspective by the fake that I'm able to continue by tweaking the make_config function to not provide the username and password. It would seem that the server is fine with proceeding in that scenario given that root has an invalid password. If that's not intended then I would imagine that is also a bug somewhere.

Even if I can avoid the issue by providing a simple configuration prior to running mysql_secure_installation that makes the root password empty, that step seems unnecessary and honestly somewhat contrary to what I'm trying to do (i.e. secure the database). I would prefer to transition it directly from a freshly installed state to a secure state without having to temporarily having an empty root password. Let me know if I'm misunderstanding something, though.

Andrew Groot added a comment - 2021-04-06 13:28 Hi Anel, I can appreciate that maybe what I'm trying to do isn't currently possible (or if I just described it poorly) but I'd be a bit surprised if it's considered unsupported. Immediately following installation, what is the expectation for the state of the root user's password? My impression was always that it was empty and that the password was then set with mysql_secure_installation (though I honestly prefer the idea that root's password is invalid). In the example you gave, if I understand correctly, the configuration file specifies an empty root password and that allows you to continue with mysql_secure_installation , but that scripts tells you "If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here." This is the case I have, so it seems like either this message is misleading or there's an issue with the implementation. I'm further pushed to this perspective by the fake that I'm able to continue by tweaking the make_config function to not provide the username and password. It would seem that the server is fine with proceeding in that scenario given that root has an invalid password. If that's not intended then I would imagine that is also a bug somewhere. Even if I can avoid the issue by providing a simple configuration prior to running mysql_secure_installation that makes the root password empty, that step seems unnecessary and honestly somewhat contrary to what I'm trying to do (i.e. secure the database). I would prefer to transition it directly from a freshly installed state to a secure state without having to temporarily having an empty root password. Let me know if I'm misunderstanding something, though.

Anel Husakovic added a comment - 2021-04-06 14:07

Hi Andrew,

Yes, based on blog authentication-in-mariadb-10-4 (suggest to read first) you have to set root password first.

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:

or use unix-socket, run mysql without anything (where you can do the same).

Configuration file: I wanted to show to my reviewer some problem but it has no user defined, if you follow example, I have set root password from mysql client before running mariadb-secure-installation (as stated in blog).
Based on message:
If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here.
I guess you are right if we look at the sentence as it is:
+ by default root user is created with CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'; and indeed password is not set, but note there is an alternative - unix-socket.
+ How would you run mysql with -uroot ? Only option is to use sudo, where you will supply password for OS user. That password will be used by root user to authenticate with unix-socket. This is where security is preserved.
The same applies in this script, if you use sudo and password for unix user, than the error message is correct, just press enter and proceed (make sure root user has all grants show grants for root@localhost;.
Regarding the security of script - documentation of mariadb-secure-installation
Let me know if we are on the same page now.

Anel Husakovic added a comment - 2021-04-06 14:07 Hi Andrew, Yes, based on blog authentication-in-mariadb-10-4 (suggest to read first) you have to set root password first. 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: or use unix-socket, run mysql without anything (where you can do the same). Configuration file: I wanted to show to my reviewer some problem but it has no user defined, if you follow example, I have set root password from mysql client before running mariadb-secure-installation (as stated in blog). Based on message: If you've just installed MariaDB, and haven't set the root password yet, you should just press enter here. I guess you are right if we look at the sentence as it is: + by default root user is created with CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'; and indeed password is not set, but note there is an alternative - unix-socket. + How would you run mysql with -uroot ? Only option is to use sudo , where you will supply password for OS user. That password will be used by root user to authenticate with unix-socket. This is where security is preserved. The same applies in this script, if you use sudo and password for unix user, than the error message is correct, just press enter and proceed (make sure root user has all grants show grants for root@localhost; . Regarding the security of script - documentation of mariadb-secure-installation Let me know if we are on the same page now.

Andrew Groot added a comment - 2021-04-06 16:50

Thanks this is helpful. It still leaves me with the question though of why the step of setting the root password before running mysql_secure_installation is necessary. In effect, the change to the make_config function effectively allows for the mysql_secure_installation to do this step for us.

As mentioned in the blog post:

In local (not system-wide) installations, this will be the user, who installed MariaDB — she automatically gets convenient password-less root-like access, because, frankly, she can access all the data files anyway.

So, if mysql_secure_installation is being run by that user, why not allow the script to take advantage of their root-like access instead of forcing them to attempt to authenticate as root? Maybe an additional question or two would help make the path through mysql_secure_installation more straightforward (i.e. to differentiate between an empty password and an installation user wants to set up the database), and so maybe this is more of a feature request, but it still feels like mysql_secure_installation is, at least in part, designed to help address this kind of use-case. My impression is just that this mode of installer-authentication was added but not translated to the mysql_secure_installation script.

Andrew Groot added a comment - 2021-04-06 16:50 Thanks this is helpful. It still leaves me with the question though of why the step of setting the root password before running mysql_secure_installation is necessary. In effect, the change to the make_config function effectively allows for the mysql_secure_installation to do this step for us. As mentioned in the blog post: In local (not system-wide) installations, this will be the user, who installed MariaDB — she automatically gets convenient password-less root-like access, because, frankly, she can access all the data files anyway. So, if mysql_secure_installation is being run by that user, why not allow the script to take advantage of their root-like access instead of forcing them to attempt to authenticate as root ? Maybe an additional question or two would help make the path through mysql_secure_installation more straightforward (i.e. to differentiate between an empty password and an installation user wants to set up the database), and so maybe this is more of a feature request, but it still feels like mysql_secure_installation is, at least in part, designed to help address this kind of use-case. My impression is just that this mode of installer-authentication was added but not translated to the mysql_secure_installation script.

Sergei Golubchik added a comment - 2021-04-06 16:58

Yes, I believe your impression is exactly correct. And mysql_secure_installation could, indeed, be extended to recognize when it's run as $USER that owns all the data files and use that somehow.

Sergei Golubchik added a comment - 2021-04-06 16:58 Yes, I believe your impression is exactly correct. And mysql_secure_installation could, indeed, be extended to recognize when it's run as $USER that owns all the data files and use that somehow.

Andrew Groot added a comment - 2021-04-07 21:00

In that case, what's the appropriate course of action from here? Should this issue be re-opened? Let me know if there's anything else I can I do to help this be addressed.

Andrew Groot added a comment - 2021-04-07 21:00 In that case, what's the appropriate course of action from here? Should this issue be re-opened? Let me know if there's anything else I can I do to help this be addressed.

Andrew Groot added a comment - 2021-04-13 13:31 - edited

Sergei/Anel, can either of you provide some guidance on what the next steps are from here? Are you willing to re-open the issue in light of Sergei's last comment? If not, let me know what other next steps are most appropriate.

Andrew Groot added a comment - 2021-04-13 13:31 - edited Sergei/Anel, can either of you provide some guidance on what the next steps are from here? Are you willing to re-open the issue in light of Sergei's last comment? If not, let me know what other next steps are most appropriate.

Anel Husakovic added a comment - 2021-04-13 13:59

Hi groot-verde, I'm working on the patch for MDEV-22486 which will address also your concerns.
It will need some time to communicate with my reviewers, have worked on some patch.
Let's leave this closed and please follow MDEV-22486.

Anel Husakovic added a comment - 2021-04-13 13:59 Hi groot-verde , I'm working on the patch for MDEV-22486 which will address also your concerns. It will need some time to communicate with my reviewers, have worked on some patch. Let's leave this closed and please follow MDEV-22486 .

Andrew Groot added a comment - 2021-04-13 14:14

Sounds good, thank you!

Andrew Groot added a comment - 2021-04-13 14:14 Sounds good, thank you!

People

Assignee:: Anel Husakovic

Reporter:: Andrew Groot

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2021-03-16 20:56

Updated:: 2021-04-13 14:14

Resolved:: 2021-04-06 11:46

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server