diff options
Diffstat (limited to 'ansible_collections/community/mysql/tests')
23 files changed, 1429 insertions, 88 deletions
diff --git a/ansible_collections/community/mysql/tests/integration/targets/setup_controller/tasks/verify.yml b/ansible_collections/community/mysql/tests/integration/targets/setup_controller/tasks/verify.yml index 74aa0f26e..e5b4c9410 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/setup_controller/tasks/verify.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/setup_controller/tasks/verify.yml @@ -19,8 +19,11 @@ - name: Assert that test container runs the expected MySQL/MariaDB version assert: that: - - "'{{ primary_info.version.major }}.{{ primary_info.version.minor }}\ - .{{ primary_info.version.release }}' == '{{ db_version }}'" + - registred_db_version == db_version + vars: + registred_db_version: + "{{ primary_info.version.major }}.{{ primary_info.version.minor }}\ + .{{ primary_info.version.release }}" - name: Assert that mysql_info module used the expected version of pymysql assert: @@ -52,8 +55,9 @@ - name: Assert that we run the expected ansible version assert: that: - - > - "{{ ansible_version.major }}.{{ ansible_version.minor }}" - is version(test_ansible_version, '==') + - ansible_running_version == test_ansible_version + vars: + ansible_running_version: + "{{ ansible_version.major }}.{{ ansible_version.minor }}" when: - test_ansible_version != 'devel' # Devel will change overtime diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_db/tasks/state_dump_import.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_db/tasks/state_dump_import.yml index b4f9cda9b..e4ae76283 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_db/tasks/state_dump_import.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_db/tasks/state_dump_import.yml @@ -339,7 +339,7 @@ assert: that: - result is changed - - "result.db =='{{ db_name }}'" + - result.db == db_name # - name: Dump and Import | Assert database was backed up successfully # command: "file {{ db_file_name }}" diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/files/users_info_create_procedure.sql b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/files/users_info_create_procedure.sql new file mode 100644 index 000000000..5a358f0aa --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/files/users_info_create_procedure.sql @@ -0,0 +1,7 @@ +DELIMITER // +DROP PROCEDURE IF EXISTS users_info_db.get_all_items; +CREATE PROCEDURE users_info_db.get_all_items() +BEGIN +SELECT * from users_info_db.t1; +END // +DELIMITER ; diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml new file mode 100644 index 000000000..2c126c12f --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml @@ -0,0 +1,280 @@ +--- + +- module_defaults: + community.mysql.mysql_db: &mysql_defaults + login_user: "{{ mysql_user }}" + login_password: "{{ mysql_password }}" + login_host: "{{ mysql_host }}" + login_port: "{{ mysql_primary_port }}" + community.mysql.mysql_query: *mysql_defaults + community.mysql.mysql_info: *mysql_defaults + community.mysql.mysql_user: *mysql_defaults + + block: + + # ================================ Prepare ============================== + - name: Mysql_info users_info | Create databases + community.mysql.mysql_db: + name: + - users_info_db + - users_info_db2 + - users_info_db3 + state: present + + - name: Mysql_info users_info | Create tables + community.mysql.mysql_query: + query: + - >- + CREATE TABLE IF NOT EXISTS users_info_db.t1 + (id int, name varchar(9)) + - >- + CREATE TABLE IF NOT EXISTS users_info_db.T_UPPER + (id int, name1 varchar(9), NAME2 varchar(9), Name3 varchar(9)) + + # I failed to create a procedure using community.mysql.mysql_query. + # Maybe it's because we must changed the delimiter. + - name: Mysql_info users_info | Create procedure SQL file + ansible.builtin.template: + src: files/users_info_create_procedure.sql + dest: /root/create_procedure.sql + owner: root + group: root + mode: '0700' + + - name: Mysql_info users_info | Create a procedure + community.mysql.mysql_db: + name: all + state: import + target: /root/create_procedure.sql + + # Use a query instead of mysql_user, because we want to caches differences + # at the end and a bug in mysql_user would be invisible to this tests + - name: Mysql_info users_info | Prepare common tests users + community.mysql.mysql_query: + query: + - >- + CREATE USER users_info_adm@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - > + GRANT ALL ON *.* to users_info_adm@'users_info.com' WITH GRANT + OPTION + + - >- + CREATE USER users_info_schema@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT SELECT, INSERT, UPDATE, DELETE ON users_info_db.* TO + users_info_schema@'users_info.com' + + - >- + CREATE USER users_info_table@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT SELECT, INSERT, UPDATE ON users_info_db.t1 TO + users_info_table@'users_info.com' + + - >- + CREATE USER users_info_col@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + WITH MAX_USER_CONNECTIONS 100 + - >- + GRANT SELECT (id) ON users_info_db.t1 TO + users_info_col@'users_info.com' + + - >- + CREATE USER users_info_proc@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + WITH MAX_USER_CONNECTIONS 2 MAX_CONNECTIONS_PER_HOUR 60 + - >- + GRANT EXECUTE ON PROCEDURE users_info_db.get_all_items TO + users_info_proc@'users_info.com' + + - >- + CREATE USER users_info_multi@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT SELECT ON mysql.* TO + users_info_multi@'users_info.com' + - >- + GRANT ALL ON users_info_db.* TO + users_info_multi@'users_info.com' + - >- + GRANT ALL ON users_info_db2.* TO + users_info_multi@'users_info.com' + - >- + GRANT ALL ON users_info_db3.* TO + users_info_multi@'users_info.com' + + - >- + CREATE USER users_info_usage_only@'users_info.com' IDENTIFIED WITH + mysql_native_password AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT USAGE ON *.* TO + users_info_usage_only@'users_info.com' + + - >- + CREATE USER users_info_columns_uppercase@'users_info.com' + IDENTIFIED WITH mysql_native_password AS + '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT SELECT,UPDATE(name1,NAME2,Name3) ON users_info_db.T_UPPER TO + users_info_columns_uppercase@'users_info.com' + + - >- + CREATE USER users_info_multi_hosts@'%' + IDENTIFIED WITH mysql_native_password AS + '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - GRANT SELECT ON users_info_db.* TO users_info_multi_hosts@'%' + + - >- + CREATE USER users_info_multi_hosts@'localhost' + IDENTIFIED WITH mysql_native_password AS + '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - >- + GRANT SELECT ON users_info_db.* TO + users_info_multi_hosts@'localhost' + + - >- + CREATE USER users_info_multi_hosts@'host1' + IDENTIFIED WITH mysql_native_password AS + '*6C387FC3893DBA1E3BA155E74754DA6682D04747' + - GRANT SELECT ON users_info_db.* TO users_info_multi_hosts@'host1' + + # Different password than the others users_info_multi_hosts + - >- + CREATE USER users_info_multi_hosts@'host2' + IDENTIFIED WITH mysql_native_password AS + '*CB3326D5279DE7915FE5D743232165EE887883CA' + - GRANT SELECT ON users_info_db.* TO users_info_multi_hosts@'host2' + + - name: Mysql_info users_info | Prepare tests users for MariaDB + community.mysql.mysql_user: + name: "{{ item.name }}" + host: "users_info.com" + plugin: "{{ item.plugin | default(omit) }}" + plugin_auth_string: "{{ item.plugin_auth_string | default(omit) }}" + plugin_hash_string: "{{ item.plugin_hash_string | default(omit) }}" + tls_require: "{{ item.tls_require | default(omit) }}" + priv: "{{ item.priv }}" + resource_limits: "{{ item.resource_limits | default(omit) }}" + column_case_sensitive: true + state: present + loop: + - name: users_info_socket # Only for MariaDB + priv: + '*.*': 'ALL' + plugin: 'unix_socket' + when: + - db_engine == 'mariadb' + + - name: Mysql_info users_info | Prepare tests users for MySQL + community.mysql.mysql_user: + name: "{{ item.name }}" + host: "users_info.com" + plugin: "{{ item.plugin | default(omit) }}" + plugin_auth_string: "{{ item.plugin_auth_string | default(omit) }}" + plugin_hash_string: "{{ item.plugin_hash_string | default(omit) }}" + tls_require: "{{ item.tls_require | default(omit) }}" + priv: "{{ item.priv }}" + resource_limits: "{{ item.resource_limits | default(omit) }}" + column_case_sensitive: true + state: present + loop: + - name: users_info_sha256 # Only for MySQL + priv: + '*.*': 'ALL' + plugin_auth_string: + '$5$/<w*D`L4\"F$WQiI1Pev.7atAh8udYs3wqlzgdfV8LXoy7rqSEC7NF2' + plugin: 'sha256_password' + when: + - db_engine == 'mysql' + + - name: Mysql_info users_info | Prepare tests users for MySQL 8+ + community.mysql.mysql_user: + name: "{{ item.name }}" + host: "users_info.com" + plugin: "{{ item.plugin | default(omit) }}" + plugin_auth_string: "{{ item.plugin_auth_string | default(omit) }}" + plugin_hash_string: "{{ item.plugin_hash_string | default(omit) }}" + tls_require: "{{ item.tls_require | default(omit) }}" + priv: "{{ item.priv }}" + resource_limits: "{{ item.resource_limits | default(omit) }}" + column_case_sensitive: true + state: present + loop: + - name: users_info_caching_sha2 # Only for MySQL 8+ + priv: + '*.*': 'ALL' + plugin_auth_string: + '$A$005$61j/uF%Qb4-=O2xkeO82u2HNkF.lxDq0liO4U3xqi7bDUCbWM6HayRXWn1' + plugin: 'caching_sha2_password' + when: + - db_engine == 'mysql' + - db_version is version('8.0', '>=') + + # ================================== Tests ============================== + + - name: Mysql_info users_info | Collect users_info + community.mysql.mysql_info: + filter: + - users_info + register: result + + - name: Recreate users from mysql_info users_info result + community.mysql.mysql_user: + name: "{{ item.name }}" + host: "{{ item.host }}" + plugin: "{{ item.plugin | default(omit) }}" + plugin_auth_string: "{{ item.plugin_auth_string | default(omit) }}" + plugin_hash_string: "{{ item.plugin_hash_string | default(omit) }}" + tls_require: "{{ item.tls_require | default(omit) }}" + priv: "{{ item.priv | default(omit) }}" + resource_limits: "{{ item.resource_limits | default(omit) }}" + column_case_sensitive: true + state: present + loop: "{{ result.users_info }}" + loop_control: + label: "{{ item.name }}@{{ item.host }}" + register: recreate_users_result + failed_when: + - recreate_users_result is changed + when: + - item.name != 'root' + - item.name != 'mysql' + - item.name != 'mariadb.sys' + - item.name != 'mysql.sys' + - item.name != 'mysql.infoschema' + + + # ================================== Cleanup ============================ + + - name: Mysql_info users_info | Cleanup users_info + community.mysql.mysql_user: + name: "{{ item }}" + host_all: true + column_case_sensitive: true + state: absent + loop: + - users_info_adm + - users_info_schema + - users_info_table + - users_info_col + - users_info_proc + - users_info_multi + - users_info_db + - users_info_usage_only + - users_info_columns_uppercase + - users_info_multi_hosts + + - name: Mysql_info users_info | Cleanup databases + community.mysql.mysql_db: + name: + - users_info_db + - users_info_db2 + - users_info_db3 + state: absent + + - name: Mysql_info users_info | Cleanup sql file for the procedure + ansible.builtin.file: + path: /root/create_procedure.sql + state: absent diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/main.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/main.yml index be367f068..5d34da9a0 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/main.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_info/tasks/main.yml @@ -219,3 +219,7 @@ assert: that: - result.databases.allviews.size == 0 + + - name: Import tasks file to tests users_info filter + ansible.builtin.import_tasks: + file: filter_users_info.yml diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_channel.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_channel.yml index f438dbf09..7d37df05f 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_channel.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_channel.yml @@ -34,8 +34,14 @@ - assert: that: - - result is changed - - result.queries == ["CHANGE MASTER TO MASTER_HOST='{{ mysql_host }}',MASTER_USER='{{ replication_user }}',MASTER_PASSWORD='********',MASTER_PORT={{ mysql_primary_port }},MASTER_LOG_FILE='{{ mysql_primary_status.File }}',MASTER_LOG_POS={{ mysql_primary_status.Position }} FOR CHANNEL '{{ test_channel }}'"] + - result is changed + - result.queries == result_query + vars: + result_query: ["CHANGE MASTER TO MASTER_HOST='{{ mysql_host }}',\ + MASTER_USER='{{ replication_user }}',MASTER_PASSWORD='********',\ + MASTER_PORT={{ mysql_primary_port }},MASTER_LOG_FILE=\ + '{{ mysql_primary_status.File }}',MASTER_LOG_POS=\ + {{ mysql_primary_status.Position }} FOR CHANNEL '{{ test_channel }}'"] # Test startreplica mode: - name: Start replica with channel @@ -48,8 +54,11 @@ - assert: that: - - result is changed - - result.queries == ["START SLAVE FOR CHANNEL '{{ test_channel }}'"] or result.queries == ["START REPLICA FOR CHANNEL '{{ test_channel }}'"] + - result is changed + - result.queries == result_query or result_query2 + vars: + result_query: ["START SLAVE FOR CHANNEL '{{ test_channel }}'"] + result_query2: ["START REPLICA FOR CHANNEL '{{ test_channel }}'"] # Test getreplica mode: - name: Get standby status with channel @@ -62,26 +71,34 @@ - assert: that: - - replica_status.Is_Replica == true - - replica_status.Master_Host == '{{ mysql_host }}' - - replica_status.Exec_Master_Log_Pos == mysql_primary_status.Position - - replica_status.Master_Port == {{ mysql_primary_port }} - - replica_status.Last_IO_Errno == 0 - - replica_status.Last_IO_Error == '' - - replica_status.Channel_Name == '{{ test_channel }}' - - replica_status is not changed + - replica_status.Is_Replica is truthy(convert_bool=True) + - replica_status.Master_Host == mysql_host_value + - replica_status.Exec_Master_Log_Pos == mysql_primary_status.Position + - replica_status.Master_Port == mysql_primary_port_value + - replica_status.Last_IO_Errno == 0 + - replica_status.Last_IO_Error == '' + - replica_status.Channel_Name == test_channel_value + - replica_status is not changed + vars: + mysql_host_value: '{{ mysql_host }}' + mysql_primary_port_value: '{{ mysql_primary_port }}' + test_channel_value: '{{ test_channel }}' when: mysql8022_and_higher == false - assert: that: - - replica_status.Is_Replica == true - - replica_status.Source_Host == '{{ mysql_host }}' - - replica_status.Exec_Source_Log_Pos == mysql_primary_status.Position - - replica_status.Source_Port == {{ mysql_primary_port }} - - replica_status.Last_IO_Errno == 0 - - replica_status.Last_IO_Error == '' - - replica_status.Channel_Name == '{{ test_channel }}' - - replica_status is not changed + - replica_status.Is_Replica is truthy(convert_bool=True) + - replica_status.Source_Host == mysql_host_value + - replica_status.Exec_Source_Log_Pos == mysql_primary_status.Position + - replica_status.Source_Port == mysql_primary_port_value + - replica_status.Last_IO_Errno == 0 + - replica_status.Last_IO_Error == '' + - replica_status.Channel_Name == test_channel_value + - replica_status is not changed + vars: + mysql_host_value: '{{ mysql_host }}' + mysql_primary_port_value: '{{ mysql_primary_port }}' + test_channel_value: '{{ test_channel }}' when: mysql8022_and_higher == true @@ -96,8 +113,11 @@ - assert: that: - - result is changed - - result.queries == ["STOP SLAVE FOR CHANNEL '{{ test_channel }}'"] or result.queries == ["STOP REPLICA FOR CHANNEL '{{ test_channel }}'"] + - result is changed + - result.queries == result_query or result.queries == result_query2 + vars: + result_query: ["STOP SLAVE FOR CHANNEL '{{ test_channel }}'"] + result_query2: ["STOP REPLICA FOR CHANNEL '{{ test_channel }}'"] # Test reset - name: Reset replica with channel @@ -110,8 +130,11 @@ - assert: that: - - result is changed - - result.queries == ["RESET SLAVE FOR CHANNEL '{{ test_channel }}'"] or result.queries == ["RESET REPLICA FOR CHANNEL '{{ test_channel }}'"] + - result is changed + - result.queries == result_query or result.queries == result_query2 + vars: + result_query: ["RESET SLAVE FOR CHANNEL '{{ test_channel }}'"] + result_query2: ["RESET REPLICA FOR CHANNEL '{{ test_channel }}'"] # Test reset all - name: Reset replica all with channel @@ -124,5 +147,8 @@ - assert: that: - - result is changed - - result.queries == ["RESET SLAVE ALL FOR CHANNEL '{{ test_channel }}'"] or result.queries == ["RESET REPLICA ALL FOR CHANNEL '{{ test_channel }}'"] + - result is changed + - result.queries == result_query or result.queries == result_query2 + vars: + result_query: ["RESET SLAVE ALL FOR CHANNEL '{{ test_channel }}'"] + result_query2: ["RESET REPLICA ALL FOR CHANNEL '{{ test_channel }}'"] diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_initial.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_initial.yml index ca7301c5b..ea7a5ac8f 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_initial.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_replication/tasks/mysql_replication_initial.yml @@ -158,7 +158,13 @@ assert: that: - result is changed - - result.queries == ["CHANGE MASTER TO MASTER_HOST='{{ mysql_host }}',MASTER_USER='{{ replication_user }}',MASTER_PASSWORD='********',MASTER_PORT={{ mysql_primary_port }},MASTER_LOG_FILE='{{ mysql_primary_status.File }}',MASTER_LOG_POS={{ mysql_primary_status.Position }},MASTER_SSL=0,MASTER_SSL_CA=''"] + - result.queries == expected_queries + vars: + expected_queries: ["CHANGE MASTER TO MASTER_HOST='{{ mysql_host }}',\ + MASTER_USER='{{ replication_user }}',MASTER_PASSWORD='********',\ + MASTER_PORT={{ mysql_primary_port }},MASTER_LOG_FILE=\ + '{{ mysql_primary_status.File }}',MASTER_LOG_POS=\ + {{ mysql_primary_status.Position }},MASTER_SSL=0,MASTER_SSL_CA=''"] # Test startreplica mode: - name: Start replica @@ -185,26 +191,32 @@ - name: Assert that getreplica returns expected values for MySQL older than 8.0.22 and Mariadb assert: that: - - replica_status.Is_Replica == true - - replica_status.Master_Host == '{{ mysql_host }}' + - replica_status.Is_Replica is truthy(convert_bool=True) + - replica_status.Master_Host == mysql_host_value - replica_status.Exec_Master_Log_Pos == mysql_primary_status.Position - - replica_status.Master_Port == {{ mysql_primary_port }} + - replica_status.Master_Port == mysql_primary_port_value - replica_status.Last_IO_Errno == 0 - replica_status.Last_IO_Error == '' - replica_status is not changed - when: mysql8022_and_higher == false + vars: + mysql_host_value: "{{ mysql_host }}" + mysql_primary_port_value: "{{ mysql_primary_port }}" + when: mysql8022_and_higher is falsy(convert_bool=True) - name: Assert that getreplica returns expected values for MySQL newer than 8.0.22 assert: that: - - replica_status.Is_Replica == true - - replica_status.Source_Host == '{{ mysql_host }}' + - replica_status.Is_Replica is truthy(convert_bool=True) + - replica_status.Source_Host == mysql_host_value - replica_status.Exec_Source_Log_Pos == mysql_primary_status.Position - - replica_status.Source_Port == {{ mysql_primary_port }} + - replica_status.Source_Port == mysql_primary_port_value - replica_status.Last_IO_Errno == 0 - replica_status.Last_IO_Error == '' - replica_status is not changed - when: mysql8022_and_higher == true + vars: + mysql_host_value: "{{ mysql_host }}" + mysql_primary_port_value: "{{ mysql_primary_port }}" + when: mysql8022_and_higher is truthy(convert_bool=True) # Create test table and add data to it: - name: Create test table diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/main.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/main.yml index b517fc053..44e3308e2 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/main.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/main.yml @@ -18,3 +18,7 @@ - include_tasks: test_priv_subtract.yml vars: enable_check_mode: yes + +- name: Test column case sensitive + ansible.builtin.import_tasks: + file: test_column_case_sensitive.yml diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/test_column_case_sensitive.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/test_column_case_sensitive.yml new file mode 100644 index 000000000..74849e066 --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_role/tasks/test_column_case_sensitive.yml @@ -0,0 +1,149 @@ +--- + +- vars: + mysql_parameters: &mysql_params + login_user: '{{ mysql_user }}' + login_password: '{{ mysql_password }}' + login_host: '{{ mysql_host }}' + login_port: '{{ mysql_primary_port }}' + + block: + + # ========================= Prepare ======================================= + # We use query to prevent our module of changing the case + - name: Mysql_role Column case sensitive | Create a test table + community.mysql.mysql_query: + <<: *mysql_params + query: + - CREATE DATABASE mysql_role_column_case + - >- + CREATE TABLE mysql_role_column_case.t1 + (a int, B int, cC int, Dd int) + - >- + INSERT INTO mysql_role_column_case.t1 + (a, B, cC, Dd) VALUES (1,2,3,4) + + - name: Mysql_role Column case sensitive | Create users + community.mysql.mysql_user: + <<: *mysql_params + name: column_case_sensitive + host: '%' + password: 'msandbox' + + # ================= Reproduce failure ===================================== + + - name: Mysql_role Column case sensitive | Create role + community.mysql.mysql_role: + <<: *mysql_params + name: 'role_column_case_sensitive' + state: present + members: + - 'column_case_sensitive@%' + priv: + 'mysql_role_column_case.t1': 'SELECT(a, B, cC, Dd)' + + - name: Mysql_role Column case sensitive | Assert role privileges are all caps + community.mysql.mysql_query: + <<: *mysql_params + query: + - SHOW GRANTS FOR role_column_case_sensitive + register: column_case_insensitive_grants + failed_when: + # Column order may vary, thus test each separately + - >- + column_case_insensitive_grants.query_result[0][1] + is not search("A", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("B", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("CC", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("DD", ignorecase=false) + + - name: Mysql_role Column case sensitive | Assert 1 column is accessible on MySQL + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - DESC mysql_role_column_case.t1 + register: assert_1_col_accessible + failed_when: + - assert_1_col_accessible.rowcount[0] | int != 1 + when: + - db_engine == 'mysql' + + - name: Mysql_role Column case sensitive | Assert 4 column are accessible on MariaDB + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - SET ROLE role_column_case_sensitive + - DESC mysql_role_column_case.t1 + register: assert_4_col_accessible + failed_when: + - assert_4_col_accessible.rowcount[1] | int != 4 + when: + - db_engine == 'mariadb' + + # ====================== Test the fix ===================================== + + - name: Mysql_role Column case sensitive | Recreate role with case sensitive + community.mysql.mysql_role: + <<: *mysql_params + name: 'role_column_case_sensitive' + state: present + members: + - 'column_case_sensitive@%' + priv: + 'mysql_role_column_case.t1': 'SELECT(a, B, cC, Dd)' + column_case_sensitive: true + + - name: Mysql_role Column case sensitive | Assert role privileges are case sensitive + community.mysql.mysql_query: + <<: *mysql_params + query: + - SHOW GRANTS FOR role_column_case_sensitive + register: column_case_sensitive_grants + failed_when: + # Column order may vary, thus test each separately + - >- + column_case_sensitive_grants.query_result[0][1] + is not search("a", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("B", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("cC", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("Dd", ignorecase=false) + + - name: Mysql_role Column case sensitive | Assert 4 columns are accessible + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - SET ROLE role_column_case_sensitive + - DESC mysql_role_column_case.t1 + register: assert_4_col_accessible + failed_when: + - assert_4_col_accessible.rowcount[1] | int != 4 + + # ========================= Teardown ====================================== + + - name: Mysql_role Column case sensitive | Delete test users + community.mysql.mysql_user: + <<: *mysql_params + name: column_case_sensitive + host_all: true + state: absent + + - name: Mysql_role Column case sensitive | Delete role + community.mysql.mysql_role: + <<: *mysql_params + name: 'role_column_case_sensitive' + state: absent + + - name: Mysql_role Column case sensitive | Delete test database + community.mysql.mysql_db: + <<: *mysql_params + name: mysql_role_column_case + state: absent diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/main.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/main.yml index dc5c9d3cd..8ec0798c5 100644 --- a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/main.yml +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/main.yml @@ -43,6 +43,8 @@ - include_tasks: test_idempotency.yml + - include_tasks: test_password_expire.yml + # ============================================================ # Create user with no privileges and verify default privileges are assign # @@ -117,8 +119,8 @@ - name: Assert grant access for user1 on multiple database assert: that: - - "'{{ item }}' in result.stdout" - with_items: "{{ db_names }}" + - item in result.stdout + loop: "{{ db_names }}" - name: Show grants access for user2 on multiple database command: "{{ mysql_command }} -e \"SHOW GRANTS FOR '{{ user_name_2 }}'@'localhost'\"" @@ -127,8 +129,8 @@ - name: Assert grant access for user2 on multiple database assert: that: - - "'{{ item }}' in result.stdout" - with_items: "{{db_names}}" + - item in result.stdout + loop: "{{db_names}}" - include_tasks: utils/remove_user.yml vars: @@ -267,6 +269,9 @@ tags: - issue_465 + # Tests for user attributes + - include_tasks: test_user_attributes.yml + # Tests for the TLS requires dictionary - include_tasks: test_tls_requirements.yml @@ -286,3 +291,7 @@ - include_tasks: test_user_grants_with_roles_applied.yml - include_tasks: test_revoke_only_grant.yml + + - name: Mysql_user - test column case sensitive + ansible.builtin.import_tasks: + file: test_column_case_sensitive.yml diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_column_case_sensitive.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_column_case_sensitive.yml new file mode 100644 index 000000000..68e95aa4e --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_column_case_sensitive.yml @@ -0,0 +1,134 @@ +--- + +- vars: + mysql_parameters: &mysql_params + login_user: '{{ mysql_user }}' + login_password: '{{ mysql_password }}' + login_host: '{{ mysql_host }}' + login_port: '{{ mysql_primary_port }}' + + block: + + # ========================= Prepare ======================================= + # We use query to prevent our module of changing the case + - name: Mysql_user Column case sensitive | Create a test table + community.mysql.mysql_query: + <<: *mysql_params + query: + - CREATE DATABASE mysql_user_column_case + - >- + CREATE TABLE mysql_user_column_case.t1 + (a int, B int, cC int, Dd int) + - >- + INSERT INTO mysql_user_column_case.t1 + (a, B, cC, Dd) VALUES (1,2,3,4) + + # ================= Reproduce failure ===================================== + + - name: Mysql_user Column case sensitive | Create test user + community.mysql.mysql_user: + <<: *mysql_params + name: column_case_sensitive + host: '%' + password: 'msandbox' + priv: + 'mysql_user_column_case.t1': 'SELECT(a, B, cC, Dd)' + + - name: Mysql_user Column case sensitive | Assert user privileges are all caps + community.mysql.mysql_query: + <<: *mysql_params + query: + - SHOW GRANTS FOR column_case_sensitive@'%' + register: column_case_insensitive_grants + failed_when: + # Column order may vary, thus test each separately + - >- + column_case_insensitive_grants.query_result[0][1] + is not search("A", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("B", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("CC", ignorecase=false) + or column_case_insensitive_grants.query_result[0][1] + is not search("DD", ignorecase=false) + + - name: Mysql_user Column case sensitive | Assert 1 column is accessible on MySQL 5.7 + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - DESC mysql_user_column_case.t1 + register: assert_1_col_accessible + failed_when: + - assert_1_col_accessible.rowcount[0] | int != 1 + when: + - db_engine == 'mysql' and db_version is version('5.7', '<=') + + - name: Mysql_user Column case sensitive | Assert 4 column are accessible on MariaDB and MySQL 8+ + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - DESC mysql_user_column_case.t1 + register: assert_4_col_accessible + failed_when: + - assert_4_col_accessible.rowcount[0] | int != 4 + when: + - >- + db_engine == 'mariadb' + or (db_engine == 'mysql' and db_version is version('8.0', '>=')) + + # ======================== Test fix ====================================== + + - name: Mysql_user Column case sensitive | Create users with case sensitive + community.mysql.mysql_user: + <<: *mysql_params + name: column_case_sensitive + host: '%' + password: 'msandbox' + priv: + 'mysql_user_column_case.t1': 'SELECT(a, B, cC, Dd)' + column_case_sensitive: true + + - name: Mysql_user Column case sensitive | Assert user privileges are case sensitive + community.mysql.mysql_query: + <<: *mysql_params + query: + - SHOW GRANTS FOR column_case_sensitive@'%' + register: column_case_sensitive_grants + failed_when: + # Column order may vary, thus test each separately + - >- + column_case_sensitive_grants.query_result[0][1] + is not search("a", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("B", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("cC", ignorecase=false) + or column_case_sensitive_grants.query_result[0][1] + is not search("Dd", ignorecase=false) + + - name: Mysql_user Column case sensitive | Assert 4 columns are accessible + community.mysql.mysql_query: + <<: *mysql_params + login_user: column_case_sensitive + query: + - DESC mysql_user_column_case.t1 + register: assert_4_col_accessible + failed_when: + - assert_4_col_accessible.rowcount[0] | int != 4 + + # ========================= Teardown ====================================== + + - name: Mysql_user Column case sensitive | Delete test users + community.mysql.mysql_user: + <<: *mysql_params + name: column_case_sensitive + host_all: true + state: absent + + - name: Mysql_user Column case sensitive | Delete test database + community.mysql.mysql_db: + <<: *mysql_params + name: mysql_user_column_case + state: absent diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_password_expire.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_password_expire.yml new file mode 100644 index 000000000..7e70ece0e --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_password_expire.yml @@ -0,0 +1,174 @@ +--- +# Tests scenarios for password_expire + +- vars: + mysql_parameters: &mysql_params + login_user: "{{ mysql_user }}" + login_password: "{{ mysql_password }}" + login_host: "{{ mysql_host }}" + login_port: "{{ mysql_primary_port }}" + + block: + - include_tasks: utils/assert_user_password_expire.yml + vars: + username: "{{ item.username }}" + host: "{{ item.host | default('localhost')}}" + password_expire: "{{ item.password_expire }}" + password: "{{ user_password_1 }}" + expect_change: "{{ item.expect_change }}" + expect_password_expire_change: "{{ item.expect_password_expire_change }}" + expected_password_lifetime: "{{ item.expected_password_lifetime }}" + password_expire_interval: "{{ item.password_expire_interval | default(omit) }}" + expected_password_expired: "{{ item.expected_password_expired }}" + check_mode: "{{ item.check_mode | default(omit) }}" + loop: + # all variants set the password when nothing exists + # never expires + - username: "{{ user_name_1 }}" + host: "%" + password_expire: never + expect_change: true + expected_password_lifetime: "0" + expected_password_expired: "N" + # expires ussing default policy + - username: "{{ user_name_2 }}" + password_expire: default + expect_change: true + expected_password_lifetime: "-1" + expected_password_expired: "N" + # expires ussing interval + - username: "{{ user_name_3 }}" + password_expire: interval + password_expire_interval: "10" + expect_change: true + expected_password_lifetime: "10" + expected_password_expired: "N" + + # assert idempotency + - username: "{{ user_name_1 }}" + host: "%" + password_expire: never + expect_change: false + expected_password_lifetime: "0" + expected_password_expired: "N" + - username: "{{ user_name_2 }}" + password_expire: default + expect_change: false + expected_password_lifetime: "-1" + expected_password_expired: "N" + - username: "{{ user_name_3 }}" + password_expire: interval + password_expire_interval: "10" + expect_change: false + expected_password_lifetime: "10" + expected_password_expired: "N" + + # assert change is made + - username: "{{ user_name_3 }}" + password_expire: never + expect_change: true + expected_password_lifetime: "0" + expected_password_expired: "N" + - username: "{{ user_name_1 }}" + host: "%" + password_expire: default + expect_change: true + expected_password_lifetime: "-1" + expected_password_expired: "N" + - username: "{{ user_name_2 }}" + password_expire: interval + password_expire_interval: "100" + expect_change: true + expected_password_lifetime: "100" + expected_password_expired: "N" + + # assert password expires now + - username: "{{ user_name_1 }}" + host: "%" + password_expire: now + expect_change: true + expected_password_lifetime: "-1" # password lifetime should be the same + expected_password_expired: "Y" + - username: "{{ user_name_2 }}" + password_expire: now + expect_change: true + expected_password_lifetime: "100" # password lifetime should be the same + expected_password_expired: "Y" + + # assert idempotency password expires now + - username: "{{ user_name_1 }}" + host: "%" + password_expire: now + expect_change: false + expected_password_lifetime: "-1" # password lifetime should be the same + expected_password_expired: "Y" + - username: "{{ user_name_2 }}" + password_expire: now + expect_change: false + expected_password_lifetime: "100" # password lifetime should be the same + expected_password_expired: "Y" + + # assert check_mode + - username: "{{ user_name_3 }}" + password_expire: interval + password_expire_interval: 10 + check_mode: true + expect_change: false + expected_password_lifetime: "0" + expected_password_expired: "N" + + - name: password_expire | Set password_expire = interval without password_expire_interval + community.mysql.mysql_user: + <<: *mysql_params + name: '{{ user_name_4 }}' + host: '%' + password: '{{ user_password_4 }}' + password_expire: interval + state: present + register: result + ignore_errors: true + + - name: password_expire | Assert that action fails if 'password_expire_interval' not set + ansible.builtin.assert: + that: + - result is failed + + - name: password_expire | Set password_expire_interval < 1 + community.mysql.mysql_user: + <<: *mysql_params + name: '{{ user_name_4 }}' + host: '%' + password: '{{ user_password_4 }}' + password_expire: interval + password_expire_interval: -1 + state: present + register: result + ignore_errors: true + + - name: password_expire | Assert that action fails if 'password_expire_interval' is < 1 + ansible.builtin.assert: + that: + - result is failed + - "'should be positive number' in result.msg" + + - name: password_expire | check mode for user creation + community.mysql.mysql_user: + <<: *mysql_params + name: '{{ user_name_4 }}' + host: '%' + password: '{{ user_password_4 }}' + password_expire: interval + password_expire_interval: 20 + state: present + register: result + check_mode: True + failed_when: result is changed + + - include_tasks: utils/remove_user.yml + vars: + user_name: "{{ item.username }}" + loop: + - username: "{{ user_name_1 }}" + - username: "{{ user_name_2 }}" + - username: "{{ user_name_3 }}" + - username: "{{ user_name_4 }}" diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_user_attributes.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_user_attributes.yml new file mode 100644 index 000000000..b5cec1004 --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/test_user_attributes.yml @@ -0,0 +1,474 @@ +--- +- vars: + mysql_parameters: &mysql_params + login_user: '{{ mysql_user }}' + login_password: '{{ mysql_password }}' + login_host: '{{ mysql_host }}' + login_port: '{{ mysql_primary_port }}' + + block: + + - when: db_engine == 'mariadb' + block: + + # ============================================================ + # Fail creating a user with mariadb + # + + # Check mode + - name: Attributes | Attempt to create user with attributes with mariadb in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + attributes: + key1: "value1" + ignore_errors: yes + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify user creation with attributes fails with mariadb in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT user FROM mysql.user WHERE user = "{{ user_name_2 }}" AND host = "%"' + ignore_errors: yes + register: result_query + + - name: Attributes | Assert that creating user with attributes fails with mariadb in check mode + assert: + that: + - result_module is failed + - not result_query.query_result[0] + + # Real mode + - name: Attributes | Attempt to create user with attributes with mariadb + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + attributes: + key1: "value1" + ignore_errors: yes + register: result_module + + - name: Attributes | Run query to verify user creation with attributes fails with mariadb + mysql_query: + <<: *mysql_params + query: 'SELECT user FROM mysql.user WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that creating user with attributes fails with mariadb + assert: + that: + - result_module is failed + - not result_query.query_result[0] + + - when: db_engine == 'mysql' + block: + + # ============================================================ + # Create user with no attributes (test attributes return type) + # + + # Check mode + - name: Attributes | Test creating a user with no attributes in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify user creation with no attributes did not take place in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT user FROM mysql.user WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that user would have been created without attributes + assert: + that: + - result_module is changed + - result_module.attributes is none + - not result_query.query_result[0] + + # Real mode + - name: Attributes | Test creating a user with no attributes + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + register: result_module + + - name: Attributes | Run query to verify created user without attributes + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that user was created without attributes + assert: + that: + - result_module is changed + - result_module.attributes is none + - result_query.query_result[0][0]['ATTRIBUTE'] is none + + # Clean up user to allow it to be recreated with attributes + - include_tasks: utils/remove_user.yml + vars: + user_name: "{{ user_name_2 }}" + + # ============================================================ + # Create user with attributes + # + + # Check mode + - name: Attributes | Test creating a user with attributes in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + attributes: + key1: "value1" + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify user creation did not take place in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT user FROM mysql.user WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that user would have been created with attributes + assert: + that: + - result_module is changed + - result_module.attributes.key1 == "value1" + - not result_query.query_result[0] + + # Real mode + - name: Attributes | Test creating a user with attributes + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + password: '{{ user_password_2 }}' + attributes: + key1: "value1" + register: result_module + + - name: Attributes | Run query to verify created user attributes + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that user was created with attributes + assert: + that: + - result_module is changed + - result_module.attributes.key1 == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + + # ============================================================ + # Append attributes on an existing user + # + + # Check mode + - name: Attributes | Test appending attributes to an existing user in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: "value2" + register: result_module + check_mode: yes + + - name: Attributes | Run query to check appended attributes in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute would have been appended and existing attribute stays + assert: + that: + - result_module is changed + - result_module.attributes.key1 == "value1" + - result_module.attributes.key2 == "value2" + - "'key2' not in result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml" + + # Real mode + - name: Attributes | Test appending attributes to an existing user + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: "value2" + register: result_module + + - name: Attributes | Run query to check appended attributes + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that new attribute was appended and existing attribute stays + assert: + that: + - result_module is changed + - result_module.attributes.key1 == "value1" + - result_module.attributes.key2 == "value2" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key2'] == "value2" + + # ============================================================ + # Test updating existing attributes + # + + # Check mode + - name: Attributes | Test updating attributes on an existing user in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: "new_value2" + check_mode: yes + register: result_module + + - name: Attributes | Run query to verify updated attribute in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute would have been updated + assert: + that: + - result_module is changed + - result_module.attributes.key2 == "new_value2" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key2'] == "value2" + + # Real mode + - name: Attributes | Test updating attributes on an existing user + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: "new_value2" + register: result_module + + - name: Attributes | Run query to verify updated attribute + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute was updated + assert: + that: + - result_module is changed + - result_module.attributes.key2 == "new_value2" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key2'] == "new_value2" + + # ============================================================ + # Test attribute idempotency when specifying attributes + # + + # Check mode + - name: Attributes | Test attribute idempotency by trying to change an already correct attribute in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key1: "value1" + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify idempotency of already correct attribute in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute would not have been updated + assert: + that: + - result_module is not changed + - result_module.attributes.key1 == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + + # Real mode + - name: Attributes | Test attribute idempotency by trying to change an already correct attribute + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key1: "value1" + register: result_module + + - name: Attributes | Run query to verify idempotency of already correct attribute + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute was not updated + assert: + that: + - result_module is not changed + - result_module.attributes.key1 == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + + # ============================================================ + # Test attribute idempotency when not specifying attribute parameter + # + + # Check mode + - name: Attributes | Test attribute idempotency by not specifying attribute parameter in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify idempotency when not specifying attribute parameter in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute is returned in check mode + assert: + that: + - result_module is not changed + - result_module.attributes.key1 == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + + # Real mode + - name: Attributes | Test attribute idempotency by not specifying attribute parameter + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + register: result_module + + - name: Attributes | Run query to verify idempotency when not specifying attribute parameter + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute is returned + assert: + that: + - result_module is not changed + - result_module.attributes.key1 == "value1" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key1'] == "value1" + + # ============================================================ + # Test deleting attributes + # + + # Check mode + - name: Attributes | Test deleting attributes on an existing user in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: null + register: result_module + check_mode: yes + + - name: Attributes | Run query to verify deleted attribute in check mode + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute would have been deleted + assert: + that: + - result_module is changed + - "'key2' not in result_module.attributes" + - (result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml)['key2'] == "new_value2" + + # Real mode + - name: Attributes | Test deleting attributes on an existing user + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key2: null + register: result_module + + - name: Attributes | Run query to verify deleted attribute + mysql_query: + <<: *mysql_params + query: 'SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = "{{ user_name_2 }}" AND host = "%"' + register: result_query + + - name: Attributes | Assert that attribute was deleted + assert: + that: + - result_module is changed + - "'key2' not in result_module.attributes" + - "'key2' not in result_query.query_result[0][0]['ATTRIBUTE'] | from_yaml" + + # ============================================================ + # Test attribute return value when no attributes exist + # + + # Check mode + - name: Attributes | Test attributes return value when no attributes exist in check mode + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key1: null + register: result_module + check_mode: yes + + - name: Attributes | Assert attributes return value when no attributes exist in check mode + assert: + that: + - result_module is changed + - result_module.attributes is none + + # Real mode + - name: Attributes | Test attributes return value when no attributes exist + mysql_user: + <<: *mysql_params + name: '{{ user_name_2 }}' + host: '%' + attributes: + key1: null + register: result_module + + - name: Attributes | Assert attributes return value when no attributes exist + assert: + that: + - result_module is changed + - result_module.attributes is none + + # ============================================================ + # Cleanup + # + - include_tasks: utils/remove_user.yml + vars: + user_name: "{{ user_name_2 }}" diff --git a/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/utils/assert_user_password_expire.yml b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/utils/assert_user_password_expire.yml new file mode 100644 index 000000000..3798802ea --- /dev/null +++ b/ansible_collections/community/mysql/tests/integration/targets/test_mysql_user/tasks/utils/assert_user_password_expire.yml @@ -0,0 +1,56 @@ +--- +- name: Utils | Assert user password_expire | Create modify {{ username }} with password_expire + community.mysql.mysql_user: + login_user: "{{ mysql_parameters.login_user }}" + login_password: "{{ mysql_parameters.login_password }}" + login_host: "{{ mysql_parameters.login_host }}" + login_port: "{{ mysql_parameters.login_port }}" + state: present + name: "{{ username }}" + host: "{{ host }}" + password: "{{ password }}" + password_expire: "{{ password_expire }}" + password_expire_interval: "{{ password_expire_interval | default(omit) }}" + register: result + check_mode: "{{ check_mode | default(false) }}" + failed_when: result.changed != expect_change_value + vars: + expect_change_value: "{{ expect_change }}" + +- name: Utils | Assert user password_lifetime | Query user '{{ username }}' + ansible.builtin.command: + cmd: > + {{ mysql_command }} -BNe "SELECT IFNULL(password_lifetime, -1) + FROM mysql.user where user='{{ username }}' and host='{{ host }}'" + register: password_lifetime + when: + - db_engine == 'mysql' + - db_version is version('5.7.0', '>=') + failed_when: expected_password_lifetime_value not in password_lifetime.stdout_lines + vars: + expected_password_lifetime_value: "{{ expected_password_lifetime }}" + +- name: Utils | Assert user password_lifetime | Query user '{{ username }}' + ansible.builtin.command: + "{{ mysql_command }} -BNe \"SELECT JSON_EXTRACT(Priv, '$.password_lifetime') AS password_lifetime \ + FROM mysql.global_priv \ + WHERE user='{{ username }}' and host='{{ host }}'\"" + register: password_lifetime + when: + - db_engine == 'mariadb' + - db_version is version('10.4.3', '>=') + failed_when: expected_password_lifetime_value not in password_lifetime.stdout_lines + vars: + expected_password_lifetime_value: "{{ expected_password_lifetime }}" + +- name: Utils | Assert user password_expired | Query user '{{ username }}' + ansible.builtin.command: + cmd: > + {{ mysql_command }} -BNe "SELECT password_expired FROM mysql.user + WHERE user='{{ username }}' and host='{{ host }}'" + register: password_expired + when: (db_engine == 'mysql' and db_version is version('5.7.0', '>=')) or + (db_engine == 'mariadb' and db_version is version('10.4.3', '>=')) + failed_when: expected_password_expired_value not in password_expired.stdout_lines + vars: + expected_password_expired_value: "{{ expected_password_expired }}" diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.12.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.12.txt deleted file mode 100644 index c0323aff3..000000000 --- a/ansible_collections/community/mysql/tests/sanity/ignore-2.12.txt +++ /dev/null @@ -1,8 +0,0 @@ -plugins/modules/mysql_db.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_db.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen -plugins/modules/mysql_info.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_info.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_query.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_user.py validate-modules:undocumented-parameter -plugins/modules/mysql_variables.py validate-modules:doc-required-mismatch diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.13.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.13.txt deleted file mode 100644 index c0323aff3..000000000 --- a/ansible_collections/community/mysql/tests/sanity/ignore-2.13.txt +++ /dev/null @@ -1,8 +0,0 @@ -plugins/modules/mysql_db.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_db.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen -plugins/modules/mysql_info.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_info.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_query.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_user.py validate-modules:undocumented-parameter -plugins/modules/mysql_variables.py validate-modules:doc-required-mismatch diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.14.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.14.txt index c0323aff3..90ddba308 100644 --- a/ansible_collections/community/mysql/tests/sanity/ignore-2.14.txt +++ b/ansible_collections/community/mysql/tests/sanity/ignore-2.14.txt @@ -1,8 +1,2 @@ -plugins/modules/mysql_db.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_db.py validate-modules:parameter-list-no-elements plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen -plugins/modules/mysql_info.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_info.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_query.py validate-modules:parameter-list-no-elements plugins/modules/mysql_user.py validate-modules:undocumented-parameter -plugins/modules/mysql_variables.py validate-modules:doc-required-mismatch diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.15.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.15.txt index da0354c97..55b29043e 100644 --- a/ansible_collections/community/mysql/tests/sanity/ignore-2.15.txt +++ b/ansible_collections/community/mysql/tests/sanity/ignore-2.15.txt @@ -1,10 +1,4 @@ -plugins/modules/mysql_db.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_db.py validate-modules:parameter-list-no-elements plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen -plugins/modules/mysql_info.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_info.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_query.py validate-modules:parameter-list-no-elements plugins/modules/mysql_user.py validate-modules:undocumented-parameter -plugins/modules/mysql_variables.py validate-modules:doc-required-mismatch plugins/module_utils/mysql.py pylint:unused-import plugins/module_utils/version.py pylint:unused-import diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.16.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.16.txt index da0354c97..55b29043e 100644 --- a/ansible_collections/community/mysql/tests/sanity/ignore-2.16.txt +++ b/ansible_collections/community/mysql/tests/sanity/ignore-2.16.txt @@ -1,10 +1,4 @@ -plugins/modules/mysql_db.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_db.py validate-modules:parameter-list-no-elements plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen -plugins/modules/mysql_info.py validate-modules:doc-elements-mismatch -plugins/modules/mysql_info.py validate-modules:parameter-list-no-elements -plugins/modules/mysql_query.py validate-modules:parameter-list-no-elements plugins/modules/mysql_user.py validate-modules:undocumented-parameter -plugins/modules/mysql_variables.py validate-modules:doc-required-mismatch plugins/module_utils/mysql.py pylint:unused-import plugins/module_utils/version.py pylint:unused-import diff --git a/ansible_collections/community/mysql/tests/sanity/ignore-2.17.txt b/ansible_collections/community/mysql/tests/sanity/ignore-2.17.txt new file mode 100644 index 000000000..55b29043e --- /dev/null +++ b/ansible_collections/community/mysql/tests/sanity/ignore-2.17.txt @@ -0,0 +1,4 @@ +plugins/modules/mysql_db.py validate-modules:use-run-command-not-popen +plugins/modules/mysql_user.py validate-modules:undocumented-parameter +plugins/module_utils/mysql.py pylint:unused-import +plugins/module_utils/version.py pylint:unused-import diff --git a/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql.py b/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql.py index ac4de24f4..5410575b6 100644 --- a/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql.py +++ b/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql.py @@ -1,9 +1,10 @@ from __future__ import (absolute_import, division, print_function) + __metaclass__ = type import pytest -from ansible_collections.community.mysql.plugins.module_utils.mysql import get_server_version +from ansible_collections.community.mysql.plugins.module_utils.mysql import get_server_version, get_server_implementation from ..utils import dummy_cursor_class @@ -22,3 +23,21 @@ def test_get_server_version(cursor_return_version, cursor_return_type): """ cursor = dummy_cursor_class(cursor_return_version, cursor_return_type) assert get_server_version(cursor) == cursor_return_version + + +@pytest.mark.parametrize( + 'cursor_return_version,cursor_return_type,server_implementation', + [ + ('5.7.0-mysql', 'dict', 'mysql'), + ('8.0.0-mysql', 'list', 'mysql'), + ('10.5.0-mariadb', 'dict', 'mariadb'), + ('10.5.1-mariadb', 'list', 'mariadb'), + ] +) +def test_get_server_implamentation(cursor_return_version, cursor_return_type, server_implementation): + """ + Test that server implementation are handled properly by get_server_implementation() whether the server version returned as a list or dict. + """ + cursor = dummy_cursor_class(cursor_return_version, cursor_return_type) + + assert get_server_implementation(cursor) == server_implementation diff --git a/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql_user.py b/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql_user.py index 46b3b8eb6..bb1ec2446 100644 --- a/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql_user.py +++ b/ansible_collections/community/mysql/tests/unit/plugins/module_utils/test_mysql_user.py @@ -9,7 +9,8 @@ from ansible_collections.community.mysql.plugins.module_utils.user import ( handle_grant_on_col, has_grant_on_col, normalize_col_grants, - sort_column_order + sort_column_order, + privileges_unpack, ) @@ -92,3 +93,21 @@ def test_handle_grant_on_col(privileges, start, end, output): def test_normalize_col_grants(input_, expected): """Tests normalize_col_grants function.""" assert normalize_col_grants(input_) == expected + + +@pytest.mark.parametrize( + 'priv,expected,mode,column_case_sensitive,ensure_usage', + [ + ('mydb.*:SELECT', {'"mydb".*': ['SELECT']}, 'ANSI', False, False), + ('mydb.*:SELECT', {'`mydb`.*': ['SELECT']}, 'NOTANSI', False, False), + ('mydb.*:SELECT', {'"mydb".*': ['SELECT'], '*.*': ['USAGE']}, 'ANSI', False, True), + ('mydb.*:SELECT', {'`mydb`.*': ['SELECT'], '*.*': ['USAGE']}, 'NOTANSI', False, True), + ('mydb.*:SELECT (a)', {'`mydb`.*': ['SELECT (A)']}, 'NOTANSI', False, False), + ('mydb.*:UPDATE (b, a)', {'`mydb`.*': ['UPDATE (a, b)']}, 'NOTANSI', True, False), + ('mydb.*:SELECT (b, a, c)', {'`mydb`.*': ['SELECT (A, B, C)']}, 'NOTANSI', False, False), + ('mydb.*:SELECT (b, a, c)', {'`mydb`.*': ['SELECT (a, b, c)']}, 'NOTANSI', True, False), + ] +) +def test_privileges_unpack(priv, mode, column_case_sensitive, ensure_usage, expected): + """Tests privileges_unpack function.""" + assert privileges_unpack(priv, mode, column_case_sensitive, ensure_usage) == expected diff --git a/ansible_collections/community/mysql/tests/unit/plugins/modules/test_mysql_info.py b/ansible_collections/community/mysql/tests/unit/plugins/modules/test_mysql_info.py index 7aa9577e5..6aaf66e2c 100644 --- a/ansible_collections/community/mysql/tests/unit/plugins/modules/test_mysql_info.py +++ b/ansible_collections/community/mysql/tests/unit/plugins/modules/test_mysql_info.py @@ -14,15 +14,15 @@ from ansible_collections.community.mysql.plugins.modules.mysql_info import MySQL @pytest.mark.parametrize( - 'suffix,cursor_output', + 'suffix,cursor_output,server_implementation', [ - ('mysql', '5.5.1-mysql'), - ('log', '5.7.31-log'), - ('mariadb', '10.5.0-mariadb'), - ('', '8.0.22'), + ('mysql', '5.5.1-mysql', 'mysql'), + ('log', '5.7.31-log', 'mysql'), + ('mariadb', '10.5.0-mariadb', 'mariadb'), + ('', '8.0.22', 'mysql'), ] ) -def test_get_info_suffix(suffix, cursor_output): +def test_get_info_suffix(suffix, cursor_output, server_implementation): def __cursor_return_value(input_parameter): if input_parameter == "SHOW GLOBAL VARIABLES": cursor.fetchall.return_value = [{"Variable_name": "version", "Value": cursor_output}] @@ -32,6 +32,6 @@ def test_get_info_suffix(suffix, cursor_output): cursor = MagicMock() cursor.execute.side_effect = __cursor_return_value - info = MySQL_Info(MagicMock(), cursor) + info = MySQL_Info(MagicMock(), cursor, server_implementation) assert info.get_info([], [], False)['version']['suffix'] == suffix |