MariaDB - mysql.user contains 1 root accounts without password

There is a default mylsq root user which is configured without a password after running production playbook. Is this by design? I’m getting this warning when checking mysql status

service mysql status 
Nov 16 16:33:18 example_com-production /etc/mysql/debian-start[14928]: WARNING: mysql.user contains 1 root accounts without password!

And if I check the mysql table I actually see the root user w/o password:

MariaDB [mysql]> select User,Password from user;
+----------------------+-------------------------------------------+
| User                 | Password                                  |
+----------------------+-------------------------------------------+
| root                 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| root                 |                                           |
| root                 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| root                 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| debian-sys-maint     | *YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY |
| root                 | *XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |
| example_com          | *ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ |
+----------------------+-------------------------------------------+

This is on a stock Ubuntu server provisioned with Trellis?

We’ve checked a few servers and none have a root user without a password.

I should also mention that Trellis disables external access to MySQL (non-localhost) so while this is still “bad”, it’s not as bad as you’d think but it should be fixed on your server regardless.

1 Like

This was on a stock DO Ubuntu droplet provisioned with Trellis 0.9.8. I had this insecure root account on all environments (development/staging/production) which were all independently provisioned.

After some research I noticed that my droplets were not provisioned with the latest Trellis version 0.9.9, which is not affected by this issue. Maybe this has something to do with [PR693] (https://github.com/roots/trellis/pull/693) which has changed the default mariadb repo?

Updating Trellis from 0.9.8 to 0.9.9 won’t remove the “insecure” root account. A quick fix is running the following sql command on the affected environment:

USE mysql; DELETE from user WHERE Password=''; FLUSH PRIVILEGES;

1 Like

I’ve just checked two of my projects, one running Trellis 0.9.6 and one running Trellis 0.9.8, and both staging and production have the same “issue”.

Just to let you know that @o1y is not the only one.

1 Like