unix_socket authentication plugin is removed

[Yannik]

SUMMARY

On MariaDB >= 10.4, the default configuration is that the root user may use unix_socket authentication or mysql_native_password authentication: https://mariadb.com/kb/en/authentication-from-mariadb-104/

Setting the password for the root user with mysql_user removes the unix_socket authentication plugin, breaking all system scripts relying on it.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.9.25
  config file = /home/yannik/ansible/ansible.cfg
  configured module search path = ['/home/yannik/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.7 (default, Aug 30 2021, 00:00:00) [GCC 10.3.1 20210422 (Red Hat 10.3.1-1)]

COLLECTION VERSION

ansible-galaxy collection: error: argument COLLECTION_ACTION: invalid choice: 'list' (choose from 'init', 'build', 'publish', 'install')

CONFIGURATION

ANSIBLE_NOCOWS(/home/yannik/ansible/ansible.cfg) = True
DEFAULT_HOST_LIST(/home/yannik/ansible/ansible.cfg) = ['/home/yannik/ansible/hosts']
DEFAULT_ROLES_PATH(/home/yannik/ansible/ansible.cfg) = ['/home/yannik/ansible/vendor_roles']
RETRY_FILES_ENABLED(/home/yannik/ansible/ansible.cfg) = False

OS / ENVIRONMENT

Fresh mariadb-server 10.5.12 installation on debian 11.

STEPS TO REPRODUCE

Install mariadb-server >= 10.4.

unix_socket authentication for the root user works.

Output of select json_detailed(priv) from mysql.global_priv where User = 'root'; is:

| {
    "access": 549755813887,
    "plugin": "mysql_native_password",
    "authentication_string": "invalid",
    "auth_or": 
    [

        {
            "plugin": "unix_socket"
        },

        {
        }
    ],
    "version_id": 100512,
    "password_last_changed": 1635248824
} |

Now execute the following playbook:

- name: Set MariaDB root password
    mysql_user:
    name: root
    password: "secret"
    login_unix_socket: /var/run/mysqld/mysqld.sock

EXPECTED RESULTS

The results should match what set password = password('secret'); results in:

| {
    "access": 549755813887,
    "plugin": "mysql_native_password",
    "authentication_string": "*14E65567ABDB5135D0CFD9A70B3032C179A49EE7",
    "auth_or": 
    [

        {
            "plugin": "unix_socket"
        },

        {
        }
    ],
    "version_id": 100512,
    "password_last_changed": 1635248954
} |

ACTUAL RESULTS

unix_socket authentication does not work anymore and has been removed from mysql.global_priv:

| {
    "access": 549755813887,
    "plugin": "mysql_native_password",
    "authentication_string": "*14E65567ABDB5135D0CFD9A70B3032C179A49EE7",
    "auth_or": 
    [

        {
        }
    ],
    "version_id": 100512,
    "password_last_changed": 1635248912
} |

[Andersson007]

@Yannik hi, thanks for reporting this! Is there a chance you try the task with the latest version of Ansible?

[Yannik]

I can confirm the issue is also present with the latest ansible version:

ansible [core 2.11.6] 
  config file = /home/yannik/ansible/ansible.cfg
  configured module search path = ['/home/yannik/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/yannik/.local/lib/python3.9/site-packages/ansible
  ansible collection location = /home/yannik/ansible/vendor_collections
  executable location = /usr/local/bin/ansible
  python version = 3.9.7 (default, Aug 30 2021, 00:00:00) [GCC 10.3.1 20210422 (Red Hat 10.3.1-1)]
  jinja version = 2.11.3
  libyaml = True

and latest community.mysql collection:

# /home/yannik/ansible/vendor_collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.1  

[Andersson007]

@Yannik thanks for the feedback

[Andersson007]

For information: I marked this PR with help_wanted. Any help would be much appreciated. If someone figures out how to fix this, we have the Quick start guide which should help set up everything needed locally and submit a PR.

[topconf]

Any news about this?

To keep unix_socket authentication when changing the root password by mysql_user I just had to use this gag

I replaced this:

---
- hosts: all
  tasks:
    - name: Set root password
      mysql_user:
        user: root
        password: "{{ mysql_root_password }}"
      no_log: yes

to:

---
- hosts: all
  tasks:
    - name: Set root password
      mysql_query:
        query: >
          ALTER USER root@localhost
          IDENTIFIED VIA mysql_native_password USING PASSWORD("{{ mysql_root_password }}")
          OR unix_socket;
      no_log: yes

[laurent-indermuehle]

@topconf To me, It's a problem with MariaDB. I tested on a 10.4.25:

MariaDB [(none)]> show create user mysql@localhost;
+------------------------------------------------------------+
| CREATE USER for mysql@localhost                            |
+------------------------------------------------------------+
| CREATE USER `mysql`@`localhost` IDENTIFIED VIA unix_socket |
+------------------------------------------------------------+

MariaDB [(none)]> alter user mysql@localhost identified by 'redacted';

MariaDB [(none)]> show create user mysql@localhost;
+------------------------------------------------------------------------+
| CREATE USER for mysql@localhost                                        |
+-------------------------------------------------------------------------+
| CREATE USER `mysql`@`localhost` IDENTIFIED BY PASSWORD 'hash-redacted' |
+-------------------------------------------------------------------------+

alter user mysql@localhost identified via mysql_native_password using password("manager") or unix_socket;

MariaDB [(none)]> show create user mysql@localhost;
+------------------------------------------------------------------------------------------------------------------+
| CREATE USER for mysql@localhost                                                                                |
+--------------------------------------------------------------------------------------------------------------+
| CREATE USER `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'hash-redacted' OR unix_socket |
+--------------------------------------------------------------------------------------------------------------------+

But we could modify the plugins to allow to specify a list of plugins we want enabled.

Before that, I'm interested in the use case. In mine, I don't need this. root@localhost and mysql@localhost are both local account with all privileges. And I have a 3rd admin account with a password that I use for remote connections.

[laurent-indermuehle]

@topconf could you please tell us your use case?