root@localhost password set with Ansible mysql_user module doesn't work

Question

I have the following playbook:

- hosts: myserver
  vars:
    mysql_root_password: foobarbaz

[...]

tasks:

  [...]

  - name: update mysql root password for all root accounts
      mysql_user: name=root host={{ item }} password={{ mysql_root_password }} priv=*.*:ALL,GRANT
      with_items:
        - "{{ ansible_hostname }}"
        - 127.0.0.1
        - ::1
        - "localhost"
      become: true
      tags: mysql

  [...]

  # I've ommitted the tasks to install the mysql packages, 
  # store the password in /root/.my.cnf and restart the server)

The problem is the desired pasword is correctly saved in mysql.user for 127.0.0.1, ::1 and the hostname but not for localhost, i.e.

mysql> select host,user,authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             |                                           |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 |
| ubuntu    | root             | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 |
| 127.0.0.1 | root             | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 |
| ::1       | root             | *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 |
+-----------+------------------+-------------------------------------------+

Where *8C52... is the encrypted password:

mysql> select password('foobarbaz');
+-------------------------------------------+
| password('foobarbaz')                     |
+-------------------------------------------+
| *8C5206E23A3B76002AA6E152691F5C5D7ABC31F9 |
+-------------------------------------------+

Therefore, this fails:

william@ubuntu:/etc/mysql$ mysql -u root --password=foobarbaz
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

and also if I use sudo, I can connect to mysql as root using any password or none.

To put it another way, this ansible command doesn't do anything, but if I use any other user or host it works.

ansible myserver -m mysql_user -a "name=root host=localhost password=foobarbaz priv=*.*:ALL,GRANT" -b
myserver | SUCCESS => {
    "changed": true,
    "user": "root"
}

MySQL: Ver 14.14 Distrib 5.7.15
Ubuntu 16.04.1 LTS
Ansible 2.1.2.0

Answer 1

Since Ansible supports Unix domain socket for local connections now, there is pretty elegant way (without initial root user setup and any file config):

tasks:
  # assume that pymysql and mysql-server (with default config) are installed before this task

  - name: add user to mysql server
    mysql_user:
      # you can get socket path from /etc/mysql/mysql.conf.d/mysqld.cnf
      login_unix_socket: /var/run/mysqld/mysqld.sock  # default path
      name: username
      password: user_password
      priv: "database.*:ALL"    # not sure about dot, colon and asterisk, so I used quotes here
    become: yes

login_unix_socket parameter is available for all Ansible mysql modules (v2.9 | v2.10+)

Answer 2

The cause is this limitation of the Ansible mysql_user module:

Currently, there is only support for the mysql_native_password encryted password hash module.

You can install MySQL packages with or without specifying a root password.

Installation with root password

With a password, the root@localhost login uses mysql_native_password, which is the password hashing method introduced in MySQL 4.1.1, where passwords are 41 bytes long beginning with a single asterisk, and the password() function generates the hash.

mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+-------------------------------------------+-----------------------+
| host      | user             | authentication_string                     | plugin                |
+-----------+------------------+-------------------------------------------+-----------------------+
| localhost | root             | *9B500343BC52E2911172EB52AE5CF4847604C6E5 | mysql_native_password |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 | mysql_native_password |
+-----------+------------------+-------------------------------------------+-----------------------+

Installation without root password

Without a password, root@localhost uses the auth_socket plugin:

mysql> select host,user,authentication_string,plugin from mysql.user;
+-----------+------------------+-------------------------------------------+-----------------------+
| host      | user             | authentication_string                     | plugin                |
+-----------+------------------+-------------------------------------------+-----------------------+
| localhost | root             |                                           | auth_socket           |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | debian-sys-maint | *DA57FEBA9C5C5119186DB8834C7B83216E450117 | mysql_native_password |
+-----------+------------------+-------------------------------------------+-----------------------+

auth_socket only works if you're connecting from localhost through a unix_socket. It simply checks if the username making the connection matches the username in the 'user' field, there is no password.

That's why you can do sudo mysql and use any password you like, or none, but doing mysql -u root -p as an unprivileged user will never work, regardless of what password you type.

The Ansible module is currently unable to set a password unless the plugin is mysql_native_password, so you need to set the plugin value correctly first.

There's already a feature request for fixing this.

Solution

Install MySQL with a password, by using the debconf module to supply it in advance (you have to set it twice, once for the confirmation screen). Then install MySQL, save the password to /root/.my.cnf so root user can connect automatically, and use mysql_user to set whatever other logins you need.

- hosts: myserver
  vars:
    mysql_root_password: foobarbaz

  tasks:
    - name: Specify MySQL root password before installing
      # without this, auth_socket will be used for root@localhost, and we won't be able to set the password
      debconf: name='mysql-server' question='mysql-server/root_password' value='{{mysql_root_password | quote}}' vtype='password'
      become: true

    - name: Confirm MySQL root password before installing
      debconf: name='mysql-server' question='mysql-server/root_password_again' value='{{mysql_root_password | quote}}' vtype='password'
      become: true

    - name: Install MySQL server
      apt: name={{ item }} state=present
      with_items:
        - mysql-server
        - python-mysqldb
      become: true

    - name: Start MySQL
      service: name=mysql state=started
      become: true

    - name: create /root/.my.cnf (from template) with password credentials
      template: src=/etc/ansible/templates/root/.my.cnf dest=/root/.my.cnf owner=root mode=0600
      become: true

    - name: update mysql root password for all root accounts
      mysql_user: name=root host={{ item }} password={{ mysql_root_password }} sql_log_bin=yes priv=*.*:ALL,GRANT
      with_items:
        - "{{ ansible_hostname }}"
        - 127.0.0.1
        - ::1
        - "localhost"
      become: true