Changing the MySQL root User from auth_socket to Password Authentication Using Ansible

MySQL 5.7 introduced a change in which at install time, the root user is configured using the auth_socket authentication plugin. It means that if you start the MySQL client from the shell as root (connecting through the socket, as the name of the plugin implies,) then you’re automatically authenticated, without a password, based on your user ID on the system. It’s a good thing, because it’s secure out of the box and there’s no root password to set.

But sometimes you have a setup where you need an old-style password on the root user. In my case, I wanted to replace a Ubuntu development machine with an up-to-date one, and I wanted to provision it with Ansible. We had a lot of PHP projects in development running on the old machine, and they all connected to MySQL using the root user, so it wasn’t very practical to create a new user to work around the new plugin set-up. Unfortunately, at the time of this writing, the Ansible mysql_user module does not support authentication plugins, and will fail if you try to set a password on the root user.

Updating the Ansible mysql_user module to support the new plugin seemed the right thing to do, but it turned out to be more complicated than I expected. Authentication plugins were introduced in MariaDB, then found their way into MySQL, and the plugin and column names, as well as the syntax to some commands changed a lot between flavours and versions. Thus it’s hard to test your changes, even with the help of Docker.

I finally found a pretty decent workaround using the unaltered Ansible mysql_user module and some SQL commands, and I want to share it here.

Disclaimer: This method has not been extensively tested, and although it work for the author, is likely to be insecure. Use it only on development VMs or in other non-critical situations.

The concept is to first create a new auth_socket user for Ansible to use, and to delete and recreate the root user with a password if it’s using the auth_socket plugin.

At the base of our trick is a task file that we are going to import for all the users we want to alter:

# mysql_setup_user.yml
# 
# Setup a MySQL user, supporting both the mysql_native_password
# and auth_socket plugins.
# 
# Variables used:
# - login_user: auth_socket user to invoke the MySQL client with (default: root)
# - username: user to configure
# - plugin: authentication plugin for username (default: mysql_native_password)
# - password: password for username
# - priv: privileges to grant
# - host: host to grant privileges for

- name: Apply default values
  set_fact:
    actual_login_user: '{{ login_user | default("root") }}'
    actual_plugin: '{{ plugin | default("mysql_native_password") }}'
    actual_host: '{{ host | default("localhost") }}'

# Check existence and authentication plugin of the user to configure
- name: 'Get {{ username }}@{{ actual_host }} user'
  shell: |
    mysql -u {{ actual_login_user }} -p'{{ login_password }}' -NBe 'SELECT plugin FROM mysql.user WHERE Host="{{ actual_host }}" AND User = "{{ username }}"'
  failed_when: no
  changed_when: no
  register: mysql_plugin_output

# User exists if the output of the previous task is not empty
# The auth plugin is the output of the previous task
- name: 'Store {{ username }} user info'
  set_fact:
    user_exists: '{{ mysql_plugin_output.stdout != "" }}'
    user_current_plugin: '{{ mysql_plugin_output.stdout }}'

# If the requested user doesn’t exist and the plugin is mysql_native_password,
# create it using the mysql_user module
- name: 'Create {{ username }} user (if plugin == mysql_native_password)'
  mysql_user:
    login_user: '{{ actual_login_user }}'
    login_password: '{{ login_password }}'
    name: '{{ username }}'
    host: '{{ actual_host }}'
  when: 'actual_plugin == "mysql_native_password" and not user_exists'

# If the requested user doesn’t exist but the plugin is auth_socket,
# then we can’t create it using the mysql_user module, so we resort
# to a shell command
- name: 'Create {{ username }} user (if plugin == auth_socket)'
  shell: |
    mysql -u {{ actual_login_user }} -p'{{ login_password }}' -NBe 'CREATE USER "{{ username }}"@"{{ actual_host }}" IDENTIFIED WITH auth_socket'
  when: 'actual_plugin == "auth_socket" and not user_exists'

# Change the plugin if needed, providing the password if the new plugin
# is mysql_native_password.
# It only supports a clear text password, so use a vault.
- name: 'Alter {{ username }} user to use {{ actual_plugin }}'
  shell: |
    mysql -u {{ actual_login_user }} -p'{{ login_password }}' -NBe 'ALTER USER "{{ username }}"@"{{ actual_host }}" IDENTIFIED WITH {{ actual_plugin }} {% if actual_plugin == "mysql_native_password" %}BY "{{ password }}"{% endif %}'
  when: 'user_current_plugin != actual_plugin'

# If the plugin is mysql_native_password, update the user using Ansible mysql_user,
# to change its password.
- name: 'Update {{ username }} user (if plugin == mysql_native_password)'
  mysql_user:
    login_user: '{{ actual_login_user }}'
    login_password: '{{ login_password }}'
    name: '{{ username }}'
    password: '{{ password }}'
    host: '{{ actual_host }}'
  when: 'actual_plugin == "mysql_native_password"'

# Grant privileges
- name: 'Grant privileges to user {{ username }}'
  mysql_user:
    login_user: '{{ actual_login_user }}'
    login_password: '{{ login_password }}'
    name: '{{ username }}'
    host: '{{ actual_host }}'
    priv: '{{ priv }}'

We are going to use this task file in two plays: one to set-up a new auth_socket user so we can alter the root user, and the other one to actually alter it:

# mysql-root-playbook.yml

- name: Set MySQL root password
  hosts: all
  become: yes

  tasks:
    - name: Create user Ansible for MySQL administration with auth_socket
      user:
        name: 'ansible'
        state: present
        createhome: no

    - name: Setup ansible user
      include_tasks: mysql_setup_user.yml
      vars:
        username: ansible
        login_password: '{{ mysql_root_password }}'
        plugin: auth_socket
        priv: '*.*:ALL,GRANT'

- name: Configure MySQL root user (using user ansible)
  hosts: all
  become: yes
  become_user: ansible

  tasks:
    - name: Setup root user
      include_tasks: mysql_setup_user.yml
      vars:
        login_user: ansible
        login_password: '{{ mysql_root_password }}'
        username: root
        plugin: mysql_native_password
        password: '{{ mysql_root_password }}'
        priv: '*.*:ALL,GRANT'
        host: '{{ item }}'
      with_items:
        - 'localhost'
        - '127.0.0.1'
        - '%'

Now if you set variable mysql_root_password and run the above playbook with ansible-playbook -i hosts.yml mysql-root-playbook.yml (replace hosts.yml with your actual inventory), the following is going to happen:

The first play, as root, will:

• Create a new system user, ansible;
• Create MySQL user ansible@localhost with plugin auth_socket (no password needed) and full privileges, using the current root user with auth_socket.

The second play, as user ansible will:

• See that root@localhost is using plugin auth_socket;
• Alter the root user to use mysql_native_password and the desired password.

If you run the playbook again, it will see (as root with the newly set password) that the ansible user is configured as desired, and them as ansible with auth_socket that the root user is configured as desired as well.

Three drawbacks to this method:

• It’s quite verbose;
• It runs shell commands with the MySQL password for root in the command line;
• It will fail if you subsequently change the root password, because it won’t be able to connect as root with a password.

However, this is a workaround intended for development machines, so it doesn’t really matter. On a production machine, you should be perfectly happy with using auth_socket for root@localhost.