diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-18 05:52:22 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-18 05:52:22 +0000 |
| commit | 38b7c80217c4e72b1d8988eb1e60bb6e77334114 (patch) | |
| tree | 356e9fd3762877d07cde52d21e77070aeff7e789 /ansible_collections/community/mysql | |
| parent | Adding upstream version 7.7.0+dfsg. (diff) | |
| download | ansible-38b7c80217c4e72b1d8988eb1e60bb6e77334114.tar.xz ansible-38b7c80217c4e72b1d8988eb1e60bb6e77334114.zip | |
Adding upstream version 9.4.0+dfsg.upstream/9.4.0+dfsg
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
46 files changed, 2392 insertions, 288 deletions
diff --git a/ansible_collections/community/mysql/.github/workflows/ansible-test-plugins.yml b/ansible_collections/community/mysql/.github/workflows/ansible-test-plugins.yml index 6533f9461..78644bb25 100644 --- a/ansible_collections/community/mysql/.github/workflows/ansible-test-plugins.yml +++ b/ansible_collections/community/mysql/.github/workflows/ansible-test-plugins.yml @@ -22,9 +22,9 @@ jobs: strategy: matrix: ansible: - - stable-2.12 - - stable-2.13 - stable-2.14 + - stable-2.15 + - stable-2.16 - devel steps: - name: Perform sanity testing @@ -41,9 +41,9 @@ jobs: fail-fast: false matrix: ansible: - - stable-2.12 - - stable-2.13 - stable-2.14 + - stable-2.15 + - stable-2.16 - devel db_engine_name: - mysql @@ -112,10 +112,13 @@ jobs: python: '3.10' - db_engine_version: 5.7.40 - ansible: stable-2.13 + ansible: stable-2.14 - db_engine_version: 5.7.40 - ansible: stable-2.14 + ansible: stable-2.15 + + - db_engine_version: 5.7.40 + ansible: stable-2.16 - db_engine_version: 5.7.40 ansible: devel @@ -172,22 +175,25 @@ jobs: connector_version: 2.0.3 - python: '3.8' - ansible: stable-2.13 + ansible: stable-2.14 - python: '3.8' - ansible: stable-2.14 + ansible: stable-2.15 + + - python: '3.8' + ansible: stable-2.16 - python: '3.8' ansible: devel - python: '3.9' - ansible: stable-2.12 + ansible: stable-2.15 - python: '3.9' - ansible: devel + ansible: stable-2.16 - - python: '3.10' - ansible: stable-2.12 + - python: '3.9' + ansible: devel services: db_primary: @@ -334,22 +340,22 @@ jobs: fail-fast: true matrix: ansible: - - stable-2.12 - - stable-2.13 - stable-2.14 + - stable-2.15 + - stable-2.16 - devel python: - 3.8 - 3.9 exclude: - python: '3.8' - ansible: stable-2.13 - - python: '3.8' ansible: stable-2.14 - python: '3.8' + ansible: stable-2.15 + - python: '3.8' + ansible: stable-2.16 + - python: '3.8' ansible: devel - - python: '3.9' - ansible: stable-2.12 steps: - name: >- diff --git a/ansible_collections/community/mysql/.github/workflows/ansible-test-roles.yml b/ansible_collections/community/mysql/.github/workflows/ansible-test-roles.yml index 13e7d4178..da8a805c5 100644 --- a/ansible_collections/community/mysql/.github/workflows/ansible-test-roles.yml +++ b/ansible_collections/community/mysql/.github/workflows/ansible-test-roles.yml @@ -24,31 +24,16 @@ jobs: mysql: - 2.0.12 ansible: - - stable-2.11 - - stable-2.12 - stable-2.13 + - stable-2.14 + - stable-2.15 - devel python: - - 3.6 - 3.8 - 3.9 exclude: - - python: 3.6 - ansible: stable-2.12 - - python: 3.6 - ansible: stable-2.13 - - python: 3.6 - ansible: devel - - python: 3.8 - ansible: stable-2.11 - - python: 3.8 - ansible: stable-2.13 - python: 3.8 ansible: devel - - python: 3.9 - ansible: stable-2.11 - - python: 3.9 - ansible: stable-2.12 steps: diff --git a/ansible_collections/community/mysql/CHANGELOG.rst b/ansible_collections/community/mysql/CHANGELOG.rst index 31ee41a87..cc7ab8525 100644 --- a/ansible_collections/community/mysql/CHANGELOG.rst +++ b/ansible_collections/community/mysql/CHANGELOG.rst @@ -6,6 +6,56 @@ Community MySQL Collection Release Notes This changelog describes changes after version 2.0.0. +v3.9.0 +====== + +Release Summary +--------------- + +This is a minor release of the ``community.mysql`` collection. +This changelog contains all changes to the modules and plugins in this +collection that have been made after the previous release. + +Major Changes +------------- + +- Collection version 2.*.* is EOL, no more bugfixes will be backported. Please consider upgrading to the latest version. + +Minor Changes +------------- + +- mysql_user - add the ``password_expire`` and ``password_expire_interval`` arguments to implement the password expiration management for mysql user (https://github.com/ansible-collections/community.mysql/pull/598). +- mysql_user - add user attribute support via the ``attributes`` parameter and return value (https://github.com/ansible-collections/community.mysql/pull/604). + +Bugfixes +-------- + +- mysql_info - the ``slave_status`` filter was returning an empty list on MariaDB with multiple replication channels. It now returns all channels by running ``SHOW ALL SLAVES STATUS`` for MariaDB servers (https://github.com/ansible-collections/community.mysql/issues/603). + +v3.8.0 +====== + +Release Summary +--------------- + +This is the minor release of the ``community.mysql`` collection. +This changelog contains all changes to the modules and plugins in this +collection that have been made after the previous release. + +Major Changes +------------- + +- The community.mysql collection no longer supports ``ansible-core 2.12`` and ``ansible-core 2.13``. While we take no active measures to prevent usage and there are no plans to introduce incompatible code to the modules, we will stop testing those versions. Both are or will soon be End of Life and if you are still using them, you should consider upgrading to the ``latest Ansible / ansible-core 2.15 or later`` as soon as possible (https://github.com/ansible-collections/community.mysql/pull/574). +- mysql_role - the ``column_case_sensitive`` argument's default value will be changed to ``true`` in community.mysql 4.0.0. If your playbook expected the column to be automatically uppercased for your roles privileges, you should set this to false explicitly (https://github.com/ansible-collections/community.mysql/issues/578). +- mysql_user - the ``column_case_sensitive`` argument's default value will be changed to ``true`` in community.mysql 4.0.0. If your playbook expected the column to be automatically uppercased for your users privileges, you should set this to false explicitly (https://github.com/ansible-collections/community.mysql/issues/577). + +Minor Changes +------------- + +- mysql_info - add filter ``users_info`` (https://github.com/ansible-collections/community.mysql/pull/580). +- mysql_role - add ``column_case_sensitive`` option to prevent field names from being uppercased (https://github.com/ansible-collections/community.mysql/pull/569). +- mysql_user - add ``column_case_sensitive`` option to prevent field names from being uppercased (https://github.com/ansible-collections/community.mysql/pull/569). + v3.7.2 ====== diff --git a/ansible_collections/community/mysql/CONTRIBUTING.md b/ansible_collections/community/mysql/CONTRIBUTING.md index 70cd5557e..1b6ecdfd7 100644 --- a/ansible_collections/community/mysql/CONTRIBUTING.md +++ b/ansible_collections/community/mysql/CONTRIBUTING.md @@ -1,5 +1,80 @@ -# Contributing +# Contributing to this project -Refer to the [Ansible Contributing guidelines](https://docs.ansible.com/ansible/devel/community/index.html) to learn how to contribute to this collection. +In this guide, you will find information relevant for code contributions, though any other kinds of contribution mentioned in the [Ansible Contributing guidelines](https://docs.ansible.com/ansible/devel/community/index.html) are equally appreciated and valuable. -Refer to the [review checklist](https://docs.ansible.com/ansible/devel/community/collection_contributors/collection_reviewing.html) when triaging issues or reviewing PRs. +If you have any questions after reading, please contact the community via one or more of the [available channels](https://github.com/ansible-collections/community.mysql#communication). Any feedback on this guide is very welcome. + +## Reviewing open issue and pull requests + +Refer to the [review checklist](https://docs.ansible.com/ansible/devel/community/collection_contributors/collection_reviewing.html) when triaging issues or reviewing pull requests (hereinafter PRs). + +Most important things to pay attention to: + +- Do not let major/breaking changes sneak into a minor/bugfix release! All such changes should be discussed in a dedicated issue, added to a corresponding milestone (which can be found or created in the project's Issues), and merged right before the major release. Take a look at similar issues to see what needs to be done and reflect on the steps you did/need to do in the issue. +- Every PR (except doc, refactoring, test-related, or a PR containing a new module/plugin) contains a [changelog fragment](https://docs.ansible.com/ansible/latest/community/development_process.html#creating-a-changelog-fragment). Let's give users a chance to know about the changes. +- Every new module `DOCUMENTATION` section contains the `version_added: 'x.y.z'` field. Besides the informative purpose, it is used by the changelog-generating tool to add a corresponding entry to the changelog. As the project follows SemVer, it is typically a next minor (x.y.0) version. +- Every new module argument contains the `version_added: 'x.y.z'` field. As the project follows SemVer, it is typically a next minor (x.y.0) version. +- Non-refactoring code changes (bugfixes, new features) are covered with, at least, integration tests! There can be exceptions but generally it is a requirement. + +## Code contributions + +If you want to submit a bugfix or new feature, refer to the [Quick-start development guide](https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html) first. + +## Project-specific info + +We assume you have read the [Quick-start development guide](https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html). + +In order for any submitted PR to get merged, this project requires sanity, unit, and integration tests to pass. +Codecov job is there but not required. +We use the GitHub Actions platform to run the tests. +You can see the result in the bottom of every PR in the box listing the jobs and their results: + +- Green checkmark: the test has been passed, no more action is needed. +- Red cross: the test has failed. You can see the reason by clicking the ``Details`` link. Fix them locally and push the commit. + +Generally, all jobs must be green. +Sometimes, there can be failures unrelated to a PR, for example, when a test container is unavailable or there is another part of the code that does not satisfy recently introduced additional sanity checks. +If you think the failure does not relate to your changes, put a comment about it. + +## CI testing + +The jobs are launched automatically by GitHub Actions in every PR based on the [matrix](https://github.com/ansible-collections/community.mysql/blob/main/.github/workflows/ansible-test-plugins.yml). + +As the project is included in `ansible` community package, it is a requirement for us to test against all supported `ansible-core` versions and corresponding Python versions. +To keep the matrix relevant, we are subscribed to the [news-for-maintainers](https://github.com/ansible-collections/news-for-maintainers) repository and the [Collection maintainers & contributors](https://forum.ansible.com/g/CollectionMaintainer) forum group to track announcements affecting CI. + +If our matrix is permanently outdated, for example, when supported `ansible-core` versions are missed, the collections can get excluded from the package, so keep it updated! + +Read more about our CI implementation in the [TESTING.md](https://github.com/ansible-collections/community.mysql/blob/main/TESTING.md) file. + +## Adding tests + +If you are new here, read the [Quick-start development guide](https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html) first. + +When fixing a bug, first reproduce it by adding a task as reported to a suitable file under the ``tests/integration/targets/<module_name>/tasks/`` directory and run the integration tests as described below. The same is relevant for new features. + +It is not necessary but if you want you can also add unit tests to a suitable file under the ``tests/units/`` directory and run them as described below. + +## Checking your code locally + +It will make your and other people's life a bit easier if you run the tests locally and fix all failures before pushing. If you're unable to run the tests locally, please create your PR as a **draft** to avoid reviewers being added automatically. + +If you are new here, read the [Quick-start development guide](https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html) first. + +We assume you [prepared your local environment](https://docs.ansible.com/ansible/devel/community/create_pr_quick_start.html#prepare-your-environment) as described in the guide before running the following commands. Otherwise, the command will fail. + +### Sanity tests + +``` console +$ ansible-test sanity path/to/changed_file.py --docker -v +``` + +### Integration tests + +See the [TESTING.md](https://github.com/ansible-collections/community.mysql/blob/main/TESTING.md) file to learn how to run integration tests against different server/connector versions. + +### Unit tests + +``` console +$ ansible-test units tests/unit/plugins/unit_test_file.py --docker +``` diff --git a/ansible_collections/community/mysql/CONTRIBUTORS b/ansible_collections/community/mysql/CONTRIBUTORS index 3acc8f33d..06fb57907 100644 --- a/ansible_collections/community/mysql/CONTRIBUTORS +++ b/ansible_collections/community/mysql/CONTRIBUTORS @@ -141,6 +141,7 @@ kalaisubbiah kenichi-ogawa-1988 kkeane klingac +kmarse koleo kotso kuntalFreshBooks @@ -151,6 +152,7 @@ ldesgrange leeadh LeonB leucos +lkthomas loomsen lorin lowwalker diff --git a/ansible_collections/community/mysql/FILES.json b/ansible_collections/community/mysql/FILES.json index e8a410307..b9139920f 100644 --- a/ansible_collections/community/mysql/FILES.json +++ b/ansible_collections/community/mysql/FILES.json @@ -25,14 +25,14 @@ "name": ".github/workflows/ansible-test-plugins.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "853a324a5d61d6fcfcbacfdf8103802f3ec763c85d546b29b9d8351170069a83", + "chksum_sha256": "6066bd23ec1ab65baeb8593bfd0b4db728a81c31ab66e5d2905eb8d4d20d61d5", "format": 1 }, { "name": ".github/workflows/ansible-test-roles.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "a7935fe56bc37e6fd38a78d7db568c91ca3d1b7ce4cbdcb19a98ddaa56a48109", + "chksum_sha256": "1ae22305f85045b4491fcd8bbee1821e30905b21687663db68788d5bbb0ff2f8", "format": 1 }, { @@ -179,7 +179,7 @@ "name": "changelogs/changelog.yaml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b3508fbc7594b06c88b696e61e87b126ed3ccd549c50f9d63a34ed27b41d9122", + "chksum_sha256": "0e910481ea590e77408e127beebe9ce1533d8acc0e9bf6175d0ab0bd640ba189", "format": 1 }, { @@ -221,7 +221,7 @@ "name": "plugins/doc_fragments/mysql.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "68615ead33b2ffd38c639a47980ae62ff6b7a3d9063ab322580072c6c7d5c452", + "chksum_sha256": "d3866b8a4d0aa946f3dd6b354c2e931e3c1732becb380fd3389acbf8e4245e57", "format": 1 }, { @@ -263,7 +263,7 @@ "name": "plugins/module_utils/implementations/mariadb/user.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "946bda63f668235fdc900f12cd3c4323054c7a45ef8aca91cd7379ace294bd7d", + "chksum_sha256": "a07ee8fa2b1a27e64c93ceb1065f137cdd84fd817e72d2291dcaca9ff2f55a77", "format": 1 }, { @@ -291,7 +291,7 @@ "name": "plugins/module_utils/implementations/mysql/user.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "f2f802810550b7d69e13d63f9002e524ab598e633c861caf424e7a5d8c293891", + "chksum_sha256": "40b6e268e04472b0b59acf22561c8af1e4877d3a26cccfb0ea7e1151ca954d58", "format": 1 }, { @@ -312,14 +312,14 @@ "name": "plugins/module_utils/mysql.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "5a8f8404b771e315d091a872e4369d2164c4b54e470ef496bd159caab1001e0c", + "chksum_sha256": "140e789e451eb4bf11fd3a39507f1858fb62df985ca74590b3d5e229aaa58424", "format": 1 }, { "name": "plugins/module_utils/user.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b8c93c05fecea1778fd0ef1d46bbc7d01f19742fbd4da8162d715fe6a0ed8a2e", + "chksum_sha256": "dd37494d932a1be94268c0153570a994667ac787a686cd114f29e4ed01bd2cf6", "format": 1 }, { @@ -340,49 +340,49 @@ "name": "plugins/modules/mysql_db.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "6a0800f77595c95ea3fb253e9465606946aad289f5599da037f09e7b06c2b4ce", + "chksum_sha256": "8cee774c3df78199cb4491893e1ea14d8c9e8bf8b7c1214bfb37e8c7e3dd6217", "format": 1 }, { "name": "plugins/modules/mysql_info.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "c66d795038d4ee8fef281f942a936b939a1fbd3a2ba23e4d54ed7b3b47d7ddda", + "chksum_sha256": "189fedc5e3f309c1d5ff2ea83d5f9d7cad2df1900a58aee03681089c13090e02", "format": 1 }, { "name": "plugins/modules/mysql_query.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b379afe3a4f5ea3d77c5738ad9f4426e33870a7f6d4b679200bfb72272460688", + "chksum_sha256": "4ad32cf4d161d446c8ae90fc8e2c49f21200f4162a4aa78abfca1e103ddc137c", "format": 1 }, { "name": "plugins/modules/mysql_replication.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "922d024797a958d73462c5bab30c4877db8a509d182402b9f336afad6f8d9a6c", + "chksum_sha256": "03e354a0d1fc3efcf958a7c549187f83b64f2a0bd975a160615866dd30d7bedf", "format": 1 }, { "name": "plugins/modules/mysql_role.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "c10005349310d333fb7911bfe357ce6d9d6d407d4e61f7b256c98f3a0cc93878", + "chksum_sha256": "9d5cf95f5462001f080ef5597b027c36db190eeea17104b17a0128fc7f5acb57", "format": 1 }, { "name": "plugins/modules/mysql_user.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "4ec672963bde8c6d512225bee75a06896a0d0719234d59068e7f4ec754f82cb2", + "chksum_sha256": "cb4ca1a4d5b3dd399c71ab07d43e4c8b9dc9bf7a1c2cf90791242dc3a7f97335", "format": 1 }, { "name": "plugins/modules/mysql_variables.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "a3301591b157740d3c77b8562cd278f8e626f3324741a98eaf14a740ea961135", + "chksum_sha256": "44e334271d8c23f015f6ad318328b67fa5f5f75f4f594c13135ac8cc7ecb5d5b", "format": 1 }, { @@ -746,7 +746,7 @@ "name": "tests/integration/targets/setup_controller/tasks/verify.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "73cc400b068f45bc2449c791deb0bf602d3a1b5b93254326733b3ece4300c06b", + "chksum_sha256": "16ad6d71d64e3b029b4d67502f83d6a3321f801a5ee8e98bcb1ffaa7a6f47763", "format": 1 }, { @@ -886,7 +886,7 @@ "name": "tests/integration/targets/test_mysql_db/tasks/state_dump_import.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b022e730780c4ff9071df0bebeaaf6789265e2ef6f9cfbabd1b5a23228b78e67", + "chksum_sha256": "92d2da1df27c444a44ab9b966bc1aa59151323a9f723feff7ab7a23996bd8793", "format": 1 }, { @@ -918,6 +918,20 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_info/files", + "ftype": "dir", + "chksum_type": null, + "chksum_sha256": null, + "format": 1 + }, + { + "name": "tests/integration/targets/test_mysql_info/files/users_info_create_procedure.sql", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "922c833006f50c3a6ba9f1f612ede6d356a4881abbed00be8d49798ee83a18a9", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_info/meta", "ftype": "dir", "chksum_type": null, @@ -946,6 +960,13 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_info/tasks/filter_users_info.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "d0bdd9f57b6bc4675f405d6c8af0de9599b2e69374803946c084df4d76483c1e", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_info/tasks/issue-28.yml", "ftype": "file", "chksum_type": "sha256", @@ -956,7 +977,7 @@ "name": "tests/integration/targets/test_mysql_info/tasks/main.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "ecfe0e433cd0468c3fab8f2da767b04effe068fa8b44a16c686801f3bd64feee", + "chksum_sha256": "f0fb15a65dfbb875eaf5ff4e7cef8b1f786a724bec084938f45722ba4fc7c6a8", "format": 1 }, { @@ -1103,14 +1124,14 @@ "name": "tests/integration/targets/test_mysql_replication/tasks/mysql_replication_channel.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "9f8bfabce4c19f9a6acc128ee84e8da916405bbed84f31f19e9d37986f8e596e", + "chksum_sha256": "a8d52f907432ad98dd76a66fb0110ba94e777831c23acb08452c2454333e90bc", "format": 1 }, { "name": "tests/integration/targets/test_mysql_replication/tasks/mysql_replication_initial.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "7855e5b87c17e4ce34c5718cee9c0be79b55abcf8ca342ae50bae03fde7e22a8", + "chksum_sha256": "d667eb810ccbc6619a1b0f515235c7c3bbf5579e5166ee56166d22bc915b6629", "format": 1 }, { @@ -1173,7 +1194,7 @@ "name": "tests/integration/targets/test_mysql_role/tasks/main.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "98aac442e4be66cf3a9600995379ff46a7b9957358c2402ff3bd9a84fc79e7e0", + "chksum_sha256": "d3131bfb97ad6c009c06926341249f28c8009b841992a06e244df018e4b9d6fb", "format": 1 }, { @@ -1184,6 +1205,13 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_role/tasks/test_column_case_sensitive.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "dc0678ccd9017b7c34be148abbf3376569fdf8ca985c2d7149cc1705eb1b7e27", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_role/tasks/test_priv_subtract.yml", "ftype": "file", "chksum_type": "sha256", @@ -1282,6 +1310,13 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_user/tasks/utils/assert_user_password_expire.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "583f31d9b63b441847f5f628bdcc55b966b8ae7529970e5e4c031448bd57a1ef", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_user/tasks/utils/create_user.yml", "ftype": "file", "chksum_type": "sha256", @@ -1334,7 +1369,14 @@ "name": "tests/integration/targets/test_mysql_user/tasks/main.yml", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b471f216d58c90862f192e564df73859521fb7e4395ba38910fc1e53992d72e7", + "chksum_sha256": "e233a28e6d60c826bf9426371db1f01f59ca0d42902a2ef746201fa3137cd063", + "format": 1 + }, + { + "name": "tests/integration/targets/test_mysql_user/tasks/test_column_case_sensitive.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "08031e812e21161a3d2dbe03b92741d511e0563a8685409d2f49bef730af5a9c", "format": 1 }, { @@ -1345,6 +1387,13 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_user/tasks/test_password_expire.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "576ca717899192950e7810d1584a9b9587281e2ca99b28c8cd93957b43e343b5", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_user/tasks/test_priv_append.yml", "ftype": "file", "chksum_type": "sha256", @@ -1408,6 +1457,13 @@ "format": 1 }, { + "name": "tests/integration/targets/test_mysql_user/tasks/test_user_attributes.yml", + "ftype": "file", + "chksum_type": "sha256", + "chksum_sha256": "967211699ff5ecb1b54f6a534db88060c36f2588c5b30b853aa93000848808e3", + "format": 1 + }, + { "name": "tests/integration/targets/test_mysql_user/tasks/test_user_grants_with_roles_applied.yml", "ftype": "file", "chksum_type": "sha256", @@ -1527,38 +1583,31 @@ "format": 1 }, { - "name": "tests/sanity/ignore-2.12.txt", - "ftype": "file", - "chksum_type": "sha256", - "chksum_sha256": "26cf6bdacb30652f6105a97ed3695fbfcdce39966654c8f1179427f5ccd10267", - "format": 1 - }, - { - "name": "tests/sanity/ignore-2.13.txt", + "name": "tests/sanity/ignore-2.14.txt", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "26cf6bdacb30652f6105a97ed3695fbfcdce39966654c8f1179427f5ccd10267", + "chksum_sha256": "027977eacb26c23ae808bd0f993bbfd4f65368206ff163a07fe5f0311463cf09", "format": 1 }, { - "name": "tests/sanity/ignore-2.14.txt", + "name": "tests/sanity/ignore-2.15.txt", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "26cf6bdacb30652f6105a97ed3695fbfcdce39966654c8f1179427f5ccd10267", + "chksum_sha256": "d6b04eec2f75f64f71a7942f89bf13a0d46359054e94aab9d89b95676a8794f5", "format": 1 }, { - "name": "tests/sanity/ignore-2.15.txt", + "name": "tests/sanity/ignore-2.16.txt", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "c0db40238b384c184e5e3fc2261d8457fb174721ca6503da105ba173ae4ae203", + "chksum_sha256": "d6b04eec2f75f64f71a7942f89bf13a0d46359054e94aab9d89b95676a8794f5", "format": 1 }, { - "name": "tests/sanity/ignore-2.16.txt", + "name": "tests/sanity/ignore-2.17.txt", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "c0db40238b384c184e5e3fc2261d8457fb174721ca6503da105ba173ae4ae203", + "chksum_sha256": "d6b04eec2f75f64f71a7942f89bf13a0d46359054e94aab9d89b95676a8794f5", "format": 1 }, { @@ -1607,7 +1656,7 @@ "name": "tests/unit/plugins/module_utils/test_mysql.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "20d9ead509f38c46b09e931439e186ce1b678f9ca4ca9cc3242c725c20d05f57", + "chksum_sha256": "9e9f549f450946532d3a4f0fde4880131e62383318e954196c2ff8a438924e29", "format": 1 }, { @@ -1621,7 +1670,7 @@ "name": "tests/unit/plugins/module_utils/test_mysql_user.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "7e705d79f6e69be73ad67c40998df270780757ed8d42e3f36d66562dfceedc4a", + "chksum_sha256": "ba9713a572af0894360891a32160852a8b9ebe2b91f512817fd26a1c816b6f05", "format": 1 }, { @@ -1649,7 +1698,7 @@ "name": "tests/unit/plugins/modules/test_mysql_info.py", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "d21791537625289a86302891669eae6260c6f06f2417e28d0345ffad0e988ffa", + "chksum_sha256": "539229e1f86eb83fbe151a20269b9785d87ee5d73e569ff91ec1b006a49f9da2", "format": 1 }, { @@ -1677,21 +1726,21 @@ "name": "CHANGELOG.rst", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b2aa4c6fb55f7d38aeb3774d4bff2108a8a4bfe71931dc540fd2ad55585baa8d", + "chksum_sha256": "9cdedb6a51859ee0917d7ffffaa73996d3801c6bbde74228904ab88e47e4b2f0", "format": 1 }, { "name": "CONTRIBUTING.md", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "b0c50cf3715d59964a341dc651a6f626322209ef9fa8c0d03047d3a2b2e420a4", + "chksum_sha256": "1441827f0c03057047e9451c3abf9f19a73e9e31ce84cc458fe225a65e6dcbac", "format": 1 }, { "name": "CONTRIBUTORS", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "2f07f95d703ea52b586842f9a918bf0c7319f8745c0b11099b7f4b6d334fb8cc", + "chksum_sha256": "2b3af63595ec0506a0e1b078de18e875d392af93558ecbaa01371e70a181babe", "format": 1 }, { @@ -1705,7 +1754,7 @@ "name": "MAINTAINERS", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "edb7885116c564025a692f93e694dcefa5af95ede853cd86e3aed5bf91bb5a7f", + "chksum_sha256": "95adaa9e221938f88c50163021d0ea45c35540196b55c89ff9c209dd935d38c2", "format": 1 }, { @@ -1733,14 +1782,14 @@ "name": "README.md", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "8f9a28ac1c7026f63d93849398c90b42a1414cebb40e06efff39e64fd6e2e332", + "chksum_sha256": "2402f4a603c9077d343fbb9956f79af16ca8b334357752c8de64c3a471a9dcde", "format": 1 }, { "name": "TESTING.md", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "d511d97260c97ffbd4ac1c83dda64b612a2924e19f2dd94b5fe473a3f0c4278d", + "chksum_sha256": "31a3aad08369f80c20d083c7a34439f99931b2dd8f8456d9f34c047f1644420c", "format": 1 }, { diff --git a/ansible_collections/community/mysql/MAINTAINERS b/ansible_collections/community/mysql/MAINTAINERS index 2228e0044..73feaa42c 100644 --- a/ansible_collections/community/mysql/MAINTAINERS +++ b/ansible_collections/community/mysql/MAINTAINERS @@ -1,6 +1,3 @@ betanummeric -bmalynovytch -Jorge-Rodriguez -rsicart laurent-indermuehle -Andersson007 (andersson007_ in #ansible-community IRC/Matrix) +Andersson007 diff --git a/ansible_collections/community/mysql/MANIFEST.json b/ansible_collections/community/mysql/MANIFEST.json index 8df21709b..244b4d287 100644 --- a/ansible_collections/community/mysql/MANIFEST.json +++ b/ansible_collections/community/mysql/MANIFEST.json @@ -2,7 +2,7 @@ "collection_info": { "namespace": "community", "name": "mysql", - "version": "3.7.2", + "version": "3.9.0", "authors": [ "Ansible community" ], @@ -25,7 +25,7 @@ "name": "FILES.json", "ftype": "file", "chksum_type": "sha256", - "chksum_sha256": "8d05eab7e7ecee1701730919ad3879343876d2b8c4eabd9abe159d0c771552a3", + "chksum_sha256": "e89323c7dfbe033bbd3c2a6e1709e8ca4773970b6040f5fdcf9a9912f282458d", "format": 1 }, "format": 1 diff --git a/ansible_collections/community/mysql/README.md b/ansible_collections/community/mysql/README.md index 5cb22712f..40264d259 100644 --- a/ansible_collections/community/mysql/README.md +++ b/ansible_collections/community/mysql/README.md @@ -3,6 +3,12 @@ This collection is a part of the Ansible package. +## Our mission + +The Ansible `community.mysql` collection goals are to produce and maintain simple, +flexible, and powerful open-source software for automating MySQL and MariaDB related tasks +providing good documentation for easy deployment and use. + ## Code of Conduct We follow the [Ansible Code of Conduct](https://docs.ansible.com/ansible/latest/community/code_of_conduct.html) in all our interactions within this project. @@ -17,7 +23,7 @@ We are actively accepting new contributors. Any kind of contribution is very welcome. -You don't know how to start? Refer to our [contribution guide](https://github.com/ansible-collections/community.mysql/blob/main/CONTRIBUTING.md)! +You don't know how to start? Refer to our [contribution guide](https://github.com/ansible-collections/community.mysql/blob/main/CONTRIBUTING.md) or ask us in the [#mysql:ansible.com room](https://matrix.to/#/#mysql:ansible.com) on [Matrix](https://docs.ansible.com/ansible/devel/community/communication.html#ansible-community-on-matrix)! ## Collection maintenance @@ -34,9 +40,21 @@ They also should be subscribed to Ansible's [The Bullhorn newsletter](https://do ## Communication +> The `GitHub Discussions` feature is disabled in this repository. Use the `mysql` tag on the forum in the [Project Discussions](https://forum.ansible.com/new-topic?title=topic%20title&body=topic%20body&category=project&tags=mysql) or [Get Help](https://forum.ansible.com/new-topic?title=topic%20title&body=topic%20body&category=help&tags=mysql) category instead. + We announce releases and important changes through Ansible's [The Bullhorn newsletter](https://eepurl.com/gZmiEP). Be sure you are subscribed. -Join us on Matrix in the `#mysql:ansible.com` [room](https://matrix.to/#/#mysql:ansible.com), the `#users:ansible.com` [room](https://matrix.to/#/#users:ansible.com) (general use questions and support), `#ansible-community:ansible.com` [room](https://matrix.to/#/#community:ansible.com) (community and collection development questions), and other Matrix rooms or corresponding bridged Libera.Chat channels. See the [Ansible Communication Guide](https://docs.ansible.com/ansible/devel/community/communication.html) for details. +Join [our team](https://forum.ansible.com/g/MySQLTeam) on: +* The Ansible forums: + * [News & Announcements](https://forum.ansible.com/c/news/5/none) + * [Get Help](https://forum.ansible.com/c/help/6/none) + * [Social Spaces](https://forum.ansible.com/c/chat/4) + * [Posts tagged 'mysql'](https://forum.ansible.com/tag/mysql) +* Matrix: + * `#mysql:ansible.com` [room](https://matrix.to/#/#mysql:ansible.com): questions on how to contribute and use this collection. + * `#users:ansible.com` [room](https://matrix.to/#/#users:ansible.com): general use questions and support. + * `#ansible-community:ansible.com` [room](https://matrix.to/#/#community:ansible.com): community and collection development questions. + * other Matrix rooms; see the [Ansible Communication Guide](https://docs.ansible.com/ansible/devel/community/communication.html) for details. We take part in the global quarterly [Ansible Contributor Summit](https://github.com/ansible/community/wiki/Contributor-Summit) virtually or in-person. Track [The Bullhorn newsletter](https://eepurl.com/gZmiEP) and join us. @@ -44,9 +62,11 @@ For more information about communication, refer to the [Ansible Communication gu ## Governance +We, [the MySQL team](https://forum.ansible.com/g/MySQLTeam), use [the forum](https://forum.ansible.com/tag/mysql) posts tagged with `mysql` for general announcements and discussions. + The process of decision making in this collection is based on discussing and finding consensus among participants. -Every voice is important and every idea is valuable. If you have something on your mind, create an issue or dedicated discussion and let's discuss it! +Every voice is important and every idea is valuable. If you have something on your mind, create an issue or dedicated forum [discussion](https://forum.ansible.com/new-topic?title=topic%20title&body=topic%20body&category=project&tags=mysql) and let's discuss it! ## Included content @@ -62,12 +82,12 @@ Every voice is important and every idea is valuable. If you have something on yo ## Releases Support Timeline -It has been [decided](https://github.com/ansible-collections/community.mysql/discussions/537) to maintain each major release (1.x.y, 2.x.y, ...) for two years after the next major version is released. +We maintain each major release (1.x.y, 2.x.y, ...) for two years after the next major version is released. Here is the table for the support timeline: - 1.x.y: released 2020-08-17, EOL -- 2.x.y: released 2021-04-15, supported until 2023-12-01 +- 2.x.y: released 2021-04-15, EOL - 3.x.y: released 2021-12-01, current - 4.x.y: To be released @@ -76,9 +96,9 @@ Here is the table for the support timeline: ### ansible-core -- 2.12 -- 2.13 -- 2.14 +- stable-2.14 +- stable-2.15 +- stable-2.16 - current development version ### Databases diff --git a/ansible_collections/community/mysql/TESTING.md b/ansible_collections/community/mysql/TESTING.md index 7bbafc31d..9e0840a10 100644 --- a/ansible_collections/community/mysql/TESTING.md +++ b/ansible_collections/community/mysql/TESTING.md @@ -52,6 +52,8 @@ The Makefile accept the following options - "stable-2.12" - "stable-2.13" - "stable-2.14" + - "stable-2.15" + - "stable-2.16" - "devel" - Description: Version of ansible to install in a venv to run ansible-test @@ -75,7 +77,7 @@ The Makefile accept the following options - `connector_name` - Mandatory: true - Choices: - - "pymysql + - "pymysql" - "mysqlclient" - Description: The python package of the connector to use. In addition to selecting the test container, this value is also used for tests filtering: `when: connector_name == 'pymysql'`. @@ -151,7 +153,7 @@ python run_all_tests.py ### Add a new Python, Connector or Database version -You can look into `[.github/workflows/ansible-test-plugins.yml](https://github.com/ansible-collections/community.mysql/tree/main/.github/workflows)` to see how those containers are built using [build-docker-image.yml](https://github.com/ansible-collections/community.mysql/blob/main/.github/workflows/build-docker-image.yml) and all [docker-image-xxx.yml](https://github.com/ansible-collections/community.mysql/blob/main/.github/workflows/docker-image-mariadb103-py38-mysqlclient201.yml) files. +You can look into [.github/workflows/ansible-test-plugins.yml](https://github.com/ansible-collections/community.mysql/tree/main/.github/workflows) to see how those containers are built using [build-docker-image.yml](https://github.com/ansible-collections/community.mysql/blob/main/.github/workflows/build-docker-image.yml) and all [docker-image-xxx.yml](https://github.com/ansible-collections/community.mysql/blob/main/.github/workflows/docker-image-mariadb103-py38-mysqlclient201.yml) files. 1. Add a workflow in [.github/workflows/](.github/workflows) 1. Add a new folder in [test-containers](test-containers) containing a new Dockerfile. Your container must contains 3 things: diff --git a/ansible_collections/community/mysql/changelogs/changelog.yaml b/ansible_collections/community/mysql/changelogs/changelog.yaml index e3431f34f..eb4264dcc 100644 --- a/ansible_collections/community/mysql/changelogs/changelog.yaml +++ b/ansible_collections/community/mysql/changelogs/changelog.yaml @@ -346,3 +346,63 @@ releases: - 3.7.2.yml - 553_fix_connection_arguemnts_for_old_mysqldb_driver.yaml release_date: '2023-05-25' + 3.8.0: + changes: + major_changes: + - The community.mysql collection no longer supports ``ansible-core 2.12`` and + ``ansible-core 2.13``. While we take no active measures to prevent usage and + there are no plans to introduce incompatible code to the modules, we will + stop testing those versions. Both are or will soon be End of Life and if you + are still using them, you should consider upgrading to the ``latest Ansible + / ansible-core 2.15 or later`` as soon as possible (https://github.com/ansible-collections/community.mysql/pull/574). + - mysql_role - the ``column_case_sensitive`` argument's default value will be + changed to ``true`` in community.mysql 4.0.0. If your playbook expected the + column to be automatically uppercased for your roles privileges, you should + set this to false explicitly (https://github.com/ansible-collections/community.mysql/issues/578). + - mysql_user - the ``column_case_sensitive`` argument's default value will be + changed to ``true`` in community.mysql 4.0.0. If your playbook expected the + column to be automatically uppercased for your users privileges, you should + set this to false explicitly (https://github.com/ansible-collections/community.mysql/issues/577). + minor_changes: + - mysql_info - add filter ``users_info`` (https://github.com/ansible-collections/community.mysql/pull/580). + - mysql_role - add ``column_case_sensitive`` option to prevent field names from + being uppercased (https://github.com/ansible-collections/community.mysql/pull/569). + - mysql_user - add ``column_case_sensitive`` option to prevent field names from + being uppercased (https://github.com/ansible-collections/community.mysql/pull/569). + release_summary: 'This is the minor release of the ``community.mysql`` collection. + + This changelog contains all changes to the modules and plugins in this + + collection that have been made after the previous release.' + fragments: + - 3.8.0.yml + - 569_fix_column_uppercasing.yml + - drop_ansible_core_2_12_and_2_13.yml + - lie_mysql_info_users_info.yml + release_date: '2023-10-25' + 3.9.0: + changes: + bugfixes: + - mysql_info - the ``slave_status`` filter was returning an empty list on MariaDB + with multiple replication channels. It now returns all channels by running + ``SHOW ALL SLAVES STATUS`` for MariaDB servers (https://github.com/ansible-collections/community.mysql/issues/603). + major_changes: + - Collection version 2.*.* is EOL, no more bugfixes will be backported. Please + consider upgrading to the latest version. + minor_changes: + - mysql_user - add the ``password_expire`` and ``password_expire_interval`` + arguments to implement the password expiration management for mysql user (https://github.com/ansible-collections/community.mysql/pull/598). + - mysql_user - add user attribute support via the ``attributes`` parameter and + return value (https://github.com/ansible-collections/community.mysql/pull/604). + release_summary: 'This is a minor release of the ``community.mysql`` collection. + + This changelog contains all changes to the modules and plugins in this + + collection that have been made after the previous release.' + fragments: + - 0-stable-2-eol.yml + - 3.9.0.yml + - 598-password_expire-support-for-mysql_user.yml + - 602-show-all-slaves-status.yaml + - 604-user-attributes.yaml + release_date: '2024-02-22' diff --git a/ansible_collections/community/mysql/plugins/doc_fragments/mysql.py b/ansible_collections/community/mysql/plugins/doc_fragments/mysql.py index 939126cba..27ec6509a 100644 --- a/ansible_collections/community/mysql/plugins/doc_fragments/mysql.py +++ b/ansible_collections/community/mysql/plugins/doc_fragments/mysql.py @@ -110,4 +110,7 @@ notes: - Alternatively, to avoid using I(login_unix_socket) argument on each invocation you can specify the socket path using the `socket` option in your MySQL config file (usually C(~/.my.cnf)) on the destination host, for example C(socket=/var/lib/mysql/mysql.sock). +attributes: + check_mode: + description: Can run in check_mode and return changed status prediction without modifying target. ''' diff --git a/ansible_collections/community/mysql/plugins/module_utils/implementations/mariadb/user.py b/ansible_collections/community/mysql/plugins/module_utils/implementations/mariadb/user.py index c1d2b6133..cdc14b217 100644 --- a/ansible_collections/community/mysql/plugins/module_utils/implementations/mariadb/user.py +++ b/ansible_collections/community/mysql/plugins/module_utils/implementations/mariadb/user.py @@ -23,3 +23,9 @@ def server_supports_alter_user(cursor): version = get_server_version(cursor) return LooseVersion(version) >= LooseVersion("10.2") + + +def server_supports_password_expire(cursor): + version = get_server_version(cursor) + + return LooseVersion(version) >= LooseVersion("10.4.3") diff --git a/ansible_collections/community/mysql/plugins/module_utils/implementations/mysql/user.py b/ansible_collections/community/mysql/plugins/module_utils/implementations/mysql/user.py index 1bdad5740..4e41c0542 100644 --- a/ansible_collections/community/mysql/plugins/module_utils/implementations/mysql/user.py +++ b/ansible_collections/community/mysql/plugins/module_utils/implementations/mysql/user.py @@ -24,3 +24,9 @@ def server_supports_alter_user(cursor): version = get_server_version(cursor) return LooseVersion(version) >= LooseVersion("5.6") + + +def server_supports_password_expire(cursor): + version = get_server_version(cursor) + + return LooseVersion(version) >= LooseVersion("5.7") diff --git a/ansible_collections/community/mysql/plugins/module_utils/mysql.py b/ansible_collections/community/mysql/plugins/module_utils/mysql.py index b95d20d0d..10ccfcf44 100644 --- a/ansible_collections/community/mysql/plugins/module_utils/mysql.py +++ b/ansible_collections/community/mysql/plugins/module_utils/mysql.py @@ -207,6 +207,13 @@ def get_server_version(cursor): return version_str +def get_server_implementation(cursor): + if 'mariadb' in get_server_version(cursor).lower(): + return "mariadb" + else: + return "mysql" + + def set_session_vars(module, cursor, session_vars): """Set session vars.""" for var, value in session_vars.items(): diff --git a/ansible_collections/community/mysql/plugins/module_utils/user.py b/ansible_collections/community/mysql/plugins/module_utils/user.py index a63ad89b5..17ad4b0c2 100644 --- a/ansible_collections/community/mysql/plugins/module_utils/user.py +++ b/ansible_collections/community/mysql/plugins/module_utils/user.py @@ -10,6 +10,7 @@ __metaclass__ = type # Simplified BSD License (see simplified_bsd.txt or https://opensource.org/licenses/BSD-2-Clause) import string +import json import re from ansible.module_utils.six import iteritems @@ -112,35 +113,57 @@ def get_grants(cursor, user, host): return grants.split(", ") -def get_existing_authentication(cursor, user): +def get_existing_authentication(cursor, user, host): # Return the plugin and auth_string if there is exactly one distinct existing plugin and auth_string. cursor.execute("SELECT VERSION()") - if 'mariadb' in cursor.fetchone()[0].lower(): + srv_type = cursor.fetchone() + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(srv_type, dict): + srv_type = list(srv_type.values()) + + if 'mariadb' in srv_type[0].lower(): # before MariaDB 10.2.19 and 10.3.11, "password" and "authentication_string" can differ # when using mysql_native_password cursor.execute("""select plugin, auth from ( select plugin, password as auth from mysql.user where user=%(user)s + and host=%(host)s union select plugin, authentication_string as auth from mysql.user where user=%(user)s - ) x group by plugin, auth limit 2 - """, {'user': user}) + and host=%(host)s) x group by plugin, auth limit 2 + """, {'user': user, 'host': host}) else: - cursor.execute("""select plugin, authentication_string as auth from mysql.user where user=%(user)s - group by plugin, authentication_string limit 2""", {'user': user}) + cursor.execute("""select plugin, authentication_string as auth + from mysql.user where user=%(user)s and host=%(host)s + group by plugin, authentication_string limit 2""", {'user': user, 'host': host}) rows = cursor.fetchall() - if len(rows) == 1: - return {'plugin': rows[0][0], 'auth_string': rows[0][1]} + + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(rows, dict): + rows = list(rows.values()) + + if isinstance(rows[0], tuple): + return {'plugin': rows[0][0], 'plugin_auth_string': rows[0][1]} + + if isinstance(rows[0], dict): + return {'plugin': rows[0].get('plugin'), 'plugin_auth_string': rows[0].get('auth')} return None def user_add(cursor, user, host, host_all, password, encrypted, plugin, plugin_hash_string, plugin_auth_string, new_priv, - tls_requires, check_mode, reuse_existing_password): + attributes, tls_requires, reuse_existing_password, module, + password_expire, password_expire_interval): + # If attributes are set, perform a sanity check to ensure server supports user attributes before creating user + if attributes and not get_attribute_support(cursor): + module.fail_json(msg="user attributes were specified but the server does not support user attributes") + # we cannot create users without a proper hostname if host_all: - return {'changed': False, 'password_changed': False} + return {'changed': False, 'password_changed': False, 'attributes': attributes} - if check_mode: - return {'changed': True, 'password_changed': None} + if module.check_mode: + return {'changed': True, 'password_changed': None, 'attributes': attributes} # Determine what user management method server uses old_user_mgmt = impl.use_old_user_mgmt(cursor) @@ -149,7 +172,7 @@ def user_add(cursor, user, host, host_all, password, encrypted, used_existing_password = False if reuse_existing_password: - existing_auth = get_existing_authentication(cursor, user) + existing_auth = get_existing_authentication(cursor, user, host) if existing_auth: plugin = existing_auth['plugin'] plugin_hash_string = existing_auth['auth_string'] @@ -183,12 +206,25 @@ def user_add(cursor, user, host, host_all, password, encrypted, query_with_args_and_tls_requires = query_with_args + (tls_requires,) cursor.execute(*mogrify(*query_with_args_and_tls_requires)) + if password_expire: + if not impl.server_supports_password_expire(cursor): + module.fail_json(msg="The server version does not match the requirements " + "for password_expire parameter. See module's documentation.") + set_password_expire(cursor, user, host, password_expire, password_expire_interval) + if new_priv is not None: for db_table, priv in iteritems(new_priv): privileges_grant(cursor, user, host, db_table, priv, tls_requires) if tls_requires is not None: privileges_grant(cursor, user, host, "*.*", get_grants(cursor, user, host), tls_requires) - return {'changed': True, 'password_changed': not used_existing_password} + + final_attributes = None + + if attributes: + cursor.execute("ALTER USER %s@%s ATTRIBUTE %s", (user, host, json.dumps(attributes))) + final_attributes = attributes_get(cursor, user, host) + + return {'changed': True, 'password_changed': not used_existing_password, 'attributes': final_attributes} def is_hash(password): @@ -201,7 +237,8 @@ def is_hash(password): def user_mod(cursor, user, host, host_all, password, encrypted, plugin, plugin_hash_string, plugin_auth_string, new_priv, - append_privs, subtract_privs, tls_requires, module, role=False, maria_role=False): + append_privs, subtract_privs, attributes, tls_requires, module, + password_expire, password_expire_interval, role=False, maria_role=False): changed = False msg = "User unchanged" grant_option = False @@ -261,27 +298,48 @@ def user_mod(cursor, user, host, host_all, password, encrypted, if current_pass_hash != encrypted_password: password_changed = True msg = "Password updated" - if module.check_mode: - return {'changed': True, 'msg': msg, 'password_changed': password_changed} - if old_user_mgmt: - cursor.execute("SET PASSWORD FOR %s@%s = %s", (user, host, encrypted_password)) - msg = "Password updated (old style)" - else: - try: - cursor.execute("ALTER USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, encrypted_password)) - msg = "Password updated (new style)" - except (mysql_driver.Error) as e: - # https://stackoverflow.com/questions/51600000/authentication-string-of-root-user-on-mysql - # Replacing empty root password with new authentication mechanisms fails with error 1396 - if e.args[0] == 1396: - cursor.execute( - "UPDATE mysql.user SET plugin = %s, authentication_string = %s, Password = '' WHERE User = %s AND Host = %s", - ('mysql_native_password', encrypted_password, user, host) - ) - cursor.execute("FLUSH PRIVILEGES") - msg = "Password forced update" - else: - raise e + if not module.check_mode: + if old_user_mgmt: + cursor.execute("SET PASSWORD FOR %s@%s = %s", (user, host, encrypted_password)) + msg = "Password updated (old style)" + else: + try: + cursor.execute("ALTER USER %s@%s IDENTIFIED WITH mysql_native_password AS %s", (user, host, encrypted_password)) + msg = "Password updated (new style)" + except (mysql_driver.Error) as e: + # https://stackoverflow.com/questions/51600000/authentication-string-of-root-user-on-mysql + # Replacing empty root password with new authentication mechanisms fails with error 1396 + if e.args[0] == 1396: + cursor.execute( + "UPDATE mysql.user SET plugin = %s, authentication_string = %s, Password = '' WHERE User = %s AND Host = %s", + ('mysql_native_password', encrypted_password, user, host) + ) + cursor.execute("FLUSH PRIVILEGES") + msg = "Password forced update" + else: + raise e + changed = True + + # Handle password expiration + if bool(password_expire): + if not impl.server_supports_password_expire(cursor): + module.fail_json(msg="The server version does not match the requirements " + "for password_expire parameter. See module's documentation.") + update = False + mariadb_role = True if "mariadb" in str(impl.__name__) else False + current_password_policy = get_password_expiration_policy(cursor, user, host, maria_role=mariadb_role) + password_expired = is_password_expired(cursor, user, host) + # Check if changes needed to be applied. + if not ((current_password_policy == -1 and password_expire == "default") or + (current_password_policy == 0 and password_expire == "never") or + (current_password_policy == password_expire_interval and password_expire == "interval") or + (password_expire == 'now' and password_expired)): + + update = True + + if not module.check_mode: + set_password_expire(cursor, user, host, password_expire, password_expire_interval) + password_changed = True changed = True # Handle plugin authentication @@ -335,9 +393,8 @@ def user_mod(cursor, user, host, host_all, password, encrypted, if db_table not in new_priv: if user != "root" and "PROXY" not in priv: msg = "Privileges updated" - if module.check_mode: - return {'changed': True, 'msg': msg, 'password_changed': password_changed} - privileges_revoke(cursor, user, host, db_table, priv, grant_option, maria_role) + if not module.check_mode: + privileges_revoke(cursor, user, host, db_table, priv, grant_option, maria_role) changed = True # If the user doesn't currently have any privileges on a db.table, then @@ -346,9 +403,8 @@ def user_mod(cursor, user, host, host_all, password, encrypted, for db_table, priv in iteritems(new_priv): if db_table not in curr_priv: msg = "New privileges granted" - if module.check_mode: - return {'changed': True, 'msg': msg, 'password_changed': password_changed} - privileges_grant(cursor, user, host, db_table, priv, tls_requires, maria_role) + if not module.check_mode: + privileges_grant(cursor, user, host, db_table, priv, tls_requires, maria_role) changed = True # If the db.table specification exists in both the user's current privileges @@ -387,17 +443,58 @@ def user_mod(cursor, user, host, host_all, password, encrypted, if len(grant_privs) + len(revoke_privs) > 0: msg = "Privileges updated: granted %s, revoked %s" % (grant_privs, revoke_privs) - if module.check_mode: - return {'changed': True, 'msg': msg, 'password_changed': password_changed} - if len(revoke_privs) > 0: - privileges_revoke(cursor, user, host, db_table, revoke_privs, grant_option, maria_role) - if len(grant_privs) > 0: - privileges_grant(cursor, user, host, db_table, grant_privs, tls_requires, maria_role) + if not module.check_mode: + if len(revoke_privs) > 0: + privileges_revoke(cursor, user, host, db_table, revoke_privs, grant_option, maria_role) + if len(grant_privs) > 0: + privileges_grant(cursor, user, host, db_table, grant_privs, tls_requires, maria_role) + else: + changed = True # after privilege manipulation, compare privileges from before and now after_priv = privileges_get(cursor, user, host, maria_role) changed = changed or (curr_priv != after_priv) + # Handle attributes + attribute_support = get_attribute_support(cursor) + final_attributes = {} + + if attributes: + if not attribute_support: + module.fail_json(msg="user attributes were specified but the server does not support user attributes") + else: + current_attributes = attributes_get(cursor, user, host) + + if current_attributes is None: + current_attributes = {} + + attributes_to_change = {} + + for key, value in attributes.items(): + if key not in current_attributes or current_attributes[key] != value: + attributes_to_change[key] = value + + if attributes_to_change: + msg = "Attributes updated: %s" % (", ".join(["%s: %s" % (key, value) for key, value in attributes_to_change.items()])) + + # Calculate final attributes by re-running attributes_get when not in check mode, and merge dictionaries when in check mode + if not module.check_mode: + cursor.execute("ALTER USER %s@%s ATTRIBUTE %s", (user, host, json.dumps(attributes_to_change))) + final_attributes = attributes_get(cursor, user, host) + else: + # Final if statements excludes items whose values are None in attributes_to_change, i.e. attributes that will be deleted + final_attributes = {k: v for d in (current_attributes, attributes_to_change) for k, v in d.items() if k not in attributes_to_change or + attributes_to_change[k] is not None} + + # Convert empty dict to None per return value requirements + final_attributes = final_attributes if final_attributes else None + changed = True + else: + final_attributes = current_attributes + else: + if attribute_support: + final_attributes = attributes_get(cursor, user, host) + if role: continue @@ -405,24 +502,23 @@ def user_mod(cursor, user, host, host_all, password, encrypted, current_requires = get_tls_requires(cursor, user, host) if current_requires != tls_requires: msg = "TLS requires updated" - if module.check_mode: - return {'changed': True, 'msg': msg, 'password_changed': password_changed} - if not old_user_mgmt: - pre_query = "ALTER USER" - else: - pre_query = "GRANT %s ON *.* TO" % ",".join(get_grants(cursor, user, host)) + if not module.check_mode: + if not old_user_mgmt: + pre_query = "ALTER USER" + else: + pre_query = "GRANT %s ON *.* TO" % ",".join(get_grants(cursor, user, host)) - if tls_requires is not None: - query = " ".join((pre_query, "%s@%s")) - query_with_args = mogrify_requires(query, (user, host), tls_requires) - else: - query = " ".join((pre_query, "%s@%s REQUIRE NONE")) - query_with_args = query, (user, host) + if tls_requires is not None: + query = " ".join((pre_query, "%s@%s")) + query_with_args = mogrify_requires(query, (user, host), tls_requires) + else: + query = " ".join((pre_query, "%s@%s REQUIRE NONE")) + query_with_args = query, (user, host) - cursor.execute(*query_with_args) + cursor.execute(*query_with_args) changed = True - return {'changed': changed, 'msg': msg, 'password_changed': password_changed} + return {'changed': changed, 'msg': msg, 'password_changed': password_changed, 'attributes': final_attributes} def user_delete(cursor, user, host, host_all, check_mode): @@ -478,6 +574,12 @@ def privileges_get(cursor, user, host, maria_role=False): return x for grant in grants: + + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(grant, dict): + grant = list(grant.values()) + if not maria_role: res = re.match("""GRANT (.+) ON (.+) TO (['`"]).*\\3@(['`"]).*\\4( IDENTIFIED BY PASSWORD (['`"]).+\\6)? ?(.*)""", grant[0]) else: @@ -627,7 +729,7 @@ def sort_column_order(statement): return '%s(%s)' % (priv_name, ', '.join(columns)) -def privileges_unpack(priv, mode, ensure_usage=True): +def privileges_unpack(priv, mode, column_case_sensitive, ensure_usage=True): """ Take a privileges string, typically passed as a parameter, and unserialize it into a dictionary, the same format as privileges_get() above. We have this custom format to avoid using YAML/JSON strings inside YAML playbooks. Example @@ -663,9 +765,14 @@ def privileges_unpack(priv, mode, ensure_usage=True): pieces[0] = object_type + '.'.join(dbpriv) if '(' in pieces[1]: - output[pieces[0]] = re.split(r',\s*(?=[^)]*(?:\(|$))', pieces[1].upper()) - for i in output[pieces[0]]: - privs.append(re.sub(r'\s*\(.*\)', '', i)) + if column_case_sensitive is True: + output[pieces[0]] = re.split(r',\s*(?=[^)]*(?:\(|$))', pieces[1]) + for i in output[pieces[0]]: + privs.append(re.sub(r'\s*\(.*\)', '', i)) + else: + output[pieces[0]] = re.split(r',\s*(?=[^)]*(?:\(|$))', pieces[1].upper()) + for i in output[pieces[0]]: + privs.append(re.sub(r'\s*\(.*\)', '', i)) else: output[pieces[0]] = pieces[1].upper().split(',') privs = output[pieces[0]] @@ -715,6 +822,14 @@ def privileges_grant(cursor, user, host, db_table, priv, tls_requires, maria_rol priv_string = ",".join([p for p in priv if p not in ('GRANT', )]) query = ["GRANT %s ON %s" % (priv_string, db_table)] + # MySQL and MariaDB don't store roles in the user table the same manner: + # select user, host from mysql.user; + # +------------------+-----------+ + # | user | host | + # +------------------+-----------+ + # | role_foo | % | <- MySQL + # | role_foo | | <- MariaDB + # +------------------+-----------+ if not maria_role: query.append("TO %s@%s") params = (user, host) @@ -772,6 +887,11 @@ def get_resource_limits(cursor, user, host): cursor.execute(query, (user, host)) res = cursor.fetchone() + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(res, dict): + res = list(res.values()) + if not res: return None @@ -783,11 +903,22 @@ def get_resource_limits(cursor, user, host): } cursor.execute("SELECT VERSION()") - if 'mariadb' in cursor.fetchone()[0].lower(): + srv_type = cursor.fetchone() + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(srv_type, dict): + srv_type = list(srv_type.values()) + + if 'mariadb' in srv_type[0].lower(): query = ('SELECT max_statement_time AS MAX_STATEMENT_TIME ' 'FROM mysql.user WHERE User = %s AND Host = %s') cursor.execute(query, (user, host)) res_max_statement_time = cursor.fetchone() + + # Mysql_info use a DictCursor so we must convert back to a list + # otherwise we get KeyError 0 + if isinstance(res_max_statement_time, dict): + res_max_statement_time = list(res_max_statement_time.values()) current_limits['MAX_STATEMENT_TIME'] = res_max_statement_time[0] return current_limits @@ -872,6 +1003,111 @@ def limit_resources(module, cursor, user, host, resource_limits, check_mode): return True +def set_password_expire(cursor, user, host, password_expire, password_expire_interval): + """Fuction to set passowrd expiration for user. + + Args: + cursor (cursor): DB driver cursor object. + user (str): User name. + host (str): User hostname. + password_expire (str): Password expiration mode. + password_expire_days (int): Invterval of days password expires. + """ + if password_expire.lower() == "never": + statement = "PASSWORD EXPIRE NEVER" + elif password_expire.lower() == "default": + statement = "PASSWORD EXPIRE DEFAULT" + elif password_expire.lower() == "interval": + statement = "PASSWORD EXPIRE INTERVAL %d DAY" % (password_expire_interval) + elif password_expire.lower() == "now": + statement = "PASSWORD EXPIRE" + + cursor.execute("ALTER USER %s@%s " + statement, (user, host)) + + +def get_password_expiration_policy(cursor, user, host, maria_role=False): + """Function to get password policy for user. + + Args: + cursor (cursor): DB driver cursor object. + user (str): User name. + host (str): User hostname. + maria_role (bool, optional): mariadb or mysql. Defaults to False. + + Returns: + policy (int): Current users password policy. + """ + if not maria_role: + statement = "SELECT IFNULL(password_lifetime, -1) FROM mysql.user \ + WHERE User = %s AND Host = %s", (user, host) + else: + statement = "SELECT JSON_EXTRACT(Priv, '$.password_lifetime') AS password_lifetime \ + FROM mysql.global_priv \ + WHERE User = %s AND Host = %s", (user, host) + cursor.execute(*statement) + policy = cursor.fetchone()[0] + return int(policy) + + +def is_password_expired(cursor, user, host): + """Function to check if password is expired + + Args: + cursor (cursor): DB driver cursor object. + user (str): User name. + host (str): User hostname. + + Returns: + expired (bool): True if expired, else False. + """ + statement = "SELECT password_expired FROM mysql.user \ + WHERE User = %s AND Host = %s", (user, host) + cursor.execute(*statement) + expired = cursor.fetchone()[0] + if str(expired) == "Y": + return True + return False + + +def get_attribute_support(cursor): + """Checks if the MySQL server supports user attributes. + + Args: + cursor (cursor): DB driver cursor object. + Returns: + True if attributes are supported, False if they are not. + """ + try: + # information_schema.tables does not hold the tables within information_schema itself + cursor.execute("SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES LIMIT 0") + cursor.fetchone() + except mysql_driver.Error: + return False + + return True + + +def attributes_get(cursor, user, host): + """Get attributes for a given user. + + Args: + cursor (cursor): DB driver cursor object. + user (str): User name. + host (str): User host name. + + Returns: + None if the user does not exist or the user has no attributes set, otherwise a dict of attributes set on the user + """ + cursor.execute("SELECT attribute FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE user = %s AND host = %s", (user, host)) + + r = cursor.fetchone() + # convert JSON string stored in row into a dict - mysql enforces that user_attributes entires are in JSON format + j = json.loads(r[0]) if r and r[0] else None + + # if the attributes dict is empty, return None instead + return j if j else None + + def get_impl(cursor): global impl cursor.execute("SELECT VERSION()") diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_db.py b/ansible_collections/community/mysql/plugins/modules/mysql_db.py index 5a8fe3e3e..2cb67dce2 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_db.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_db.py @@ -188,13 +188,14 @@ requirements: - mysql (command line binary) - mysqldump (command line binary) notes: - - Supports C(check_mode). - Requires the mysql and mysqldump binaries on the remote host. - This module is B(not idempotent) when I(state) is C(import), and will import the dump file each time if run more than once. +attributes: + check_mode: + support: full extends_documentation_fragment: - community.mysql.mysql - ''' EXAMPLES = r''' @@ -576,14 +577,14 @@ def db_create(cursor, db, encoding, collation): def main(): argument_spec = mysql_common_argument_spec() argument_spec.update( - name=dict(type='list', required=True, aliases=['db']), + name=dict(type='list', elements='str', required=True, aliases=['db']), encoding=dict(type='str', default=''), collation=dict(type='str', default=''), target=dict(type='path'), state=dict(type='str', default='present', choices=['absent', 'dump', 'import', 'present']), single_transaction=dict(type='bool', default=False), quick=dict(type='bool', default=True), - ignore_tables=dict(type='list', default=[]), + ignore_tables=dict(type='list', elements='str', default=[]), hex_blob=dict(default=False, type='bool'), force=dict(type='bool', default=False), master_data=dict(type='int', default=0, choices=[0, 1, 2]), diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_info.py b/ansible_collections/community/mysql/plugins/modules/mysql_info.py index 11b1a8003..0be25fa8d 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_info.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_info.py @@ -5,6 +5,7 @@ # GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) from __future__ import absolute_import, division, print_function + __metaclass__ = type DOCUMENTATION = r''' @@ -19,7 +20,7 @@ options: description: - Limit the collected information by comma separated string or YAML list. - Allowable values are C(version), C(databases), C(settings), C(global_status), - C(users), C(engines), C(master_status), C(slave_status), C(slave_hosts). + C(users), C(users_info), C(engines), C(master_status), C(slave_status), C(slave_hosts). - By default, collects all subsets. - You can use '!' before value (for example, C(!settings)) to exclude it from the information. - If you pass including and excluding values to the filter, for example, I(filter=!settings,version), @@ -47,7 +48,10 @@ options: notes: - Calculating the size of a database might be slow, depending on the number and size of tables in it. To avoid this, use I(exclude_fields=db_size). -- Supports C(check_mode). + +attributes: + check_mode: + support: full seealso: - module: community.mysql.mysql_variables @@ -71,6 +75,9 @@ EXAMPLES = r''' # Display only databases and users info: # ansible mysql-hosts -m mysql_info -a 'filter=databases,users' +# Display all users privileges: +# ansible mysql-hosts -m mysql_info -a 'filter=users_info' + # Display only slave status: # ansible standby -m mysql_info -a 'filter=slave_status' @@ -119,6 +126,38 @@ EXAMPLES = r''' - databases exclude_fields: db_size return_empty_dbs: true + +- name: Clone users from one server to another + block: + # Step 1 + - name: Fetch information from a source server + delegate_to: server_source + community.mysql.mysql_info: + filter: + - users_info + register: result + + # Step 2 + # Don't work with sha256_password and cache_sha2_password + - name: Clone users fetched in a previous task to a target server + 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 }}" + when: + - item.name != 'root' # In case you don't want to import admin accounts + - item.name != 'mariadb.sys' + - item.name != 'mysql' ''' RETURN = r''' @@ -178,11 +217,31 @@ global_status: sample: - { "Innodb_buffer_pool_read_requests": 123, "Innodb_buffer_pool_reads": 32 } users: - description: Users information. + description: Return a dictionnary of users grouped by host and with global privileges only. returned: if not excluded by filter type: dict sample: - { "localhost": { "root": { "Alter_priv": "Y", "Alter_routine_priv": "Y" } } } +users_info: + description: + - Information about users accounts. + - The output can be used as an input of the M(community.mysql.mysql_user) plugin. + - Useful when migrating accounts to another server or to create an inventory. + - Does not support proxy privileges. If an account has proxy privileges, they won't appear in the output. + - Causes issues with authentications plugins C(sha256_password) and C(caching_sha2_password). + If the output is fed to M(community.mysql.mysql_user), the + ``plugin_auth_string`` will most likely be unreadable due to non-binary + characters. + returned: if not excluded by filter + type: dict + sample: + - { "plugin_auth_string": '*1234567', + "name": "user1", + "host": "host.com", + "plugin": "mysql_native_password", + "priv": "db1.*:SELECT/db2.*:SELECT", + "resource_limits": { "MAX_USER_CONNECTIONS": 100 } } + version_added: '3.8.0' engines: description: Information about the server's storage engines. returned: if not excluded by filter @@ -234,6 +293,13 @@ from ansible_collections.community.mysql.plugins.module_utils.mysql import ( mysql_driver_fail_msg, get_connector_name, get_connector_version, + get_server_implementation, +) + +from ansible_collections.community.mysql.plugins.module_utils.user import ( + privileges_get, + get_resource_limits, + get_existing_authentication, ) from ansible.module_utils.six import iteritems from ansible.module_utils._text import to_native @@ -261,9 +327,10 @@ class MySQL_Info(object): 5. add info about the new subset with an example to RETURN block """ - def __init__(self, module, cursor): + def __init__(self, module, cursor, server_implementation): self.module = module self.cursor = cursor + self.server_implementation = server_implementation self.info = { 'version': {}, 'databases': {}, @@ -271,6 +338,7 @@ class MySQL_Info(object): 'global_status': {}, 'engines': {}, 'users': {}, + 'users_info': {}, 'master_status': {}, 'slave_hosts': {}, 'slave_status': {}, @@ -339,6 +407,9 @@ class MySQL_Info(object): if 'users' in wanted: self.__get_users() + if 'users_info' in wanted: + self.__get_users_info() + if 'master_status' in wanted: self.__get_master_status() @@ -429,7 +500,10 @@ class MySQL_Info(object): def __get_slave_status(self): """Get slave status if the instance is a slave.""" - res = self.__exec_sql('SHOW SLAVE STATUS') + if self.server_implementation == "mariadb": + res = self.__exec_sql('SHOW ALL SLAVES STATUS') + else: + res = self.__exec_sql('SHOW SLAVE STATUS') if res: for line in res: host = line['Master_Host'] @@ -477,6 +551,86 @@ class MySQL_Info(object): if vname not in ('Host', 'User'): self.info['users'][host][user][vname] = self.__convert(val) + def __get_users_info(self): + """Get user privileges, passwords, resources_limits, ... + + Query the server to get all the users and return a string + of privileges that can be used by the mysql_user plugin. + For instance: + + "users_info": [ + { + "host": "users_info.com", + "priv": "*.*: ALL,GRANT", + "name": "users_info_adm" + }, + { + "host": "users_info.com", + "priv": "`mysql`.*: SELECT/`users_info_db`.*: SELECT", + "name": "users_info_multi" + } + ] + """ + res = self.__exec_sql('SELECT * FROM mysql.user') + if not res: + return None + + output = list() + for line in res: + user = line['User'] + host = line['Host'] + + user_priv = privileges_get(self.cursor, user, host) + + if not user_priv: + self.module.warn("No privileges found for %s on host %s" % (user, host)) + continue + + priv_string = list() + for db_table, priv in user_priv.items(): + # Proxy privileges are hard to work with because of different quotes or + # backticks like ''@'', ''@'%' or even ``@``. In addition, MySQL will + # forbid you to grant a proxy privileges through TCP. + if set(priv) == {'PROXY', 'GRANT'} or set(priv) == {'PROXY'}: + continue + + unquote_db_table = db_table.replace('`', '').replace("'", '') + priv_string.append('%s:%s' % (unquote_db_table, ','.join(priv))) + + # Only keep *.* USAGE if it's the only user privilege given + if len(priv_string) > 1 and '*.*:USAGE' in priv_string: + priv_string.remove('*.*:USAGE') + + resource_limits = get_resource_limits(self.cursor, user, host) + + copy_ressource_limits = dict.copy(resource_limits) + output_dict = { + 'name': user, + 'host': host, + 'priv': '/'.join(priv_string), + 'resource_limits': copy_ressource_limits, + } + + # Prevent returning a resource limit if empty + if resource_limits: + for key, value in resource_limits.items(): + if value == 0: + del output_dict['resource_limits'][key] + if len(output_dict['resource_limits']) == 0: + del output_dict['resource_limits'] + + authentications = get_existing_authentication(self.cursor, user, host) + if authentications: + output_dict.update(authentications) + + # TODO password_option + # TODO lock_option + # but both are not supported by mysql_user atm. So no point yet. + + output.append(output_dict) + + self.info['users_info'] = output + def __get_databases(self, exclude_fields, return_empty_dbs): """Get info about databases.""" if not exclude_fields: @@ -544,8 +698,8 @@ def main(): argument_spec = mysql_common_argument_spec() argument_spec.update( login_db=dict(type='str'), - filter=dict(type='list'), - exclude_fields=dict(type='list'), + filter=dict(type='list', elements='str'), + exclude_fields=dict(type='list', elements='str'), return_empty_dbs=dict(type='bool', default=False), ) @@ -590,10 +744,12 @@ def main(): 'Exception message: %s' % (connector_name, connector_version, config_file, to_native(e))) module.fail_json(msg) + server_implementation = get_server_implementation(cursor) + ############################### # Create object and do main job - mysql = MySQL_Info(module, cursor) + mysql = MySQL_Info(module, cursor, server_implementation) module.exit_json(changed=False, connector_name=connector_name, diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_query.py b/ansible_collections/community/mysql/plugins/modules/mysql_query.py index 12d5a5630..fd3a8e09e 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_query.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_query.py @@ -36,6 +36,7 @@ options: - List of values to be passed as positional arguments to the query. - Mutually exclusive with I(named_args). type: list + elements: raw named_args: description: - Dictionary of key-value arguments to pass to the query. @@ -50,6 +51,9 @@ options: - Where passed queries run in a single transaction (C(yes)) or commit them one-by-one (C(no)). type: bool default: false +attributes: + check_mode: + support: none seealso: - module: community.mysql.mysql_db author: @@ -138,7 +142,7 @@ def main(): argument_spec.update( query=dict(type='raw', required=True), login_db=dict(type='str'), - positional_args=dict(type='list'), + positional_args=dict(type='list', elements='raw'), named_args=dict(type='dict'), single_transaction=dict(type='bool', default=False), ) diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_replication.py b/ansible_collections/community/mysql/plugins/modules/mysql_replication.py index 33e14bc26..934b479b9 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_replication.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_replication.py @@ -23,12 +23,12 @@ options: mode: description: - Module operating mode. Could be - C(changeprimary) (CHANGE PRIMARY TO), - C(getprimary) (SHOW PRIMARY STATUS), - C(getreplica) (SHOW REPLICA), + C(changeprimary) (CHANGE MASTER TO), + C(getprimary) (SHOW MASTER STATUS), + C(getreplica) (SHOW REPLICA STATUS), C(startreplica) (START REPLICA), C(stopreplica) (STOP REPLICA), - C(resetprimary) (RESET PRIMARY) - supported since community.mysql 0.1.0, + C(resetprimary) (RESET MASTER) - supported since community.mysql 0.1.0, C(resetreplica) (RESET REPLICA), C(resetreplicaall) (RESET REPLICA ALL). type: str @@ -190,10 +190,13 @@ options: notes: - If an empty value for the parameter of string type is needed, use an empty string. +attributes: + check_mode: + support: none + extends_documentation_fragment: - community.mysql.mysql - seealso: - module: community.mysql.mysql_info - name: MySQL replication reference diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_role.py b/ansible_collections/community/mysql/plugins/modules/mysql_role.py index 070d7939d..3e3462ab1 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_role.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_role.py @@ -121,11 +121,24 @@ options: type: bool default: true + column_case_sensitive: + description: + - The default is C(false). + - When C(true), the module will not uppercase the field in the privileges. + - When C(false), the field names will be upper-cased. This was the default before this + feature was introduced but since MySQL/MariaDB is case sensitive you should set this + to C(true) in most cases. + type: bool + version_added: '3.8.0' + notes: - Pay attention that the module runs C(SET DEFAULT ROLE ALL TO) all the I(members) passed by default when the state has changed. If you want to avoid this behavior, set I(set_default_role_all) to C(no). - - Supports C(check_mode). + +attributes: + check_mode: + support: full seealso: - module: community.mysql.mysql_user @@ -136,6 +149,8 @@ seealso: author: - Andrew Klychkov (@Andersson007) - Felix Hamme (@betanummeric) + - kmarse (@kmarse) + - Laurent Indermühle (@laurent-indermuehle) extends_documentation_fragment: - community.mysql.mysql @@ -916,8 +931,9 @@ class Role(): if privs: result = user_mod(self.cursor, self.name, self.host, None, None, None, None, None, None, - privs, append_privs, subtract_privs, None, - self.module, role=True, maria_role=self.is_mariadb) + privs, append_privs, subtract_privs, None, None, + self.module, None, None, role=True, + maria_role=self.is_mariadb) changed = result['changed'] if admin: @@ -954,7 +970,8 @@ def main(): detach_members=dict(type='bool', default=False), check_implicit_admin=dict(type='bool', default=False), set_default_role_all=dict(type='bool', default=True), - members_must_exist=dict(type='bool', default=True) + members_must_exist=dict(type='bool', default=True), + column_case_sensitive=dict(type='bool', default=None), # TODO 4.0.0 add default=True ) module = AnsibleModule( argument_spec=argument_spec, @@ -989,6 +1006,7 @@ def main(): db = '' set_default_role_all = module.params['set_default_role_all'] members_must_exist = module.params['members_must_exist'] + column_case_sensitive = module.params['column_case_sensitive'] if priv and not isinstance(priv, (str, dict)): msg = ('The "priv" parameter must be str or dict ' @@ -1001,6 +1019,13 @@ def main(): if mysql_driver is None: module.fail_json(msg=mysql_driver_fail_msg) + # TODO Release 4.0.0 : Remove this test and variable assignation + if column_case_sensitive is None: + column_case_sensitive = False + module.warn("Option column_case_sensitive is not provided. " + "The default is now false, so the column's name will be uppercased. " + "The default will be changed to true in community.mysql 4.0.0.") + cursor = None try: if check_implicit_admin: @@ -1038,7 +1063,7 @@ def main(): module.fail_json(msg=to_native(e)) try: - priv = privileges_unpack(priv, mode, ensure_usage=not subtract_privs) + priv = privileges_unpack(priv, mode, column_case_sensitive, ensure_usage=not subtract_privs) except Exception as e: module.fail_json(msg='Invalid privileges string: %s' % to_native(e)) diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_user.py b/ansible_collections/community/mysql/plugins/modules/mysql_user.py index e87fe12db..e02b15326 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_user.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_user.py @@ -155,6 +155,37 @@ options: - Cannot be used to set global variables, use the M(community.mysql.mysql_variables) module instead. type: dict version_added: '3.6.0' + password_expire: + description: + - C(never) - I(password) will never expire. + - C(default) - I(password) is defined using global system variable I(default_password_lifetime) setting. + - C(interval) - I(password) will expire in days which is defined in I(password_expire_interval). + - C(now) - I(password) will expire immediately. + type: str + choices: [ now, never, default, interval ] + version_added: '3.9.0' + password_expire_interval: + description: + - Number of days I(password) will expire. Requires I(password_expire=interval). + type: int + version_added: '3.9.0' + + column_case_sensitive: + description: + - The default is C(false). + - When C(true), the module will not uppercase the field names in the privileges. + - When C(false), the field names will be upper-cased. This is the default + - This feature was introduced because MySQL 8 and above uses case sensitive + fields names in privileges. + type: bool + version_added: '3.8.0' + attributes: + description: + - "Create, update, or delete user attributes (arbitrary 'key: value' comments) for the user." + - MySQL server must support the INFORMATION_SCHEMA.USER_ATTRIBUTES table. Provided since MySQL 8.0. + - To delete an existing attribute, set its value to null. + type: dict + version_added: '3.9.0' notes: - "MySQL server installs with default I(login_user) of C(root) and no password. @@ -163,7 +194,10 @@ notes: 2) drop a C(~/.my.cnf) file containing the new root credentials. Subsequent runs of the playbook will then succeed by reading the new credentials from the file." - Currently, there is only support for the C(mysql_native_password) encrypted password hash module. - - Supports (check_mode). + +attributes: + check_mode: + support: full seealso: - module: community.mysql.mysql_info @@ -178,9 +212,11 @@ author: - Jonathan Mainguy (@Jmainguy) - Benjamin Malynovytch (@bmalynovytch) - Lukasz Tomaszkiewicz (@tomaszkiewicz) +- kmarse (@kmarse) +- Laurent Indermühle (@laurent-indermuehle) + extends_documentation_fragment: - community.mysql.mysql - ''' EXAMPLES = r''' @@ -242,6 +278,13 @@ EXAMPLES = r''' FUNCTION my_db.my_function: EXECUTE state: present +- name: Modify user attributes, creating the attribute 'foo' and removing the attribute 'bar' + community.mysql.mysql_user: + name: bob + attributes: + foo: "foo" + bar: null + - name: Modify user to require TLS connection with a valid client certificate community.mysql.mysql_user: name: bob @@ -390,6 +433,7 @@ def main(): tls_requires=dict(type='dict'), append_privs=dict(type='bool', default=False), subtract_privs=dict(type='bool', default=False), + attributes=dict(type='dict'), check_implicit_admin=dict(type='bool', default=False), update_password=dict(type='str', default='always', choices=['always', 'on_create', 'on_new_username'], no_log=False), sql_log_bin=dict(type='bool', default=True), @@ -399,6 +443,9 @@ def main(): resource_limits=dict(type='dict'), force_context=dict(type='bool', default=False), session_vars=dict(type='dict'), + column_case_sensitive=dict(type='bool', default=None), # TODO 4.0.0 add default=True + password_expire=dict(type='str', choices=['now', 'never', 'default', 'interval'], no_log=True), + password_expire_interval=dict(type='int', required_if=[('password_expire', 'interval', True)], no_log=True), ) module = AnsibleModule( argument_spec=argument_spec, @@ -421,6 +468,7 @@ def main(): append_privs = module.boolean(module.params["append_privs"]) subtract_privs = module.boolean(module.params['subtract_privs']) update_password = module.params['update_password'] + attributes = module.params['attributes'] ssl_cert = module.params["client_cert"] ssl_key = module.params["client_key"] ssl_ca = module.params["ca_cert"] @@ -434,6 +482,9 @@ def main(): plugin_auth_string = module.params["plugin_auth_string"] resource_limits = module.params["resource_limits"] session_vars = module.params["session_vars"] + column_case_sensitive = module.params["column_case_sensitive"] + password_expire = module.params["password_expire"] + password_expire_interval = module.params["password_expire_interval"] if priv and not isinstance(priv, (str, dict)): module.fail_json(msg="priv parameter must be str or dict but %s was passed" % type(priv)) @@ -444,6 +495,10 @@ def main(): if mysql_driver is None: module.fail_json(msg=mysql_driver_fail_msg) + if password_expire_interval and password_expire_interval < 1: + module.fail_json(msg="password_expire_interval value \ + should be positive number") + cursor = None try: if check_implicit_admin: @@ -460,6 +515,13 @@ def main(): module.fail_json(msg="unable to connect to database, check login_user and login_password are correct or %s has the credentials. " "Exception message: %s" % (config_file, to_native(e))) + # TODO Release 4.0.0 : Remove this test and variable assignation + if column_case_sensitive is None: + column_case_sensitive = False + module.warn("Option column_case_sensitive is not provided. " + "The default is now false, so the column's name will be uppercased. " + "The default will be changed to true in community.mysql 4.0.0.") + if not sql_log_bin: cursor.execute("SET SQL_LOG_BIN=0;") @@ -473,23 +535,28 @@ def main(): mode = get_mode(cursor) except Exception as e: module.fail_json(msg=to_native(e)) - priv = privileges_unpack(priv, mode, ensure_usage=not subtract_privs) + + priv = privileges_unpack(priv, mode, column_case_sensitive, ensure_usage=not subtract_privs) password_changed = False + final_attributes = None if state == "present": if user_exists(cursor, user, host, host_all): try: if update_password == "always": result = user_mod(cursor, user, host, host_all, password, encrypted, plugin, plugin_hash_string, plugin_auth_string, - priv, append_privs, subtract_privs, tls_requires, module) + priv, append_privs, subtract_privs, attributes, tls_requires, module, + password_expire, password_expire_interval) else: result = user_mod(cursor, user, host, host_all, None, encrypted, None, None, None, - priv, append_privs, subtract_privs, tls_requires, module) + priv, append_privs, subtract_privs, attributes, tls_requires, module, + password_expire, password_expire_interval) changed = result['changed'] msg = result['msg'] password_changed = result['password_changed'] + final_attributes = result['attributes'] except (SQLParseError, InvalidPrivsError, mysql_driver.Error) as e: module.fail_json(msg=to_native(e)) @@ -502,9 +569,11 @@ def main(): reuse_existing_password = update_password == 'on_new_username' result = user_add(cursor, user, host, host_all, password, encrypted, plugin, plugin_hash_string, plugin_auth_string, - priv, tls_requires, module.check_mode, reuse_existing_password) + priv, attributes, tls_requires, reuse_existing_password, module, + password_expire, password_expire_interval) changed = result['changed'] password_changed = result['password_changed'] + final_attributes = result['attributes'] if changed: msg = "User added" @@ -521,7 +590,7 @@ def main(): else: changed = False msg = "User doesn't exist" - module.exit_json(changed=changed, user=user, msg=msg, password_changed=password_changed) + module.exit_json(changed=changed, user=user, msg=msg, password_changed=password_changed, attributes=final_attributes) if __name__ == '__main__': diff --git a/ansible_collections/community/mysql/plugins/modules/mysql_variables.py b/ansible_collections/community/mysql/plugins/modules/mysql_variables.py index f404d5aab..dfe8466f0 100644 --- a/ansible_collections/community/mysql/plugins/modules/mysql_variables.py +++ b/ansible_collections/community/mysql/plugins/modules/mysql_variables.py @@ -44,8 +44,9 @@ options: default: global version_added: '0.1.0' -notes: -- Does not support C(check_mode). +attributes: + check_mode: + support: none seealso: - module: community.mysql.mysql_info @@ -175,7 +176,7 @@ def setvariable(cursor, mysqlvar, value, mode='global'): def main(): argument_spec = mysql_common_argument_spec() argument_spec.update( - variable=dict(type='str'), + variable=dict(type='str', required=True), value=dict(type='str'), mode=dict(type='str', choices=['global', 'persist', 'persist_only'], default='global'), ) 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 |