diff --git a/docs/releasenotes.rst b/docs/releasenotes.rst index 99c60223fe324abe521f896ca9768f781639b3e7..83390b6c2bbca692991b4385a83393ed5753e813 100644 --- a/docs/releasenotes.rst +++ b/docs/releasenotes.rst @@ -34,6 +34,12 @@ upgrade to Python 3.x, dropping support for Python 2.7. required in order to fix the deprecation warnings being sent out when the ``pip_check_requirements_upgrades.sh`` script is run. +* ``database_server`` role + + * Parameter ``db_root_password`` has been deprecated. The root user + can now login into the database (as the root database user) via + unix socket authentication. + * ``ldap_server`` role * Parameter ``ldap_server_domain`` is now mandatory. diff --git a/docs/rolereference.rst b/docs/rolereference.rst index 4336da1eb0f4bf585dca379d4f5241e7a8a5878e..00286d3c22b478b56a8458af8cae22ca99d5ee0a 100644 --- a/docs/rolereference.rst +++ b/docs/rolereference.rst @@ -2023,8 +2023,7 @@ Depends on the following roles: Parameters ~~~~~~~~~~ -**db_root_password** (string, mandatory) - Password for the *root* database user. +This role has no parameters. Distribution compatibility @@ -2038,13 +2037,7 @@ Role is compatible with the following distributions: Examples ~~~~~~~~ -Here is an example configuration for setting-up the database server: - -.. code-block:: yaml - - --- - - db_root_password: root +This role has no parameters which can be configured configure. Database diff --git a/roles/database_server/molecule/default/tests/test_default.py b/roles/database_server/molecule/default/tests/test_default.py index 13adfdafe0fe3bd721989cb5e0e575585a1261a9..c4bcce1edb3772115c4296e339d63954e2190bd6 100644 --- a/roles/database_server/molecule/default/tests/test_default.py +++ b/roles/database_server/molecule/default/tests/test_default.py @@ -33,50 +33,40 @@ def test_service(host): assert service.is_running -def test_root_password(host): +def test_root_my_cnf_is_absent(host): """ - Tests if the root password has been set correctly. + 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). """ - login = host.run("mysql -uroot -proot_password -BNe 'show databases'") + with host.sudo(): - assert login.rc == 0 - assert "information_schema" in login.stdout - assert "mysql" in login.stdout - assert "performance_schema" in login.stdout + assert not host.file('/root/.my.cnf').exists -def test_root_my_cnf(host): +def test_root_password_is_not_empty(host): """ - Tests if the root my.cnf configuration has been set-up with correct - user/password and permissions. + Tests if the database server root password is empty. """ - with host.sudo(): + login = host.run("mysql -uroot -BNe 'show databases'") - my_cnf = host.file('/root/.my.cnf') + assert login.rc != 0 - assert my_cnf.is_file - assert my_cnf.user == 'root' - assert my_cnf.group == 'root' - assert my_cnf.mode == 0o400 - assert "user=root" in my_cnf.content_string - assert "password=root_password" in my_cnf.content_string - -def test_root_my_cnf_login(host): +def test_root_os_user_can_login(host): """ - Tets if the database server root login works using just the my.cnf - configuration file. + 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'") - login = host.run("mysql -BNe 'show databases'") - - assert "information_schema" in login.stdout - assert "mysql" in login.stdout - assert "performance_schema" in login.stdout + assert login.rc == 0 + assert "information_schema" in login.stdout + assert "mysql" in login.stdout + assert "performance_schema" in login.stdout def test_utf8_configuration_file(host): @@ -98,20 +88,38 @@ def test_utf8_configuration(host): Tests if UTF-8 configuration has been applied correctly to server. """ - assert host.run("mysql -uroot -proot_password -BNe 'drop database if exists test'").rc == 0 - assert host.run("mysql -uroot -proot_password -BNe 'create database test'").rc == 0 + with host.sudo(): + assert host.run("mysql -uroot -BNe 'drop database if exists test'").rc == 0 + assert host.run("mysql -uroot -BNe 'create database test'").rc == 0 + + check_server = host.run("mysql -uroot test -BNe 'select @@character_set_server; select @@collation_server'") + + assert check_server.rc == 0 + assert check_server.stdout == "utf8\nutf8_general_ci\n" + + check_database = host.run("mysql -uroot test -BNe 'select @@character_set_database; select @@collation_database'") + + assert check_database.rc == 0 + assert check_database.stdout == "utf8\nutf8_general_ci\n" - check_server = host.run("mysql -uroot -proot_password test -BNe 'select @@character_set_server; select @@collation_server'") + check_database = host.run("mysql -uroot -BNe 'select @@character_set_connection; select @@collation_connection'") - assert check_server.rc == 0 - assert check_server.stdout == "utf8\nutf8_general_ci\n" + assert check_database.rc == 0 + assert check_database.stdout == "utf8\nutf8_general_ci\n" - check_database = host.run("mysql -uroot -proot_password test -BNe 'select @@character_set_database; select @@collation_database'") - assert check_database.rc == 0 - assert check_database.stdout == "utf8\nutf8_general_ci\n" +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'") - check_database = host.run("mysql -uroot -proot_password -BNe 'select @@character_set_connection; select @@collation_connection'") + assert root_logins_without_unix_socket_count.rc == 0 + assert root_logins_without_unix_socket_count.stdout.strip() == "0" - assert check_database.rc == 0 - assert check_database.stdout == "utf8\nutf8_general_ci\n" + assert root_logins_with_unix_socket.rc == 0 + assert root_logins_with_unix_socket.stdout.strip() == "root localhost" diff --git a/roles/database_server/tasks/main.yml b/roles/database_server/tasks/main.yml index fc2a5477801025845738afdade6630e10e3469b6..a6fbdd5e9c5804e839728e92b8a2d51da15fc5fb 100644 --- a/roles/database_server/tasks/main.yml +++ b/roles/database_server/tasks/main.yml @@ -18,36 +18,40 @@ name: mysql state: started -- name: Set password for the root database user - mysql_user: - check_implicit_admin: true - name: root - password: "{{ db_root_password }}" - -- name: Deploy username and password for the root database user - template: - src: "root_my.cnf.j2" - dest: "/root/.my.cnf" - owner: root - group: root - mode: 0400 - - 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 plugin='unix_socket';" + 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: This is essentially a leftover from the days of Debian -# Jessie, which by default did not use unix socket -# authentication for the root account. To make the deployment -# the same on both distros, unix socket authentication was -# disabled on Debian Stretch at the time. It might be worth the -# effort to revisit this and figure out if unix socket -# authentication should be reenabled instead (and whether -# password for the root account should be used at all). -- 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;\"" - when: "root_using_unix_socket_authentication.stdout" +# @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: Set UTF-8 encoding as default for MariaDB copy: