DietPi-Software | MariaDB: Fix database/user creation

[MichaIng]

On testing installation of updated software versions, I found several installation to fail and to possibly break further installations. The problem is the unix_socket authentication, coming by default with MariaDB and i.a. how it is handles within Jessie:

• Since Stretch, no real mysql-server is available any more, but mariadb-server is actually installed.
• Thus (besides other reasons) we disabled MySQL on Stretch and switched to MariaDB as default choice on all distro versions.
• MariaDB comes on all distro versions with unix_socket authentication plugin enabled for root user by default.
• This enables unix user root to use mysql commands without password (e.g. mysqladmin -uroot <command> works), but does NOT allow any other user to use mysql root user, as there also does not exist any password (e.g. sudo -u www-data mysql -uroot -p<whatever> <command> breaks).
• Since Stretch, if -p is added, it just gets ignored and mysql command runs, if executed by root. But on Jessie, it is not even allowed to add a password. Thus on Jessie+MariaDB we MUST execute every mysql command as unix root user WITHOUT password. This already needs rework on several software (un)installations and expecially: https://github.com/Fourdee/DietPi/blob/testing/dietpi/func/create_mysql_db
• The next problem is, if one uses this script to create a new database, GRANT ALL PRIVILEGES ON '$DATABASE_NAME'.* TO '$DATABASE_USER'@'localhost' IDENTIFIED BY '$DATABASE_PW'; in case overwrites root users authentication method to use the given password. Thus afterwards root authentication without password is broken.

Solutions:

• Create dietpi-software wide $MYSQL_AUTH variable to include -uroot or -uroot -p$GLOBAL_PW depending on MySQL or MariaDB installation. Use this for all kind of mysql commands (mysql/mysqladmin/...)
• Remove IDENTIFIED BY '$DATABASE_PW' on every mysql execution (especially create_mysql_db), if $DATABASE_USER=='root' is given.
• If software needs to create a database as another user than root (e.g. ownCloud+Nextcloud on their occ maintenance:install command), then we need to create+use a temporary admin user with password authentication:

mysql_-uroot -e "grant all privileges on *.* to 'tmp_root'@'localhost' identified by '$GLOBAL_PW' with grant option"
<do mysql command, using 'tmp_root'>
mysql_-uroot -e "drop user 'tmp_root'@'localhost'"

OR:

• Migrate also all Jessie users to MariaDB and remove the password for root completely.
• But this might lead to confusion for users, that are familiar with their MySQL to use with password.

OR the 2nd:

• Switch MariaDB to password authentication.
• But by this we loose security and comfort advantage that is provided by unix_socket authentication.

Commands to play around with:
mysql -uroot -pdietpi
mysql -uroot
select host, user, password, plugin from mysql.user;
grant usage on *.* to 'root'@'localhost' identified by 'dietpi';
grant usage on *.* to 'root'@'localhost' identified via unix_socket;
install plugin unix_socket soname 'auth_socket'; //necessary on Jessie after switched to password authentication and back to unix_socket!
mysql -uroot -e "create database test; grant all privileges on test.* to 'root'@'localhost' identified by 'dietpi'"

[MichaIng]

@Fourdee
Fixed within database creation script, and some other quirks I noticed: https://github.com/Fourdee/DietPi/pull/1383

Especially on existing symlink at /var/lib/mysql the installation fails and the log does not give the right hints, if one not tries to install manually.

[MichaIng]

Fix so far: https://github.com/Fourdee/DietPi/pull/1398

Further discussion: https://github.com/Fourdee/DietPi/issues/1397#issuecomment-358132301