Changing mysql root password on Ubuntu

[abo-adapt]

After installing mysql-server with this formula, with a root_password set in pillar, I change the password in pillar and run state.highstate, but the root password in mysql stays the same. I see salt changing the mysql root password in debconf, but that is only used during install of the package.

[bogdanr]

This also happens on Debian 9

[daks]

In fact, this formula does not allow changing root password. I think it's logical because it does not have access to the old and the new password at the same time to change it.

The README says

Debian OS family supports setting MySQL root password during install via debconf.

which I understand like:

• only setting password at install is supported
• and only Debian is supported

But maybe README could be updated to specify it.

[johnkeates]

Isn't that readme line clear as-is? Debconf and Debian, only on Debian and Debian-alike systems. This is expected behaviour because the MySQL and MariaDB packages on Debian allow setting the initial root password via the DPKG installer's Debconf settings.

This was probably implemented by someone initially adding Debian support or creating the formula.

You can't change the root password for MySQL with only 1 point of storage, i.e. mysql.server.root_password. The MySQL module wouldn't be able to store both the old and the new password and thus won't be able to log in using the old one and then set the new one. This means you would have to store the password twice (as suggested earlier), once on the server for salt-minion to read on login, and once in the pillar. Then it would be able to match the passwords, detect changes, apply them to the server first and then to the local stored password.

This whole setup would be insecure at best and most likely simply not something you'd ever want on a production system. On the other hand, the debian sys maintainer user already does this initially after installation, so if that setup were to be replicated, now insecure would it really be? Also, if you are root on a box, you practically own everything already, so having a plaintext root password file that can only be read by root might not be all that 'less secure' at all, unless there would be a way for a non-root user to exploit a bug and read the file, but then would go for the sys-maint user as well. Tricky stuff.

I'd suggest the following until this is actually resolved:

• don't allow any special root modification to begin with, except for what is already supported
• add a caveat section to the readme explaining the chicken-and-egg problem with the root password

One way to implement this more universally is by using the normal root password recovery method, but you'd still have to check if the current root password differs from the installed root password. This could be done by logging in and checking if it succeeded. If not: use root password recovery to reset it. If the login worked, the password was already good as-is. But then you'd run the risk of adding more and more entries since logging in also depends on the LHOST and RHOST, so you'd have to account for that as well. On top of that, the database will be offline twice while it stops and starts, and in the middle of the password change it will be insecure since 'ignore grant' will be on after the first shutdown-startup loop. Since it is a pretty hard password reset way it should probably come with a warning in the docs, and a special pillar value like 'recover_password' that must be set to true before root password changes are allowed.

Maybe MySQL simply has no 'good' way of managing the root user automatically. It would be great if it had something like Postgres where the postgres local user is also the administrative database user and instead of using a password to ID the user, you would use Unix user ID's on a local socket. Or maybe something like LDAP or GSSAPI...