William Turrell William Turrell - 1 month ago 14
MySQL Question

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

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
jccdev | SUCCESS => {
"changed": true,
"user": "root"
}


MySQL: Ver 14.14 Distrib 5.7.15

Ubuntu 16.04.1 LTS

Ansible 2.1.2.0

Answer

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: jccdev
  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