Changeset - 5dd6b0b1cc59
Parent rev.
Child rev.
[Not reviewed]
0 4 0
Branko Majic (branko) - 3 years ago 2020-12-23 22:13:25
branko@majic.rs
MAR-168: Drop the user of passwords for the root MySQL account:

- Rely on unix_socket authentication plugin instead.
- Updated role reference documentation.
- Updated tests.
4 files changed with 84 insertions and 73 deletions:
docs/releasenotes.rst
6
docs/rolereference.rst
2
9
roles/database_server/molecule/default/tests/test_default.py
46
38
roles/database_server/tasks/main.yml
30
26
0 comments (0 inline, 0 general)
docs/releasenotes.rst
➞
Show inline comments
 
@@ -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.

			




        

    


    
    

    

        

                

                    docs/rolereference.rst
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -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

			




        

    


    
    

    

        

                

                    roles/database_server/molecule/default/tests/test_default.py
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -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"

			




        

    


    
    

    

        

                

                    roles/database_server/tasks/main.yml
                

                

                  
                      
                        

                      ➞

                      
                        
                  

                  
                      
                  
                      
                  
                      
                  
                      
                  
                  
                

                

                    Show inline comments
                    
                

        

        

            



	
	
		
 
@@ -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:

			




        

    



    


    



    



      

      





    
    0 comments (0 inline, 0 general)
    





    


  

  







  


    




    








    
        
    
    
        This site is powered by
            Kallithea 0.7.0,
        which is
        © 2010–2023 by various authors & licensed under GPLv3.