> but I don't understand how it solves this situation.

makes the mysql_install_db create the root user authenticated by unix_socket rather than leaving it to package scripts.

I'm not exactly sure what to do here. It's not an upstream (MariaDB) bug (yet). It needs to be fixed downstream (in Debian packaging). I don't know how downstream wants to fix the bug either. Not switching existing users to unix_socket seems obvious enough to me.

danblack Thanks for the comment above but unfortunately I still don't understand how it works, what is the sequence of events, what does this mean for new install and upgrades, what things that change will rule out that will stop working etc. Can you please extend the context a bit?

We could also sit down an finalize this passwordless root login issue at the dev meetup next month if you wish to spend some time on it there.

scripts/mysql_system_tables_data.sql could be modified to add a root user account with a unix_socket auth.

The plugin would also need to be inserted here into the plugins table.

This would cover the install case as this is incorporated into the mysql_install_db script. some wrapping here will be needed to avoid installing this on Windows installs.

As upgrades don't invoke mysql_install_db the only thing required in mariadb-server-X.X.postinst is to populate user=root in debian.cnf instead of user = debian-sys-maint if its a socket user. The test_mysql_access bit of the postinit script is incorrect as this will return true if its a socket authed root user.

Happy to spend some time on it at the dev conference. Its going to be pretty busy I suspect.

serg What is the proper way to inject at the earlies possible stage that on new installs the root account uses the unix_sockert plugin and that plugin is always installed? Our experiments with Daniel Black failed as we didn't get it injected early enough so that it would also apply to the test suite run environment correctly. Please advice.

For existing installs or current users I don't think we need to do anything, they can keep on using their passwords, but when new mysql.user tables are created the root account login should be based on unix_socket.

serg danblack This issue is still unresolved. With Daniel we hacked on it in Amsterdam but didn't find a solution that is compatible with also the build time test suite run. Personally I think it would be important to get this sorted out ASAP so that the MariaDB version in Debian and also in upcoming Ubuntu 16.04 would be properly fixed. Current passwordless root login implementation needs an improvement.

serg svoj danblack Implemented in https://github.com/ottok/mariadb-10.0 and uploaded to Debian as 10.0.22-6 after manual testing that build, test suite and install and upgrade from MariaDB and upgrade from MySQL (version in Debian unstable) works.

This issue can be closed for Debian part, but maybe we need a separate issue to track upstreaming this feature?

The patch in Debian (https://github.com/ottok/mariadb-10.0/blob/master/debian/patches/mdev-8375-passwordless-root-via-socket-auth.patch) looks like this

From: Daniel Black <daniel.black@openquery.com.au>

Date: Tue, 26 May 2015 18:48:57 +1000

Desciption: Make root account by default on new installs passwordless

 and use the unix socket authentication instead.

 .

 Remove this the day passwordless root account becomes the default in

 upstream too and not just in Debian packaging.

Origin: https://github.com/ottok/mariadb-10.0/pull/23/

Bug: https://mariadb.atlassian.net/browse/MDEV-8375

--- a/scripts/mysql_system_tables_data.sql

+++ b/scripts/mysql_system_tables_data.sql

@@ -29,7 +29,7 @@ SELECT LOWER( REPLACE((SELECT REPLACE(@@

 -- Fill "user" table with default users allowing root access

 -- from local machine if "user" table didn't exist before

 CREATE TEMPORARY TABLE tmp_user LIKE user;

-INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N', 'N');

+INSERT INTO tmp_user VALUES ('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'unix_socket','','N', 'N');

 REPLACE INTO tmp_user SELECT @current_hostname,'root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N' FROM dual WHERE @current_hostname != 'localhost';

 REPLACE INTO tmp_user VALUES ('127.0.0.1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');

 REPLACE INTO tmp_user VALUES ('::1','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','','N','N');

I wonder if we should completely remove the root account lines for 127.0.0.1 and ::1 now too? They become passwordless and unix_socketless on installed systems, and might be some kind of security risk (though difficult to test as logging in always falls back to 'localhost' and not IPv4 or IPv6 address no matter what I try).

Users looking at the syslog will also notice this:

Jan 10 15:44:17 sid /etc/mysql/debian-start[2345]: Checking for insecure root accounts.

Jan 10 15:44:17 sid /etc/mysql/debian-start[2349]: WARNING: mysql.user contains 2 root accounts without password or plugin!

Monty suggested in a discussion that the IPv4 and IPv6 localhost root lines whould be removed from default install.

I did the above here on a separate feature branch https://github.com/ottok/mariadb-10.0/commit/6f69ad4934d5f4773f0196b37fe35e2c54ff36f5
However it made some tests fail like this:

rpl.rpl_ip_mix                           w3 [ fail ]

        Test ended at 2016-01-13 23:59:51

CURRENT_TEST: rpl.rpl_ip_mix

mysqltest: In included file "./include/rpl_ip_mix.inc": 

included from /«PKGBUILDDIR»/mysql-test/suite/rpl/t/rpl_ip_mix.test at line 11:

At line 2: query 'connect  master,$IPv6,root,,test,$MASTER_MYPORT' failed: 1130: Host '::1' is not allowed to connect to this MariaDB server

The result from queries just before the failure was:

#################### IP: ::1 ###########################

connect (master,::1,root,,test,MASTER_MYPORT);

 - saving '/«PKGBUILDDIR»/builddir/mysql-test/var/3/log/rpl.rpl_ip_mix/' to '/«PKGBUILDDIR»/builddir/mysql-test/var/log/rpl.rpl_ip_mix/'

Retrying test rpl.rpl_ip_mix, attempt(2/3)...

rpl.rpl_ip_mix                           w3 [ retry-fail ]

        Test ended at 2016-01-13 23:59:51

CURRENT_TEST: rpl.rpl_ip_mix

mysqltest: In included file "./include/rpl_ip_mix.inc": 

included from /«PKGBUILDDIR»/mysql-test/suite/rpl/t/rpl_ip_mix.test at line 11:

At line 2: query 'connect  master,$IPv6,root,,test,$MASTER_MYPORT' failed: 1130: Host '::1' is not allowed to connect to this MariaDB server

The result from queries just before the failure was:

#################### IP: ::1 ###########################

connect (master,::1,root,,test,MASTER_MYPORT);

 - saving '/«PKGBUILDDIR»/builddir/mysql-test/var/3/log/rpl.rpl_ip_mix/' to '/«PKGBUILDDIR»/builddir/mysql-test/var/log/rpl.rpl_ip_mix/'

Test rpl.rpl_ip_mix has failed 2 times, no more retries!

rpl.rpl_ipv4_as_ipv6                     w3 [ skipped ]  wrong IP

...

Too many failed: Failed 10/3981 tests, 99.75% were successful.

Failing test(s): rpl.rpl_ip_mix rpl.rpl_ip_mix2 rpl.rpl_ipv6 main.ipv4_and_ipv6 main.ipv6 main.join_cache main.explain_non_select perfschema.privilege_table_io main.failed_auth_3909

Full log at https://launchpadlibrarian.net/234084010/buildlog_ubuntu-xenial-ppc64el.mariadb-10.0_10.0.23-2~xenial1~1452727847.6f69ad4_BUILDING.txt.gz

I see a big problem here if the behavior of mysql_install_db is to always create the "root" account using unix_socket. This would badly damage the tarball installation workflow, which allows MariaDB to be installed and used by any user.

We sure don't want to break anything. Where is the tarball installation flow documented? Do you have a suggestion on how to mitigate this?

People use the tarball installations in many ways. A very common use case is to simply unpack them anywhere you want on the filesystem (in an unprivileged location), run mysql_install_db, and then start mysqld running as your own OS user. There is no need to become root at all in order to unpack the tarball, so tying MariaDB's "root" user to the OS' "root" in this context makes no sense and would in fact prevent the person running the server from logging in to it!

As for how to solve this, I suppose there are several different options. Perhaps mysql_install_db needs some options to indicate which behavior is desired. Maybe a --root-access option that could take one of several arguments such as "unix_socket" or "localhost" or ... ?

Is it too late to completely re-evaluate this entire thing? I think requiring the use of the OS root user to have basic interaction with MariaDB is a really bad practice. Many people I've talked to seem to agree with me. Is this really the direction we want to go?

Not too late, of course. I but think it's fine to have a passwordless (unix_socket) root account in MariaDB installed from packages (RPM, DEB). But I totally agree that it should not be done in binary tarballs.

kolbe How does authentication in tarball installs now work? Using root username and password? When/where is that password defined? When the password is added to mysql.user table, it could enable access for root with password and remove the unix_socket authentication plugin.

Another idea: currently if unix_socket is defined as the authentication method, any password supplied is ignored. What if this was changed so, that unix_socket auth method would fallback to trying username/password if they are supplied and not empty? This way users could log in if password has been set and is used.

When you run mysql_install_db, the normal passwordless root users are created along with the anonymous users. After you run mysql_install_db, you either run mysql_secure_installation or manually change the root passwords and get rid of the anonymous users et cetera.

Falling back to a password if unix_socket auth fails sounds like a nice feature, but it should be possible to disable this, too.

This has been implemented in Debian and Ubuntu for a while, but as confirmed by ratzpo in the MariaDB Developers Meeting in Amsterdam 2016, it was not in the 10.2 roadmap and it is too late to include it now. So this is postponed to 10.3. Yet another year (or more) to wait...

This is the single biggest issue that has stopped me from upstreaming the Debian packaging changes to first 10.1, then 10.2, then 10.3 etc..

I really hope that in the 10.4 cycle we can start doing this in the upstream MariaDB.org packages as well, so we get rid of many maintainer script and configuration differences between the different Debian packaging versions.

The delta between the versions slow down everything else, because it adds complexity when the same thing can exists in many versions, and makes it harder to grasp to overall situation everywhere. Once this is merged upstream, expect all other packaging bugs to get closed much faster.

otto, so you you want MariaDB upstream to be the same as Debian packaging. I get that. What problems in the Passwordless root login solving for Debian and how? What are the test cases around this packaging?

Can the end user confusion over root login be solved with a auth_socket account for the mysql unix user (would help solve MDEV-16621 by using the mysql user in logrotate)?

mysql_install_db (MDEV-14578, 10.2+) installs the auth_socket plugin if `--auth-root-authentication-method=socket` is specified.

otto, this task depends on MDEV-11340, which is supposed to be implemented in 10.4. So if it'll go as planned, this task will be done in 10.4 as well.

Unix socket authentication has been available also in upstream since 10.4. All corner cases and regressions related to it should be fixed by now, so closing this issue.