mysql_user: add option to reuse password of existing user when creating a new one

[betanummeric]

ISSUE TYPE

• Feature Idea

COMPONENT NAME

mysql_user

SUMMARY

In MySQL/MariaDB the password is a property of a username-source-address-combination. However, I want to use the same password per username, for all source addresses of that username. (I only need password authentication, I'm not sure how this generalizes to other authentication methods).
For that, I would need to reuse the existing password when creating a new user with the same name. This new behavior could be enabled with a new value for the existing module argument: update_password: on_new_username.

For further processing, I need a password_changed boolean in the module return object. This could/should also be used to indicate whether the password of an existing user was changed.

I can submit a PR for this.

EXAMPLE

When I request a new user with

- name: add user with other IP
  community.mysql.mysql_user:
    state: present
    name: "test"
    host: "2001:db8::1"
    password: "newpassword"
    update_password: on_new_username

• and the user "test"@"localhost" already exists with password existingpassword, I would like to get the user "test"@"2001:db8::1" with password existingpassword (and ignore the newpassword). password_changed should be false.
• and there are multiple preexisting users with the same name, but varying passwords, I suggest to fall back to the supplied newpassword. password_changed should be true.
• and there is no user called test, the "test"@"2001:db8::1" user is created normally with the password newpassword. password_changed should be true.

[hubiongithub]

Hello @betanummeric

Can't this be solved outside of mysql.user by using items and adding hosts to the list as required:

- name: add user with other IP
  community.mysql.mysql_user:
    state: present
    name: "test"
    host: {{ item }}
    password: "password you want for these users"
    update_password: on_create/always       # depending on wether ansible is the truth or the database over time
  with_items:
    - host1
    - host2
    - ...

This works for all kind of auth methods and no need to read the hashes ...

[betanummeric]

Hi @hubiongithub, my point is that I want to reuse a password/hash which only exists on the mysql/mariadb instance - I don't know it in the inventory or playbook. (The truth lies in the database, not in ansible.) Of course I can query the existing hash outside of the mysql_user module and pass it there with update_password: on_create, but I thought it would be better placed in the module.

My PR just looks for an existing plugin-authentication_string-combination, so it should work with all kinds of authentication methods.

[rsicart]

Hi @betanummeric

As @hubiongithub already said, you have an easy alternative solution, which is storing credentials in a vault and setting them with a loop, using same credentials for same usernames.

It seems a very specific use case to me. In what situation do you need to manage database users without managing their credentials?

[betanummeric]

Hi @rsicart

I have a desired state of database users, each of which consists of a name and a list of allowed source addresses. I am using the module to ensure the desired users exist on the MySQL/MariaDB instance. I create a user with a generated initial password and send the password to the person who owns the user with the request to change it. I am not storing the password in the desired state because a database user can change its own password anytime (and is encouraged to do so) and I don't want to break access by overwriting the changed password. So far, this works fine with update_password: on_create.

In MySQL/MariaDB, a password is an attribute of a username-address-pair. I want to simplify that, so that the same password is used for all addresses of the same username. This makes it consistent with PostgreSQL. Hence, when creating a new username-address-pair and a different pair with the same username already exists, I need to reuse the password and can skip sending an initial password to the person owning the user. This part can be implemented with the proposed update_password: on_new_username.

I thought that others may find that useful too, so I implemented it in the module. If you say this is too specific, I am fine with putting the logic in my own code outside of the module.

[rsicart]

Hey, thanks for the details, I understand better now.

I'm not closed to that, just want to be sure to understand. I'd like to know what other collaborators think about that :)

@

[Andersson007]

@betanummeric thanks for bringing this up!

My opinion: the case feels specific to me to introduce a new value for that (though i'm not a user).

[betanummeric]

Are there any more opinions? Can we decide on merging or rejecting?

ping @bmalynovytch @Jorge-Rodriguez

[bmalynovytch]

My opinion is that what @betanummeric asks is not that silly: I don't have the use case but I would have the same workflow if I needed to alter user settings without knowing it's password. Moreover I ask users to set their own passwords, which I never store, which means if I need to manipulate user data, all I'll have is a hash.

The main difficulty IMHO is the implementation: will someone work on it ? 😉

[betanummeric]

Hi @bmalynovytch, I already implemented it, see PR #365.

[bmalynovytch]

Hi @bmalynovytch, I already implemented it, see PR #365.

Sorry for the noise ^^