Cannot set a user's plugin to caching_sha2_password from scratch #484

jean-christophe-manciot · 2023-01-12T16:34:49Z

SUMMARY

When setting the plugin to caching_sha2_password, the user is actually created with the mysql_native_password plugin.

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.14.1]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.10/dist-packages/ansible
  ansible collection location = /opt
  executable location = /usr/local/bin/ansible
  python version = 3.10.9 (main, Dec  7 2022, 13:47:07) [GCC 12.2.0] (/usr/bin/python3.10)
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

ansible-galaxy collection list community.mysql

# /usr/local/lib/python3.10/dist-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1  

# /opt/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1

CONFIGURATION

CACHE_PLUGIN(/etc/ansible/ansible.cfg) = redis
CACHE_PLUGIN_CONNECTION(/etc/ansible/ansible.cfg) = tls://localhost:<port>:0:<password>
CACHE_PLUGIN_TIMEOUT(/etc/ansible/ansible.cfg) = 259200
CALLBACKS_ENABLED(/etc/ansible/ansible.cfg) = ['community.general.yaml']
COLLECTIONS_PATHS(/etc/ansible/ansible.cfg) = ['/opt']
CONFIG_FILE() = /etc/ansible/ansible.cfg
DEFAULT_EXECUTABLE(/etc/ansible/ansible.cfg) = /bin/bash
DEFAULT_FORKS(/etc/ansible/ansible.cfg) = 1000
DEFAULT_GATHERING(/etc/ansible/ansible.cfg) = explicit
DEFAULT_HASH_BEHAVIOUR(/etc/ansible/ansible.cfg) = replace
DEFAULT_HOST_LIST(/etc/ansible/ansible.cfg) = ['/etc/ansible/hosts']
DEFAULT_LOAD_CALLBACK_PLUGINS(/etc/ansible/ansible.cfg) = True
DEFAULT_LOG_PATH(/etc/ansible/ansible.cfg) = /var/log/ansible.log
DEFAULT_PRIVATE_ROLE_VARS(/etc/ansible/ansible.cfg) = False
DEFAULT_ROLES_PATH(/etc/ansible/ansible.cfg) = ['/etc/ansible/roles'>
DEFAULT_STDOUT_CALLBACK(/etc/ansible/ansible.cfg) = community.general.yaml
DEFAULT_TIMEOUT(/etc/ansible/ansible.cfg) = 240
DEFAULT_TRANSPORT(/etc/ansible/ansible.cfg) = ssh
ENABLE_TASK_DEBUGGER(/etc/ansible/ansible.cfg) = True
GALAXY_SERVER_LIST(/etc/ansible/ansible.cfg) = ['release_galaxy', 'test_galaxy', 'local_https', 'local_ssh', 'local_file', 'automation_hub']
HOST_KEY_CHECKING(/etc/ansible/ansible.cfg) = True
INJECT_FACTS_AS_VARS(/etc/ansible/ansible.cfg) = True
INTERPRETER_PYTHON(/etc/ansible/ansible.cfg) = /usr/bin/python3
INVENTORY_ENABLED(/etc/ansible/ansible.cfg) = ['ini', 'script', 'auto', 'yaml']
PERSISTENT_COMMAND_TIMEOUT(/etc/ansible/ansible.cfg) = 3599
PERSISTENT_CONNECT_RETRY_TIMEOUT(/etc/ansible/ansible.cfg) = 300
PERSISTENT_CONNECT_TIMEOUT(/etc/ansible/ansible.cfg) = 3600
RETRY_FILES_ENABLED(/etc/ansible/ansible.cfg) = False
SHOW_CUSTOM_STATS(/etc/ansible/ansible.cfg) = True

OS / ENVIRONMENT

controller: Ubuntu 22.10
target: Ubuntu 22.10

STEPS TO REPRODUCE

Creating a test user with the caching_sha2_password plugin

- mysql_user:
      append_privs: false
      ca_cert: null
      check_hostname: null
      check_implicit_admin: false
      client_cert: null
      client_key: null
      config_file: /etc/mysql/mysql.conf.d/mysqld.cnf
      connect_timeout: 30
      encrypted: false
      force_context: false
      host: '%.example.com'
      host_all: false
      login_host: mysql1-kvm.example.com
      login_password: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      login_port: 3306
      login_unix_socket: /run/mysqld/mysqld.sock
      login_user: root
      name: test
      password: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      plugin: caching_sha2_password
      plugin_auth_string: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      plugin_hash_string: null
      priv: '*.*:ALL'
      resource_limits: null
      sql_log_bin: true
      state: present
      subtract_privs: false
      tls_requires: null
      update_password: always
      user: test

EXPECTED RESULTS

The user test should have been created with the requested plugin.

ACTUAL RESULTS

ansible call seems right

redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
Using module file /opt/ansible_collections/community/mysql/plugins/modules/mysql_user.py
Pipelining is enabled.
<mysql1-kvm.example.com> ESTABLISH SSH CONNECTION FOR USER: root
<mysql1-kvm.example.com> SSH: EXEC ssh -vvv -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="root"' -o ConnectTimeout=240 -o 'ControlPath="/root/.ansible/cp/ea5ee7ce1e"' mysql1-kvm.example.com '/bin/bash -c '"'"'/usr/bin/python3 && sleep 0'"'"''
<mysql1-kvm.example.com> (0, b'
{"changed": true, "user": "test", "msg": "User added", "password_changed": true, "invocation": {"module_args": {"config_file": "/etc/mysql/mysql.conf.d/mysqld.cnf", "host": "%.example.com", "login_host": "mysql1-kvm.example.com", "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "login_port": 3306, "login_unix_socket": "/run/mysqld/mysqld.sock", "login_user": "root", "name": "test", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "plugin": "caching_sha2_password", "plugin_auth_string": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "priv": "*.*:ALL", "state": "present", "user": "test", "connect_timeout": 30, "encrypted": false, "host_all": false, "append_privs": false, "subtract_privs": false, "check_implicit_admin": false, "update_password": "always", "sql_log_bin": true, "force_context": false, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "tls_requires": null, "plugin_hash_string": null, "resource_limits": null}}}
', b"OpenSSH_9.0p1 Ubuntu-1ubuntu7.1, OpenSSL 3.0.7 1 Nov 2022\r
")
changed: [mysql1-kvm.example.com] => changed=true 
  invocation:
    module_args:
      append_privs: false
      ca_cert: null
      check_hostname: null
      check_implicit_admin: false
      client_cert: null
      client_key: null
      config_file: /etc/mysql/mysql.conf.d/mysqld.cnf
      connect_timeout: 30
      encrypted: false
      force_context: false
      host: '%.example.com'
      host_all: false
      login_host: mysql1-kvm.example.com
      login_password: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      login_port: 3306
      login_unix_socket: /run/mysqld/mysqld.sock
      login_user: root
      name: test
      password: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      plugin: caching_sha2_password
      plugin_auth_string: VALUE_SPECIFIED_IN_NO_LOG_PARAMETER
      plugin_hash_string: null
      priv: '*.*:ALL'
      resource_limits: null
      sql_log_bin: true
      state: present
      subtract_privs: false
      tls_requires: null
      update_password: always
      user: test
  msg: User added
  password_changed: true
  user: test

But the plugin is actually wrong:

mysql --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY --user=root --host=mysql1-kvm.example.com --port=3306 '--password=password' '--execute=SELECT User, Host, Plugin, HEX(authentication_string) FROM mysql.user;' | grep test
mysql: [Warning] Using a password on the command line interface can be insecure.
| test             | %.sdxlive.com | mysql_native_password | 2A31423945363134373544453130354638353032344639373136313237453634394642443431334344

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

jean-christophe-manciot · 2023-01-12T16:54:02Z

However, when the user is created (after having deleted the previous one) with a local mysql client call, everything is fine:

mysql --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY --user=root --host=mysql1-kvm.example.com --port=3306 '--password=password' '--execute=CREATE USER test@"%.example.com" IDENTIFIED WITH caching_sha2_password BY "password";'
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY --user=root --host=mysql1-kvm.example.com --port=3306 '--password=password' '--execute=GRANT ALL ON *.* TO test@"%.example.com";'
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY --user=root --host=mysql1-kvm.example.com --port=3306 '--password=password' '--execute=FLUSH PRIVILEGES;'
mysql: [Warning] Using a password on the command line interface can be insecure.

The plugin is set as expected:

mysql --ssl-ca=ca.pem --ssl-mode=VERIFY_IDENTITY --user=root --host=mysql1-kvm.example.com --port=3306 '--password=password' '--execute=SELECT User, Host, Plugin, HEX(authentication_string) FROM mysql.user;' | grep test
mysql: [Warning] Using a password on the command line interface can be insecure.
| test             | %.sdxlive.com | caching_sha2_password | 24412430303524230D274D37195B102935175F7F100578715B42176651372F4C3753376B386F6F4168314A505678614866764876526C61313766795245763849754E48306E36 |

Andersson007 · 2023-01-13T09:17:40Z

@jean-christophe-manciot hello, thanks for reporting the issue and welcome to the community!

I have a couple of questions that can help us investigate the problem:

Do you use the collection from this repo directly or you've got it with the ansible package (via pip or your distro's package manager) or via ansible-galaxy? The context of the question is that there were changes in the related code made last week but there have been no releases since then but if you install the collection directly from the repo, you have them.
If the answer to question 1 is negative (i.e. you don't use the repo directly), has it stopped working after update? If yes, in what version did it work correctly for you last time. I'm trying to understand if the bug was always there or was introduced with recent releases.
does it only happen when you create the user from scratch? if you run the playbook the second time, is result the same?
if the module worked as expected in the past, maybe you had introduced some new parameters and after that it stopped working correctly? I'm trying to understand if a certain combination of parameters can be the reason
weren't there any changes in my.cnf recently added compared to the working configuration?

Waiting for your feedback

jean-christophe-manciot · 2023-01-13T14:53:34Z

@Andersson007

from pip3 ansible and ansible-galaxy:
- pip install ansible
- ansible-galaxy collection install --force-with-deps --server release_galaxy community.mysql
I've just checked my ansible logs. It turns out I never used mysql_user to create a mysql user, before but I always successfully relied on my own bash scripts as shown earlier. This time, I chose the ansible module because of the 'force_context' feature which I don't know how to implement with a mysql command. Also, the issue is still there if the force_context is false.
I've only tested creating the user from scratch (with other users already present). If I run the same role a second time, the user is created with ... the right plugin!!
No
No

Andersson007 · 2023-01-20T08:22:40Z

@jean-christophe-manciot thanks for the detailed feedback! we'll take a look

Andersson007 · 2023-01-20T08:37:43Z

@jean-christophe-manciot i think the issue is that password is set (i'm not user so i can be wrong).
The doc says
password: Set the user’s password. Only for mysql_native_password authentication. For other authentication plugins see the combination of plugin, plugin_hash_string, plugin_auth_string.
I see related conditions in the function which adds the user: if the password is set, it will run related code and the next elif condition responsible for handling auth plugins will never be reached.
If i understand it correctly, we should use password OR plugin* related options but not both.

Could you please try to remove the password argument and try to create a new user? Let us know how it works
If it works, do you, as a user, think this is a right behavior? Should we make password and pluging parameters forbidden to use together, i.e. mutually exclusive? the module will raise an error saying that if yes

Thanks!

hubiongithub · 2023-01-20T09:31:49Z

Hello,

@Andersson007 you are right, the presence of password: will force an user with mysql_native_password authentication
in the code (plugins/module_utils/user.py line 163...)

The second point ( make password and plugin parameters mutually exclusive) is a good idea, the logic in the code does not use a combination of these, but checks password first an if set -> mysql_native_password, if not -> check plugin ...

Andersson007 · 2023-01-20T10:15:17Z

@hubiongithub thanks much for the feedback!
Would you like to pick it up? I guess we need:

Add a sentence to password and plugin's descriptions saying - Mutually exclusive with I(argument).
Check if there are wrong examples in the EXAMPLES block
Add a corresponding tuple to mutually_exclusive=( in module = AnsibleModule( invocation in main
I think a changelog entry should be minor_changes

What do you think?

jean-christophe-manciot · 2023-01-20T11:09:53Z

@Andersson007 @hubiongithub

make password and plugin parameters mutually exclusive

I strongly disagree. In the official MySQL 8.0 docs, nothing prevents the user from being created with both caching_sha2_password plugin AND password:

CREATE USER 'sha2user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'password';

That's why I'm able to create any user with a combination of both using a mysql client CLI command.

The fact that it not possible to do so with the ansible module is an issue: any ansible user would expect to be able to create any user with that plugin by setting its password.

hubiongithub · 2023-01-20T11:42:19Z

Hello @jean-christophe-manciot

"password" has two different meanings here:

a) mysql documentation:
CREATE USER 'sha2user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
here 'password' is the string you provide as credential, and the WITH caching_sha2_password part tells mysql to use a different plugin.
CREATE USER 'sha2user'@'localhost' IDENTIFIED BY 'password';
will create an user with mysql_native_password auth. (as long as mysql defaults to mysql_native_password, that might get changed in the future)

b) but in ansible code

- name: create a mysql user
  mysql_user:
    user: "username"
    password: "thisisthepasswordstring"

here 'password' is a parameter name containing a string to be used as credential

The code interpreting the ansible parameter to mysql_user look like this:
it checks for parameter password (containing the string to use as password)
if not set it checks for plugin and plugin_auth_string and uses a different way to create the user:

   if password and encrypted:
       if impl.supports_identified_by_password(cursor):
           query_with_args = "CREATE USER %s@%s IDENTIFIED BY PASSWORD %s", (user, host, password)
       else:
           query_with_args = "CREATE USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, password)
   elif password and not encrypted:
       if old_user_mgmt:
           query_with_args = "CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password)
       else:
           cursor.execute("SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1(%s)))))", (password,))
           encrypted_password = cursor.fetchone()[0]
           query_with_args = "CREATE USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, encrypted_password)
   elif plugin and plugin_hash_string:
       query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s AS %s", (user, host, plugin, plugin_hash_string)
   elif plugin and plugin_auth_string:
       # Mysql and MariaDB differ in naming pam plugin and Syntax to set it
       if plugin == 'pam':  # Used by MariaDB which requires the USING keyword, not BY
           query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)
       else:
           query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s BY %s", (user, host, plugin, plugin_auth_string)
   elif plugin:
       query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s", (user, host, plugin)
   else:
       query_with_args = "CREATE USER %s@%s", (user, host)

Andersson007 · 2023-01-20T11:53:21Z

@hubiongithub how do you think we should proceed here?
Important things are:

if the behavior is not fair, we must ensure there will be no breaking changes introduced by our fixes
if the behavior is fair, we probably need to update our docs to make it less confusing
if the behavior is not fair but the risk of breaking current user workflows is high, i would go with doc updates only

Andersson007 · 2023-01-20T11:59:18Z

@jean-christophe-manciot thanks for the feedback! Didn't notice your comment. So the question above is still relevant ^

jean-christophe-manciot · 2023-01-20T12:08:24Z

@hubiongithub

it checks for parameter password (containing the string to use as password)
if not set it checks for plugin and plugin_auth_string and uses a different way to create the user:

Hence the issue. The code must have been written several years ago when I suppose the choice of plugins was very limited.

Anyway, the code which handles the user creation must be overhauled and fixed: it must first begin by checking for the type of plugin which is more important, then the password which must be present for some types of plugin.

hubiongithub · 2023-01-20T13:04:45Z

Hello @Andersson007 @jean-christophe-manciot
I'm not sure what is meant by "behavior is [not] fair".

At the moment it works like documented (and yes probably the code is old), not using the parameter "password" will make your code behave like you expect.
I'm not sure why this "must be overhauled and fixed:". Beside that I won't be able to rip this apart and reconstruct it in a suitable way that all mysql/mariadb versions accept that change.
And depending on how the change is made:
I assume you want the code to accept the parameter 'password' for plugins if they needed one.
That is not clear as we do not know what plugins are used.
And it will probably break any code written according the actual documentation.
I'm far away of being something like a maintainer here, I just try to fix stuff that break for me while using ansible.

From my point of view as a user (that's me) of this community build software:
just delete the password parameter from your code an it will be fine (except for mariadb, as those don't have a caching_sha2_password plugin yet) so if you plan or are forced to use mariadb you probably need to adjust your code).
The change to the documentation to make the usage clearer and getting a (well described) error message when the parameter password and plugin are used together is ok, but I even would say the part "password: Set the user’s password. Only for mysql_native_password authentication. For other authentication plugins see the combination of plugin, plugin_hash_string, plugin_auth_string" is already clear enough.

From a point of view of a developer (that's not me, so I might be wrong here) it might be nice to have a consistent API for all possible use cases, but nothing I would call "must be overhauled and fixed:" as it works as documented.

Andersson007 · 2023-01-25T10:39:13Z

I'm not sure how we should proceed now as the opinions are quite different:)

hubiongithub · 2023-01-25T10:52:34Z

Hello @Andersson007 @jean-christophe-manciot

From the point of the issue headline "Cannot set a user's plugin to caching_sha2_password from scratch"
there is a simple reason and solution: don't use the parameter "password" when creating "non mysql_native_password" users.
As it is documented.
From that point of view we could close the issue.

From the point of view that it would be more pleasing to have only one parameter for passwords
(probably not named password?) someone would have to rewrite that code that handles that part.
With everything that is part of it (on_create/always behaviour, mysql/mariadb differences, version differences, ...).
I won't be able to dig into that.

Andersson007 · 2023-01-25T14:36:05Z

@hubiongithub thanks for the feedback!
@hubiongithub @jean-christophe-manciot if just following the docs works, i would be for leaving things as they are, i.e. i wouldn't touch the code. Maybe we could add some comments to the examples to make this a bit more noticeable.

Andersson007 · 2023-01-26T11:51:15Z

@jean-christophe-manciot we could leave the issue open for further discussion.
Would you like to change the title and the description correspondingly to reflect our findings?

Andersson007 · 2023-02-10T09:57:13Z

will close this as the discussion has actually stopped, we can open it later if needed
thanks everyone!

jean-christophe-manciot changed the title ~~Cannot set a user's plugin to caching_sha2_password anymore~~ Cannot set a user's plugin to caching_sha2_password Jan 13, 2023

jean-christophe-manciot changed the title ~~Cannot set a user's plugin to caching_sha2_password~~ Cannot set a user's plugin to caching_sha2_password from scratch Jan 13, 2023

Andersson007 mentioned this issue Jan 20, 2023

add service name to plugin pam/auth_pam usage #445

Merged

Andersson007 closed this as completed Feb 10, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cannot set a user's plugin to caching_sha2_password from scratch #484

Cannot set a user's plugin to caching_sha2_password from scratch #484

jean-christophe-manciot commented Jan 12, 2023 •

edited

Loading

jean-christophe-manciot commented Jan 12, 2023

Andersson007 commented Jan 13, 2023 •

edited

Loading

jean-christophe-manciot commented Jan 13, 2023

Andersson007 commented Jan 20, 2023

Andersson007 commented Jan 20, 2023

hubiongithub commented Jan 20, 2023 •

edited

Loading

Andersson007 commented Jan 20, 2023 •

edited

Loading

jean-christophe-manciot commented Jan 20, 2023

hubiongithub commented Jan 20, 2023 •

edited

Loading

Andersson007 commented Jan 20, 2023

Andersson007 commented Jan 20, 2023

jean-christophe-manciot commented Jan 20, 2023

hubiongithub commented Jan 20, 2023

Andersson007 commented Jan 25, 2023

hubiongithub commented Jan 25, 2023

Andersson007 commented Jan 25, 2023

Andersson007 commented Jan 26, 2023

Andersson007 commented Feb 10, 2023

Cannot set a user's plugin to caching_sha2_password from scratch #484

Cannot set a user's plugin to caching_sha2_password from scratch #484

Comments

jean-christophe-manciot commented Jan 12, 2023 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

COLLECTION VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

jean-christophe-manciot commented Jan 12, 2023

Andersson007 commented Jan 13, 2023 • edited Loading

jean-christophe-manciot commented Jan 13, 2023

Andersson007 commented Jan 20, 2023

Andersson007 commented Jan 20, 2023

hubiongithub commented Jan 20, 2023 • edited Loading

Andersson007 commented Jan 20, 2023 • edited Loading

jean-christophe-manciot commented Jan 20, 2023

hubiongithub commented Jan 20, 2023 • edited Loading

Andersson007 commented Jan 20, 2023

Andersson007 commented Jan 20, 2023

jean-christophe-manciot commented Jan 20, 2023

hubiongithub commented Jan 20, 2023

Andersson007 commented Jan 25, 2023

hubiongithub commented Jan 25, 2023

Andersson007 commented Jan 25, 2023

Andersson007 commented Jan 26, 2023

Andersson007 commented Feb 10, 2023

jean-christophe-manciot commented Jan 12, 2023 •

edited

Loading

Andersson007 commented Jan 13, 2023 •

edited

Loading

hubiongithub commented Jan 20, 2023 •

edited

Loading

Andersson007 commented Jan 20, 2023 •

edited

Loading

hubiongithub commented Jan 20, 2023 •

edited

Loading