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.

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.

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?

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]

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

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.

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.

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.

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.

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.

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.

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.

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.

Sounds good, thank you!