does not work a create user with a password hash and the caching_sha2_password plugin

[Hramoff]

SUMMARY

It is not possible to create a user with the caching_sha2_password plugin using plugin_hash_string.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.mysql.mysql_user

ANSIBLE VERSION

ansible [core 2.12.4]
  python version = 3.9.1 (default, Aug 18 2023, 15:40:04) [GCC 12.2.0]
  jinja version = 3.1.1
  libyaml = True

COLLECTION VERSION

Collection      Version
--------------- -------
community.mysql 3.8.0 

CONFIGURATION

CALLBACKS_ENABLED(/home/maxsim-sergeevich/Desktop/ansible/ansible.cfg) = ['ansible.posix.profile_tasks']
DEFAULT_FORKS(/home/maxsim-sergeevich/Desktop/ansible/ansible.cfg) = 5
DEFAULT_HASH_BEHAVIOUR(/home/maxsim-sergeevich/Desktop/ansible/ansible.cfg) = merge
DEFAULT_MODULE_PATH(/home/maxsim-sergeevich/Desktop/ansible/ansible.cfg) = ['/home/maxsim-sergeevich/Desktop/ansible/modules']
DEFAULT_ROLES_PATH(/home/maxsim-sergeevich/Desktop/ansible/ansible.cfg) = ['/home/maxsim-sergeevich/.ansible/roles', '/usr/share/ansible/roles', '/etc/ansible/roles', '/home/maxsim-sergeevich/Desktop/ansible/roles']

OS / ENVIRONMENT

No LSB modules are available.
Distributor ID: Debian
Description:    Debian GNU/Linux 12 (bookworm)
Release:        12
Codename:       bookworm

STEPS TO REPRODUCE

- name: user
  mysql_user:
    login_unix_socket: /var/run/mysqld/mysqld.sock
    login_user: "{{ mysql_database_root_user }}"
    login_password: "{{ mysql_root_password }}"
    name: user1
    plugin: caching_sha2_password
    plugin_hash_string: "$A$005$7s0af617373776f7264"
    host: "%"
    encrypted: "true"
    priv: '*.*:ALL'
  tags:
    - qwerty

EXPECTED RESULTS

Must create a user with username user1, plugin - caching_sha2_password and password - $A$005$7s0af617373776f7264

ACTUAL RESULTS

fatal: [my-db-host]: FAILED! => {"changed": false, "msg": "(1827, \"The password hash doesn't have the expected format.\")"}

[Andersson007]

@Hramoff hello, thanks for reporting the issue. Is it something that've appeared after upgrading from an earlier collection version to the latest or you just discovered it accidentally? If this started to appear after an update, could you please specify the exact version where it worked? Thanks

[laurent-indermuehle]

I think I recall having to convert the password with a weird command when I wrote this: https://github.com/ansible-collections/community.mysql/blob/main/tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml#L192-L213

I think it's print_identified_with_as_hex from https://dev.mysql.com/doc/refman/8.0/en/show-create-user.html

Does that help?

[Andersson007]

@Hramoff ^

[Hramoff]

I'm sorry that I disappeared for a while, but I managed to analyze your advice. I'll answer everyone now.

It is important to me to be able to create user using exactly the HASH of their script. We use a special application that stores password hashes in mysql format for our employees.

I answer @Andersson007. We decided to start upgrading our databases to MySQL 8, and at the same time change the authentication plugin from mysql_native_password to cache_sha2_password, and when I tried to implement this through Ansible, I ran into this problem.

I answer @laurent-indermuehle . I have little understanding of testing roles and playbooks, but I know Ansible quite well. You create a user by passing this text with a password, which is then converted to a hash (https://github.com/ansible-collections/community.mysql/blob/81ab18d56c64f64cb5bc369ce7fc79ff1aba1eed/tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml#L209)

so you can enter through it. The plugin_hash_string parameter is not used at all. Relative solutions with HEX.

If i user this task:

- name: ADD USER
  community.mysql.mysql_user:
    login_unix_socket: /var/run/mysqld/mysqld.sock
    login_user: root
    login_password: "{{ mysql_root_password }}"
    name: asdfd
    state: present
    plugin: caching_sha2_password
    priv:  "*.*: ALL"
    plugin_hash_string: '0x244124303035241D5D2E5F634A622A6550394535296D1404453C4D4146644B5644765A6A732E516D57614C785874723631416C486E436B74707541447639564E435870557537'
  tags: qwerty

then I get an error:

fatal: [myserv]: FAILED! => {"changed": false, "msg": "(1827, \"The password hash doesn't have the expected format.\")"}

what is 0x224... ?

this is the result of the command:

SHOW CREATE USER 'asdf'@'localhost'\GG
*************************** 1. row ***************************
CREATE USER for asdf@localhost: CREATE USER `asdf`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035241D5D2E5F634A622A6550394535296D1404453C4D4146644B5644765A6A732E516D57614C785874723631416C486E436B74707541447639564E435870557537 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0,00 sec)

I can copy this and create a user on a different base quite successfully:

CREATE USER `zxc`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035241D5D2E5F634A622A6550394535296D1404453C4D4146644B5644765A6A732E516D57614C785874723631416C486E436B74707541447639564E435870557537 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
Query OK, 0 rows affected (0,01 sec)

But if I pass this value to the playbook then the known error appears.

It seems to me that you need to look at the code - https://github.com/ansible-collections/community.mysql/blob/81ab18d56c64f64cb5bc369ce7fc79ff1aba1eed/plugins/module_utils/user.py#L187C5-L187C5

I'm not really a developer, but I can think.

here a request is generated to create a user (https://github.com/ansible-collections/community.mysql/blob/81ab18d56c64f64cb5bc369ce7fc79ff1aba1eed/plugins/module_utils/user.py#L188C27-L188C113)

if you take this request and insert the data into it yourself, for example

CREATE USER testuser@localhost IDENTIFIED WITH caching_sha2_password AS "0x00200000... ;

then you can see the error

CREATE USER `u2`@`localhost` IDENTIFIED WITH caching_sha2_password AS '0x24412430303524042C31091B0C7D1217551547705B2129163301094F4B454D61456F4352514461714133644C7349726A416D54643958617762504675664F426E6A766A4E6241';
ERROR 1827 (HY000): The password hash doesn't have the expected format.

I also checked the options - pass the password in clear text, pass the sha2 and HEX hash. None of these options work and it seems that the problem is in MySQL 8 itself.

[laurent-indermuehle]

@Hramoff thanks for your analys. I'm not sure I get your conclusion. To me the working command doesn't enclose the hash in quotes. When you use quote you get the error message. And you can't remove the quote if you use community.mysql. Are you able to reproduce this with the integrations tests?