proxysql_mysql_users is not able to handle hashed passwords

[markuman]

SUMMARY

proxysql_mysql_users handles passwords only in plaintext.
Once MYSQL USERS is loaded to runtime, proxysql is hashing the passwords automatically. That means:

• mysql_servers table holds passwords in plaintext
• runtime_mysql_servers table holds passwords hashed.

When you now load MYSQL USERS from runtime, and save them to disk, mysql_servers holds the user passwords also hashed. But after that, proxysql_mysql_users is not able to handle this anymore and is trying to create a new user. This results in errors because of table constraints.

example: (here is a full playbook example)

    - name: add user to proxysql
      proxysql_mysql_users:
        state: present
        username: someuser
        password: password
        default_hostgroup: 0
        login_user: admin
        login_password: admin
        load_to_runtime: yes

    - name: idempotent add user to proxysql
      proxysql_mysql_users:
        state: present
        username: someuser
        password: password
        default_hostgroup: 0
        login_user: admin
        login_password: admin
        load_to_runtime: yes

    - name: load users back to memory because they are hashed in runtime
      proxysql_manage_config:
        login_user: admin
        login_password: admin
        action: "SAVE"
        config_settings: MYSQL USERS
        direction: "FROM"
        config_layer: "RUNTIME"

    - name: save hashed users also to disk
      proxysql_manage_config:
        login_user: admin
        login_password: admin
        action: "SAVE"
        config_settings: MYSQL USERS
        direction: "TO"
        config_layer: "DISK"

    - name: idempotent add user to proxysql
      proxysql_mysql_users:
        state: present
        username: someuser
        password: password
        default_hostgroup: 0
        login_user: admin
        login_password: admin
        load_to_runtime: yes

Result

TASK [add user to proxysql] *********************************************************************************************************************************************************************************************************
[WARNING]: Module did not set no_log for encrypt_********
changed: [proxysql]

TASK [idempotent add user to proxysql] **********************************************************************************************************************************************************************************************
ok: [proxysql]

TASK [load users back to runtime because they are hashed in runtime] ****************************************************************************************************************************************************************
changed: [proxysql]

TASK [save hashed users also to disk] ***********************************************************************************************************************************************************************************************
changed: [proxysql]

TASK [idempotent add user to proxysql] **********************************************************************************************************************************************************************************************
fatal: [proxysql]: FAILED! => {"changed": false, "msg": "unable to modify user.. (1045, 'ProxySQL Admin Error: UNIQUE constraint failed: mysql_users.username, mysql_users.frontend')"}

References:

• https://proxysql.com/documentation/password-management/
• https://proxysql.com/documentation/configuring-proxysql/

select password('some-password') as password; function is not available in proxysql.
therefore the hash-creation must be implemented within python.

maybe we need a new parameter to control if a password should be hashed or not ...

ISSUE TYPE

• Bug Report

COMPONENT NAME

proxysql_mysql_users

[markuman]

Well, there is a parameter already. blame me. encrypt_password:
the only question left is, if we should/can fix the behaviour above, and detect if the unterlying password is hashed already.

[markuman]

Ok, the error persists when I use in the last task from the example above encrypt_password: yes

    - name: idempotent add user to proxysql
      proxysql_mysql_users:
        state: present
        username: someuser
        password: password
        default_hostgroup: 0
        login_user: admin
        login_password: admin
        load_to_runtime: yes
        encrypt_password: yes

[Andersson007]

maybe there's something in community.mysql.mysql_user that can help

[markuman]

Using only encrypted_password: yes works perfectly. There is no need to use the workaround when you're already using community.proxysql.
When you do the workaround, proxysql_mysql_users will fail. Imo, we should not waste time on fixing that.

What is not working is, when you're using plaintext password first (default), and want to change to encrypted_password.
This will fail with the same error message.
That should be fixed imo.

something like that

if requested_password != existing_password or encrypt_cleartext_password(requested_password) == existing_password:
    # we can change
elif:
  # we cannot handle this because we don't know if the password just changed