- name: parameters-mandatory-buster64

  - name: deprecated-buster64

    groups:

      - deprecated

    box: debian/contrib-buster64

    memory: 512

    cpus: 1

- hosts: parameters-mandatory,deprecated

- hosts: deprecated

  become: true

  tasks:

    - name: Install MariaDB

      apt:

        name:

          - mariadb-client

          - mariadb-server

          - python3-pymysql

        state: present

    - name: Enable and start MariaDB

      service:

        name: mysql

        state: started

        enabled: true

    - name: Deploy username and password for the root database user

      copy:

        src: "deprecated-root-my.cnf"

        dest: "/root/.my.cnf"

        owner: root

        group: root

        mode: 0400

    - name: Set password for the root database user (creating separate entry for different hosts)

      mysql_user:

        check_implicit_admin: true

        name: root

        host: "{{ item }}"

        password: "root_password"

        login_unix_socket: "/var/run/mysqld/mysqld.sock"

      with_items:

        - "localhost"

        - "127.0.0.1"

        - "::1"

        - "{{ ansible_hostname }}"

    - name: Disable use of unix socket login

      command: "mysql -B -e \"update mysql.user set plugin='' where user='root' and plugin='unix_socket'; flush privileges;\""

    - name: Create Debian system maintenance user

      mysql_user:

        name: debian-sys-maint

        password: debian-sys-maint-password

        login_unix_socket: "/var/run/mysqld/mysqld.sock"

    - name: Deploy Debian system maintenance user login configuration

      copy:

        src: "deprecated-debian.cnf"

        dest: "/etc/mysql/debian.cnf"

        owner: root

        group: root

        mode: 0600

testinfra_hosts += testinfra.utils.ansible_runner.AnsibleRunner(

    os.environ['MOLECULE_INVENTORY_FILE']).get_hosts('deprecated')

testinfra_hosts = sorted(set(testinfra_hosts))

def test_root_my_cnf_is_absent(host):

    """

    Tests if the root my.cnf configuration file is absent (root should

    be able to login via unix socket, and does not need its password

    set).

    """

    with host.sudo():

        assert not host.file('/root/.my.cnf').exists

def test_root_password_is_not_empty(host):

    """

    Tests if the database server root password is empty.

    """

    login = host.run("mysql -uroot -BNe 'show databases'")

    assert login.rc != 0

def test_root_os_user_can_login(host):

    """

    Tests if the root account can log-in without providing any password (via unix socket).

    """

    with host.sudo():

        login = host.run("mysql -uroot -BNe 'show databases'")

    assert login.rc == 0

    assert "information_schema" in login.stdout

    assert "mysql" in login.stdout

    assert "performance_schema" in login.stdout

def test_root_can_login_via_unix_socket_only(host):

    """

    Tests if the root login is possible only via unix socket.

    """

    with host.sudo():

        root_logins_without_unix_socket_count = host.run("mysql -BNe %s", "select count(*) from mysql.user where user = 'root' and plugin != 'unix_socket'")

        root_logins_with_unix_socket = host.run("mysql -BNe %s", "select User, Host, Password from mysql.user where user = 'root' and plugin = 'unix_socket'")

        assert root_logins_without_unix_socket_count.rc == 0

        assert root_logins_without_unix_socket_count.stdout.strip() == "0"

        assert root_logins_with_unix_socket.rc == 0

        assert root_logins_with_unix_socket.stdout.strip() == "root	localhost"

def test_debian_system_maintenance_user_is_absent(host):

    """

    Tests if the dedicated Debian system maintenance user is absent

    (leftover from Debian Jessie and previous versions).

    """

    with host.sudo():

        debian_system_maintenance_user = host.run("mysql -BNe %s", "select count(*) from mysql.user where user = 'debian-sys-maint'")

        assert debian_system_maintenance_user.rc == 0

        assert debian_system_maintenance_user.stdout.strip() == "0"

def test_debian_system_maintenance_configuration_file(host):

    """

    Tests if the Debian system maintenance configuration file has been

    set-up properly.

    """

    with host.sudo():

        config = host.file("/etc/mysql/debian.cnf")

        assert config.is_file

        assert config.user == 'root'

        assert config.group == 'root'

        assert config.mode == 0o600

        assert "debian-sys-maint" not in config.content_string

def test_debian_system_maintenance_configuration_file_can_be_used_for_login(host):

    """

    Tests if the Debian system maintenance configuration file can be

    used for authenticating as the root user.

    """

    with host.sudo():

        login = host.run("mysql --defaults-file=/etc/mysql/debian.cnf -NBe %s", "select current_user();")

        assert login.rc == 0

        assert login.stdout.strip() == "root@localhost"

- name: Check if root user authentication is based on use of unix_socket module

  command: mysql --skip-column-names -B -e "select 1 from mysql.user where user='root' and host='localhost' and plugin='unix_socket';"

  register: "root_using_unix_socket_authentication"

  changed_when: false

# @TODO: It should be possible to replace this with mysql_user

#        invocation once MAR gets upgraded to use Ansible 2.10.x,

#        where mysql_user module has support for specifying the

#        authentication plugin. Once the switch is done, the above

#        task that registers the root_using_unix_socket_authentication

#        variable can be dropped as well.

- name: Set-up unix socket authentication for the root user

  command: mysql --skip-column-names -B -e "grant all privileges on *.* to root@localhost identified via unix_socket;"

  when: "not root_using_unix_socket_authentication.stdout"

- name: Check if there are any root-like database accounts available where host is not localhost

  command: mysql --skip-column-names -B -e "select 1 from mysql.user where user='root' and host!='localhost';"

  register: "additional_root_users"

  changed_when: false

- name: Drop all excess root user logins

  command:

    argv:

      - "mysql"

      - "-N"

      - "-B"

      - "-e"

      - "delete from mysql.user where User='root' and Host != 'localhost'; flush privileges;"

  when: "additional_root_users.stdout"

- name: Remove (now deprecated) my.cnf configuration file for the root database user

  file:

    path: "/root/.my.cnf"

    state: absent

- name: Check if Debian system maintenance configuration file uses the root account

  command: "grep -q 'user.*=.*root' /etc/mysql/debian.cnf"

  register: debian_maintenance_configuration_uses_root

  failed_when: false

  changed_when: false

- name: Deploy Debian system maintenance configuration file that uses root account

  copy:

    src: "debian.cnf"

    dest: "/etc/mysql/debian.cnf"

    owner: root

    group: root

    mode: 0600

  when: "debian_maintenance_configuration_uses_root.rc != 0"

- name: Drop the dedicated Debian system maintenance user

  mysql_user:

    name: "debian-sys-maint"

    state: absent

    login_unix_socket: "/var/run/mysqld/mysqld.sock"