Cannot create user with random password from variable

[djboni]

SUMMARY

Cannot create user with random password from variable

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.13.5]
  config file = /home/user/.ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/.local/lib/python3.10/site-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/user/.local/bin/ansible
  python version = 3.10.6 (main, Aug 10 2022, 11:40:04) [GCC 11.3.0]
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

# /home/user/.local/lib/python3.10/site-packages/ansible_collections
Collection        Version
----------------- -------
community.general 5.7.0  

CONFIGURATION

DEFAULT_HOST_LIST(/home/user/.ansible.cfg) = ['/home/user/Documents/ansible/inventory.yml']
INTERPRETER_PYTHON(/home/user/.ansible.cfg) = auto

OS / ENVIRONMENT

Host: Ubuntu 22.04
Targets: Ubuntu 22.04, Debian 11

STEPS TO REPRODUCE

See playbook below:

---
- name: Not creating user with random password from variable

  # Tested on Ubuntu 22.04 and Debian 11
  hosts: test-ubuntu

  vars:
    # This does NOT work:
    mariadb_user_password1: "{{ lookup('ansible.builtin.password', '/dev/null', length=4, chars='hexdigits') }}"

    # This does work:
    mariadb_user_password2: "test"

  tasks:
    - name: Install MariaDB
      apt:
        name:
          - mariadb-server
          - python3-mysqldb
        state: present

    - name: Create user named 'database_user1' with privileges to the database
      mysql_user:
        name: database_user1
        host: localhost
        state: present
        password: "{{ mariadb_user_password1 }}"

    - name: Create user named 'database_user2' with privileges to the database
      mysql_user:
        name: database_user2
        host: localhost
        state: present
        password: "{{ mariadb_user_password2 }}"

    - name: Restart MariaDB
      action: service name=mariadb state=restarted enabled=yes

    - name: Print MySQL user and password
      debug:
        msg: "CANNOT LOGIN: mysql -u database_user1 -p{{ mariadb_user_password1 }} "

    - name: Print MySQL user and password
      debug:
        msg: "CAN LOGIN: mysql -u database_user2 -p{{ mariadb_user_password2 }} "

EXPECTED RESULTS

I expect to be able to login with both database_user1 and database_user2.

ACTUAL RESULTS

Cannot login with database_user1. Can login with database_user2.

root@test-debian:~# mysql -u database_user1 -p2eaE
ERROR 1045 (28000): Access denied for user 'database_user1'@'localhost' (using password: YES)

root@test-debian:~# mysql -u database_user2 -ptest
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 33
Server version: 10.5.15-MariaDB-0+deb11u1 Debian 11
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> 

[djboni]

Note that the return value from
lookup('ansible.builtin.password', '/dev/null', length=4, chars='hexdigits')
has type
ansible.utils.unsafe_proxy.AnsibleUnsafeText.

[rsicart]

Hi @djboni,

Thanks for opening this issue.

Can you show what's the result of you lookup in clear text ?

In the examples of the module ansible.builtin.password, I see similar cases, like yours, with different syntax. For example:

lookup('ansible.builtin.password', '/dev/null chars=ascii_lowercase,digits length=8'

Could you please try your playbook with the syntax above? In your case, that would be:

lookup('ansible.builtin.password', '/dev/null length=4 chars=hexdigits')

[djboni]

Hi, I tried the code below (old and suggested lookups):

---
- name: Not creating user with random password from variable
  hosts: test-ubuntu
  vars:
    mariadb_user_password1: "{{ lookup('ansible.builtin.password', '/dev/null', length=4, chars='hexdigits') }}"
    mariadb_user_password2: "{{ lookup('ansible.builtin.password', '/dev/null length=4 chars=hexdigits') }}"
  tasks:
    - name: Lookup 1
      debug:
        msg: "Lookup 1: mariadb_user_password1={{ mariadb_user_password1 }} type={{ mariadb_user_password1 | type_debug }}."
    - name: Lookup 2
      debug:
        msg: "Lookup 2: mariadb_user_password2={{ mariadb_user_password2 }} type={{ mariadb_user_password2 | type_debug }}."

The output is:

TASK [Lookup 1] ************************************************************************************************************
ok: [test-ubuntu] => {
    "msg": "Lookup 1: mariadb_user_password1=F2Ae type=AnsibleUnsafeText."
}

TASK [Lookup 2] ************************************************************************************************************
ok: [test-ubuntu] => {
    "msg": "Lookup 2: mariadb_user_password2=2efD type=AnsibleUnsafeText."
}

[rsicart]

After reading Ansible documentation about unsafe text, it seems normal and recommended to use that for passwords.

Did you check if users where created correctly in users table, with a password set and for the right host?

[djboni]

Yes the users are created:

# echo "select user,host,password from user;" | mysql -u root mysql

User	Host	Password
mariadb.sys	localhost	
root	localhost	invalid
mysql	localhost	invalid
database_user1	localhost	*DF1434D536015CFB52BF62D468EE416167FCCC1C
database_user2	localhost	*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

[rsicart]

Thanks for the details.

Did you try to connect to the server directly with MySQL client and the created credentials?

Could you add a flush privileges play before restarting the server?