Ansible module with mysql_user fails to create new user, if option encrypted is set

[Bernie Hoeneisen]

With MySQL version 8.0, the ansible mysql module runs into an error while creating a new MySQL user with the option "encrypted: yes" (if the MySQL user does not exist yet).

The Bug appears to be in the 'user_add' function in file '/usr/lib/python3/dist-packages/ansible/modules/database/mysql/mysql_user.py' (see arrow below):

def user_add(cursor, user, host, host_all, password, encrypted, new_priv, check_mode):
    # we cannot create users without a proper hostname
    if host_all:
        return False

    if check_mode:
        return True

    if password and encrypted:
---> cursor.execute("CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)) <---
    elif password and not encrypted:
        cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password))
    else:
        cursor.execute("CREATE USER %s@%s", (user, host))
    if new_priv is not None:
        for db_table, priv in iteritems(new_priv):
            privileges_grant(cursor, user, host, db_table, priv)
    return True

Ansible translates this to and tries to execute the following MySQL command which fails (here the output from mysql after retrying):

  mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY PASSWORD "*123456789012345678901234567890";
  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD "*123456789012345678901234567890"' at line 1

The following MySQL command actually does what I am trying to accomplish:

  mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password AS '*123456789012345678901234567890';
  Query OK, 0 rows affected (0.01 sec)

---

ansible-playbook version: 2.9.9
python version = 3.8.6 (default, Jan 27 2021, 15:42:20) [GCC 10.2.0]

[Bernie Hoeneisen (bhoeneis)]

Looks like this has been fixed for later versions of Ansible, however version 2.9.X ships with Ubuntu focal and groovy. (The LTS release focal will still be around for some time.)

Adding a patch to mirror the following code (along with the function impl.supports_identified_by_password() )

    if password and encrypted:
        if impl.supports_identified_by_password(cursor):
            query_with_args = "CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)
        else:
            query_with_args = "CREATE USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, password)

would certainly help.

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.