mysql_secure_installation/mysqladmin cannot set password when auth_socket is enabled

[Daniel Barrett]

If MySQL 5.7 is installed with an empty password, UNIX socket authentication will be enabled for the root user. If the user then tries to set a root password using mysql_secure_installation or mysqladmin, the command will appear to succeed, but the user will still only allow access through socket authentication. This is a bug in mysqladmin/mysql_secure_installation.

Upstream bug: http://bugs.mysql.com/bug.php?id=81965

To reproduce:

1. sudo apt-get install mysql-server (noninteractive or enter empty password)
2. sudo mysqladmin -uroot password 'mypass' (seems to succeed)
3. mysql -uroot -pmypass (access denied)

Workarounds:

a) Set plugin after installation:
User needs to manually set auth plugin back to password with an sql:
1. Log in with sudo mysql
2. run «ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'mypass';»

Or from command line:
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'mypass';"

b) Set password before installation:
It's possible to script the answer to the root password query using debconf-set-selections as shown below
1. debconf-set-selections <<EOT
mysql-server-5.7 mysql-server/root_password password mypass
mysql-server-5.7 mysql-server/root_password_again password mypass
EOT
2. apt-get install mysql-server
---
Other people have encountered the same problem:
http://askubuntu.com/questions/766334/cant-login-as-mysql-user-root-from-normal-user-account-in-ubuntu-16-04

[Daniel Barrett (dbarrett-m)]

FYI, if you have any difficulty reproducing this issue, I actually installed the mysql-server package using Puppet (version 3.8.5), not manually with apt-get. The puppet manifest file contains simply:

package { 'mysql-server':
   ensure => 'installed',
}
To run it, simply place the above lines into a file (say) "build.pp" and run this puppet command:

$ sudo puppet apply build.pp

I suspect this may be relevant.

[Lars Tangvald (lars-tangvald)]

This is related to a bug in mysqladmin failing to properly set the password for an account with auth_socket enabled: http://bugs.mysql.com/bug.php?id=81965 (I'm not sure if secure_installation uses mysqladmin or if it's just the same logic).

When installing the package with an empty root password, the root user will have unix socket authentication enabled, so only the system root user can log in as root. To set a password, the auth plugin must also be reset to mysql_native_password, i.e. with «ALTER USER 'root'@'localhost' IDENTIFIED BY 'passphrase' WITH 'mysql_native_password';»

After running secure_installation, can you still log in with «sudo mysql» with no password or user specified?

Also note that mysql_secure_installation is largely redundant for a fresh 5.7 installation; The only feature 5.7 doesn't set by default is the password validation plugin.

[Daniel Barrett (dbarrett-m)]

>After running secure_installation, can you still log in with «sudo
>mysql» with no password or user specified?

I succeeded with "sudo mysql -u root". I did not try it with no user specified.

[Lars Tangvald (lars-tangvald)]

If you don't specify the user it'll just use the username you run the command as (so root, with sudo).

As noted you should be able to work around the bug with ALTER USER, i.e. running:
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'passphrase';"
on the command line

[Robie Basak (racb)]

This bug is tracked upstream. Importance: Low for Ubuntu - we'll fix when upstream does.

[Colin Law (colin-law)]

For Ubuntu 18.04 the default installation of mysql-server has socket authentication enabled (if I understand correctly) so it is not possible to set the root pwd using mysql_secure_installation. This is most unfortunate as there is a great deal of apparently authoritative documentation out there that says this is the way to do it. For example
https://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installation.html
In addition there are very few references to the fact that instead one has to use the workaround in the bug description.

[Christian Ehrhardt  (paelzer)]

After all this time the upstream case isn't resolved yet.
@Lars - is there something we/you could do to get back some traction on it - actually is it even reasonable in 2022 and mysql-8.0 times?

Because without that there is not much we can do for Ubuntu and I'm afraid that bug will just stay open forever which isn't helpful either :-/