icingaweb2 role , complex db passwords

[l0tke]

When defining mysql command for icingaweb, if special characters are used
( for example password set to 33t#$pppE@#e ) , role will fail with bad password error

Affected code is in

roles/icingaweb2/tasks/manage_mysql_imports.yml
roles/icingaweb2/tasks/manage_icingaweb_mysql_db.yml

If you change:

- name: Build mysql command
  ansible.builtin.set_fact:
    _tmp_mysqlcmd: >-
      mysql {% if _db['host'] | default('localhost') != 'localhost'  %} -h "{{ _db['host'] }}" {%- endif %}
      {% if _db['port'] is defined %} -P "{{ _db['port'] }}" {%- endif %}
      {% if _db['ssl_mode'] is defined %} --ssl-mode "{{ _db['ssl_mode'] }}" {%- endif %}
      {% if _db['ssl_ca'] is defined %} --ssl-ca "{{ _db['ssl_ca'] }}" {%- endif %}
      {% if _db['ssl_cert'] is defined %} --ssl-cert "{{ _db['ssl_cert'] }}" {%- endif %}
      {% if _db['ssl_key'] is defined %} --ssl-key "{{ _db['ssl_key'] }}" {%- endif %}
      {% if _db['ssl_cipher'] is defined %} --ssl-cipher "{{ _db['ssl_cipher'] }}" {%- endif %}
      {% if _db['ssl_extra_options'] is defined %} {{ _db['ssl_extra_options'] }} {%- endif %}
      -u "{{ _db['user'] }}"
      -p"{{ _db['password'] }}"
      "{{ _db['name'] }}"

To:

- name: Build mysql command
  ansible.builtin.set_fact:
    _tmp_mysqlcmd: >-
      mysql {% if _db['host'] | default('localhost') != 'localhost'  %} -h "{{ _db['host'] }}" {%- endif %}
      {% if _db['port'] is defined %} -P "{{ _db['port'] }}" {%- endif %}
      {% if _db['ssl_mode'] is defined %} --ssl-mode "{{ _db['ssl_mode'] }}" {%- endif %}
      {% if _db['ssl_ca'] is defined %} --ssl-ca "{{ _db['ssl_ca'] }}" {%- endif %}
      {% if _db['ssl_cert'] is defined %} --ssl-cert "{{ _db['ssl_cert'] }}" {%- endif %}
      {% if _db['ssl_key'] is defined %} --ssl-key "{{ _db['ssl_key'] }}" {%- endif %}
      {% if _db['ssl_cipher'] is defined %} --ssl-cipher "{{ _db['ssl_cipher'] }}" {%- endif %}
      {% if _db['ssl_extra_options'] is defined %} {{ _db['ssl_extra_options'] }} {%- endif %}
      -u '{{ _db['user'] }}'
      -p'{{ _db['password'] }}'
      '{{ _db['name'] }}'

It will work with complex passwords.

Ansible error for reference

task path: /home/blah/.ansible/collections/ansible_collections/icinga/icinga/roles/icingaweb2/tasks/manage_icingaweb_mysql_db.yml:43
fatal: [blah-icinga]: FAILED! => {
    "changed": true,
    "cmd": "mysql         -u \"icingaweb\" -p\"33t#$pppE@#e\" \"icingaweb\" < /usr/share/icingaweb2/schema/mysql.schema.sql\n",
    "delta": "0:00:00.008596",
    "end": "2024-01-24 12:52:10.328878",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql         -u \"icingaweb\" -p\"33t#$pppE@#e\" \"icingaweb\" < /usr/share/icingaweb2/schema/mysql.schema.sql\n",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "expand_argument_vars": true,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true
        }
    },
    "msg": "non-zero return code",
    "rc": 1,
    "start": "2024-01-24 12:52:10.320282",
    "stderr": "ERROR 1045 (28000): Access denied for user 'icingaweb'@'localhost' (using password: YES)",
    "stderr_lines": [
        "ERROR 1045 (28000): Access denied for user 'icingaweb'@'localhost' (using password: YES)"
    ],
    "stdout": "",
    "stdout_lines": []
}

[mkayontour]

Thanks for the issue, I know what's the problem.

We need to filter the password through the quote filter.
If you have the ability change the following line:
-p'{{ _db['password'] }}' to -p'{{ _db['password'] | quote }}'

This should quote the password for shell usage.

I'll run a few tests, if you can test as well give me feedback if this is working for your case ;)

EDIT; No it does not solve the issue.

[l0tke]

Hello,
Thanks for quick response.

This change in manage_icingaweb_mysql_db.yml
from:

          -p"{{ icingaweb2_priv_db_password | default(icingaweb2_db['password']) }}"

to

          -p{{ icingaweb2_priv_db_password | default(icingaweb2_db['password']) |quote }}

Works ok

I have not tryed to change manage_mysql_imports.yml because as far as i can see is only called from
icinga/icinga/roles/icingaweb2/tasks/modules/x509.yml which i do not use ( not using x509 ).

I guess the right change for manage_mysql_imports.yml would be
from

      -p"{{ _db['password'] }}"

to

      -p{{ _db['password'] |quote }}

or just

      -p'{{ _db['password'] }}'

Just to clarify things a bit, if i apply variation of your "quote" method on task manage_icingaweb_mysql_db.yml
and set that password line to

          -p'{{ icingaweb2_priv_db_password | default(icingaweb2_db['password']) |quote }}'

Ansible would pass double quotes

"cmd": "mysql         -u \"icingaweb\" -p''xxxxxxxxx'' \"icingaweb\" < /usr/share/icingaweb2/schema/mysql.schema.sql\n",

[mkayontour]

Yes, thanks for the correction - noticed right away that this won't work.