If there is no strong demand for this to happen fast due to any policies or whatever, I would rather wait for the automatic fallback. The change of the default authentication method seems to have caused considerable confusion among users, at least I have seen questions about why it is happening and how to get back to the usual password authentication on multiple occasions on resources like stackoverflow and such.

I have also run into a few users who are confused by this change. For example, I submitted MDEV-12835 on behalf of the latest confused user. However, I have no idea if the confused users are representative of the majority of MariaDB users.

GeoffMontee, fairly representative. Unfortunately, questions about it are quite common on #maria channel on Freenode.

The old Debian packaging git repo server has been shut down and replaced by Gitlab based system at https://salsa.debian.org/mariadb-team/

There are different use cases.

If MariaDB is being installed system-wide and configured as a root user, this is the most common use case, then we can create a root@localhost account that can authenticate either with unix_socket or, alternatively, with a password. But we'll set an invalid password, so by default there is no password-less root account, still a root can log in and do SET PASSWORD if needed.

Another use case is installing MariaDB locally as a non-root user. In this case the previous approach would require one to sudo to be able to log in as root (debian bug#848616). For this use case we could also create a $USER@localhost account with the same authentication as above.

I suggest the default behavior of mysql_install_db should cover both cases as above and command line options would cover for other, less common, use cases (like installing MariaDB as non-root and then copying into system-wide location, or forcing password-less root).

For the systemwide case I recommend packaging (rpm/deb) a mysql@localhost IDENTIFIED VIA unix_socket user so:

• that logrotate (MDEV-16621)
• debian/additions/debian-start; and
• other packages the create users/databases.

It has the advantages that:

• its a non-root user (aka) mysql, distros often desire for scripts;
• its a unix account already;
• removes systemd PermissionStartOnly=true (root) permission for running /etc/mysql/debian-start (other requirements removed in gh pr #1143 and #1105)
• a low susceptibility to compromise;
• immune from the dodgy hacks that people have posted everywhere on resetting the root@localhost account;
• isn't a password that breaks when updating replication master/slaves

So would be in addition to the root@localhost that people can and will break (but will be able to be reset without `skip-priv-tables`)

A potential downside with creating a mysql@localhost account with unix_socket authentication is that the mysql Unix user's default shell is /sbin/nologin on a lot of systems, so it might be kind of awkward for DBAs to use the account with unix_socket authentication.

For example, if we create the MariaDB user with unix_socket authentication:

MariaDB [(none)]> INSTALL SONAME 'auth_socket';

Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> CREATE USER mysql@localhost IDENTIFIED VIA unix_socket;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost WITH GRANT OPTION;

Query OK, 0 rows affected (0.000 sec)

And then if we try to log in with the Unix account on RHEL 7:

[ec2-user@ip-172-30-1-236 ~]$ sudo passwd mysql

Changing password for user mysql.

New password:

Retype new password:

passwd: all authentication tokens updated successfully.

[ec2-user@ip-172-30-1-236 ~]$ su -l mysql

Password:

Last failed login: Mon Jan 21 03:55:21 UTC 2019 on pts/0

There was 1 failed login attempt since the last successful login.

This account is currently not available.

[ec2-user@ip-172-30-1-236 ~]$ getent passwd mysql

mysql:x:997:994:MySQL server:/var/lib/mysql:/sbin/nologin

[ec2-user@ip-172-30-1-236 ~]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.5 (Maipo)

It fails if we actually try to login as the user, because the user's default shell is /sbin/nologin.

So to make this work, you'd either have to change the Unix user's default shell or find ways to authenticate as the Unix user without logging into a shell, such as by using sudo, like in the following example:

[ec2-user@ip-172-30-1-236 ~]$ sudo -u mysql mysql -u mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 12

Server version: 10.3.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT CURRENT_USER();

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

| CURRENT_USER()  |

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

| mysql@localhost |

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

1 row in set (0.000 sec)

If this mysql@localhost account would only be intended for automated things like logrotate and package installation scripts, then maybe this isn't a problem.

Thanks for the tests GeoffMontee. Yes was thinking of it for automated purposes.

sudo -u mysql mysql -u mysql

Can still access an interactive mysql@localhost user even with `/bin/nologin` set as the shell, for password recovery without downtime.

I suspect that mysql@localhost could have a rather good chance of conflicting with user accounts, looks like a typical name one could create for some mysql-based applications.

I agree someone may have done it, and packaging scripts will need to account for this, however I've never seen or found a stack overflow, dba.stackexchange.com, blog or any other reference to it. I tried searching Google for CREATE USER mysql and haven't found an exact match . All examples I've seen focus on a personal name (dan), a role (admin), root. or and application name.

Okay. I'll try this — two all-privilege accounts, authenticated with unix_socket. One is root. The other is:

  # Use $auth_root_socket_user if explicitly specified.

  # Otherwise use the owner of datadir - ${user:-$USER}

  # Use 'root' as a fallback

  auth_root_socket_user=${auth_root_socket_user:-${user:-${USER:-root}}}

The logic here is — both root user and the owner of the datadir do not need to know the password, they have the full access to the data anyway. So we don't ask them to provide the password.

This covers both your mysql@localhost suggestion and debian bug#848616 (installed locally under a normal user) use cases.

Nice work serg. Always great to see large chunks of packaging script disappear (https://github.com/mariadb/server/commit/25c6d626570e048fc8ddf1ef500a0c601d8b8a60). Trivial, minor, and not without RPM implications, does `./support-files/mysql-log-rotate.sh` need a `su mysql mysql`?

No, I don't think so. That's the same with Debian — if a script can read /etc/mysql/debian.cnf then it's run as root and that alone is enough. In mysql-log-rotate.sh it looks like it needs to read ~root/.my.cnf, so it's enough too.

By the way, with mysql user it's somewhat tricky. I didn't dig too deep but the only sudo that I managed to get working was

$ sudo sudo -umysql mysql -umysql

which looks a bit redundant to me. For running as root simple sudo mysql works.

Great to see this finally done upstream! A big thank you serg for taking the time to merge it in.