`mysql_user`: can't get `plugin_hash_string` working with `caching_sha2_password` #621

Aohzan · 2024-04-03T09:13:17Z

SUMMARY

Hello,
I use caching_sha2_password plugin for my users on MySQL 8 servers.
At beginning, I just use

- name: myyser
  community.mysql.mysql_user:
    name: myuser
    plugin_auth_string: "xxxxx"
    plugin: "caching_sha2_password"

but I had idempotence issue because, each time the password was marked as changed because the hash generated by MySQL was different.
So I tried to use plugin_hash_string instead of plugin_auth_string to provide directly the hash generated from ansible to avoid idempotence issue. Like

- name: myyser
  community.mysql.mysql_user:
    name: myuser
    plugin_hash_string:  "$A$005$9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0fd02fe"
    plugin: "caching_sha2_password"

but I have many problems:

the test done on this ansible module is wrong it should use plugin_hash_string instead of plugin_auth_string
As hash generated by MySQL are not string and contains special character, I can't provide a correct hash from ansible, maybe I can initiate a PR to provide a base64 encoded hash that will be decoded before send to MySQL ? Like plugin_hash_string = base64.b64decode(plugin_hash_base64).decode("utf-8")
The lack of documentation of how generate a correct hash is a problem
I found this on MySQL source code, with the hash like $A$005$salthash (salf length 20, hash length 43) but cannot log with it. Maybe a correct example can be added to the ansible documentation ?

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.11/dist-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] (/usr/bin/python3)
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

--------------- -------
community.mysql 3.9.0  
----------------- -------
community.general 8.5.0

CONFIGURATION

OS / ENVIRONMENT

Debian 12

STEPS TO REPRODUCE

example of hash

- name: myyser
  community.mysql.mysql_user:
    name: myuser
    plugin_hash_string: "{{ '$A$005$' ~ 'abcdefghtrdlsorafgthe' ~ ('test' | hash('sha256'))[0:42] }}"
    plugin: "caching_sha2_password"

EXPECTED RESULTS

Changed hash, can connect with password before hash, and idempotence ok

ACTUAL RESULTS

Change hash, cannot connect with password
or
Hash not accepted by MySQL

The text was updated successfully, but these errors were encountered:

laurent-indermuehle · 2024-04-10T09:25:47Z

Hi @Aohzan and thank you for your well documented issue.

Regarding tests using plugin_auth_string instead of plugin_hash_string, I'll fix that in a PR I'm working on (but not yet pushed here). Thank you for bringing that up.

For the non binary characters you are right. The collection need a way to pass them to MySQL and currently the documentation and the tests are lacking.

As I only work with MariaDB, I can't fix those issues myself. But I'll be happy to help with the tests and reviewing a PR.

Aohzan · 2024-04-10T09:29:14Z

Thanks, I will make a PR as soon as I find a solution to generate a working hash 😁

laurent-indermuehle · 2024-04-10T11:39:57Z

Great, let me know when you start working on this. Because I can't fix the test_mysql_info to use plugin_hash_string without a way to pass the hash :

TASK [test_mysql_info : Mysql_info users_info | Prepare tests users for MySQL 8+] ***
failed: [testhost] (item={'name': 'users_info_caching_sha2', 'priv': {'*.*': 'ALL'}, 'plugin_hash_string': '$A$005$61j/uF%Qb4-=O2xkeO82u2HNkF.lxDq0liO4U3xqi7bDUCbWM6HayRXWn1', 'plugin': 'caching_sha2_password'}) => {"ansible_loop_var": "item", "changed": false, "item": {"name": "users_info_caching_sha2", "plugin": "caching_sha2_password", "plugin_hash_string": "$A$005$61j/uF%Qb4-=O2xkeO82u2HNkF.lxDq0liO4U3xqi7bDUCbWM6HayRXWn1", "priv": {"*.*": "ALL"}}, "msg": "(1827, \"The password hash doesn't have the expected format.\")"}

Aohzan · 2024-04-10T12:55:47Z

fyi, I opened a bug report to MySQL https://bugs.mysql.com/bug.php?id=114610&thanks=4

Aohzan · 2024-04-11T09:38:53Z

We made a python script (based on this) to generate a MySQL hash compatible with caching_sha2_password plugin, what is the best way to integrate it in this module ?
Maybe there is a better solution to generate it, but we tested passlib, hashlib and it was never compatible with MySQL format

Maybe a filter will be the best solution, but we need a way for give a hash in hex format to mysql_user

"""Generate MySQL caching_sha2_password hash for a given password and salt."""

import hashlib


def to64(v: int, n: int) -> str:
    """Convert a 32-bit integer to a base-64 string"""
    i64 = (
        [".", "/"]
        + [chr(x) for x in range(48, 58)]
        + [chr(x) for x in range(65, 91)]
        + [chr(x) for x in range(97, 123)]
    )
    result: str = ""
    while n > 0:
        n -= 1
        result += i64[v & 0x3F]
        v >>= 6
    return result


def hashlib_sha256(data: bytes) -> bytes:
    """Return SHA-256 digest from hashlib ."""
    return hashlib.sha256(data).digest()


def sha256_digest(bits: int, key: str, salt: str, loops: int) -> str:
    """Return a SHA-256 digest of the concatenation of the key, the salt, and the key, repeated as necessary."""
    num_bytes: bytes = bits // 8
    bytes_key: bytes = key.encode()
    bytes_salt: bytes = salt.encode()
    b = hashlib_sha256(bytes_key + bytes_salt + bytes_key)

    tmp = bytes_key + bytes_salt
    for i in range(len(bytes_key), 0, -num_bytes):
        tmp += b if i > num_bytes else b[:i]

    i = len(bytes_key)
    while i > 0:
        tmp += b if (i & 1) != 0 else bytes_key
        i >>= 1

    a = hashlib_sha256(tmp)

    tmp = b""
    for i in range(len(bytes_key)):
        tmp += bytes_key

    dp = hashlib_sha256(tmp)

    p = b""
    for i in range(len(bytes_key), 0, -num_bytes):
        p += dp if i > num_bytes else dp[:i]

    tmp = b""
    til = 16 + a[0]

    for i in range(til):
        tmp += bytes_salt

    ds = hashlib_sha256(tmp)

    s = b""
    for i in range(len(bytes_salt), 0, -num_bytes):
        s += ds if i > num_bytes else ds[:i]

    c = a

    for i in range(loops):
        tmp = p if (i & 1) else c
        if i % 3:
            tmp += s
        if i % 7:
            tmp += p
        tmp += c if (i & 1) else p
        c = hashlib_sha256(tmp)

    inc1, inc2, mod, end = (10, 21, 30, 0) if bits == 256 else (21, 22, 63, 21)

    i = 0
    tmp = ""

    while True:
        tmp += to64(
            (c[i] << 16) | (c[(i + inc1) % mod] << 8) | c[(i + inc1 * 2) % mod], 4
        )
        i = (i + inc2) % mod
        if i == end:
            break

    tmp += to64((c[31] << 8) | c[30], 3) if bits == 256 else to64(c[63], 2)

    return tmp


def mysql_caching_sha2_password_hex(password: str, salt: str) -> str:
    """Return a MySQL compatible caching_sha2_password hash in hex format."""
    assert len(salt) == 20, "Salt must be 20 characters long."

    count = 5
    iteration = 1000 * count

    digest = sha256_digest(256, password, salt, iteration)
    return f"$A${count:>03}${salt}{digest}".encode().hex().upper()


mysql_hash = mysql_caching_sha2_password_hex("testpassword", "TDwqdanU82d0yNtvaabb")
print(mysql_hash)
print(
    f"ALTER USER `mysql`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x{mysql_hash};"
)

laurent-indermuehle · 2024-04-11T17:18:23Z

That is quite amazing job you did there @Aohzan to replicate the MySQL behavior!
But I'm not liking the fact that you didn't get an answer from the developers (they never heard of Ansible, seriously?) and I'm a bit worried we will have issue maintaining that.

Do you think that adding the above method as a filter is enough? Or do we need to add some new options like 'salt' to the module for caching_sha2_password to fully works?

We need to be 100% sure that your method will always produce the same hash as MySQL 8+ expects. Do you know if there has been any changes on the those authentication plugins in MySQL 8.1, 8.2 and 8.3?

Aohzan · 2024-04-11T21:18:34Z

That is quite amazing job you did there @Aohzan to replicate the MySQL behavior! But I'm not liking the fact that you didn't get an answer from the developers (they never heard of Ansible, seriously?) and I'm a bit worried we will have issue maintaining that.

Yes I agree 😞

Do you think that adding the above method as a filter is enough? Or do we need to add some new options like 'salt' to the module for caching_sha2_password to fully works?

Option 1:

a filter to generate the hash from a string, with an optional or required salt
a parameter in mysql_user to give a hash in hex format like plugin_hash_hex (or modify plugin_hash_string to detect the hex format)

Option 2:

a parameter to give a salt in mysql_user and generate the hash from the password given in plugin_auth_string

Maybe an other option ?

We need to be 100% sure that your method will always produce the same hash as MySQL 8+ expects. Do you know if there has been any changes on the those authentication plugins in MySQL 8.1, 8.2 and 8.3?

I will test it to be sure, but I think it works in the same way

laurent-indermuehle · 2024-04-12T11:52:31Z

I prefer Option 2.
We can detect the usage of caching_sha2_password from the plugin option.
Then we can make a salt option mandatory when using plugin_auth_string.

The C source is not very simple: https://github.com/mysql/mysql-server/blob/ea7d2e2d16ac03afdd9cb72a972a95981107bf51/sql/auth/sha2_password.cc
but it would be great to be able to wrap it from Python.

Regarding your offered solution, it has a bit too much variables with one or two letters to my taste. It makes it hard to understand what is going on. For instance, what are the variables a, dp, ds?

Aohzan · 2024-04-12T14:04:56Z

it's the name of digest used in the algorithm: https://www.akkadia.org/drepper/SHA-crypt.txt
I update the script to add the link, and renamed with digest_ prefix those variables

Aohzan · 2024-04-12T14:10:07Z

there is the updated script

"""Generate MySQL caching_sha2_password hash for a given password and salt."""

import hashlib


def to64(v: int, n: int) -> str:
    """Convert a 32-bit integer to a base-64 string"""
    i64 = (
        [".", "/"]
        + [chr(x) for x in range(48, 58)]
        + [chr(x) for x in range(65, 91)]
        + [chr(x) for x in range(97, 123)]
    )
    result: str = ""
    while n > 0:
        n -= 1
        result += i64[v & 0x3F]
        v >>= 6
    return result


def hashlib_sha256(data: bytes) -> bytes:
    """Return SHA-256 digest from hashlib ."""
    return hashlib.sha256(data).digest()


def sha256_digest(key: str, salt: str, loops: int) -> str:
    """Return a SHA-256 digest of the concatenation of the key, the salt, and the key, repeated as necessary."""
    # https://www.akkadia.org/drepper/SHA-crypt.txt
    num_bytes: bytes = 32
    bytes_key: bytes = key.encode()
    bytes_salt: bytes = salt.encode()
    digest_b = hashlib_sha256(bytes_key + bytes_salt + bytes_key)

    tmp = bytes_key + bytes_salt
    for i in range(len(bytes_key), 0, -num_bytes):
        tmp += digest_b if i > num_bytes else digest_b[:i]

    i = len(bytes_key)
    while i > 0:
        tmp += digest_b if (i & 1) != 0 else bytes_key
        i >>= 1

    digest_a = hashlib_sha256(tmp)

    tmp = b""
    for i in range(len(bytes_key)):
        tmp += bytes_key

    digest_dp = hashlib_sha256(tmp)

    byte_sequence_p = b""
    for i in range(len(bytes_key), 0, -num_bytes):
        byte_sequence_p += digest_dp if i > num_bytes else digest_dp[:i]

    tmp = b""
    til = 16 + digest_a[0]

    for i in range(til):
        tmp += bytes_salt

    digest_ds = hashlib_sha256(tmp)

    byte_sequence_s = b""
    for i in range(len(bytes_salt), 0, -num_bytes):
        byte_sequence_s += digest_ds if i > num_bytes else digest_ds[:i]

    digest_c = digest_a

    for i in range(loops):
        tmp = byte_sequence_p if (i & 1) else digest_c
        if i % 3:
            tmp += byte_sequence_s
        if i % 7:
            tmp += byte_sequence_p
        tmp += digest_c if (i & 1) else byte_sequence_p
        digest_c = hashlib_sha256(tmp)

    inc1, inc2, mod, end = (10, 21, 30, 0)

    i = 0
    tmp = ""

    while True:
        tmp += to64(
            (digest_c[i] << 16) | (digest_c[(i + inc1) % mod] << 8) | digest_c[(i + inc1 * 2) % mod], 4
        )
        i = (i + inc2) % mod
        if i == end:
            break

    tmp += to64((digest_c[31] << 8) | digest_c[30], 3)

    return tmp


def mysql_caching_sha2_password_hex(password: str, salt: str) -> str:
    """Return a MySQL compatible caching_sha2_password hash in hex format."""
    assert len(salt) == 20, "Salt must be 20 characters long."

    count = 5
    iteration = 1000 * count

    digest = sha256_digest(password, salt, iteration)
    return f"$A${count:>03}${salt}{digest}".encode().hex().upper()


mysql_hash = mysql_caching_sha2_password_hex("testpassword", "TDwqdanU82d0yNtvaabb")
print(mysql_hash)
print(
    f"ALTER USER `testuser1`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x{mysql_hash};"
)

laurent-indermuehle · 2024-04-12T14:41:17Z

Thanks for the revision of your code @Aohzan. It's better now. I'm not saying that I understand everything thought ;)

I must be too tired... caching_sha2_password:
When I create a user on Mysql 8.3 using plugin_auth_string it's not idempotent. As you noticed.
When I create the same user using plugin_hash_string I get 1827, The password hash doesn't have the expected format. As we knew.

But when I grab the user info using mysql_info and pass them to mysql_user then it works and in addition it is idempotent !??! Why !?!

I used this tests from my in progress PR : https://github.com/ansible-collections/community.mysql/pull/629/files#diff-0a20e475385b180622c5b82eba4351e27488c54255c6ca7cd3435abf82a2b51f

First good news is that the salt is optional, right?

laurent-indermuehle · 2024-04-12T14:45:15Z

I managed to find the successful test in the logs: https://github.com/ansible-collections/community.mysql/actions/runs/8663542726/job/23757899256#step:11:4183
Do you have an idea why this is working for me @Aohzan?

tchernomax · 2024-04-16T20:08:25Z

But when I grab the user info using mysql_info and pass them to mysql_user then it works and in addition it is idempotent !??! Why !?!

From what I understood.

https://dev.mysql.com/doc/refman/8.3/en/create-user.html

For syntax that uses AS 'auth_string', …

A hashed string can be either a string literal or a hexadecimal value.

so when using IDENTIFIED WITH auth_plugin AS 'auth_string', auth_string can be a literal string or it's representation in hexadecimal.

the "literal string" auth_string in the case of caching_sha2_password contains some binary value as this plugin doesn't limit itself to the utf-8 encoding range.
→ which make it very hard to pass auth_string as a "literal string" in yaml (since you have to write "impossible letters"/binary in your yaml text file)

https://github.com/ansible-collections/community.mysql/actions/runs/8663542726/job/23757899256#step:11:4183

… "plugin_hash_string": "$A$005$\u0013;1C8\u000el\u001…

Here mysql_info get the "literal string" binary value directly from mysql and convert it is python/yaml compatible string in a variable, which you then pass to mysql_user → so it works.

A much more realistic approach is to pass auth_string as an hexadecimal (which is easily writable in a yaml text file).

First good news is that the salt is optional, right?

I am not sure I get what you mean.

If you mean : we can use an empty salt in the hashing algorithm

The salt is an important part of the hash algorithm security.
Without salt it might work, but the hash become much more vulnerable to "rainbow table" attack.
If you mean: let's not provide the salt option to the user and generate it randomly.

Then it defy the whole purpose of plugin_hash_hex (or plugin_hash_string) option.
Because the hash will change every time the module is run and it will report a change even if, internally, the password will be the same.

laurent-indermuehle · 2024-04-18T10:59:54Z

@tchernomax thanks for taking the time to clarify things for me.
Ok so, what is needed to create/change a caching_sha2_password is:

A way to provide a clear text password to plugin_auth_string + a salt
A way to provide a hashed password to plugin_hash_string that can be stored in the ansible inventory safely.

I'm not sure I got this right.
Also, a PR would be welcome, I don't have time for MySQL (barely have enough for MariadB).

Aohzan · 2024-04-19T06:27:50Z

Yes we're starting to work on it

laurent-indermuehle mentioned this issue Apr 12, 2024

Fix hashed passwords being returned by get_existing_authentication() via the plugin_auth_string variable instead of plugin_hash_string #629

Merged

Aohzan mentioned this issue Apr 19, 2024

Add salt parameter to hash generation for sha256 plugins #631

Merged

laurent-indermuehle closed this as completed in #631 Jun 11, 2024

marbonilla mentioned this issue Jun 18, 2024

feat: create playbook to install mysql8.4 eduNEXT/atlas-ansible-utils#33

Merged

mcanevet mentioned this issue Sep 11, 2024

creating user on aws rds mysql 8.0.35 petoju/terraform-provider-mysql#116

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

`mysql_user`: can't get `plugin_hash_string` working with `caching_sha2_password` #621

`mysql_user`: can't get `plugin_hash_string` working with `caching_sha2_password` #621

Aohzan commented Apr 3, 2024 •

edited

Loading

laurent-indermuehle commented Apr 10, 2024

Aohzan commented Apr 10, 2024

laurent-indermuehle commented Apr 10, 2024

Aohzan commented Apr 10, 2024

Aohzan commented Apr 11, 2024 •

edited

Loading

laurent-indermuehle commented Apr 11, 2024

Aohzan commented Apr 11, 2024 •

edited

Loading

laurent-indermuehle commented Apr 12, 2024

Aohzan commented Apr 12, 2024

Aohzan commented Apr 12, 2024

laurent-indermuehle commented Apr 12, 2024

laurent-indermuehle commented Apr 12, 2024

tchernomax commented Apr 16, 2024

laurent-indermuehle commented Apr 18, 2024

Aohzan commented Apr 19, 2024

mysql_user: can't get plugin_hash_string working with caching_sha2_password #621

mysql_user: can't get plugin_hash_string working with caching_sha2_password #621

Comments

Aohzan commented Apr 3, 2024 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

COLLECTION VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

laurent-indermuehle commented Apr 10, 2024

Aohzan commented Apr 10, 2024

laurent-indermuehle commented Apr 10, 2024

Aohzan commented Apr 10, 2024

Aohzan commented Apr 11, 2024 • edited Loading

laurent-indermuehle commented Apr 11, 2024

Aohzan commented Apr 11, 2024 • edited Loading

laurent-indermuehle commented Apr 12, 2024

Aohzan commented Apr 12, 2024

Aohzan commented Apr 12, 2024

laurent-indermuehle commented Apr 12, 2024

laurent-indermuehle commented Apr 12, 2024

tchernomax commented Apr 16, 2024

laurent-indermuehle commented Apr 18, 2024

Aohzan commented Apr 19, 2024

`mysql_user`: can't get `plugin_hash_string` working with `caching_sha2_password` #621

`mysql_user`: can't get `plugin_hash_string` working with `caching_sha2_password` #621

Aohzan commented Apr 3, 2024 •

edited

Loading

Aohzan commented Apr 11, 2024 •

edited

Loading

Aohzan commented Apr 11, 2024 •

edited

Loading