mariadb in chroot gives Access denied for user 'root'@'localhost' even though password is set

Question

I have a script that runs mariadb in a chroot, its a qemu-arm-static rasoberrypi chroot but I don't that that should change much.

The script installs and then sets the password for mariadb root user, then tries to create a database as user 'pi' using the set password.

Here is the script:

apt-get install -y mariadb-server
mysqld_safe &
echo "waiting for sql server to go online"
sleep 10

mysql -u root <<-EOF
UPDATE mysql.user SET Password=PASSWORD('root') WEHRE User='root';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.db WHERE Db='test' OR Db='test_%';
FLUSH PRIVILEGES;
EOF

mysql --user=root -e "select user, host, password, plugin, authentication_string from mysql.user where user='root';"
mysql --user=root -e "show grants for 'root'@'localhost';"

mysqladmin shutdown
sleep 10
mysqld_safe &
echo "waiting for sql server to go online"
sleep 10
su pi -c 'mysql --user=root --password=root -e "CREATE DATABASE dbname;"'
mysqladmin shutdown

Here is the output:

update-alternatives: using /usr/bin/xterm to provide /usr/bin/x-terminal-emulator (x-terminal-emulator) in auto mode
update-alternatives: using /usr/bin/lxterm to provide /usr/bin/x-terminal-emulator (x-terminal-emulator) in auto mode
Processing triggers for libc-bin (2.24-11+deb9u3) ...
Processing triggers for systemd (232-25+deb9u2) ...
W: chown to _apt:root of directory /var/cache/apt/archives/partial failed - SetupAPTPartialDirectory (1: Operation not permitted)
W: Not using locking for nfs mounted lock file /var/cache/apt/archives/lock
+ echo 'waiting for sql server to go online'
waiting for sql server to go online
+ sleep 10
+ mysqld_safe
180628 08:27:53 mysqld_safe Logging to syslog.
180628 08:27:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
+ mysql -u root
+ mysql --user=root -e 'select user, host, password, plugin, authentication_string from mysql.user where user='\''root'\'';'
user    host    password        plugin  authentication_string
root    localhost       *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B       unix_socket
+ mysql --user=root -e 'show grants for '\''root'\''@'\''localhost'\'';'
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
+ mysqladmin shutdown
+ sleep 10
+ echo 'waiting for sql server to go online'
waiting for sql server to go online
+ sleep 10
+ mysqld_safe
180628 08:28:18 mysqld_safe Logging to syslog.
180628 08:28:18 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
+ su pi -c 'mysql --user=root --password=root -e "CREATE DATABASE dbname;"'
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Running mysql as root always works and does not ask for password.

I can see that the password hash changes if I change the password from 'root' to something else, but I still get "access denied", is there anything else that needs to be done so root can login?

Answer 1

Ok, so the issue is, which I figured thanks to #maria IRC channel is that Debian systems (and probably other posix systems) have an auth plugin called unix_socket. This plugin does not let you access root user with a password. So what you can do is create another user, and give it all permissions root has (or the permissions you want).

This makes the script above look like this, which is now part of CustomPiOS mysql install module:

# Mysql user (not root, because root is only accessible from command-line)
MYSQL_USER=pi
MYSQL_USER_PASSWORD=raspberry

apt-get install -y mariadb-server

mysqld_safe &

echo "waiting for sql server to go online"
sleep 10

mysql --user=root -e "CREATE USER '"${MYSQL_USER}"'@'localhost' IDENTIFIED BY '${MYSQL_USER_PASSWORD}';"
mysql --user=root -e "GRANT ALL PRIVILEGES ON  *.* to 'pi'@'localhost' WITH GRANT OPTION;"

# Debug
# mysql --user=root -e "select user, host, password, plugin, authentication_string from mysql.user;"
# mysql --user=root -e "show grants for '"${MYSQL_USER}"'@'localhost';"

mysqladmin shutdown

# Example to create a new DB
# mysqld_safe &
# echo "waiting for sql server to go online"
# sleep 10
# su pi -c 'mysql --user=pi --password=raspberry -e "CREATE DATABASE dbname;"'
# mysqladmin shutdown

echo "Done installing mysql"