SSL Connection to Azure Mysql DB doesn't work

[loomsen]

SUMMARY

When trying to connect to an Azure Mysql Server instance, the SSL option doesn't work.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.10.4
  config file = /home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/ansible
  executable location = /home/user/.pyenv/versions/ansible/bin/ansible
  python version = 3.9.1 (default, Dec  8 2020, 00:00:00) [GCC 10.2.1 20201125 (Red Hat 10.2.1-9)]

CONFIGURATION

ANSIBLE_NOCOWS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = True
ANSIBLE_SSH_CONTROL_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = %(directory)s/ssh-%%h
COLLECTIONS_PATHS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/home/user/projects/foo/infrastructure-common/infrastructure/collections']
DEFAULT_ACTION_PLUGIN_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/usr/share/ansible_plugins/action_plugins']
DEFAULT_CALLBACK_WHITELIST(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['profile_tasks']
DEFAULT_CONNECTION_PLUGIN_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/usr/share/ansible_plugins/connection_plugins']
DEFAULT_FILTER_PLUGIN_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/home/user/projects/foo/infrastructure-common/infrastructure/filter_plugins/filter']
DEFAULT_FORKS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = 15
DEFAULT_GATHERING(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = implicit
DEFAULT_JINJA2_EXTENSIONS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = jinja2.ext.do
DEFAULT_LOOKUP_PLUGIN_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/usr/share/ansible_plugins/lookup_plugins']
DEFAULT_MANAGED_STR(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = Ansible managed
DEFAULT_POLL_INTERVAL(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = 15
DEFAULT_REMOTE_PORT(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = 22
DEFAULT_ROLES_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/home/user/projects/foo/infrastructure-common/infrastructure/roles']
DEFAULT_SCP_IF_SSH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = True
DEFAULT_STDOUT_CALLBACK(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = debug
DEFAULT_TIMEOUT(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = 10
DEFAULT_TRANSPORT(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = smart
DEFAULT_VARS_PLUGIN_PATH(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = ['/usr/share/ansible_plugins/vars_plugins']
DEFAULT_VAULT_PASSWORD_FILE(env: ANSIBLE_VAULT_PASSWORD_FILE) = /home/user/.ansible/vault/rebbits_secret.txt
DEPRECATION_WARNINGS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = True
DISPLAY_SKIPPED_HOSTS(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = False
HOST_KEY_CHECKING(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = False
INVENTORY_UNPARSED_IS_FAILED(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = False
RETRY_FILES_ENABLED(/home/user/projects/foo/infrastructure-common/infrastructure/ansible.cfg) = False

OS / ENVIRONMENT

Target: Azure Database for MySQL server
Version: 5.7

Default settings have Enforce SSL Connection checked.

STEPS TO REPRODUCE

Create a server on azure and try to connect with below playbook.

Connection works from CLI with:
mysql -h instance -u dba@instance -p --ssl

  - name: Create database users with all database privileges on its DB
    mysql_user:
      name: db1
      password: testpass
      priv: 'db1.*:ALL'
      state: present
      login_user: dba@instance
      login_password: admin_password
      login_host: instance
      tls_requires:
        SSL:

EXPECTED RESULTS

Create a database users with all database privileges on the specifiied database on an Azure Mysql Server.

ACTUAL RESULTS

TL;DR
unable to connect to database, check login_user and login_password are correct or /home/nvarz/.my.cnf has the credentials. Exception message: (9002, 'SSL connection is required. Please specify SSL options and retry.\x00')

TASK [Create database users with all database privileges] ******************************************************************************************************************************************
task path: /home/user/projects/foo/infrastructure-common/infrastructure/playbooks/azure_mysql.yml:44
The full traceback is:
  File "/tmp/ansible_mysql_user_payload_p55_tlxr/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 1078, in main
  File "/tmp/ansible_mysql_user_payload_p55_tlxr/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/mysql.py", line 103, in mysql_connect
    db_connection = mysql_driver.connect(autocommit=autocommit, **config)
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
    self.connect()
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/connections.py", line 633, in connect
    self._request_authentication()
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/connections.py", line 907, in _request_authentication
    auth_packet = self._read_packet()
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/user/.pyenv/versions/ansible/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
failed: [localhost] (item={'name': 'db1', 'password': 'testpass'}) => {
    "ansible_loop_var": "item",
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/home/user/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "instance.mysql.database.azure.com",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "dbadmin@instance",
            "name": "db1",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "rufnummerntausch.*:ALL",
            "resource_limits": null,
            "sql_log_bin": true,
            "state": "present",
            "tls_requires": {
                "SSL": null
            },
            "update_password": "always",
            "user": "db1"
        }
    },
    "item": {
        "name": "db1",
        "password": "testpass"
    }
}

MSG:

unable to connect to database, check login_user and login_password are correct or /home/user/.my.cnf has the credentials. Exception message: (9002, 'SSL connection is required. Please specify SSL options and retry.\x00')

[Andersson007]

@loomsen hi, thanks for reporting the issue!
@Jorge-Rodriguez could you please take a look?

[Andersson007]

@loomsen , I looked at the module's documentation looks like the tls_require option relates to user's permission, not to the connection to the database from your playbook.
Have you tried the ca_cert, client_key, client_cert options?

@Jorge-Rodriguez what do you think?

[loomsen]

@Andersson007 Thank you for looking into this.

I haven't tried the ca_cert, client_key and client_cert options, because I literally don't have any of these files. The only thing I add to my command line mysql invocation is --ssl and it works. So I assume this involves a server cert only, just like connecting to a website over https instead of http. Just guessing here though, not exactly sure how it is implemented in mysql.

[Jorge-Rodriguez]

@Andersson007 you're right, the tls_requirements parameter does not pertain to user connection.
I'd have to look at the code to see how we implement the TLS option passing on connection and what options to use in this case.

I'll get back to you @loomsen

[loomsen]

Hi @Jorge-Rodriguez @Andersson007,

looking into the code made me somewhat figure out, that check_hostname is the actual parameter to use for SSL connections.
With check_hostname: yes it works. Maybe we could add this to the docs somehow or even consider renaming the check_hostname option?

I think this issue could be closed technically, but I would really suggest updating the docs. WDYT?

[Andersson007]

Hi everyone,
@loomsen thanks for the clue!
After @Jorge-Rodriguez looks at the code and confirms, IMO, it's definitely worth adding clarification to the option's doc and maybe adding an alias (the renaming would break backwards compatibility).
If anyone wants to do it, please go ahead, I'd be happy to review.

[Jorge-Rodriguez]

@loomsen I'd welcome your suggestions for the parameter name. As someone who found check_hostname misleading, you're in a better position to tell us what kind of alternative names would make sense.

[loomsen]

Hi @Jorge-Rodriguez,

The current docs for check_hostname read:Whether to validate the server host name when an SSL connection is required.

This is misleading in a way, that it kind of implies there would be another option to actually initiate the SSL connection. At least that's how I read it. When in fact this very option initiates the SSL connection in the first place.

How about ssl_required or ssl_connection? Or even just ssl to be inline with the mysql CLI.

[Jorge-Rodriguez]

@loomsen it's a bit more complicated than that, because this is not the only option that initiates an SSL connection. You could have check_hostname being false but also specify ssl_cert and that would also initiate an SSL connection, one where the server certificate is not validated but a client certificate is sent for validation.

While I agree with you that the option name can be misleading, it seems to me renaming is not the best solution. Also check_hostname is consistent across Ansible modules in addition to holding more semantic meaning than just starting an SSL connection.

Maybe a better solution would be to add an ssl option dictionary to enclose all the other SSL connection options.

Alternatively, we could go further and have a connection_options dictionary that would hold all connection related options, including host, port, username, password, etc.

Or we could go even further and nest both solutions with the connection_options dictionary holding the ssl dictionary.

@Andersson007 @bmalynovytch any thoughts on these?

[Andersson007]

for me personally, everything that is really needed / nice to have and doesn't introduce breaking changes is ok;)

[loomsen]

I see. Maybe we could settle for an addition to the docs like: corresponds to --ssl in the mysql cli. That would've been totally sufficient for me while I was trying to figure it out.

[Andersson007]

@loomsen , to add a clarification to documentation is always a good idea. Feel free to create a PR.
If you're busy or something, @Jorge-Rodriguez or I could add it.
@Jorge-Rodriguez what do you think about adding what @loomsen is suggesting?

[Jorge-Rodriguez]

Clarifying the documentation is of course there path of least resistance, I have no problem with that.
My only note is what I already mentioned on my comment above, that check_hostname is not the only option that implies an SSL connection, so we'll want to be clear and verbose when updating the docs.

[Andersson007]

OK, folks, if you work with it, you know how to formulate the update better, please do it. I'd be happy to review

[Jorge-Rodriguez]

I'm reopening this issue as it is related to an obscure handling of TLS by the MySQLdb connector.