search

geerlingguy / ansible-role-mysql

Ansible Role - MySQL
https://galaxy.ansible.com/geerlingguy/mysql/
MIT License
1.05k stars 865 forks source link

init scripts & unix_socket authentication broken on Debian 10 #421

Closed spacekpe closed 3 years ago

spacekpe commented 3 years ago

Role version: 3.1.0 Environment: Debian 10, package mariadb-server-10.3 1:10.3.22-0+deb10u1 amd64

Task "Update MySQL root password for localhost root account" in tasks/secure-installation.yml breaks init scripts on Debian 10.

Debian 10 depends on unix_socket authentication in upgrade scripts, but this role by default forces password authentication for root user.

Symptoms in journal (journalctl -u mariadb -o cat):

Starting MariaDB 10.3.22 database server...
2020-11-21 15:32:53 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0+deb10u1) starting as process 9039 ...
Started MariaDB 10.3.22 database server.
Upgrading MySQL tables if necessary.
/usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Looking for 'mysql' as: /usr/bin/mysql
Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
FATAL ERROR: Upgrade failed

This happens even if role user does not specify any explicit values for (mysql_root_password, mysql_user_password, mysql_root_password_update). Default values in the role will provide default value for mysql_root_password = root and it will set it as password when mysql_install_packages is true.

I consider this confusing and unexpected, but most importantly it is insecure (because it silently sets weak passwords) and breaks stuff on default system installations.

I propose a different approach: Do not set root password if one is not explicitly provided in variables. That will not break Debian 10 and also will avoid setting weak passwords by default.

spacekpe commented 3 years ago

If anyone stumbles on this, easiest way to revert to Debian 10 default is this SQL snippet:

ALTER USER root@localhost IDENTIFIED VIA unix_socket;
stale[bot] commented 3 years ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

stale[bot] commented 3 years ago

This issue has been closed due to inactivity. If you feel this is in error, please reopen the issue or file a new issue with the relevant details.