Mariadb-docker: Allow root user to login via both password and unix_socket

[dbjpanda]

Currently by default the root login is set to password only or to use mysql_native_password plugin as you can see below.
docker-compose exec mariadb mysql -u root -ppass -e "select user, host, password, plugin from mysql.user;"

| user     | host      | password                                  | plugin                |
+----------+-----------+-------------------------------------------+-----------------------+
| root     | localhost | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 | mysql_native_password |
| test     | %         | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |                       |
| root     | %         | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |                       |
| dbjpanda | %         | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |                       |
+----------+-----------+-------------------------------------------+-----------------------+

From docker container prospective it will be really helpful to login via unix socket.
So can we just allow both ways of login for root user. Something like

GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

[tianon]

I'm not sure why this happens (or what it means), but we're using pretty standard SQL commands to achieve our default state, so I'm not sure what would make sense here? I definitely can't reproduce a failure with either TCP or unix socket connections given the default configuration the image uses:

$ docker pull mariadb:10.3
10.3: Pulling from library/mariadb
Digest: sha256:46345ab06961cf29aa340788f71cb0509420de65a7359039b4ef6599d937bae9
Status: Image is up to date for mariadb:10.3

$ docker run -dit --name test -e MYSQL_ROOT_PASSWORD=example mariadb:10.3
ef040dabdcaddb23ef6262fdeb6b31c04fa7cbcb7e966bbf7aaa760c0c3db7bf

$ docker logs --tail=2 test
2019-01-10 23:30:06 0 [Note] mysqld: ready for connections.
Version: '10.3.12-MariaDB-1:10.3.12+maria~bionic'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

$ # testing unix socket
$ docker exec -it test mysql -uroot -pexample
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.12-MariaDB-1:10.3.12+maria~bionic mariadb.org binary distribution

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 user, host, password, plugin from mysql.user;
+------+-----------+-------------------------------------------+-----------------------+
| user | host      | password                                  | plugin                |
+------+-----------+-------------------------------------------+-----------------------+
| root | localhost | *57237BB49761F29AB9724BA084E811D70C12393D | mysql_native_password |
| root | %         | *57237BB49761F29AB9724BA084E811D70C12393D |                       |
+------+-----------+-------------------------------------------+-----------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> Bye

$ # testing TCP connection
$ docker run -it --rm --link test mariadb:10.3 mysql -uroot -pexample -htest
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.12-MariaDB-1:10.3.12+maria~bionic mariadb.org binary distribution

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 user, host, password, plugin from mysql.user;
+------+-----------+-------------------------------------------+-----------------------+
| user | host      | password                                  | plugin                |
+------+-----------+-------------------------------------------+-----------------------+
| root | localhost | *57237BB49761F29AB9724BA084E811D70C12393D | mysql_native_password |
| root | %         | *57237BB49761F29AB9724BA084E811D70C12393D |                       |
+------+-----------+-------------------------------------------+-----------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> Bye

[dbjpanda]

@tianon Here is the official documentation . So to test the unix socket you don't need to provide password. Instead you have to either provide your socket path (mounted from container to host) or nothing provided you are login to your container using that user which is allowed for socket authentication.

So you should test something like

$ # testing unix socket
$ docker exec -it test mysql -uroot --socket=$pwd/mysqld.sock  

[dbjpanda]

https://github.com/docker-library/mariadb/blob/db27681a5753e6f22eb73b5a9575d6b833ba1238/10.3/docker-entrypoint.sh#L94
should be

mysql_install_db --datadir="$DATADIR" --rpm "${@:2}" --auth-root-authentication-method=socket --auth-root-socket-user=root

[tianon]

Ah, so it's non-default behavior (although it appears the plugin is enabled by default, making it easier to use right off the bat), that makes more sense. What I'd recommend is using your own .sql file in /docker-entrypoint-initdb.d to adjust/create users as desired in order to take advantage of this -- it doesn't seem like it's something we ought to adjust the image itself to do by default.

As a point of interest, this is how PostgreSQL works by default, and we've found that it's _very_ surprising behavior for users to not require authentication via docker exec (for example), so we'd be even more hesitant to implement that sort of behavior intentionally unless there was an explicit request / recommendation from MariaDB upstream that we do so.

[wglambert]

Closing as we don't wish to modify the default behavior for this, and making use of the container's mysql unix socket in this way is entirely possible with some configuration