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/postgresql/tests | |
| 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 'ansible_collections/community/postgresql/tests')
77 files changed, 4290 insertions, 1332 deletions
diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/defaults/main.yml index 766feeecc..d5c9deb57 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/defaults/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/defaults/main.yml @@ -1,4 +1,5 @@ db_name: 'ansible_db' +db_name_icu: 'ansible_db_icu' db_user1: 'ansible.db.user1' db_user2: 'ansible.db.user2' tmp_dir: '/tmp' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/main.yml index dd55c3f98..2c5fc7c95 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/main.yml @@ -45,3 +45,6 @@ # Simple test to create and then drop with force - import_tasks: manage_database.yml + +# Test the comment feature +- import_tasks: postgresql_db_comment.yml diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_comment.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_comment.yml new file mode 100644 index 000000000..1e4d8426c --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_comment.yml @@ -0,0 +1,189 @@ +# Test code for the postgresql_db comment module feature +# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <andrew.a.klychkov@gmail.com> +# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) + +- name: Set parameters we use with most of tasks + ansible.builtin.set_fact: + task_parameters: &task_parameters + become_user: "{{ pg_user }}" + become: true + register: result + +- name: Create DB with comment + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + trust_input: false + login_user: "{{ pg_user }}" + comment: Test DB comment 1 + +- name: Assert the executed commands + assert: + that: + - result is changed + - result.db == "comment_db" + - result.executed_commands == ['CREATE DATABASE "comment_db"', "COMMENT ON DATABASE \"comment_db\" IS 'Test DB comment 1'"] + +- name: Get the DB comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Test DB comment 1" + + +- name: Create DB with another comment in check mode + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + comment: Another comment + check_mode: true + +- name: Assert the result + assert: + that: + - result is changed + +- name: Check the comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comment hasn't changed + assert: + that: + - result.query_result[0]['comment'] == "Test DB comment 1" + + +- name: Create DB with another comment in real mode + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + comment: Another comment + +- name: Assert the result + assert: + that: + - result is changed + - result.executed_commands == ["COMMENT ON DATABASE \"comment_db\" IS 'Another comment'"] + +- name: Check the comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Another comment" + + +- name: Create DB with the same comment in real mode + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + comment: Another comment + +- name: Assert the result + assert: + that: + - result is not changed + - result.executed_commands == [] + +- name: Check the comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Another comment" + + +- name: Not specifying the comment will not erase it + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + +- name: Assert the result + assert: + that: + - result is not changed + - result.executed_commands == [] + +- name: Check the comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Another comment" + + +- name: Reset the comment + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + comment: '' + +- name: Assert the result + assert: + that: + - result is changed + - result.executed_commands == ["COMMENT ON DATABASE \"comment_db\" IS ''"] + +- name: Check the comment + <<: *task_parameters + postgresql_query: + login_user: "{{ pg_user }}" + query: "SELECT pg_catalog.shobj_description(d.oid, 'pg_database') AS comment FROM pg_catalog.pg_database d WHERE datname = 'comment_db'" + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == None + + +- name: Reset the comment again + <<: *task_parameters + postgresql_db: + state: present + name: comment_db + login_user: "{{ pg_user }}" + comment: '' + +- name: Assert the result + assert: + that: + - result is not changed + - result.executed_commands == [] + + +- name: Clean up + <<: *task_parameters + postgresql_db: + state: absent + name: comment_db + login_user: "{{ pg_user }}" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_initial.yml index 472524a23..e87b4c3da 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_db/tasks/postgresql_db_initial.yml @@ -105,7 +105,7 @@ # # Test conn_limit, encoding, collate, ctype, template options # -- name: Create a DB with conn_limit, encoding, collate, ctype, and template options +- name: Create a DB with conn_limit, encoding, collate, ctype and template options become_user: "{{ pg_user }}" become: true postgresql_db: @@ -124,6 +124,35 @@ - result is changed - result.executed_commands == ["CREATE DATABASE \"{{ db_name }}\" TEMPLATE \"template0\" ENCODING 'LATIN1' LC_COLLATE 'pt_BR{{ locale_latin_suffix }}' LC_CTYPE 'es_ES{{ locale_latin_suffix }}' CONNECTION LIMIT 100"] or result.executed_commands == ["CREATE DATABASE \"{{ db_name }}\" TEMPLATE \"template0\" ENCODING E'LATIN1' LC_COLLATE E'pt_BR{{ locale_latin_suffix }}' LC_CTYPE E'es_ES{{ locale_latin_suffix }}' CONNECTION LIMIT 100"] + +# +# Test conn_limit, encoding, collate, ctype, icu_locale, icu_provider, template options +# +- block: + + - name: Create a DB with conn_limit, encoding, collate, ctype, icu_locale, locale_provider and template options + become_user: "{{ pg_user }}" + become: true + postgresql_db: + name: '{{ db_name_icu }}' + state: 'present' + conn_limit: '100' + encoding: 'LATIN1' + lc_collate: 'pt_BR{{ locale_latin_suffix }}' + lc_ctype: 'es_ES{{ locale_latin_suffix }}' + icu_locale: 'es_ES-x-icu' + locale_provider: 'icu' + template: 'template0' + login_user: "{{ pg_user }}" + register: result + + - assert: + that: + - result is changed + - result.executed_commands == ["CREATE DATABASE \"{{ db_name_icu }}\" TEMPLATE \"template0\" ENCODING 'LATIN1' LC_COLLATE 'pt_BR{{ locale_latin_suffix }}' LC_CTYPE 'es_ES{{ locale_latin_suffix }}' ICU_LOCALE 'es_ES-x-icu' LOCALE_PROVIDER 'icu' CONNECTION LIMIT 100"] or result.executed_commands == ["CREATE DATABASE \"{{ db_name_icu }}\" TEMPLATE \"template0\" ENCODING E'LATIN1' LC_COLLATE E'pt_BR{{ locale_latin_suffix }}' LC_CTYPE E'es_ES{{ locale_latin_suffix }}' ICU_LOCALE E'es_ES-x-icu' LOCALE_PROVIDER 'icu' CONNECTION LIMIT 100"] + + when: postgres_version_resp.stdout is version('15.0', '>=') + - name: Check that the DB has all of our options become_user: "{{ pg_user }}" become: true @@ -140,6 +169,29 @@ - "'en_US' not in result.stdout_lines[-2]" - "'100' in result.stdout_lines[-2]" +- block: + + - name: Check that the DB has all of our options including icu + become_user: "{{ pg_user }}" + become: true + shell: echo "select datname, datconnlimit, pg_encoding_to_char(encoding), datcollate, datctype, daticulocale, CASE datlocprovider WHEN 'i' THEN 'lib_icu' WHEN 'c' THEN 'libc' END AS localeprovider from pg_database where datname = '{{ db_name_icu }}';" | psql -d postgres + register: result + + - assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + - "'LATIN1' in result.stdout_lines[-2]" + - "'pt_BR' in result.stdout_lines[-2]" + - "'es_ES' in result.stdout_lines[-2]" + - "'es_ES-x-icu' in result.stdout_lines[-2]" + - "'lib_icu' in result.stdout_lines[-2]" + - "'UTF8' not in result.stdout_lines[-2]" + - "'en_US' not in result.stdout_lines[-2]" + - "'100' in result.stdout_lines[-2]" + + when: postgres_version_resp.stdout is version('15.0', '>=') + + - name: Check that running db creation with options a second time does nothing become_user: "{{ pg_user }}" become: true @@ -159,6 +211,30 @@ - result is not changed +- block: + + - name: Check that running db creation with icu options a second time does nothing + become_user: "{{ pg_user }}" + become: true + postgresql_db: + name: '{{ db_name_icu }}' + state: 'present' + conn_limit: '100' + encoding: 'LATIN1' + lc_collate: 'pt_BR{{ locale_latin_suffix }}' + lc_ctype: 'es_ES{{ locale_latin_suffix }}' + icu_locale: 'es_ES-x-icu' + locale_provider: 'icu' + template: 'template0' + login_user: "{{ pg_user }}" + register: result + + - assert: + that: + - result is not changed + + when: postgres_version_resp.stdout is version('15.0', '>=') + - name: Check that attempting to change encoding returns an error become_user: "{{ pg_user }}" become: true @@ -177,6 +253,50 @@ that: - result is failed +- block: + + - name: Check that attempting to change icu collate returns an error + become_user: "{{ pg_user }}" + become: true + postgresql_db: + name: '{{ db_name_icu }}' + state: 'present' + encoding: 'LATIN1' + lc_collate: 'pt_BR{{ locale_utf8_suffix }}' + lc_ctype: 'es_ES{{ locale_utf8_suffix }}' + icu_locale: 'en_US-x-icu' + locale_provider: 'icu' + template: 'template0' + login_user: "{{ pg_user }}" + register: result + ignore_errors: true + + - assert: + that: + - result is failed + + - name: Check that attempting to change locale provider returns an error + become_user: "{{ pg_user }}" + become: true + postgresql_db: + name: '{{ db_name_icu }}' + state: 'present' + encoding: 'LATIN1' + lc_collate: 'pt_BR{{ locale_utf8_suffix }}' + lc_ctype: 'es_ES{{ locale_utf8_suffix }}' + icu_locale: 'es_ES-x-icu' + locale_provider: 'libc' + template: 'template0' + login_user: "{{ pg_user }}" + register: result + ignore_errors: true + + - assert: + that: + - result is failed + + when: postgres_version_resp.stdout is version('15.0', '>=') + - name: Check that changing the conn_limit actually works become_user: "{{ pg_user }}" become: true @@ -199,13 +319,15 @@ - name: Check that conn_limit has actually been set / updated to 200 become_user: "{{ pg_user }}" become: true - shell: echo "SELECT datconnlimit AS conn_limit FROM pg_database WHERE datname = '{{ db_name }}';" | psql -d postgres + postgresql_query: + login_db: postgres + query: "SELECT datconnlimit AS conn_limit FROM pg_database WHERE datname = '{{ db_name }}'" register: result - assert: that: - - "result.stdout_lines[-1] == '(1 row)'" - - "'200' == '{{ result.stdout_lines[-2] | trim }}'" + - result.rowcount == 1 + - result.query_result[0]['conn_limit'] == 200 - name: Cleanup test DB become_user: "{{ pg_user }}" @@ -215,14 +337,43 @@ state: 'absent' login_user: "{{ pg_user }}" -- shell: echo "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = '{{ db_name }}';" | psql -d postgres +- name: Check become_user: "{{ pg_user }}" become: true + postgresql_query: + login_db: postgres + query: "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = '{{ db_name }}'" register: result - assert: that: - - "result.stdout_lines[-1] == '(0 rows)'" + - result.rowcount == 0 + +- block: + + - name: Cleanup icu test DB + become_user: "{{ pg_user }}" + become: true + postgresql_db: + name: '{{ db_name_icu }}' + state: 'absent' + login_user: "{{ pg_user }}" + + - name: Check icu test DB was removed + become_user: "{{ pg_user }}" + become: true + postgresql_query: + login_db: postgres + query: "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = '{{ db_name_icu }}'" + register: result + + - assert: + that: + - result.rowcount == 0 + + when: postgres_version_resp.stdout is version('15.0', '>=') + + # # Test db ownership @@ -320,13 +471,15 @@ - name: Check that the user owns the newly created DB become_user: "{{ pg_user }}" become: true - shell: echo "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = '{{ db_name }}';" | psql -d postgres + postgresql_query: + login_db: postgres + query: "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = '{{ db_name }}'" register: result - assert: that: - - "result.stdout_lines[-1] == '(1 row)'" - - "'{{ pg_user }}' == '{{ result.stdout_lines[-2] | trim }}'" + - result.rowcount == 1 + - result.query_result[0]['pg_get_userbyid'] == '{{ pg_user }}' - name: Cleanup db become_user: "{{ pg_user }}" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_initial.yml index 3e3eeda83..8a7afa8ca 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_initial.yml @@ -33,7 +33,7 @@ - assert: that: - result is changed - - result.queries == [] + - result.queries == ['CREATE EXTENSION "postgis"'] - name: postgresql_ext - check that extension doesn't exist after the previous step become_user: '{{ pg_user }}' @@ -55,13 +55,14 @@ login_db: postgres login_port: 5432 name: postgis + comment: Test comment 1 ignore_errors: true register: result - assert: that: - result is changed - - result.queries == ['CREATE EXTENSION "postgis"'] + - result.queries == ['CREATE EXTENSION "postgis"', "COMMENT ON EXTENSION \"postgis\" IS 'Test comment 1'"] - name: postgresql_ext - check that extension exists after the previous step become_user: '{{ pg_user }}' @@ -76,6 +77,127 @@ that: - result.rowcount == 1 +- name: Check the comment + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + query: "SELECT obj_description((SELECT oid FROM pg_catalog.pg_extension WHERE extname = 'postgis'), 'pg_extension') AS comment" + register: result + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Now after the comment was set, invoke again not pass the comment explicitly + become_user: '{{ pg_user }}' + become: true + postgresql_ext: + login_db: postgres + login_port: 5432 + name: postgis + ignore_errors: true + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: Check the comment didn't change + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + query: "SELECT obj_description((SELECT oid FROM pg_catalog.pg_extension WHERE extname = 'postgis'), 'pg_extension') AS comment" + register: result + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Reset the comment in check mode + become_user: '{{ pg_user }}' + become: true + postgresql_ext: + login_db: postgres + login_port: 5432 + name: postgis + comment: '' + ignore_errors: true + register: result + check_mode: true + +- assert: + that: + - result is changed + - result.queries == ["COMMENT ON EXTENSION \"postgis\" IS ''"] + +- name: Check the comment didn't change + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + query: "SELECT obj_description((SELECT oid FROM pg_catalog.pg_extension WHERE extname = 'postgis'), 'pg_extension') AS comment" + register: result + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Reset the comment in real mode + become_user: '{{ pg_user }}' + become: true + postgresql_ext: + login_db: postgres + login_port: 5432 + name: postgis + comment: '' + ignore_errors: true + register: result + +- assert: + that: + - result is changed + - result.queries == ["COMMENT ON EXTENSION \"postgis\" IS ''"] + +- name: Check the comment changed + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + query: "SELECT obj_description((SELECT oid FROM pg_catalog.pg_extension WHERE extname = 'postgis'), 'pg_extension') AS comment" + register: result + +- name: Check the comments match + assert: + that: + - result.query_result[0]['comment'] == None + + +- name: Reset the comment again + become_user: '{{ pg_user }}' + become: true + postgresql_ext: + login_db: postgres + login_port: 5432 + name: postgis + comment: '' + ignore_errors: true + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + + - name: postgresql_ext - drop extension postgis become_user: '{{ pg_user }}' become: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_version_opt.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_version_opt.yml index 2443fe785..ca255a87a 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_version_opt.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ext/tasks/postgresql_ext_version_opt.yml @@ -14,15 +14,19 @@ login_db: postgres block: - # Preparation: + ######## Preparation ######## + - name: postgresql_ext_version - create schema schema1 <<: *task_parameters postgresql_schema: <<: *pg_parameters name: "{{ test_schema }}" - # Do tests: - - name: postgresql_ext_version - create extension of specific version, check mode + ######## Do tests ######## + + #### create extension with specific version #### + + - name: postgresql_ext_version - create extension of specific version in check_mode <<: *task_parameters postgresql_ext: <<: *pg_parameters @@ -35,6 +39,9 @@ - assert: that: - result is changed + - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\" WITH SCHEMA \"{{ test_schema }}\" VERSION '1.0'"] + - result.prev_version == '' + - result.version == '1.0' - name: postgresql_ext_version - check that nothing was actually changed <<: *task_parameters @@ -59,6 +66,8 @@ that: - result is changed - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\" WITH SCHEMA \"{{ test_schema }}\" VERSION '1.0'"] + - result.prev_version == '' + - result.version == '1.0' - name: postgresql_ext_version - check <<: *task_parameters @@ -83,6 +92,9 @@ - assert: that: - result is not changed + - result.queries == [] + - result.prev_version == '1.0' + - result.version == result.prev_version - name: postgresql_ext_version - check <<: *task_parameters @@ -106,6 +118,9 @@ - assert: that: - result is not changed + - result.queries == [] + - result.prev_version == '1.0' + - result.version == result.prev_version - name: postgresql_ext_version - check <<: *task_parameters @@ -117,6 +132,8 @@ that: - result.rowcount == 1 + #### update the extension to the next version #### + - name: postgresql_ext_version - update the extension to the next version in check_mode <<: *task_parameters postgresql_ext: @@ -130,6 +147,9 @@ - assert: that: - result is changed + - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE TO '2.0'"] + - result.prev_version == '1.0' + - result.version == '2.0' - name: postgresql_ext_version - check, the version must be 1.0 <<: *task_parameters @@ -154,6 +174,8 @@ that: - result is changed - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE TO '2.0'"] + - result.prev_version == '1.0' + - result.version == '2.0' - name: postgresql_ext_version - check, the version must be 2.0 <<: *task_parameters @@ -165,6 +187,24 @@ that: - result.rowcount == 1 + #### check no change if extension installed but no version specified #### + + - name: postgresql_ext_version - check that version won't be changed if version won't be passed in check_mode + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + schema: "{{ test_schema }}" + trust_input: false + check_mode: true + + - assert: + that: + - result is not changed + - result.queries == [] + - result.prev_version == '2.0' + - result.version == result.prev_version + - name: postgresql_ext_version - check that version won't be changed if version won't be passed <<: *task_parameters postgresql_ext: @@ -176,6 +216,38 @@ - assert: that: - result is not changed + - result.queries == [] + - result.prev_version == '2.0' + - result.version == result.prev_version + + - name: postgresql_ext_version - check, the version must be 2.0 + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '2.0'" + + - assert: + that: + - result.rowcount == 1 + + #### update the extension to the latest version #### + + - name: postgresql_ext_version - update the extension to the latest version in check_mode + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + schema: "{{ test_schema }}" + version: latest + trust_input: false + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE"] + - result.prev_version == '2.0' + - result.version == '4.0' - name: postgresql_ext_version - check, the version must be 2.0 <<: *task_parameters @@ -200,6 +272,8 @@ that: - result is changed - result.queries == ["ALTER EXTENSION \"{{ test_ext }}\" UPDATE"] + - result.prev_version == '2.0' + - result.version == '4.0' - name: postgresql_ext_version - check <<: *task_parameters @@ -211,7 +285,7 @@ that: - result.rowcount == 1 - - name: postgresql_ext_version - try to update the extension to the latest version again which always runs an update. + - name: postgresql_ext_version - update the extension to the latest version again in check_mode <<: *task_parameters postgresql_ext: <<: *pg_parameters @@ -219,12 +293,16 @@ schema: "{{ test_schema }}" version: latest trust_input: false + check_mode: true - assert: that: - - result is changed + - result is not changed + - result.queries == [] + - result.prev_version == '4.0' + - result.version == result.prev_version - - name: postgresql_ext_version - check that version number did not change even though update ran + - name: postgresql_ext_version - check, the version must be 4.0 (latest) <<: *task_parameters postgresql_query: <<: *pg_parameters @@ -234,6 +312,34 @@ that: - result.rowcount == 1 + - name: postgresql_ext_version - update the extension to the latest version again + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + schema: "{{ test_schema }}" + version: latest + trust_input: false + + - assert: + that: + - result is not changed + - result.queries == [] + - result.prev_version == '4.0' + - result.version == result.prev_version + + - name: postgresql_ext_version - check, the version must be 4.0 (latest) + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT 1 FROM pg_extension WHERE extname = '{{ test_ext }}' AND extversion = '4.0'" + + - assert: + that: + - result.rowcount == 1 + + #### downgrade the extension version #### + - name: postgresql_ext_version - try to downgrade the extension version, must fail <<: *task_parameters postgresql_ext: @@ -248,6 +354,8 @@ that: - result.failed == true + #### drop extension #### + - name: postgresql_ext_version - drop the extension in check_mode <<: *task_parameters postgresql_ext: @@ -260,6 +368,9 @@ - assert: that: - result is changed + - result.queries == ["DROP EXTENSION \"{{ test_ext }}\""] + - result.prev_version == '4.0' + - result.version == '' - name: postgresql_ext_version - check that extension exists <<: *task_parameters @@ -282,6 +393,9 @@ - assert: that: - result is changed + - result.queries == ["DROP EXTENSION \"{{ test_ext }}\""] + - result.prev_version == '4.0' + - result.version == '' - name: postgresql_ext_version - check that extension doesn't exist after the prev step <<: *task_parameters @@ -293,6 +407,22 @@ that: - result.rowcount == 0 + - name: postgresql_ext_version - try to drop the non-existent extension again in check_mode + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + state: absent + trust_input: false + check_mode: true + + - assert: + that: + - result is not changed + - result.queries == [] + - result.prev_version == '' + - result.version == result.prev_version + - name: postgresql_ext_version - try to drop the non-existent extension again <<: *task_parameters postgresql_ext: @@ -304,6 +434,26 @@ - assert: that: - result is not changed + - result.queries == [] + - result.prev_version == '' + - result.version == result.prev_version + + #### create extension without specify version #### + + - name: postgresql_ext_version - create the extension without passing version in check_mode + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + trust_input: false + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\""] + - result.prev_version == '' + - result.version == '4.0' - name: postgresql_ext_version - create the extension without passing version <<: *task_parameters @@ -316,6 +466,8 @@ that: - result is changed - result.queries == ["CREATE EXTENSION \"{{ test_ext }}\""] + - result.prev_version == '' + - result.version == '4.0' - name: postgresql_ext_version - check <<: *task_parameters @@ -327,6 +479,37 @@ that: - result.rowcount == 1 + - name: postgresql_ext_version - create the extension without passing version again in check_mode + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + trust_input: false + check_mode: true + + - assert: + that: + - result is not changed + - result.queries == [] + - result.prev_version == '4.0' + - result.version == result.prev_version + + - name: postgresql_ext_version - create the extension without passing version again + <<: *task_parameters + postgresql_ext: + <<: *pg_parameters + name: "{{ test_ext }}" + trust_input: false + + - assert: + that: + - result is not changed + - result.queries == [] + - result.prev_version == '4.0' + - result.version == result.prev_version + + #### create non existent extension #### + - name: postgresql_ext_version - try to install non-existent extension <<: *task_parameters postgresql_ext: diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/defaults/main.yml index 7a8fe2a37..af2269ffe 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/defaults/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/defaults/main.yml @@ -11,3 +11,8 @@ test_db: acme_db test_subscription: test test_subscription2: test2 conn_timeout: 100 + +primary_port: 5432 + +# The info module tests require a replica db to test subscriptions info +replica_db_required: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/meta/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/meta/main.yml index d72e4d23c..4ce5a5837 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/meta/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/meta/main.yml @@ -1,2 +1,2 @@ dependencies: - - setup_postgresql_replication + - setup_postgresql_db diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/main.yml index 04c7788ad..0e3d5d163 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/main.yml @@ -5,8 +5,10 @@ # For testing getting publication and subscription info - import_tasks: setup_publication.yml - when: ansible_distribution == 'Ubuntu' and ansible_distribution_major_version >= '18' + when: + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support logical replication # Initial CI tests of postgresql_info module - import_tasks: postgresql_info_initial.yml - when: ansible_distribution == 'Ubuntu' and ansible_distribution_major_version >= '18' + when: + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support logical replication diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/postgresql_info_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/postgresql_info_initial.yml index 6dfe50542..be110ff9c 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/postgresql_info_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_info/tasks/postgresql_info_initial.yml @@ -126,16 +126,24 @@ - result.tablespaces - result.roles + - name: Set full server version as X.Y.Z + set_fact: + version_full: '{{ result.version.major }}.{{ result.version.minor }}.{{ result.version.patch }}' + when: result.version.major == 9 + + - name: Set full server version as X.Y + set_fact: + version_full: '{{ result.version.major }}.{{ result.version.minor }}' + when: result.version.major >= 10 + - assert: that: - result.version.patch != {} - - result.version.full == '{{ result.version.major }}.{{ result.version.minor }}.{{ result.version.patch }}' when: result.version.major == 9 - assert: that: - - result.version.full == '{{ result.version.major }}.{{ result.version.minor }}' - when: result.version.major >= 10 + - result.version.full == version_full - name: postgresql_info - check filter param passed by list <<: *task_parameters diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/aliases b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/aliases deleted file mode 100644 index a4c92ef85..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/aliases +++ /dev/null @@ -1,2 +0,0 @@ -destructive -shippable/posix/group1 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/meta/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/meta/main.yml deleted file mode 100644 index 4ce5a5837..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/meta/main.yml +++ /dev/null @@ -1,2 +0,0 @@ -dependencies: - - setup_postgresql_db diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/main.yml deleted file mode 100644 index 799501432..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/main.yml +++ /dev/null @@ -1,25 +0,0 @@ -#################################################################### -# WARNING: These are designed specifically for Ansible tests # -# and should not be used as examples of how to write Ansible roles # -#################################################################### - -- name: Include distribution specific variables - include_vars: "{{ lookup('first_found', params) }}" - vars: - params: - files: - - "{{ ansible_facts.distribution }}-{{ ansible_facts.distribution_major_version }}.yml" - - default.yml - paths: - - vars - -# Only run on CentOS 7 because there is a stack trace on CentOS 8 because the module -# is looking for the incorrect version of plpython. -# https://gist.github.com/samdoran/8fc1b4ae834d3e66d1895d087419b8d8 -- name: Initial CI tests of postgresql_lang module - when: - - ansible_facts.distribution == 'CentOS' - - ansible_facts.distribution_major_version is version ('7', '==') - block: - - include_tasks: postgresql_lang_initial.yml - - include_tasks: postgresql_lang_add_owner_param.yml diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_add_owner_param.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_add_owner_param.yml deleted file mode 100644 index a08ff82f2..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_add_owner_param.yml +++ /dev/null @@ -1,199 +0,0 @@ -# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> -# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) - -- vars: - test_user1: alice - test_user2: bob - test_lang: plperl - non_existent_role: fake_role - task_parameters: &task_parameters - become_user: '{{ pg_user }}' - become: true - register: result - pg_parameters: &pg_parameters - login_user: '{{ pg_user }}' - login_db: postgres - - block: - - name: Create roles for tests - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ item }}' - loop: - - '{{ test_user1 }}' - - '{{ test_user2 }}' - - - name: Create lang with owner in check_mode - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - owner: '{{ test_user1 }}' - trust_input: false - check_mode: true - - - assert: - that: - - result is changed - - result.queries == [] - - - name: Check that nothing was actually changed - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - AND r.rolname = '{{ test_user1 }}' - - - assert: - that: - - result.rowcount == 0 - - - name: Create lang with owner - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - owner: '{{ test_user1 }}' - trust_input: false - - - assert: - that: - - result is changed - - result.queries == ['CREATE LANGUAGE "{{ test_lang }}"', 'ALTER LANGUAGE "{{ test_lang }}" OWNER TO "{{ test_user1 }}"'] - - - name: Check - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - AND r.rolname = '{{ test_user1 }}' - - - assert: - that: - - result.rowcount == 1 - - - name: Change lang owner in check_mode - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - owner: '{{ test_user2 }}' - trust_input: true - check_mode: true - - - assert: - that: - - result is changed - - result.queries == ['ALTER LANGUAGE "{{ test_lang }}" OWNER TO "{{ test_user2 }}"'] - - - name: Check that nothing was actually changed - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - AND r.rolname = '{{ test_user2 }}' - - - assert: - that: - - result.rowcount == 0 - - - name: Change lang owner - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - owner: '{{ test_user2 }}' - - - assert: - that: - - result is changed - # TODO: the first elem of the returned list below - # looks like a bug, not related with the option owner, needs to be checked - - result.queries == ["UPDATE pg_language SET lanpltrusted = false WHERE lanname = '{{ test_lang }}'", 'ALTER LANGUAGE "{{ test_lang }}" OWNER TO "{{ test_user2 }}"'] - - - name: Check - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - AND r.rolname = '{{ test_user2 }}' - - - assert: - that: - - result.rowcount == 1 - - - name: Try to change lang owner again to the same role - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - owner: '{{ test_user2 }}' - - - assert: - that: - - result is not changed - - result.queries == [] - - - name: Check - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - AND r.rolname = '{{ test_user2 }}' - - - assert: - that: - - result.rowcount == 1 - - - name: Drop test lang with owner, must ignore - <<: *task_parameters - postgresql_lang: - <<: *pg_parameters - name: '{{ test_lang }}' - state: absent - owner: '{{ non_existent_role }}' - - - assert: - that: - - result is changed - - result.queries == ["DROP LANGUAGE \"{{ test_lang }}\""] - - - name: Check - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: > - SELECT r.rolname FROM pg_language l - JOIN pg_roles r ON l.lanowner = r.oid - WHERE l.lanname = '{{ test_lang }}' - - - assert: - that: - - result.rowcount == 0 - - # Clean up - - name: Drop test roles - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ item }}' - state: absent - loop: - - '{{ test_user1 }}' - - '{{ test_user2 }}' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_initial.yml deleted file mode 100644 index 1d24778b4..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/tasks/postgresql_lang_initial.yml +++ /dev/null @@ -1,231 +0,0 @@ -# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> -# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) - -# Preparation for tests: -- name: Install PostgreSQL support packages - become: true - action: "{{ ansible_facts.pkg_mgr }}" - args: - name: "{{ postgresql_lang_packages }}" - state: present - -############### -# Do main tests -# - -# Create language in check_mode: -- name: postgresql_lang - create plperl in check_mode - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: plperl - register: result - ignore_errors: true - check_mode: true - -- assert: - that: - - result is changed - - result.queries == [] - -- name: postgresql_lang - check that lang doesn't exist after previous step, rowcount must be 0 - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plperl'" - register: result - -- assert: - that: - - result.rowcount == 0 - -# Create language: -- name: postgresql_lang - create plperl - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: plperl - register: result - ignore_errors: true - -- assert: - that: - - result is changed - - result.queries == ['CREATE LANGUAGE "plperl"'] - -- name: postgresql_lang - check that lang exists after previous step - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plperl'" - register: result - -- assert: - that: - - result.rowcount == 1 - -# Drop language in check_mode: -- name: postgresql_lang - drop plperl in check_mode - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: plperl - state: absent - register: result - ignore_errors: true - check_mode: true - -- assert: - that: - - result is changed - - result.queries == [] - -- name: postgresql_lang - check that lang exists after previous step, rowcount must be 1 - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plperl'" - register: result - -- assert: - that: - - result.rowcount == 1 - -# Drop language: -- name: postgresql_lang - drop plperl - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: plperl - state: absent - register: result - ignore_errors: true - -- assert: - that: - - result is changed - - result.queries == ['DROP LANGUAGE "plperl"'] - -- name: postgresql_lang - check that lang doesn't exist after previous step, rowcount must be 0 - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plperl'" - register: result - -- assert: - that: - - result.rowcount == 0 - -# Check fail_on_drop true -- name: postgresql_lang - drop c language to check fail_on_drop true - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: c - state: absent - fail_on_drop: true - register: result - ignore_errors: true - -- assert: - that: - - result.failed == true - -# Check fail_on_drop no -- name: postgresql_lang - drop c language to check fail_on_drop no - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: c - state: absent - fail_on_drop: false - register: result - ignore_errors: true - -- assert: - that: - - result.failed == false - -# Create trusted language: -- name: postgresql_lang - create plpythonu - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - db: postgres - login_user: "{{ pg_user }}" - name: plpythonu - trust: true - force_trust: true - register: result - ignore_errors: true - -- assert: - that: - - result is changed - - result.queries == ['CREATE TRUSTED LANGUAGE "plpythonu"', "UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'plpythonu'"] - -- name: postgresql_lang - check that lang exists and it's trusted after previous step - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plpythonu' AND lanpltrusted = 't'" - register: result - -- assert: - that: - - result.rowcount == 1 - -# Drop language cascade, tests of aliases: -- name: postgresql_lang - drop plpythonu cascade - become_user: "{{ pg_user }}" - become: true - postgresql_lang: - login_db: postgres - login_user: "{{ pg_user }}" - login_port: 5432 - lang: plpythonu - state: absent - cascade: true - register: result - ignore_errors: true - -- assert: - that: - - result is changed - - result.queries == ['DROP LANGUAGE "plpythonu" CASCADE'] - -- name: postgresql_lang - check that lang doesn't exist after previous step, rowcount must be 0 - become_user: "{{ pg_user }}" - become: true - postgresql_query: - db: postgres - login_user: "{{ pg_user }}" - query: "SELECT 1 FROM pg_language WHERE lanname = 'plpythonu'" - register: result - -- assert: - that: - - result.rowcount == 0 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-7.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-7.yml deleted file mode 100644 index 8d4bcc7e2..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-7.yml +++ /dev/null @@ -1,3 +0,0 @@ -postgresql_lang_packages: - - postgresql-plperl - - postgresql-plpython diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-8.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-8.yml deleted file mode 100644 index 5da004c8f..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/CentOS-8.yml +++ /dev/null @@ -1,3 +0,0 @@ -postgresql_lang_packages: - - postgresql-plperl - - postgresql-plpython3 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/default.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/default.yml deleted file mode 100644 index e69de29bb..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_lang/vars/default.yml +++ /dev/null diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_owner/tasks/postgresql_owner_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_owner/tasks/postgresql_owner_initial.yml index a21160282..a8e1a9173 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_owner/tasks/postgresql_owner_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_owner/tasks/postgresql_owner_initial.yml @@ -2,9 +2,13 @@ # GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) #################### -# Prepare for tests: -# Create test roles: +# +# Prepare for tests +# + +# Create test roles + - name: postgresql_owner - create test roles become_user: '{{ pg_user }}' become: true @@ -17,6 +21,21 @@ - alice - bob +- name: postgresql_owner - create test roles superuser + become_user: '{{ pg_user }}' + become: true + postgresql_user: + login_user: '{{ pg_user }}' + db: postgres + name: '{{ item }}' + role_attr_flags: SUPERUSER + ignore_errors: true + with_items: + - alice_super + - bob_super + +# Create test database + - name: postgresql_owner - create test database become_user: '{{ pg_user }}' become: true @@ -24,13 +43,15 @@ login_user: '{{ pg_user }}' db: acme +# Create test table + - name: postgresql_owner - create test table become_user: '{{ pg_user }}' become: true postgresql_query: login_user: '{{ pg_user }}' db: acme - query: CREATE TABLE my_table (id int) + query: CREATE TABLE test_table (id int) - name: postgresql_owner - set owner become_user: '{{ pg_user }}' @@ -39,9 +60,11 @@ login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: my_table + obj_name: test_table obj_type: table +# Create test sequence + - name: postgresql_owner - create test sequence become_user: '{{ pg_user }}' become: true @@ -50,6 +73,8 @@ db: acme query: CREATE SEQUENCE test_seq +# Create test function + - name: postgresql_owner - create test function become_user: '{{ pg_user }}' become: true @@ -57,9 +82,11 @@ login_user: '{{ pg_user }}' db: acme query: > - CREATE FUNCTION increment(integer) RETURNS integer AS 'select $1 + 1;' + CREATE FUNCTION test_function(integer) RETURNS integer AS 'select $1 + 1;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; +# Create test schema + - name: postgresql_owner - create test schema become_user: '{{ pg_user }}' become: true @@ -68,13 +95,17 @@ db: acme query: CREATE SCHEMA test_schema +# Create test view + - name: postgresql_owner - create test view become_user: '{{ pg_user }}' become: true postgresql_query: login_user: '{{ pg_user }}' db: acme - query: CREATE VIEW test_view AS SELECT * FROM my_table + query: CREATE VIEW test_view AS SELECT * FROM test_table + +# Create test materialized view - name: postgresql_owner - create test materialized view become_user: '{{ pg_user }}' @@ -82,9 +113,11 @@ postgresql_query: login_user: '{{ pg_user }}' db: acme - query: CREATE MATERIALIZED VIEW test_mat_view AS SELECT * FROM my_table + query: CREATE MATERIALIZED VIEW test_mat_view AS SELECT * FROM test_table when: postgres_version_resp.stdout is version('9.4', '>=') +# Create test materialized tablespace + - name: postgresql_owner - drop dir for test tablespace become: true file: @@ -119,12 +152,212 @@ owner: alice location: '{{ test_tablespace_path }}' +# Create test procedure + +- name: postgresql_owner - create test procedure + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE OR REPLACE PROCEDURE test_procedure(id integer) + LANGUAGE SQL + AS $$ + INSERT INTO test_table VALUES (id); + $$ + when: postgres_version_resp.stdout is version('11', '>=') + +# Create test type + +- name: postgresql_owner - create test type + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: CREATE TYPE test_type AS ENUM ('new', 'open', 'closed') + +# Create test aggregate + +- name: postgresql_owner - create test aggregate + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE AGGREGATE test_aggregate (float8) + ( + sfunc = float8_accum, + stype = float8[], + finalfunc = float8_avg, + initcond = '{0,0,0}' + ) + +# Create test routine + +- name: postgresql_owner - create test routine + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE FUNCTION test_routine(integer) RETURNS integer AS 'select $1 + 1;' + LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT + +# No need to create test language as 'plpgsql' is present by default + +# Create test domain + +- name: postgresql_owner - create test domain + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE DOMAIN test_domain AS TEXT + CHECK( + VALUE ~ '^\d{5}$' + OR VALUE ~ '^\d{5}-\d{4}$' + ) + +# Create test collation + +- name: postgresql_owner - create test collation + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE COLLATION test_collation (locale = 'en_US.utf8') + +# No need to create test conversion as 'windows_1256_to_utf8' is present by default + +# No need to create test test search configuration as 'simple' is present by default + +# No need to create test test search dict as 'simple' is present by default + +# Create test foreign data wrapper + +- name: postgresql_owner - create test foreign data wrapper + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE FOREIGN DATA WRAPPER test_foreign_data_wrapper + +# Create test server + +- name: postgresql_owner - create test server + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE SERVER test_server FOREIGN DATA WRAPPER test_foreign_data_wrapper + +# Create test foreign table + +- name: postgresql_owner - create test foreign table + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE FOREIGN TABLE test_foreign_table (id int) SERVER test_server + +# Create test event trigger + +- name: postgresql_owner - create test event trigger function + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE FUNCTION test_event_trigger_function() + RETURNS event_trigger + LANGUAGE plpgsql + AS $$ + BEGIN + PERFORM pg_is_in_recovery(); + END; + $$ + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - create test event trigger + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE EVENT TRIGGER test_event_trigger ON ddl_command_start + EXECUTE FUNCTION test_event_trigger_function() + when: postgres_version_resp.stdout is version('11', '>=') + +# Create test large object + +- name: postgresql_owner - create test large object + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + SELECT lo_creat(-1); + register: result_large_object + +- name: postgresql_owner - parse test large object OID + set_fact: + test_large_object: "{{ result_large_object.query_result[0]['lo_creat'] }}" + +# Create test publication + +- name: postgresql_owner - create test publication + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE PUBLICATION test_publication FOR TABLE test_table + +# Create test statistics + +- name: postgresql_owner - create test statistics table + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE TABLE test_statistics_table (a int, b int) + +- name: postgresql_owner - create test statistics + become_user: '{{ pg_user }}' + become: true + postgresql_query: + login_user: '{{ pg_user }}' + db: acme + query: > + CREATE STATISTICS test_statistics (dependencies) ON a, b FROM test_statistics_table + ################ # Do main tests: # # check reassign_owned_by param # + # try to reassign ownership to non existent user: - name: postgresql_owner - reassign_owned_by to non existent user become_user: '{{ pg_user }}' @@ -179,7 +412,7 @@ db: acme login_user: '{{ pg_user }}' query: > - SELECT 1 FROM pg_tables WHERE tablename = 'my_table' + SELECT 1 FROM pg_tables WHERE tablename = 'test_table' AND tableowner = 'alice' ignore_errors: true register: result @@ -210,7 +443,7 @@ postgresql_query: db: acme login_user: '{{ pg_user }}' - query: SELECT 1 FROM pg_tables WHERE tablename = 'my_table' AND tableowner = 'alice' + query: SELECT 1 FROM pg_tables WHERE tablename = 'test_table' AND tableowner = 'alice' ignore_errors: true register: result @@ -260,7 +493,9 @@ # # ############################# -# check_mode obj_type: database + +# Test obj_type: database + - name: postgresql_owner - set db owner in check_mode become_user: '{{ pg_user }}' become: true @@ -358,6 +593,8 @@ that: - result.rowcount == 1 +# Test obj_type: table + - name: postgresql_owner - set table owner in check_mode become_user: '{{ pg_user }}' become: true @@ -365,7 +602,7 @@ login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: my_table + obj_name: test_table obj_type: table check_mode: true register: result @@ -373,7 +610,7 @@ - assert: that: - result is changed - - result.queries == ['ALTER TABLE "my_table" OWNER TO "bob"'] + - result.queries == ['ALTER TABLE "test_table" OWNER TO "bob"'] - name: postgresql_owner - check that nothing changed after the previous step become_user: '{{ pg_user }}' @@ -382,7 +619,7 @@ db: acme login_user: '{{ pg_user }}' query: > - SELECT 1 FROM pg_tables WHERE tablename = 'my_table' + SELECT 1 FROM pg_tables WHERE tablename = 'test_table' AND tableowner = 'bob' ignore_errors: true register: result @@ -391,21 +628,21 @@ that: - result.rowcount == 0 -- name: postgresql_owner - set db owner +- name: postgresql_owner - set table owner become_user: '{{ pg_user }}' become: true postgresql_owner: login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: my_table + obj_name: test_table obj_type: table register: result - assert: that: - result is changed - - result.queries == ['ALTER TABLE "my_table" OWNER TO "bob"'] + - result.queries == ['ALTER TABLE "test_table" OWNER TO "bob"'] - name: postgresql_owner - check that table owner has been changed after the previous step become_user: '{{ pg_user }}' @@ -414,7 +651,7 @@ db: acme login_user: '{{ pg_user }}' query: > - SELECT 1 FROM pg_tables WHERE tablename = 'my_table' + SELECT 1 FROM pg_tables WHERE tablename = 'test_table' AND tableowner = 'bob' ignore_errors: true register: result @@ -423,14 +660,14 @@ that: - result.rowcount == 1 -- name: postgresql_owner - set db owner again +- name: postgresql_owner - set table owner again become_user: '{{ pg_user }}' become: true postgresql_owner: login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: my_table + obj_name: test_table obj_type: table register: result @@ -446,7 +683,7 @@ db: acme login_user: '{{ pg_user }}' query: > - SELECT 1 FROM pg_tables WHERE tablename = 'my_table' + SELECT 1 FROM pg_tables WHERE tablename = 'test_table' AND tableowner = 'bob' ignore_errors: true register: result @@ -455,6 +692,8 @@ that: - result.rowcount == 1 +# Test obj_type: sequence + - name: postgresql_owner - set sequence owner in check_mode become_user: '{{ pg_user }}' become: true @@ -489,7 +728,7 @@ that: - result.rowcount == 0 -- name: postgresql_owner - set db owner +- name: postgresql_owner - set sequence owner become_user: '{{ pg_user }}' become: true postgresql_owner: @@ -522,7 +761,7 @@ that: - result.rowcount == 1 -- name: postgresql_owner - set db owner again +- name: postgresql_owner - set sequence owner again become_user: '{{ pg_user }}' become: true postgresql_owner: @@ -555,6 +794,8 @@ that: - result.rowcount == 1 +# Test obj_type: function + - name: postgresql_owner - set function owner in check_mode become_user: '{{ pg_user }}' become: true @@ -562,7 +803,7 @@ login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: increment + obj_name: test_function obj_type: function check_mode: true register: result @@ -571,7 +812,7 @@ - assert: that: - result is changed - - result.queries == ['ALTER FUNCTION increment OWNER TO "bob"'] + - result.queries == ['ALTER FUNCTION test_function OWNER TO "bob"'] when: postgres_version_resp.stdout is version('10', '>=') - name: postgresql_owner - check that nothing changed after the previous step @@ -582,7 +823,7 @@ login_user: '{{ pg_user }}' query: > SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r - ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob' + ON f.proowner = r.oid WHERE f.proname = 'test_function' AND r.rolname = 'bob' ignore_errors: true register: result when: postgres_version_resp.stdout is version('10', '>=') @@ -592,14 +833,14 @@ - result.rowcount == 0 when: postgres_version_resp.stdout is version('10', '>=') -- name: postgresql_owner - set func owner +- name: postgresql_owner - set function owner become_user: '{{ pg_user }}' become: true postgresql_owner: login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: increment + obj_name: test_function obj_type: function register: result when: postgres_version_resp.stdout is version('10', '>=') @@ -607,10 +848,10 @@ - assert: that: - result is changed - - result.queries == ['ALTER FUNCTION increment OWNER TO "bob"'] + - result.queries == ['ALTER FUNCTION test_function OWNER TO "bob"'] when: postgres_version_resp.stdout is version('10', '>=') -- name: postgresql_owner - check that func owner has been changed after the previous step +- name: postgresql_owner - check that function owner has been changed after the previous step become_user: '{{ pg_user }}' become: true postgresql_query: @@ -618,7 +859,7 @@ login_user: '{{ pg_user }}' query: > SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r - ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob' + ON f.proowner = r.oid WHERE f.proname = 'test_function' AND r.rolname = 'bob' ignore_errors: true register: result when: postgres_version_resp.stdout is version('10', '>=') @@ -628,14 +869,14 @@ - result.rowcount == 1 when: postgres_version_resp.stdout is version('10', '>=') -- name: postgresql_owner - set func owner again +- name: postgresql_owner - set function owner again become_user: '{{ pg_user }}' become: true postgresql_owner: login_user: '{{ pg_user }}' db: acme new_owner: bob - obj_name: increment + obj_name: test_function obj_type: function register: result when: postgres_version_resp.stdout is version('10', '>=') @@ -654,7 +895,7 @@ login_user: '{{ pg_user }}' query: > SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r - ON f.proowner = r.oid WHERE f.proname = 'increment' AND r.rolname = 'bob' + ON f.proowner = r.oid WHERE f.proname = 'test_function' AND r.rolname = 'bob' ignore_errors: true register: result when: postgres_version_resp.stdout is version('10', '>=') @@ -664,6 +905,8 @@ - result.rowcount == 1 when: postgres_version_resp.stdout is version('10', '>=') +# Test obj_type: schema + - name: postgresql_owner - set schema owner in check_mode become_user: '{{ pg_user }}' become: true @@ -761,6 +1004,8 @@ that: - result.rowcount == 1 +# Test obj_type: view + - name: postgresql_owner - set view owner in check_mode become_user: '{{ pg_user }}' become: true @@ -852,6 +1097,8 @@ that: - result.rowcount == 1 +# Test obj_type: matview + - name: postgresql_owner - set matview owner in check_mode become_user: '{{ pg_user }}' become: true @@ -877,7 +1124,7 @@ postgresql_query: db: acme login_user: '{{ pg_user }}' - query: SELECT 1 FROM pg_matviews WHERE matviewname = 'test_view' AND matviewowner = 'bob' + query: SELECT 1 FROM pg_matviews WHERE matviewname = 'test_mat_view' AND matviewowner = 'bob' ignore_errors: true register: result when: postgres_version_resp.stdout is version('9.4', '>=') @@ -955,6 +1202,8 @@ - result.rowcount == 1 when: postgres_version_resp.stdout is version('9.4', '>=') +# Test obj_type: tablespace + - name: postgresql_owner - set tablespace owner in check_mode become_user: '{{ pg_user }}' become: true @@ -1052,9 +1301,1761 @@ that: - result.rowcount == 1 +# Test obj_type: procedure + +- name: postgresql_owner - set procedure owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_procedure + obj_type: procedure + check_mode: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER PROCEDURE "test_procedure" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_procedure' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 0 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set procedure owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_procedure + obj_type: procedure + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER PROCEDURE "test_procedure" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that procedure owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_procedure' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set procedure owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_procedure + obj_type: procedure + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is not changed + - result.queries == [] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that procedure owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_procedure' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +# Test obj_type: type + +- name: postgresql_owner - set type owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_type + obj_type: type + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TYPE "test_type" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_type' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set type owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_type + obj_type: type + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TYPE "test_type" OWNER TO "bob"'] + +- name: postgresql_owner - check that type owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_type' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set type owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_type + obj_type: type + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that type owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_type' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: aggregate + +- name: postgresql_owner - set aggregate owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_aggregate(float8) + obj_type: aggregate + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER AGGREGATE test_aggregate(float8) OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_aggregate' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set aggregate owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_aggregate(float8) + obj_type: aggregate + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER AGGREGATE test_aggregate(float8) OWNER TO "bob"'] + +- name: postgresql_owner - check that aggregate owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_aggregate' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set aggregate owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_aggregate(float8) + obj_type: aggregate + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that aggregate owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_aggregate' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: routine + +- name: postgresql_owner - set routine owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_routine + obj_type: routine + check_mode: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER ROUTINE "test_routine" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_routine' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 0 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set routine owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_routine + obj_type: routine + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER ROUTINE "test_routine" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that routine owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_routine' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set routine owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_routine + obj_type: routine + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is not changed + - result.queries == [] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that routine owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_proc AS f JOIN pg_roles AS r + ON f.proowner = r.oid WHERE f.proname = 'test_routine' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +# Test obj_type: language + +- name: postgresql_owner - set language owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: plpgsql + obj_type: language + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER LANGUAGE plpgsql OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_language AS l JOIN pg_roles AS r + ON l.lanowner = r.oid WHERE l.lanname = 'plpgsql' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set language owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: plpgsql + obj_type: language + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER LANGUAGE plpgsql OWNER TO "bob"'] + +- name: postgresql_owner - check that language owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_language AS l JOIN pg_roles AS r + ON l.lanowner = r.oid WHERE l.lanname = 'plpgsql' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set language owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: plpgsql + obj_type: language + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that language owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_language AS l JOIN pg_roles AS r + ON l.lanowner = r.oid WHERE l.lanname = 'plpgsql' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: domain + +- name: postgresql_owner - set domain owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_domain + obj_type: domain + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER DOMAIN "test_domain" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_domain' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set domain owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_domain + obj_type: domain + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER DOMAIN "test_domain" OWNER TO "bob"'] + +- name: postgresql_owner - check that domain owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_domain' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set domain owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_domain + obj_type: domain + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that domain owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_type AS t JOIN pg_roles AS r + ON t.typowner = r.oid WHERE t.typname = 'test_domain' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: collation + +- name: postgresql_owner - set collation owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_collation + obj_type: collation + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER COLLATION "test_collation" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_collation AS c JOIN pg_roles AS r + ON c.collowner = r.oid WHERE c.collname = 'test_collation' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set collation owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_collation + obj_type: collation + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER COLLATION "test_collation" OWNER TO "bob"'] + +- name: postgresql_owner - check that collation owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_collation AS c JOIN pg_roles AS r + ON c.collowner = r.oid WHERE c.collname = 'test_collation' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set collation owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_collation + obj_type: collation + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that collation owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_collation AS c JOIN pg_roles AS r + ON c.collowner = r.oid WHERE c.collname = 'test_collation' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: conversion + +- name: postgresql_owner - set conversion owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: windows_1256_to_utf8 + obj_type: conversion + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER CONVERSION "windows_1256_to_utf8" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_conversion AS c JOIN pg_roles AS r + ON c.conowner = r.oid WHERE c.conname = 'windows_1256_to_utf8' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set conversion owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: windows_1256_to_utf8 + obj_type: conversion + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER CONVERSION "windows_1256_to_utf8" OWNER TO "bob"'] + +- name: postgresql_owner - check that conversion owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_conversion AS c JOIN pg_roles AS r + ON c.conowner = r.oid WHERE c.conname = 'windows_1256_to_utf8' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set conversion owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: windows_1256_to_utf8 + obj_type: conversion + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that conversion owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_conversion AS c JOIN pg_roles AS r + ON c.conowner = r.oid WHERE c.conname = 'windows_1256_to_utf8' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: text_search_configuration + +- name: postgresql_owner - set text search configuration owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_configuration + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TEXT SEARCH CONFIGURATION "simple" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_config AS t JOIN pg_roles AS r + ON t.cfgowner = r.oid WHERE t.cfgname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set text search configuration owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_configuration + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TEXT SEARCH CONFIGURATION "simple" OWNER TO "bob"'] + +- name: postgresql_owner - check that text search configuration owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_config AS t JOIN pg_roles AS r + ON t.cfgowner = r.oid WHERE t.cfgname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set text search configuration owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_configuration + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that text search configuration owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_config AS t JOIN pg_roles AS r + ON t.cfgowner = r.oid WHERE t.cfgname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: text_search_dictionary + +- name: postgresql_owner - set text search dict owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_dictionary + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TEXT SEARCH DICTIONARY "simple" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_dict AS t JOIN pg_roles AS r + ON t.dictowner = r.oid WHERE t.dictname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set text search dict owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_dictionary + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER TEXT SEARCH DICTIONARY "simple" OWNER TO "bob"'] + +- name: postgresql_owner - check that text search dict owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_dict AS t JOIN pg_roles AS r + ON t.dictowner = r.oid WHERE t.dictname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set text search dict owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: simple + obj_type: text_search_dictionary + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that text search dict owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_ts_dict AS t JOIN pg_roles AS r + ON t.dictowner = r.oid WHERE t.dictname = 'simple' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: foreign_data_wrapper + +- name: postgresql_owner - set foreign_data_wrapper owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_foreign_data_wrapper + obj_type: foreign_data_wrapper + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER FOREIGN DATA WRAPPER "test_foreign_data_wrapper" OWNER TO "bob_super"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_data_wrapper AS f JOIN pg_roles AS r + ON f.fdwowner = r.oid WHERE f.fdwname = 'test_foreign_data_wrapper' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set foreign_data_wrapper owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_foreign_data_wrapper + obj_type: foreign_data_wrapper + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER FOREIGN DATA WRAPPER "test_foreign_data_wrapper" OWNER TO "bob_super"'] + +- name: postgresql_owner - check that foreign_data_wrapper owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_data_wrapper AS f JOIN pg_roles AS r + ON f.fdwowner = r.oid WHERE f.fdwname = 'test_foreign_data_wrapper' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set foreign_data_wrapper owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_foreign_data_wrapper + obj_type: foreign_data_wrapper + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that foreign_data_wrapper owner is bob_super + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_data_wrapper AS f JOIN pg_roles AS r + ON f.fdwowner = r.oid WHERE f.fdwname = 'test_foreign_data_wrapper' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: server + +- name: postgresql_owner - set server owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_server + obj_type: server + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER SERVER "test_server" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_server AS f JOIN pg_roles AS r + ON f.srvowner = r.oid WHERE f.srvname = 'test_server' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set server owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_server + obj_type: server + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER SERVER "test_server" OWNER TO "bob"'] + +- name: postgresql_owner - check that server owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_server AS f JOIN pg_roles AS r + ON f.srvowner = r.oid WHERE f.srvname = 'test_server' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set server owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_server + obj_type: server + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that server owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_foreign_server AS f JOIN pg_roles AS r + ON f.srvowner = r.oid WHERE f.srvname = 'test_server' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: foreign_table + +- name: postgresql_owner - set foreign_table owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_foreign_table + obj_type: foreign_table + check_mode: true + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER FOREIGN TABLE "test_foreign_table" OWNER TO "bob"'] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_class AS c JOIN pg_roles AS r + ON c.relowner = r.oid WHERE c.relkind = 'f' + AND c.relname = 'test_foreign_table' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set foreign_table owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_foreign_table + obj_type: foreign_table + register: result + +- assert: + that: + - result is changed + - result.queries == ['ALTER FOREIGN TABLE "test_foreign_table" OWNER TO "bob"'] + +- name: postgresql_owner - check that foreign_table owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_class AS c JOIN pg_roles AS r + ON c.relowner = r.oid WHERE c.relkind = 'f' + AND c.relname = 'test_foreign_table' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set foreign_table owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_foreign_table + obj_type: foreign_table + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that foreign_table owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_class AS c JOIN pg_roles AS r + ON c.relowner = r.oid WHERE c.relkind = 'f' + AND c.relname = 'test_foreign_table' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: event_trigger + +- name: postgresql_owner - set event_trigger owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_event_trigger + obj_type: event_trigger + check_mode: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER EVENT TRIGGER "test_event_trigger" OWNER TO "bob_super"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_event_trigger AS e JOIN pg_roles AS r + ON e.evtowner = r.oid WHERE e.evtname = 'test_event_trigger' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 0 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set event_trigger owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_event_trigger + obj_type: event_trigger + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER EVENT TRIGGER "test_event_trigger" OWNER TO "bob_super"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that event_trigger owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_event_trigger AS e JOIN pg_roles AS r + ON e.evtowner = r.oid WHERE e.evtname = 'test_event_trigger' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set event_trigger owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob_super + obj_name: test_event_trigger + obj_type: event_trigger + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is not changed + - result.queries == [] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that event_trigger owner is bob_super + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_event_trigger AS e JOIN pg_roles AS r + ON e.evtowner = r.oid WHERE e.evtname = 'test_event_trigger' AND r.rolname = 'bob_super' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +# Test obj_type: large_object + +- name: postgresql_owner - set large_object owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: '{{ test_large_object }}' + obj_type: large_object + check_mode: true + register: result + +- set_fact: + query: 'ALTER LARGE OBJECT {{ test_large_object }} OWNER TO "bob"' + +- assert: + that: + - result is changed + - result.queries == [query] + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_largeobject_metadata AS l JOIN pg_roles AS r + ON l.lomowner = r.oid WHERE l.oid = '{{ test_large_object }}' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 0 + +- name: postgresql_owner - set large_object owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: '{{ test_large_object }}' + obj_type: large_object + register: result + +- assert: + that: + - result is changed + - result.queries == [query] + +- name: postgresql_owner - check that large_object owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_largeobject_metadata AS l JOIN pg_roles AS r + ON l.lomowner = r.oid WHERE l.oid = '{{ test_large_object }}' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +- name: postgresql_owner - set large_object owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: '{{ test_large_object }}' + obj_type: large_object + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: postgresql_owner - check that large_object owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_largeobject_metadata AS l JOIN pg_roles AS r + ON l.lomowner = r.oid WHERE l.oid = '{{ test_large_object }}' AND r.rolname = 'bob' + ignore_errors: true + register: result + +- assert: + that: + - result.rowcount == 1 + +# Test obj_type: publication + +- name: postgresql_owner - set publication owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_publication + obj_type: publication + check_mode: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER PUBLICATION "test_publication" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_publication AS p JOIN pg_roles AS r + ON p.pubowner = r.oid WHERE p.pubname = 'test_publication' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 0 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set publication owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_publication + obj_type: publication + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER PUBLICATION "test_publication" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that publication owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_publication AS p JOIN pg_roles AS r + ON p.pubowner = r.oid WHERE p.pubname = 'test_publication' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set publication owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_publication + obj_type: publication + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is not changed + - result.queries == [] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that publication owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_publication AS p JOIN pg_roles AS r + ON p.pubowner = r.oid WHERE p.pubname = 'test_publication' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +# Test obj_type: statistics + +- name: postgresql_owner - set statistics owner in check_mode + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_statistics + obj_type: statistics + check_mode: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER STATISTICS "test_statistics" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that nothing changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_statistic_ext AS s JOIN pg_roles AS r + ON s.stxowner = r.oid WHERE s.stxname = 'test_statistics' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 0 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set statistics owner + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_statistics + obj_type: statistics + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is changed + - result.queries == ['ALTER STATISTICS "test_statistics" OWNER TO "bob"'] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that statistics owner has been changed after the previous step + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_statistic_ext AS s JOIN pg_roles AS r + ON s.stxowner = r.oid WHERE s.stxname = 'test_statistics' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - set statistics owner again + become_user: '{{ pg_user }}' + become: true + postgresql_owner: + login_user: '{{ pg_user }}' + db: acme + new_owner: bob + obj_name: test_statistics + obj_type: statistics + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result is not changed + - result.queries == [] + when: postgres_version_resp.stdout is version('11', '>=') + +- name: postgresql_owner - check that statistics owner is bob + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: acme + login_user: '{{ pg_user }}' + query: > + SELECT 1 FROM pg_statistic_ext AS s JOIN pg_roles AS r + ON s.stxowner = r.oid WHERE s.stxname = 'test_statistics' AND r.rolname = 'bob' + ignore_errors: true + register: result + when: postgres_version_resp.stdout is version('11', '>=') + +- assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('11', '>=') + +# ############################# + # -# Crean up +# Clean up # + - name: postgresql_owner - drop test database become_user: '{{ pg_user }}' become: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_pg_hba/tasks/postgresql_pg_hba_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_pg_hba/tasks/postgresql_pg_hba_initial.yml index 2a9505a5b..01d98cd5f 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_pg_hba/tasks/postgresql_pg_hba_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_pg_hba/tasks/postgresql_pg_hba_initial.yml @@ -11,7 +11,6 @@ netmask: 'ffff:fff0::' method: md5 backup: 'True' - order: sud state: "{{item}}" check_mode: true with_items: @@ -30,7 +29,6 @@ dest: /tmp/pg_hba.conf method: "{{item.method|default('md5')}}" netmask: "{{item.netmask|default('')}}" - order: sud source: "{{item.source|default('')}}" state: absent users: "{{item.users|default('all')}}" @@ -51,7 +49,6 @@ dest: /tmp/pg_hba.conf method: "{{item.method|default('md5')}}" netmask: "{{item.netmask|default('')}}" - order: sud source: "{{item.source|default('')}}" state: present users: "{{item.users|default('all')}}" @@ -62,7 +59,6 @@ postgresql_pg_hba: dest: "/tmp/pg_hba.conf" users: "+some" - order: "sud" state: "present" contype: "local" method: "cert" @@ -76,7 +72,6 @@ postgresql_pg_hba: dest: "/tmp/pg_hba.conf" users: "+some" - order: "sud" state: "present" contype: "{{ item.contype }}" method: "{{ item.method }}" @@ -100,7 +95,6 @@ dest: /tmp/pg_hba.conf method: "{{item.method|default('md5')}}" netmask: "{{item.netmask|default('')}}" - order: sud source: "{{item.source|default('')}}" state: present users: "{{item.users|default('all')}}" @@ -119,7 +113,6 @@ dest: /tmp/pg_hba.conf method: md5 netmask: 255.255.255.0 - order: sud source: '172.21.0.0' state: present register: pg_hba_backup_check2 @@ -130,7 +123,6 @@ contype: host dest: /tmp/pg_hba.conf method: md5 - order: sud source: '172.21.0.0/24' state: present register: netmask_sameas_prefix_check @@ -146,7 +138,6 @@ dest: /tmp/pg_hba.conf method: md5 netmask: '255.255.255.255' - order: sud source: all state: present register: pg_hba_fail_src_all_with_netmask @@ -196,7 +187,6 @@ create: true method: md5 address: "2001:db8::1/128" - order: sud state: present comment: "comment1" @@ -220,7 +210,6 @@ dest: /tmp/pg_hba2.conf method: md5 address: "2001:db8::2/128" - order: sud state: present comment: "comment2" @@ -244,7 +233,6 @@ dest: /tmp/pg_hba2.conf method: md5 address: "2001:db8::3/128" - order: sud state: present comment: "comment3" keep_comments_at_rules: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/defaults/main.yml deleted file mode 100644 index 73eb55ae2..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/defaults/main.yml +++ /dev/null @@ -1,2 +0,0 @@ ---- -db_default: postgres diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/handlers/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/handlers/main.yml new file mode 100644 index 000000000..535dd6467 --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/handlers/main.yml @@ -0,0 +1,6 @@ +- name: Drop test user + become: true + become_user: "{{ pg_user }}" + community.postgresql.postgresql_user: + name: "{{ ping_test_user }}" + state: absent diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/main.yml index bcb18d2fe..73f3e189a 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/main.yml @@ -4,6 +4,5 @@ #################################################################### # Initial CI tests of postgresql_ping module -- import_tasks: postgresql_ping_initial.yml - vars: - db_name_nonexist: fake_db +- name: Import the original test task file + ansible.builtin.import_tasks: postgresql_ping_initial.yml diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/postgresql_ping_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/postgresql_ping_initial.yml index 218ae9fd7..be48065df 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/postgresql_ping_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/tasks/postgresql_ping_initial.yml @@ -2,186 +2,198 @@ # Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> # GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) -- name: postgresql_ping - test return values - become_user: "{{ pg_user }}" - become: true - postgresql_ping: +- name: Set parameters we use with most of tasks + ansible.builtin.set_fact: + task_parameters: &task_parameters + become_user: "{{ pg_user }}" + become: true + register: result + + +- name: Test return values + <<: *task_parameters + ignore_errors: true + community.postgresql.postgresql_ping: db: "{{ db_default }}" login_user: "{{ pg_user }}" - register: result - ignore_errors: true -- assert: +- name: Assert return values + ansible.builtin.assert: that: - - result.is_available == true - - result.server_version != {} - - result.server_version.raw is search('PostgreSQL') - - result.server_version.major != '' - - result.server_version.minor != '' - - result is not changed + - result.is_available == true + - result.server_version != {} + - result.server_version.raw is search("PostgreSQL") + - result.server_version.major != "" + - result.server_version.minor != "" + - result is not changed + +- name: Set full server version as X.Y.Z + set_fact: + version_full: '{{ result.server_version.major }}.{{ result.server_version.minor }}.{{ result.server_version.patch }}' + when: result.server_version.major == 9 + +- name: Set full server version as X.Y + set_fact: + version_full: '{{ result.server_version.major }}.{{ result.server_version.minor }}' + when: result.server_version.major >= 10 - assert: that: - result.server_version.patch != {} - - result.server_version.full == '{{ result.server_version.major }}.{{ result.server_version.minor }}.{{ result.server_version.patch }}' when: result.server_version.major == 9 - assert: that: - - result.server_version.full == '{{ result.server_version.major }}.{{ result.server_version.minor }}' - when: result.server_version.major >= 10 + - result.server_version.full == version_full + -- name: postgresql_ping - check ping of non-existing database doesn't return anything - become_user: "{{ pg_user }}" - become: true - postgresql_ping: +- name: Test ping of non-existing database returns nothing + <<: *task_parameters + ignore_errors: true + community.postgresql.postgresql_ping: db: "{{ db_name_nonexist }}" login_user: "{{ pg_user }}" - register: result - ignore_errors: true -- assert: +- name: Assert that ping of non-existing database returns nothing + ansible.builtin.assert: that: - - result.is_available == false - - result.server_version == {} - - result is not changed + - result.is_available == false + - result.server_version == {} + - result is not changed + -- name: postgresql_ping - check ping of the database on non-existent port does not return anything - become_user: "{{ pg_user }}" - become: true +- name: Test ping of the database on non-existent port returns nothing + <<: *task_parameters environment: PGPORT: 5435 ignore_errors: true - postgresql_ping: + community.postgresql.postgresql_ping: db: "{{ db_default }}" login_user: "{{ pg_user }}" - register: result -- assert: +- name: Assert that ping of the database on non-existent port returns nothing + ansible.builtin.assert: that: - - result.is_available == false - - result.server_version == {} - - result is not changed + - result.is_available == false + - result.server_version == {} + - result is not changed + -- name: postgresql_ping - check ping of the database by a non-existent user does not return anything - become_user: "{{ pg_user }}" - become: true +- name: Test ping of the database by a non-existent user returns nothing + <<: *task_parameters environment: - PGUSER: 'test_user' + PGUSER: "none_existent_test_user" ignore_errors: true - postgresql_ping: + community.postgresql.postgresql_ping: db: "{{ db_default }}" - register: result -- assert: +- name: Assert that ping of the database by a non-existent user returns nothing + ansible.builtin.assert: that: - - result.is_available == false - - result.server_version == {} - - result is not changed + - result.is_available == false + - result.server_version == {} + - result is not changed + -- name: Creating a "test_user" in postresql - shell: - cmd: psql -U "{{ pg_user }}" -c "CREATE ROLE test_user WITH LOGIN PASSWORD 'TEST_PASSWORD';" +- name: Create a {{ ping_test_user }} + <<: *task_parameters + notify: Drop test user + community.postgresql.postgresql_user: + name: "{{ ping_test_user }}" + role_attr_flags: LOGIN + password: "{{ ping_test_user_pass }}" -- name: postgresql_ping - check ping of the database by a existent user - become_user: "{{ pg_user }}" - become: true +- name: Test ping of the database by existent user + <<: *task_parameters environment: - PGUSER: 'test_user' + PGUSER: "{{ ping_test_user }}" ignore_errors: true - postgresql_ping: + community.postgresql.postgresql_ping: db: "{{ db_default }}" - login_password: "TEST_PASSWORD" - register: result + login_password: "{{ ping_test_user_pass }}" -- assert: +- name: Assert ping of the database by existent user + ansible.builtin.assert: that: - - result.is_available == true - - result.server_version != {} - - result.server_version.raw is search('PostgreSQL') - - result.server_version.major != '' - - result.server_version.minor != '' - - result is not changed - -- name: postgresql_ping - ping DB with SSL 1 - become_user: "{{ pg_user }}" - become: true - postgresql_ping: - db: "{{ ssl_db }}" - login_user: "{{ ssl_user }}" - login_password: "{{ ssl_pass }}" - login_host: 127.0.0.1 - login_port: 5432 - ssl_mode: require - ca_cert: '{{ ssl_rootcert }}' - trust_input: true - register: result - when: - - ansible_os_family == 'Debian' - - postgres_version_resp.stdout is version('9.4', '>=') - -- assert: - that: - result.is_available == true - - result.conn_err_msg == '' - when: - - ansible_os_family == 'Debian' - - postgres_version_resp.stdout is version('9.4', '>=') - -- name: postgresql_ping - ping DB with SSL 2 - become_user: "{{ pg_user }}" - become: true - postgresql_ping: - db: "{{ ssl_db }}" - login_user: "{{ ssl_user }}" - login_password: "{{ ssl_pass }}" - login_host: 127.0.0.1 - login_port: 5432 - ssl_mode: verify-full - ca_cert: '{{ ssl_rootcert }}' - ssl_cert: '{{ ssl_cert }}' - ssl_key: '{{ ssl_key }}' - trust_input: true - register: result - when: - - ansible_os_family == 'Debian' - - postgres_version_resp.stdout is version('9.4', '>=') + - result.server_version != {} + - result.server_version.raw is search("PostgreSQL") + - result.server_version.major != "" + - result.server_version.minor != "" + - result is not changed -- assert: - that: - - result.is_available == true - - result.conn_err_msg == '' - when: - - ansible_os_family == 'Debian' - - postgres_version_resp.stdout is version('9.4', '>=') -- name: postgresql_ping - check trust_input - become_user: "{{ pg_user }}" - become: true - postgresql_ping: +- name: Test SSL block + when: + - ansible_os_family == "Debian" + - postgres_version_resp.stdout is version("9.4", ">=") + block: + + - name: Test ping DB with SSL 1 + <<: *task_parameters + community.postgresql.postgresql_ping: + db: "{{ ssl_db }}" + login_user: "{{ ssl_user }}" + login_password: "{{ ssl_pass }}" + login_host: 127.0.0.1 + login_port: 5432 + ssl_mode: require + ca_cert: "{{ ssl_rootcert }}" + trust_input: true + + - name: Assert ping DB with SSL 1 + ansible.builtin.assert: + that: + - result.is_available == true + - result.conn_err_msg == "" + + + - name: Test ping DB with SSL 2 + <<: *task_parameters + community.postgresql.postgresql_ping: + db: "{{ ssl_db }}" + login_user: "{{ ssl_user }}" + login_password: "{{ ssl_pass }}" + login_host: 127.0.0.1 + login_port: 5432 + ssl_mode: verify-full + ca_cert: "{{ ssl_rootcert }}" + ssl_cert: "{{ ssl_cert }}" + ssl_key: "{{ ssl_key }}" + trust_input: true + + - name: Assert ping DB with SSL 2 + ansible.builtin.assert: + that: + - result.is_available == true + - result.conn_err_msg == "" + + +- name: Test trust_input is false and input looks suspicious + <<: *task_parameters + ignore_errors: true + community.postgresql.postgresql_ping: db: "{{ db_default }}" login_user: "{{ pg_user }}" trust_input: false session_role: 'curious.anonymous"; SELECT * FROM information_schema.tables; --' - register: result - ignore_errors: true -- assert: +- name: Assert result when trust_input is false and input looks suspicious + ansible.builtin.assert: that: - result is failed - - result.msg is search('is potentially dangerous') + - result.msg is search("is potentially dangerous") + # Check conn_err_msg return value - name: Try to connect to non-existent DB - become_user: "{{ pg_user }}" - become: true - postgresql_ping: - db: blahblah + <<: *task_parameters + community.postgresql.postgresql_ping: + db: "{{ db_name_nonexist }}" login_user: "{{ pg_user }}" - register: result -- name: Check conn_err_msg return value - assert: +- name: Assert connection to non-existent DB + ansible.builtin.assert: that: - result is succeeded - - result.conn_err_msg is search("database \"blahblah\" does not exist") + - result.conn_err_msg is search("database \"{{ db_name_nonexist }}\" does not exist") diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/vars/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/vars/main.yml new file mode 100644 index 000000000..8b7a34379 --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_ping/vars/main.yml @@ -0,0 +1,5 @@ +db_default: postgres +db_name_nonexist: fake_db + +ping_test_user: ping_test_user +ping_test_user_pass: ping_test_user_pass diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_general.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_general.yml index 4b4621010..d83472ba1 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_general.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_general.yml @@ -73,7 +73,7 @@ # Grant rights to the public schema, since in PostgreSQL 15 # the rights to this schema are taken away from all users except the owner -- name: GRANT ALL PRIVILEGES ON SCHEMA public TO ansible_db_user1,2,3 +- name: GRANT ALL PRIVILEGES ON SCHEMA public TO ansible_db_user2,3 community.postgresql.postgresql_privs: db: "{{ db_name }}" privs: ALL @@ -96,14 +96,12 @@ obj: TABLES privs: all state: present - usage_on_types: true register: result check_mode: true - assert: that: - result is changed - - result.queries is search('ON TYPES') # Also covers https://github.com/ansible-collections/community.general/issues/884 - name: Set table default privs on the schema with hyphen in the name @@ -117,13 +115,11 @@ obj: TABLES privs: all state: present - usage_on_types: false register: result - assert: that: - result is changed - - result.queries is not search('ON TYPES') - name: Delete table default privs on the schema with hyphen in the name postgresql_privs: @@ -597,7 +593,7 @@ register: result - assert: - that: "'{{ db_user2 }}=X/{{ db_user3 }}' in '{{ result.stdout_lines[0] }}'" + that: result.stdout_lines[0] is search('{{ db_user2 }}=X/{{ db_user3 }}') # Test - name: Grant execute to all functions again @@ -839,16 +835,16 @@ - result is changed when: postgres_version_resp.stdout is version('11', '>=') -################################################# -# Test ALL_IN_SCHEMA for 'partioned tables type # -################################################# +################################################### +# Test ALL_IN_SCHEMA for 'partitioned tables type # +################################################### # Partitioning tables is a feature introduced in Postgresql 10. # (see https://www.postgresql.org/docs/10/ddl-partitioning.html ) # The test below check for this version # Function ALL_IN_SCHEMA Setup -- name: Create partioned table for test purpose +- name: Create partitioned table for test purpose postgresql_query: query: CREATE TABLE public.testpt (id int not null, logdate date not null) PARTITION BY RANGE (logdate); db: "{{ db_name }}" @@ -958,6 +954,22 @@ - assert: that: "'{{ db_user2 }}=X*/{{ pg_user }}' in result.query_result[0].proacl" +# Test https://github.com/ansible-collections/community.postgresql/issues/668 +- name: Issue 688 + become: true + become_user: "{{ pg_user }}" + register: result + postgresql_privs: + privs: ALL + type: default_privs + schema: pg_catalog + obj: ALL_DEFAULT + db: "{{ db_name }}" + roles: "{{ db_user2 }}" + login_user: "{{ pg_user }}" + grant_option: true + state: present + # Test - name: Revoke grant option on pg_create_restore_point function postgresql_privs: @@ -1190,7 +1202,7 @@ postgresql_query: login_user: "{{ pg_user }}" login_db: "{{ db_name }}" - query: SELECT typacl FROM pg_catalog.pg_type WHERE typname = 'numeric'; + query: SELECT typacl::varchar FROM pg_catalog.pg_type WHERE typname = 'numeric'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1228,7 +1240,7 @@ postgresql_query: login_user: "{{ pg_user }}" login_db: "{{ db_name }}" - query: SELECT typacl FROM pg_catalog.pg_type WHERE typname = 'numeric'; + query: SELECT typacl::varchar FROM pg_catalog.pg_type WHERE typname = 'numeric'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1265,7 +1277,7 @@ postgresql_query: login_user: "{{ pg_user }}" login_db: "{{ db_name }}" - query: SELECT typacl FROM pg_catalog.pg_type WHERE typname = 'numeric'; + query: SELECT typacl::varchar FROM pg_catalog.pg_type WHERE typname = 'numeric'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1303,7 +1315,7 @@ postgresql_query: login_user: "{{ pg_user }}" login_db: "{{ db_name }}" - query: SELECT typacl FROM pg_catalog.pg_type WHERE typname = 'numeric'; + query: SELECT typacl::varchar FROM pg_catalog.pg_type WHERE typname = 'numeric'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1340,7 +1352,7 @@ postgresql_query: login_user: "{{ pg_user }}" login_db: "{{ db_name }}" - query: SELECT typacl FROM pg_catalog.pg_type WHERE typname = 'numeric'; + query: SELECT typacl::varchar FROM pg_catalog.pg_type WHERE typname = 'numeric'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1388,7 +1400,7 @@ login_user: "{{ pg_user }}" login_db: "{{ db_name }}" query: > - SELECT t.typacl FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n + SELECT t.typacl::varchar FROM pg_catalog.pg_type t JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace WHERE t.typname = 'compfoo' AND n.nspname = 'public'; register: typ_result when: postgres_version_resp.stdout is version('10', '>=') @@ -1558,7 +1570,7 @@ register: result - assert: that: - - result is changed + - result is not changed - name: check permissions on tables in schemas with special names become: true become_user: "{{ pg_user }}" @@ -1700,6 +1712,132 @@ - result is failed - result.msg is search('ALL_IN_SCHEMA can be used only for type') +########################################### +# Test for 'parameter' value of type parameter # +########################################### + +- when: postgres_version_resp.stdout is version('15', '>=') + block: + #### GRANT ALTER_SYSTEM/SET - test #### + - name: GRANT ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names,log_destination TO db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: present + privs: ALTER_SYSTEM + type: parameter + objs: primary_conninfo,synchronous_standby_names,log_destination + roles: "{{ db_user3 }}" + register: result + + - assert: + that: + - result is changed + + - name: GRANT SET ON PARAMETER log_destination,log_line_prefix,synchronous_standby_names TO db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: present + privs: SET + type: parameter + objs: log_destination,log_line_prefix,synchronous_standby_names + roles: "{{ db_user3 }}" + + - assert: + that: + - result is changed + + #### GRANT ALTER_SYSTEM/SET - idempotence #### + - name: GRANT ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names,log_destination TO db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: present + privs: ALTER_SYSTEM + type: parameter + objs: primary_conninfo,synchronous_standby_names,log_destination + roles: "{{ db_user3 }}" + register: result + + - assert: + that: + - result is not changed + + - name: GRANT SET ON PARAMETER log_destination,log_line_prefix,synchronous_standby_names TO db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: present + privs: SET + type: parameter + objs: log_destination,log_line_prefix,synchronous_standby_names + roles: "{{ db_user3 }}" + + - assert: + that: + - result is not changed + + #### REVOKE ALTER_SYSTEM/SET - test #### + - name: REVOKE ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names,log_destination FROM db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: absent + privs: ALTER_SYSTEM + type: parameter + objs: primary_conninfo,synchronous_standby_names,log_destination + roles: "{{ db_user3 }}" + register: result + + - assert: + that: + - result is changed + + - name: REVOKE SET ON PARAMETER log_destination,log_line_prefix,synchronous_standby_names FROM db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: absent + privs: SET + type: parameter + objs: log_destination,log_line_prefix,synchronous_standby_names + roles: "{{ db_user3 }}" + + - assert: + that: + - result is changed + + #### REVOKE ALTER_SYSTEM/SET - idempotence #### + - name: REVOKE ALTER SYSTEM ON PARAMETER primary_conninfo,synchronous_standby_names,log_destination FROM db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: absent + privs: ALTER_SYSTEM + type: parameter + objs: primary_conninfo,synchronous_standby_names,log_destination + roles: "{{ db_user3 }}" + register: result + + - assert: + that: + - result is not changed + + - name: REVOKE SET ON PARAMETER log_destination,log_line_prefix,synchronous_standby_names FROM db_user3 + community.postgresql.postgresql_privs: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + state: absent + privs: SET + type: parameter + objs: log_destination,log_line_prefix,synchronous_standby_names + roles: "{{ db_user3 }}" + + - assert: + that: + - result is not changed + # Cleanup - name: Remove privs become: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_initial.yml index 814bc348d..081b13773 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/postgresql_privs_initial.yml @@ -41,35 +41,34 @@ - name: Check that the user has the requested permissions (table1) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select array_agg(privilege_type::TEXT ORDER BY privilege_type ASC) as privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1'" register: result_table1 - name: Check that the user has the requested permissions (table2) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2'" register: result_table2 - name: Check that the user has the requested permissions (database) become_user: "{{ pg_user }}" become: true - shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select datacl from pg_database where datname='{{ db_name }}'" register: result_database - assert: that: - - "result_table1.stdout_lines[-1] == '(7 rows)'" - - "'INSERT' in result_table1.stdout" - - "'SELECT' in result_table1.stdout" - - "'UPDATE' in result_table1.stdout" - - "'DELETE' in result_table1.stdout" - - "'TRUNCATE' in result_table1.stdout" - - "'REFERENCES' in result_table1.stdout" - - "'TRIGGER' in result_table1.stdout" - - "result_table2.stdout_lines[-1] == '(1 row)'" - - "'INSERT' == '{{ result_table2.stdout_lines[-2] | trim }}'" - - "result_database.stdout_lines[-1] == '(1 row)'" - - "'{{ db_user1 }}=CTc/{{ pg_user }}' in result_database.stdout_lines[-2]" + - result_table1.query_result[0]["privilege_type"] == ["DELETE", "INSERT", "REFERENCES", "SELECT", "TRIGGER", "TRUNCATE", "UPDATE"] + - result_table2.rowcount == 1 + - result_table2.query_result[0]['privilege_type'] == 'INSERT' + - result_database.rowcount == 1 + - "'{{ db_user1 }}=CTc/{{ pg_user }}' in result_database.query_result[0]['datacl']" - name: Add another permission for the user become_user: "{{ pg_user }}" @@ -91,14 +90,14 @@ - name: Check that the user has the requested permissions (table2) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select array_agg(privilege_type::TEXT ORDER BY privilege_type ASC) as privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2'" register: result_table2 - assert: that: - - "result_table2.stdout_lines[-1] == '(2 rows)'" - - "'INSERT' in result_table2.stdout" - - "'SELECT' in result_table2.stdout" + - result_table2.query_result[0]['privilege_type'] == ['INSERT', 'SELECT'] # # Test priv setting via postgresql_privs module @@ -127,13 +126,15 @@ - name: Check that the user has the requested permissions (table2) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2'" register: result_table2 - assert: that: - - "result_table2.stdout_lines[-1] == '(1 row)'" - - "'SELECT' == '{{ result_table2.stdout_lines[-2] | trim }}'" + - result_table2.rowcount == 1 + - result_table2.query_result[0]['privilege_type'] == 'SELECT' - name: Revoke many privileges on multiple tables become_user: "{{ pg_user }}" @@ -156,19 +157,23 @@ - name: Check that permissions were revoked (table1) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1'" register: result_table1 - name: Check that permissions were revoked (table2) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2'" register: result_table2 - assert: that: - - "result_table1.stdout_lines[-1] == '(0 rows)'" - - "result_table2.stdout_lines[-1] == '(0 rows)'" + - result_table1.rowcount == 0 + - result_table2.rowcount == 0 - name: Revoke database privileges become_user: "{{ pg_user }}" @@ -186,13 +191,15 @@ - name: Check that the user has the requested permissions (database) become_user: "{{ pg_user }}" become: true - shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select datacl from pg_database where datname='{{ db_name }}'" register: result_database - assert: that: - - "result_database.stdout_lines[-1] == '(1 row)'" - - "'{{ db_user1 }}' not in result_database.stdout" + - result_database.rowcount == 1 + - "'{{ db_user1 }}' not in result_database.query_result[0]['datacl']" - name: Grant database privileges become_user: "{{ pg_user }}" @@ -216,13 +223,15 @@ - name: Check that the user has the requested permissions (database) become_user: "{{ pg_user }}" become: true - shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select datacl from pg_database where datname='{{ db_name }}'" register: result_database - assert: that: - - "result_database.stdout_lines[-1] == '(1 row)'" - - "'{{ db_user1 }}=Cc' in result_database.stdout" + - result_database.rowcount == 1 + - result_database.query_result[0]['datacl'] is search("{{ db_user1 }}=Cc") - name: Grant a single privilege on a table become_user: "{{ pg_user }}" @@ -239,13 +248,15 @@ - name: Check that permissions were added (table1) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1'" register: result_table1 - assert: that: - - "result_table1.stdout_lines[-1] == '(1 row)'" - - "'{{ result_table1.stdout_lines[-2] | trim }}' == 'INSERT'" + - result_table1.rowcount == 1 + - result_table1.query_result[0]['privilege_type'] == 'INSERT' - name: Grant many privileges on multiple tables become_user: "{{ pg_user }}" @@ -262,33 +273,23 @@ - name: Check that permissions were added (table1) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select array_agg(privilege_type::TEXT ORDER BY privilege_type ASC) as privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table1'" register: result_table1 - name: Check that permissions were added (table2) become_user: "{{ pg_user }}" become: true - shell: echo "select privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2';" | psql {{ db_name }} + postgresql_query: + login_db: '{{ db_name }}' + query: "select array_agg(privilege_type::TEXT ORDER BY privilege_type ASC) as privilege_type from information_schema.role_table_grants where grantee='{{ db_user1 }}' and table_name='test_table2'" register: result_table2 - assert: that: - - "result_table1.stdout_lines[-1] == '(7 rows)'" - - "'INSERT' in result_table1.stdout" - - "'SELECT' in result_table1.stdout" - - "'UPDATE' in result_table1.stdout" - - "'DELETE' in result_table1.stdout" - - "'TRUNCATE' in result_table1.stdout" - - "'REFERENCES' in result_table1.stdout" - - "'TRIGGER' in result_table1.stdout" - - "result_table2.stdout_lines[-1] == '(7 rows)'" - - "'INSERT' in result_table2.stdout" - - "'SELECT' in result_table2.stdout" - - "'UPDATE' in result_table2.stdout" - - "'DELETE' in result_table2.stdout" - - "'TRUNCATE' in result_table2.stdout" - - "'REFERENCES' in result_table2.stdout" - - "'TRIGGER' in result_table2.stdout" + - result_table1.query_result[0]["privilege_type"] == ["DELETE", "INSERT", "REFERENCES", "SELECT", "TRIGGER", "TRUNCATE", "UPDATE"] + - result_table2.query_result[0]["privilege_type"] == ["DELETE", "INSERT", "REFERENCES", "SELECT", "TRIGGER", "TRUNCATE", "UPDATE"] # Check passing roles with dots # https://github.com/ansible/ansible/issues/63204 @@ -343,22 +344,144 @@ target_roles: "{{ db_user_with_dots2 }}" trust_input: false -# Bugfix for https://github.com/ansible-collections/community.general/issues/857 -- name: Test passing lowercase PUBLIC role +# https://github.com/ansible-collections/community.postgresql/pull/502 - role PUBLIC +- name: Test passing lowercase PUBLIC role - Grant CREATE ON DATABASE - Test + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: present + type: 'database' + privs: 'create' + role: 'public' + register: result + +- assert: + that: + - result is changed + - result.queries == ["GRANT CREATE ON database \"{{ db_name }}\" TO PUBLIC;"] + +- name: Test passing lowercase PUBLIC role - Grant CREATE ON DATABASE - Idempotence + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: present + type: 'database' + privs: 'create' + role: 'public' + register: result + +- assert: + that: + - result is not changed + - result.queries == ["GRANT CREATE ON database \"{{ db_name }}\" TO PUBLIC;"] + +- name: Test passing lowercase PUBLIC role - Revoke CREATE ON DATABASE - Test become_user: "{{ pg_user }}" become: true postgresql_privs: db: "{{ db_name }}" login_user: "{{ pg_user }}" + state: absent type: 'database' - privs: 'connect' + privs: 'create' role: 'public' register: result - assert: that: - result is changed - - result.queries == ["GRANT CONNECT ON database \"{{ db_name }}\" TO PUBLIC;"] + - result.queries == ["REVOKE CREATE ON database \"{{ db_name }}\" FROM PUBLIC;"] + +- name: Test passing lowercase PUBLIC role - Revoke CREATE ON DATABASE - Test + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: absent + type: 'database' + privs: 'create' + role: 'public' + register: result + +- assert: + that: + - result is not changed + - result.queries == ["REVOKE CREATE ON database \"{{ db_name }}\" FROM PUBLIC;"] + +# https://github.com/ansible-collections/community.postgresql/pull/502 - role SESSION_USER +# first revoke after grant, as the privilege is already granted +- name: Test passing lowercase SESSION_USER role - Revoke CREATE ON DATABASE - Test + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: absent + type: 'database' + privs: 'create' + role: 'session_user' + register: result + +- assert: + that: + - result is changed + - result.queries == ["REVOKE CREATE ON database \"{{ db_name }}\" FROM SESSION_USER;"] + +- name: Test passing lowercase SESSION_USER role - Revoke CREATE ON DATABASE - Test + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: absent + type: 'database' + privs: 'create' + role: 'session_user' + register: result + +- assert: + that: + - result is not changed + - result.queries == ["REVOKE CREATE ON database \"{{ db_name }}\" FROM SESSION_USER;"] + +- name: Test passing lowercase SESSION_USER role - Grant CREATE ON DATABASE - Test + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: present + type: 'database' + privs: 'create' + role: 'session_user' + register: result + +- assert: + that: + - result is changed + - result.queries == ["GRANT CREATE ON database \"{{ db_name }}\" TO SESSION_USER;"] + +- name: Test passing lowercase SESSION_USER role - Grant CREATE ON DATABASE - Idempotence + become_user: "{{ pg_user }}" + become: true + postgresql_privs: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + state: present + type: 'database' + privs: 'create' + role: 'session_user' + register: result + +- assert: + that: + - result is not changed + - result.queries == ["GRANT CREATE ON database \"{{ db_name }}\" TO SESSION_USER;"] # # Cleanup diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/test_target_role.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/test_target_role.yml index 42ece0bad..bf8c76744 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/test_target_role.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_privs/tasks/test_target_role.yml @@ -55,7 +55,7 @@ register: result - assert: - that: "'{{ db_user2 }}=r/{{ db_user1 }}' in '{{ result.stdout_lines[0] }}'" + that: result.stdout_lines[0] is search('{{ db_user2 }}=r/{{ db_user1 }}') # Test - name: Revoke default privileges for new table objects diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_publication/tasks/postgresql_publication_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_publication/tasks/postgresql_publication_initial.yml index 584a4848b..ac7563c2d 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_publication/tasks/postgresql_publication_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_publication/tasks/postgresql_publication_initial.yml @@ -90,20 +90,21 @@ postgresql_publication: <<: *pg_parameters name: '{{ test_pub }}' + comment: 'Made by Ansible' trust_input: false - assert: that: - result is changed - result.exists == true - - result.queries == ["CREATE PUBLICATION \"{{ test_pub }}\" FOR ALL TABLES"] + - result.queries == ["CREATE PUBLICATION \"{{ test_pub }}\" FOR ALL TABLES", "COMMENT ON PUBLICATION \"{{ test_pub }}\" IS 'Made by Ansible'"] - result.owner == '{{ pg_user }}' - result.alltables == true - result.tables == [] - result.parameters.publish != {} # Check - - name: postgresql_publication - check that nothing has been changed + - name: postgresql_publication - check the publication was created <<: *task_parameters postgresql_query: <<: *pg_parameters @@ -115,6 +116,85 @@ that: - result.rowcount == 1 + - name: Check the comment + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT obj_description(p.oid, 'pg_publication') AS comment FROM pg_publication AS p WHERE p.pubname = '{{ test_pub }}'" + + - assert: + that: + - result.query_result[0]['comment'] == 'Made by Ansible' + + - name: Not specify the comment explicitly + <<: *task_parameters + postgresql_publication: + <<: *pg_parameters + name: '{{ test_pub }}' + + - assert: + that: + - result is not changed + - result.queries == [] + + - name: Change the comment in check mode + <<: *task_parameters + postgresql_publication: + <<: *pg_parameters + name: '{{ test_pub }}' + comment: 'Made by me' + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON PUBLICATION \"{{ test_pub }}\" IS 'Made by me'"] + + - name: Check the comment didn't change + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT obj_description(p.oid, 'pg_publication') AS comment FROM pg_publication AS p WHERE p.pubname = '{{ test_pub }}'" + + - assert: + that: + - result.query_result[0]['comment'] == 'Made by Ansible' + + - name: Reset the comment in real mode + <<: *task_parameters + postgresql_publication: + <<: *pg_parameters + name: '{{ test_pub }}' + comment: '' + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON PUBLICATION \"{{ test_pub }}\" IS ''"] + + - name: Check the comment was reset + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT obj_description(p.oid, 'pg_publication') AS comment FROM pg_publication AS p WHERE p.pubname = '{{ test_pub }}'" + + - assert: + that: + - result.query_result[0]['comment'] == None + + - name: Reset the comment again in check mode + <<: *task_parameters + postgresql_publication: + <<: *pg_parameters + name: '{{ test_pub }}' + comment: '' + check_mode: true + + - assert: + that: + - result is not changed + - result.queries == [] + # Test - name: postgresql_publication - drop publication, check_mode <<: *task_parameters diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test0.sql b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test0.sql deleted file mode 100644 index e8a5ca03d..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test0.sql +++ /dev/null @@ -1,6 +0,0 @@ -SELECT version(); - -SELECT story FROM test_table - WHERE id = %s OR story = 'Данные'; - - diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test1.sql b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test1.sql deleted file mode 100644 index 028c192d7..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/files/test1.sql +++ /dev/null @@ -1,10 +0,0 @@ -CREATE FUNCTION add(integer, integer) RETURNS integer - AS 'select $1 + $2;' - LANGUAGE SQL - IMMUTABLE - RETURNS NULL ON NULL INPUT; - -SELECT story FROM test_table - WHERE id = %s OR story = 'Данные'; - -SELECT version(); diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/tasks/postgresql_query_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/tasks/postgresql_query_initial.yml index 5d447d608..b784955a4 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/tasks/postgresql_query_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_query/tasks/postgresql_query_initial.yml @@ -25,19 +25,6 @@ shell: psql postgres -U "{{ pg_user }}" -t -c "INSERT INTO test_table (id, story) VALUES (1, 'first'), (2, 'second'), (3, 'third');" ignore_errors: true - - name: Copy script files - become: true - copy: - src: '{{ item }}' - dest: '~{{ pg_user }}/{{ item }}' - owner: '{{ pg_user }}' - force: true - loop: - - test0.sql - - test1.sql - register: sql_file_created - ignore_errors: true - - name: postgresql_query - analyze test_table become_user: '{{ pg_user }}' become: true @@ -57,29 +44,6 @@ - result.query_result == {} - result.query_all_results == [{}] - - name: postgresql_query - run queries from SQL script - become_user: '{{ pg_user }}' - become: true - postgresql_query: - <<: *pg_parameters - path_to_script: ~{{ pg_user }}/test0.sql - positional_args: - - 1 - encoding: UTF-8 - as_single_query: false - register: result - ignore_errors: true - when: sql_file_created - - - assert: - that: - - result is not changed - - result.query == "\n\nSELECT story FROM test_table\n WHERE id = 1 OR story = 'Данные'" - - result.query_result[0].story == 'first' - - result.rowcount == 2 - - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' - when: sql_file_created - - name: postgresql_query - simple select query to test_table become_user: '{{ pg_user }}' become: true @@ -270,6 +234,37 @@ - result.rowcount == 0 - result.statusmessage == 'ALTER TABLE' + - name: postgresql_query - alter test_table using encoding + become_user: '{{ pg_user }}' + become: true + postgresql_query: + <<: *pg_parameters + query: ALTER TABLE test_table ADD COLUMN foo2 int + encoding: 'UTF-8' + register: result + ignore_errors: true + + - assert: + that: + - result is changed + - result.query == "ALTER TABLE test_table ADD COLUMN foo2 int" + - result.rowcount == 0 + - result.statusmessage == 'ALTER TABLE' + + - name: postgresql_query - alter test_table using bad encoding + become_user: '{{ pg_user }}' + become: true + postgresql_query: + <<: *pg_parameters + query: ALTER TABLE test_table ADD COLUMN foo888 int + encoding: 'UTF-888-bad' + register: result + ignore_errors: true + + - assert: + that: + - result.failed == true + - name: postgresql_query - vacuum without autocommit must fail become_user: '{{ pg_user }}' become: true @@ -502,28 +497,6 @@ that: - result is failed - # Tests for the as_single_query option - - name: Run queries from SQL script as a single query - become_user: '{{ pg_user }}' - become: true - postgresql_query: - <<: *pg_parameters - path_to_script: ~{{ pg_user }}/test1.sql - positional_args: - - 1 - encoding: UTF-8 - as_single_query: true - register: result - - - name: > - Must pass. Not changed because we can only - check statusmessage of the last query - assert: - that: - - result is not changed - - result.statusmessage == 'SELECT 1' or result.statusmessage == 'SELECT' - - result.query_list[0] == "CREATE FUNCTION add(integer, integer) RETURNS integer\n AS 'select $1 + $2;'\n LANGUAGE SQL\n IMMUTABLE\n RETURNS NULL ON NULL INPUT;\n\nSELECT story FROM test_table\n WHERE id = %s OR story = 'Данные';\n\nSELECT version();\n" - ############################################################################# # Issue https://github.com/ansible-collections/community.postgresql/issues/45 - name: Create table containing a decimal value @@ -602,3 +575,15 @@ - result.rowcount == 3 - result.query_result == [{"?column?": 1}] - 'result.query_all_results == [[{"?column?": 1}], [{"?column?": 1}], [{"?column?": 1}]]' + + - name: Run SHOW query + become_user: '{{ pg_user }}' + become: true + postgresql_query: + <<: *pg_parameters + query: "SHOW hba_file" + register: result + + - assert: + that: + - result is not changed
\ No newline at end of file diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_schema/tasks/postgresql_schema_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_schema/tasks/postgresql_schema_initial.yml index 58832f049..625fb45e2 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_schema/tasks/postgresql_schema_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_schema/tasks/postgresql_schema_initial.yml @@ -314,6 +314,205 @@ - result.rowcount == 0 +# Test the comment argument +- name: Create schema with comment + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: Test schema 1 + register: result + +- name: Check return values + assert: + that: + - result is changed + - result.queries == ['CREATE SCHEMA "comment_schema"', "COMMENT ON SCHEMA \"comment_schema\" IS 'Test schema 1'"] + +- name: Check the comment + become: true + become_user: "{{ pg_user }}" + postgresql_query: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + query: "SELECT obj_description((SELECT oid FROM pg_namespace WHERE nspname = 'comment_schema'), 'pg_namespace') AS comment" + register: result + +- assert: + that: + - result.rowcount == 1 + - result.query_result[0]['comment'] == 'Test schema 1' + + +- name: Set the same comment in check mode + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: Test schema 1 + register: result + check_mode: true + +- name: Check return values + assert: + that: + - result is not changed + + +- name: Set another comment in check mode + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: Test schema 2 + register: result + check_mode: true + +- name: Check return values + assert: + that: + - result is changed + +- name: Check the comment didn't change + become: true + become_user: "{{ pg_user }}" + postgresql_query: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + query: "SELECT obj_description((SELECT oid FROM pg_namespace WHERE nspname = 'comment_schema'), 'pg_namespace') AS comment" + register: result + +- assert: + that: + - result.rowcount == 1 + - result.query_result[0]['comment'] == 'Test schema 1' + + +- name: Set another comment in real mode + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: Test schema 2 + register: result + +- name: Check return values + assert: + that: + - result is changed + - result.queries == ["COMMENT ON SCHEMA \"comment_schema\" IS 'Test schema 2'"] + +- name: Check the comment changed + become: true + become_user: "{{ pg_user }}" + postgresql_query: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + query: "SELECT obj_description((SELECT oid FROM pg_namespace WHERE nspname = 'comment_schema'), 'pg_namespace') AS comment" + register: result + +- assert: + that: + - result.rowcount == 1 + - result.query_result[0]['comment'] == 'Test schema 2' + + +- name: Don's specify the comment explicitly + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + register: result + +- name: Check return values + assert: + that: + - result is not changed + - result.queries == [] + +- name: Check the comment didn't change + become: true + become_user: "{{ pg_user }}" + postgresql_query: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + query: "SELECT obj_description((SELECT oid FROM pg_namespace WHERE nspname = 'comment_schema'), 'pg_namespace') AS comment" + register: result + +- assert: + that: + - result.rowcount == 1 + - result.query_result[0]['comment'] == 'Test schema 2' + + +- name: Reset the comment + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: '' + register: result + +- name: Check return values + assert: + that: + - result is changed + - result.queries == ["COMMENT ON SCHEMA \"comment_schema\" IS ''"] + +- name: Check the comment is None + become: true + become_user: "{{ pg_user }}" + postgresql_query: + db: "{{ db_name }}" + login_user: "{{ pg_user }}" + query: "SELECT obj_description((SELECT oid FROM pg_namespace WHERE nspname = 'comment_schema'), 'pg_namespace') AS comment" + register: result + +- assert: + that: + - result.rowcount == 1 + - result.query_result[0]['comment'] == None + + +- name: Reset the comment again + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + comment: '' + register: result + +- name: Check return values + assert: + that: + - result is not changed + - result.queries == [] + + +- name: Drop schema + become_user: "{{ pg_user }}" + become: true + postgresql_schema: + login_user: "{{ pg_user }}" + database: "{{ db_name }}" + name: comment_schema + state: absent + + # Cleanup - name: Remove user postgresql_user: diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_set/tasks/options_coverage.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_set/tasks/options_coverage.yml index c4598d2a9..19622d8af 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_set/tasks/options_coverage.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_set/tasks/options_coverage.yml @@ -28,8 +28,19 @@ wal_level: replica log_statement: mod track_functions: none - shared_preload_libraries: 'pg_stat_statements, pgaudit' + shared_preload_libraries: 'pg_stat_statements, pgcrypto' log_line_prefix: 'db=%d,user=%u,app=%a,client=%h ' + unix_socket_directories: '/var/run/postgresql, /var/run/postgresql2' + + - name: Ensure all unix_socket_directories directories exist + file: + state: directory + path: "{{ item }}" + owner: "{{ pg_user }}" + group: "{{ pg_user }}" + mode: '0777' + become: true + with_list: "{{ setting_map['unix_socket_directories'].split(',') | map('trim') | list }}" # Check mode: - name: Set settings in check mode @@ -51,16 +62,33 @@ with_dict: '{{ setting_map }}' # https://github.com/ansible-collections/community.postgresql/issues/78 - - name: Test param with comma containing values + - name: Test param with comma containing values but no quotes <<: *task_parameters shell: "grep shared_preload_libraries {{ pg_auto_conf }}" register: result - assert: that: - - result.stdout == "shared_preload_libraries = 'pg_stat_statements, pgaudit'" - - # Test for single-value params with commas and spaces in value + - result.stdout == "shared_preload_libraries = 'pg_stat_statements, pgcrypto'" + + # https://github.com/ansible-collections/community.postgresql/pull/521 + # unix_socket_directories is a GUC_LIST_QUOTE parameter only from PostgreSQL 14 + - name: Test param with comma containing values and quotes + <<: *task_parameters + shell: "grep unix_socket_directories {{ pg_auto_conf }}" + register: result + + - assert: + that: + - result.stdout == "unix_socket_directories = '/var/run/postgresql, /var/run/postgresql2'" + when: postgres_version_resp.stdout is version('14', '<') + + - assert: + that: + - result.stdout == "unix_socket_directories = '\"/var/run/postgresql\", \"/var/run/postgresql2\"'" + when: postgres_version_resp.stdout is version('14', '>=') + + # https://github.com/ansible-collections/community.postgresql/pull/400 - name: Test single-value param with commas and spaces in value <<: *task_parameters shell: "grep log_line_prefix {{ pg_auto_conf }}" @@ -69,3 +97,25 @@ - assert: that: - result.stdout == "log_line_prefix = 'db=%d,user=%u,app=%a,client=%h '" + + # Restart PostgreSQL: + - name: Restart PostgreSQL + become: true + service: + name: "{{ postgresql_service }}" + state: restarted + + # Idempotence: + - name: Set settings in actual mode again after restart for idempotence + <<: *task_parameters + postgresql_set: + <<: *pg_parameters + name: '{{ item.key }}' + value: '{{ item.value }}' + register: test_idempotence + with_dict: '{{ setting_map }}' + + - name: Check idempotence after restart + assert: + that: not item.changed + with_items: '{{ test_idempotence.results }}' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/defaults/main.yml index 8709694ba..ba4513248 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/defaults/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/defaults/main.yml @@ -11,3 +11,7 @@ test_subscription: test test_role1: alice test_role2: bob conn_timeout: 100 + +primary_port: 5432 + +replica_db_required: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/meta/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/meta/main.yml index d72e4d23c..4ce5a5837 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/meta/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/meta/main.yml @@ -1,2 +1,2 @@ dependencies: - - setup_postgresql_replication + - setup_postgresql_db diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/main.yml index e440e8c80..e9cfb4172 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/main.yml @@ -6,7 +6,9 @@ # Initial tests of postgresql_subscription module: - import_tasks: setup_publication.yml - when: ansible_distribution == 'Ubuntu' and ansible_distribution_major_version >= '18' + when: + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support logical replication - import_tasks: postgresql_subscription_initial.yml - when: ansible_distribution == 'Ubuntu' and ansible_distribution_major_version >= '18' + when: + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support logical replication diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/postgresql_subscription_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/postgresql_subscription_initial.yml index b464c3dbe..d2997b480 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/postgresql_subscription_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_subscription/tasks/postgresql_subscription_initial.yml @@ -35,6 +35,7 @@ name: '{{ test_subscription }}' state: present publications: '{{ test_pub }}' + comment: Made by Ansible connparams: host: 127.0.0.1 port: '{{ primary_port }}' @@ -47,7 +48,7 @@ that: - result is changed - result.name == '{{ test_subscription }}' - - result.queries == ["CREATE SUBSCRIPTION test CONNECTION 'host=127.0.0.1 port={{ primary_port }} user={{ replication_role }} password={{ replication_pass }} dbname={{ test_db }}' PUBLICATION {{ test_pub }}"] + - result.queries == ["CREATE SUBSCRIPTION test CONNECTION 'host=127.0.0.1 port={{ primary_port }} user={{ replication_role }} password={{ replication_pass }} dbname={{ test_db }}' PUBLICATION {{ test_pub }}", "COMMENT ON SUBSCRIPTION \"test\" IS 'Made by Ansible'"] - result.exists == true - result.initial_state == {} - result.final_state.owner == '{{ pg_user }}' @@ -72,6 +73,116 @@ that: - result.rowcount == 1 + - name: Check the comment + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + login_port: '{{ replica_port }}' + query: "SELECT obj_description(s.oid, 'pg_subscription') AS comment FROM pg_subscription AS s WHERE s.subname = 'test'" + + - assert: + that: + - result.query_result[0]['comment'] == 'Made by Ansible' + + - name: Not specify the comment explicitly + <<: *task_parameters + postgresql_subscription: + <<: *pg_parameters + login_port: '{{ replica_port }}' + name: '{{ test_subscription }}' + + - assert: + that: + - result is not changed + - result.queries == [] + + - name: Check the comment is the same + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + login_port: '{{ replica_port }}' + query: "SELECT obj_description(s.oid, 'pg_subscription') AS comment FROM pg_subscription AS s WHERE s.subname = 'test'" + + - assert: + that: + - result.query_result[0]['comment'] == 'Made by Ansible' + + - name: Reset the comment in check mode + <<: *task_parameters + postgresql_subscription: + <<: *pg_parameters + login_port: '{{ replica_port }}' + name: '{{ test_subscription }}' + comment: '' + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON SUBSCRIPTION \"test\" IS ''"] + + - name: Check the comment is the same + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + login_port: '{{ replica_port }}' + query: "SELECT obj_description(s.oid, 'pg_subscription') AS comment FROM pg_subscription AS s WHERE s.subname = 'test'" + + - assert: + that: + - result.query_result[0]['comment'] == 'Made by Ansible' + + - name: Reset the comment in real mode + <<: *task_parameters + postgresql_subscription: + <<: *pg_parameters + login_port: '{{ replica_port }}' + name: '{{ test_subscription }}' + comment: '' + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON SUBSCRIPTION \"test\" IS ''"] + + - name: Check the comment was reset + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + login_port: '{{ replica_port }}' + query: "SELECT obj_description(s.oid, 'pg_subscription') AS comment FROM pg_subscription AS s WHERE s.subname = 'test'" + + - assert: + that: + - result.query_result[0]['comment'] == None + + - name: Reset the comment again + <<: *task_parameters + postgresql_subscription: + <<: *pg_parameters + login_port: '{{ replica_port }}' + name: '{{ test_subscription }}' + comment: '' + + - assert: + that: + - result is not changed + - result.queries == [] + + - name: Reset the comment again in check mode + <<: *task_parameters + postgresql_subscription: + <<: *pg_parameters + login_port: '{{ replica_port }}' + name: '{{ test_subscription }}' + comment: '' + check_mode: true + + - assert: + that: + - result is not changed + - result.queries == [] + ################### # Test mode: absent ################### diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_tablespace/tasks/postgresql_tablespace_initial.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_tablespace/tasks/postgresql_tablespace_initial.yml index b8e6d0b54..a89509af4 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_tablespace/tasks/postgresql_tablespace_initial.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_tablespace/tasks/postgresql_tablespace_initial.yml @@ -237,9 +237,205 @@ name: foo state: absent register: result - ignore_errors: true - assert: that: - result is not changed - - result.msg == "Tries to drop nonexistent tablespace 'foo'" + - result.state == 'absent' + - result.queries == [] + + +# Testing comment argument +- name: Create tablespace with comment + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: Test comment 1 + register: result + +- assert: + that: + - result is changed + - result.queries == ["CREATE TABLESPACE \"acme\" LOCATION '/ssd'", "COMMENT ON TABLESPACE \"acme\" IS 'Test comment 1'"] + +- name: Check comment + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + query: "SELECT shobj_description((SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname = 'acme'), 'pg_tablespace') AS comment" + register: result + +- assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Try to create tablespace with same comment + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: Test comment 1 + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + + +- name: Now try not to pass the comment explicitly + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + +- name: Check comment didn't change + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + query: "SELECT shobj_description((SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname = 'acme'), 'pg_tablespace') AS comment" + register: result + +- assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Set another comment in check mode + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: Test comment 2 + register: result + check_mode: true + +- assert: + that: + - result is changed + - result.queries == ["COMMENT ON TABLESPACE \"acme\" IS 'Test comment 2'"] + +- name: Check the comment didn't change + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + query: "SELECT shobj_description((SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname = 'acme'), 'pg_tablespace') AS comment" + register: result + +- assert: + that: + - result.query_result[0]['comment'] == "Test comment 1" + + +- name: Set another comment in real mode + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: Test comment 2 + register: result + +- assert: + that: + - result is changed + - result.queries == ["COMMENT ON TABLESPACE \"acme\" IS 'Test comment 2'"] + +- name: Check the comment changed + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + query: "SELECT shobj_description((SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname = 'acme'), 'pg_tablespace') AS comment" + register: result + +- assert: + that: + - result.query_result[0]['comment'] == "Test comment 2" + + +- name: Reset the comment + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: '' + register: result + +- assert: + that: + - result is changed + - result.queries == ["COMMENT ON TABLESPACE \"acme\" IS ''"] + +- name: Check the comment changed + become_user: '{{ pg_user }}' + become: true + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + query: "SELECT shobj_description((SELECT oid FROM pg_catalog.pg_tablespace WHERE spcname = 'acme'), 'pg_tablespace') AS comment" + register: result + +- assert: + that: + - result.query_result[0]['comment'] == None + + +- name: Reset the comment again + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + location: /ssd + comment: '' + register: result + +- assert: + that: + - result is not changed + - result.queries == [] + + +# Clean up +- name: Drop tablespace + become_user: '{{ pg_user }}' + become: true + postgresql_tablespace: + db: postgres + login_user: '{{ pg_user }}' + name: acme + state: absent diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/defaults/main.yml index dbcbea120..5da901258 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/defaults/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/defaults/main.yml @@ -2,3 +2,6 @@ db_name: 'ansible_db' db_user1: 'ansible_db_user1' db_user2: 'ansible_db_user2' dangerous_name: 'curious.anonymous"; SELECT * FROM information_schema.tables; --' + +# The user module tests require an sql_ascii encoded db to test client decoding +sql_ascii_db_required: true diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/main.yml index 150d26efd..8d0c8b489 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/main.yml @@ -10,3 +10,7 @@ # General tests: - import_tasks: postgresql_user_general.yml when: postgres_version_resp.stdout is version('9.4', '>=') + +# SQL_ASCII database tests: +- import_tasks: postgresql_user_sql_ascii_db.yml + when: postgres_version_resp.stdout is version('9.4', '>=') diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_general.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_general.yml index cde95b0c6..73db14149 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_general.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_general.yml @@ -5,8 +5,6 @@ - vars: test_user: hello.user.with.dots test_user2: hello - test_group1: group1 - test_group2: group2 test_table: test test_comment1: 'comment1' test_comment2: 'comment2' @@ -66,6 +64,33 @@ that: - result.rowcount == 1 +# Check comment argument: + - name: Add a comment on the user in check mode + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '{{ test_comment1 }}' + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON ROLE \"{{ test_user }}\" IS '{{ test_comment1 }}'"] + + - name: check the comment didn't change + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT pg_catalog.shobj_description(r.oid, 'pg_authid') AS comment + FROM pg_catalog.pg_roles r WHERE r.rolname = '{{ test_user }}' + + - assert: + that: + - result.rowcount == 1 + - result.query_result[0].comment == None + - name: Add a comment on the user <<: *task_parameters postgresql_user: @@ -103,7 +128,33 @@ that: - result is not changed - - name: Try to add another comment on the user + - name: Try to add another comment on the user in check mode + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '{{ test_comment2 }}' + check_mode: true + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON ROLE \"{{ test_user }}\" IS '{{ test_comment2 }}'"] + + - name: check the comment didn't change + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT pg_catalog.shobj_description(r.oid, 'pg_authid') AS comment + FROM pg_catalog.pg_roles r WHERE r.rolname = '{{ test_user }}' + + - assert: + that: + - result.rowcount == 1 + - result.query_result[0].comment == '{{ test_comment1 }}' + + - name: Try to add another comment on the user in real mode <<: *task_parameters postgresql_user: <<: *pg_parameters @@ -128,6 +179,44 @@ - result.rowcount == 1 - result.query_result[0].comment == '{{ test_comment2 }}' + - name: Reset the comment + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '' + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON ROLE \"{{ test_user }}\" IS ''"] + + - name: check the comment + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT pg_catalog.shobj_description(r.oid, 'pg_authid') AS comment + FROM pg_catalog.pg_roles r WHERE r.rolname = '{{ test_user }}' + + - assert: + that: + - result.rowcount == 1 + - result.query_result[0].comment == None + + - name: Reset the comment again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '' + + - assert: + that: + - result is not changed + - result.queries == [] +# End comment argument testing + - name: Try to create role again in check_mode <<: *task_parameters check_mode: true @@ -596,134 +685,6 @@ that: - result is not changed - # - # Test groups parameter - # - - name: Create test group - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ test_group2 }}' - role_attr_flags: NOLOGIN - - - name: Create role test_group1 and grant test_group2 to test_group1 in check_mode - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ test_group1 }}' - groups: '{{ test_group2 }}' - role_attr_flags: NOLOGIN - check_mode: true - - - assert: - that: - - result is changed - - result.user == '{{ test_group1 }}' - - result.queries == ['CREATE USER "{{ test_group1 }}" NOLOGIN', 'GRANT "{{ test_group2 }}" TO "{{ test_group1 }}"'] - - - name: check that the user doesn't exist - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_group1 }}'" - - - assert: - that: - - result.rowcount == 0 - - - name: check membership - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" - - - assert: - that: - - result.rowcount == 0 - - - name: Create role test_group1 and grant test_group2 to test_group1 - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ test_group1 }}' - groups: '{{ test_group2 }}' - role_attr_flags: NOLOGIN - trust_input: false - - - assert: - that: - - result is changed - - result.user == '{{ test_group1 }}' - - result.queries == ['CREATE USER "{{ test_group1 }}" NOLOGIN', 'GRANT "{{ test_group2 }}" TO "{{ test_group1 }}"'] - - - name: check that the user exists - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_group1 }}'" - - - assert: - that: - - result.rowcount == 1 - - - name: check membership - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" - - - assert: - that: - - result.rowcount == 1 - - - name: Grant test_group2 to test_group1 again - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ test_group1 }}' - groups: '{{ test_group2 }}' - - - assert: - that: - - result is not changed - - result.user == '{{ test_group1 }}' - - - name: check membership - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT grolist FROM pg_group WHERE groname = '{{ test_group2 }}' AND grolist != '{}'" - - - assert: - that: - - result.rowcount == 1 - - - name: Grant groups to existent role - <<: *task_parameters - postgresql_user: - <<: *pg_parameters - name: '{{ test_user }}' - groups: - - '{{ test_group1 }}' - - '{{ test_group2 }}' - trust_input: false - - - assert: - that: - - result is changed - - result.user == '{{ test_user }}' - - result.queries == ['GRANT "{{ test_group1 }}" TO "{{ test_user }}"', 'GRANT "{{ test_group2 }}" TO "{{ test_user }}"'] - - - name: check membership - <<: *task_parameters - postgresql_query: - <<: *pg_parameters - query: "SELECT * FROM pg_group WHERE groname in ('{{ test_group1 }}', '{{ test_group2 }}') AND grolist != '{}'" - - - assert: - that: - - result.rowcount == 2 - ######################## # Test trust_input param @@ -777,6 +738,35 @@ that: - result is not changed +##### Test error handling when the database is read-only + + - name: Set database as read-only + <<: *task_parameters + postgresql_set: + <<: *pg_parameters + name: 'default_transaction_read_only' + value: 'on' + + - name: Try to alter role in read-only database + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + role_attr_flags: 'CREATEDB' + register: result + ignore_errors: true + + - assert: + that: + - result.msg == 'ERROR: cannot execute ALTER ROLE in a read-only transaction\n' + + - name: Set database as read-write + <<: *task_parameters + postgresql_set: + <<: *pg_parameters + name: 'default_transaction_read_only' + value: 'off' + always: # # Clean up @@ -797,6 +787,4 @@ loop: - '{{ test_user }}' - '{{ test_user2 }}' - - '{{ test_group1 }}' - - '{{ test_group2 }}' - '{{ dangerous_name }}' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_sql_ascii_db.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_sql_ascii_db.yml new file mode 100644 index 000000000..4f322f152 --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user/tasks/postgresql_user_sql_ascii_db.yml @@ -0,0 +1,8 @@ +- name: Execute module with no changes + become_user: '{{ pg_user }}' + become: true + postgresql_user: + name: '{{ sql_ascii_user }}' + db: '{{ sql_ascii_db }}' + role_attr_flags: SUPERUSER + password: '{{ sql_ascii_pass }}' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user_obj_stat_info/tasks/postgresql_user_obj_stat_info.yml b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user_obj_stat_info/tasks/postgresql_user_obj_stat_info.yml index 62f72d9ec..48b6208e0 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user_obj_stat_info/tasks/postgresql_user_obj_stat_info.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/postgresql_user_obj_stat_info/tasks/postgresql_user_obj_stat_info.yml @@ -75,7 +75,7 @@ state: stopped when: (ansible_facts.distribution_major_version != '8' and ansible_facts.distribution == 'CentOS') or ansible_facts.distribution != 'CentOS' - - name: Pause between stop and start PosgreSQL + - name: Pause between stop and start PostgreSQL pause: seconds: 5 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/defaults/main.yml index 973d41591..823b6561a 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/defaults/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/defaults/main.yml @@ -4,9 +4,16 @@ postgresql_packages: - postgresql-server - python-psycopg2 +pip_packages: [] + pg_user: postgres pg_group: root +pg_dir: "/var/lib/pgsql/data" +pg_hba_location: "{{ pg_dir }}/pg_hba.conf" +pg_conf_location: "{{ pg_dir }}/postgresql.conf" +pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" + locale_latin_suffix: locale_utf8_suffix: @@ -18,4 +25,13 @@ ssl_user: 'ssl_user' ssl_pass: 'ssl_pass' ssl_rootcert: '/etc/server-ca.crt' ssl_cert: '/etc/client.crt' -ssl_key: '/etc/client.key'
\ No newline at end of file +ssl_key: '/etc/client.key' + +# second database, for logical replication testing +replica_data_dir: "/var/lib/pgsql_replica" +replica_port: 5533 + +# defaults for test sql_ascii database +sql_ascii_db: 'sql_ascii' +sql_ascii_user: 'sql_ascii_user' +sql_ascii_pass: 'sql_ascii_pass' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/main.yml index 80bb3c4d4..062fadef7 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/main.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/main.yml @@ -66,20 +66,15 @@ loop_var: loop_item # -# Install PostgreSQL 15 on Ubuntu 20.04 -- name: Install PostgreSQL 15 on Ubuntu 20.04 +# Prepare Ubuntu for PostgreSQL install +- name: Prepare Ubuntu for PostgreSQL when: - ansible_facts.distribution == 'Ubuntu' - - ansible_facts.distribution_major_version == '20' block: - name: Run autoremove become: true apt: autoremove: true - - - name: Install wget - package: - name: wget - name: Create the file repository configuration lineinfile: @@ -108,7 +103,7 @@ ## # -- name: install dependencies for postgresql test +- name: Install required OS packages package: name: '{{ postgresql_package_item }}' state: present @@ -116,6 +111,13 @@ loop_control: loop_var: postgresql_package_item +- name: Install required Pip packages + pip: + name: "{{ pip_package_item }}" + with_items: "{{ pip_packages }}" + loop_control: + loop_var: pip_package_item + - name: Initialize postgres (RedHat systemd) command: postgresql-setup initdb when: ansible_os_family == "RedHat" and ansible_service_mgr == "systemd" @@ -125,7 +127,7 @@ when: ansible_os_family == "RedHat" and ansible_service_mgr != "systemd" - name: Initialize postgres (Debian) - shell: . /usr/share/postgresql-common/maintscripts-functions && set_system_locale && /usr/bin/pg_createcluster -u postgres {{ pg_ver }} main + shell: . /usr/share/postgresql-common/maintscripts-functions && set_system_locale && /usr/bin/pg_createcluster -u postgres {{ pg_ver }} main args: creates: /etc/postgresql/{{ pg_ver }}/ when: ansible_os_family == 'Debian' @@ -208,7 +210,15 @@ - name: Stop postgresql service service: name={{ postgresql_service }} state=stopped - when: terminate is not succeeded + +- name: Configure postgresql.conf + ansible.builtin.lineinfile: + path: '{{ pg_conf_location }}' + regexp: '^wal_level ' + line: 'wal_level = logical' + when: + - replica_db_required is defined and replica_db_required + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support 'logical' - name: Pause between stop and start pause: @@ -277,3 +287,14 @@ when: - ansible_os_family == 'Debian' - postgres_version_resp.stdout is version('9.4', '>=') + +# Create a second database +- import_tasks: replica.yml + when: + - replica_db_required is defined and replica_db_required + - ansible_distribution_major_version != "7" # CentOS 7 with Postgres 9.2 doesn't support 'logical' + +# Create an SQL_ASCII encoded database +- import_tasks: sql_ascii.yml + when: + - sql_ascii_db_required is defined and sql_ascii_db_required diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/replica.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/replica.yml new file mode 100644 index 000000000..0570db9bb --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/replica.yml @@ -0,0 +1,59 @@ +- name: Replica - find pg_ctl + shell: find /usr/lib /usr/bin -type f -name "pg_ctl" + register: result + +- name: Replica - set path to pg_ctl + set_fact: + pg_ctl: '{{ result.stdout }}' + +- name: Replica - stop database + become: true + become_user: '{{ pg_user }}' + shell: '{{ pg_ctl }} -D {{ replica_data_dir }} stop' + ignore_errors: true + +- name: Replica - remove old db + file: + path: '{{ replica_data_dir }}' + state: absent + ignore_errors: true + +- name: Replica - create data dir + file: + state: directory + recurse: true + path: "{{ replica_data_dir }}" + owner: "{{ pg_user }}" + mode: "0700" + +- name: Replica - find initdb + shell: find /usr/lib /usr/bin -type f -name "initdb" + register: result + +- name: Replica - set path to initdb + set_fact: + initdb: '{{ result.stdout }}' + +- name: Replica - initialize database + become: true + become_user: '{{ pg_user }}' + shell: '{{ initdb }} --pgdata {{ replica_data_dir }}' + +- name: Replica - configure postgresql.conf + ansible.builtin.lineinfile: + path: '{{ replica_data_dir }}/postgresql.conf' + regexp: '^wal_level ' + line: 'wal_level = logical' + +- name: Replica - start database + become: true + become_user: '{{ pg_user }}' + shell: '{{ pg_ctl }} -D {{ replica_data_dir }} -o "-p {{ replica_port }}" -l {{ replica_data_dir }}/replica.log start' + +- name: Replica - check connectivity + become: true + become_user: '{{ pg_user }}' + postgresql_ping: + db: '{{ pg_user }}' + login_user: '{{ pg_user }}' + login_port: '{{ replica_port }}' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/sql_ascii.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/sql_ascii.yml new file mode 100644 index 000000000..d6641412a --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/tasks/sql_ascii.yml @@ -0,0 +1,36 @@ +- name: postgresql SQL_ASCII - create database + become_user: '{{ pg_user }}' + become: true + postgresql_db: + name: '{{ sql_ascii_db }}' + encoding: 'SQL_ASCII' + template: 'template0' + +- name: postgresql SQL_ASCII - ensure db exists with proper encoding + become_user: '{{ pg_user }}' + become: true + shell: "psql -c 'SHOW SERVER_ENCODING' --tuples-only --no-align --dbname {{ sql_ascii_db }}" + register: sql_ascii_db_encoding + +- ansible.builtin.assert: + that: + - sql_ascii_db_encoding.stdout == 'SQL_ASCII' + +- name: postgresql SQL_ASCII - create role + become_user: '{{ pg_user }}' + become: true + postgresql_user: + name: '{{ sql_ascii_user }}' + db: '{{ sql_ascii_db }}' + role_attr_flags: SUPERUSER + password: '{{ sql_ascii_pass }}' + +- name: postgresql SQL_ASCII - ensure role was created + become: true + become_user: "{{ pg_user }}" + shell: "psql -c \"select * from pg_authid where rolname='{{ sql_ascii_user }}';\" -d {{ sql_ascii_db }}" + register: result + +- ansible.builtin.assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Debian-8.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Debian-8.yml index 932738d39..8e16579ad 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Debian-8.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Debian-8.yml @@ -3,7 +3,6 @@ postgresql_packages: - "postgresql-common" - "python-psycopg2" -pg_hba_location: "/etc/postgresql/9.4/main/pg_hba.conf" -pg_dir: "/var/lib/postgresql/9.4/main" -pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" pg_ver: 9.4 +pg_hba_location: "/etc/postgresql/{{ pg_ver }}/main/pg_hba.conf" +pg_dir: "/var/lib/postgresql/{{ pg_ver }}/main" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-36-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-36-py3.yml new file mode 100644 index 000000000..e67a4ac4f --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-36-py3.yml @@ -0,0 +1,7 @@ +# We install both psycopg2 and psycopg3. +# As psycopg3 is only 3.0, the modules should use psycopg2. +postgresql_packages: + - "postgresql-contrib" + - "postgresql-server" + - "python3-psycopg2" + - "python3-psycopg3" # psycopg 3.0.16 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-38-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-38-py3.yml new file mode 100644 index 000000000..e233746ed --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Fedora-38-py3.yml @@ -0,0 +1,5 @@ +postgresql_packages: + - "postgresql-contrib" + - "postgresql-server" + - "python3-psycopg2" + - "python3-psycopg3" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat-py3.yml index 72041a3d7..08d2adb8a 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat-py3.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat-py3.yml @@ -1,9 +1,6 @@ postgresql_packages: - "postgresql-server" + - "postgresql-contrib" - "python3-psycopg2" - "bzip2" - "xz" - -pg_hba_location: "/var/lib/pgsql/data/pg_hba.conf" -pg_dir: "/var/lib/pgsql/data" -pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat.yml index 30720f8fe..2d82eaefd 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/RedHat.yml @@ -1,8 +1,5 @@ postgresql_packages: - "postgresql-server" + - "postgresql-contrib" - "python-psycopg2" - "bzip2" - -pg_hba_location: "/var/lib/pgsql/data/pg_hba.conf" -pg_dir: "/var/lib/pgsql/data" -pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-20-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-20-py3.yml index ff543a385..15c8b4c2b 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-20-py3.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-20-py3.yml @@ -1,13 +1,15 @@ +pg_ver: 15 + postgresql_packages: - "apt-utils" - - "postgresql" + - "postgresql-{{ pg_ver }}" - "postgresql-common" - "python3-psycopg2" - "postgresql-client" -pg_hba_location: "/etc/postgresql/15/main/pg_hba.conf" -pg_dir: "/var/lib/postgresql/15/main" +pg_conf_location: "/etc/postgresql/{{ pg_ver }}/main/postgresql.conf" +pg_hba_location: "/etc/postgresql/{{ pg_ver }}/main/pg_hba.conf" +pg_dir: "/var/lib/postgresql/{{ pg_ver }}/main" pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" -pg_ver: 15 -postgis: postgresql-15-postgis-3 +postgis: "postgresql-{{ pg_ver }}-postgis-3" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-22-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-22-py3.yml new file mode 100644 index 000000000..334ad27aa --- /dev/null +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/Ubuntu-22-py3.yml @@ -0,0 +1,18 @@ +pg_ver: 15 + +postgresql_packages: + - "apt-utils" + - "postgresql-{{ pg_ver }}" + - "postgresql-common" + # - "python3-psycopg2" + - "postgresql-client" + +pip_packages: + - "psycopg==3.1.9" # We need at least 3.1 for Client-side-binding cursors + +pg_conf_location: "/etc/postgresql/{{ pg_ver }}/main/postgresql.conf" +pg_hba_location: "/etc/postgresql/{{ pg_ver }}/main/pg_hba.conf" +pg_dir: "/var/lib/postgresql/{{ pg_ver }}/main" +pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" + +postgis: postgresql-{{ pg_ver }}-postgis-3 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default-py3.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default-py3.yml index 3ff3e0de5..c5fa981e7 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default-py3.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default-py3.yml @@ -1,7 +1,3 @@ postgresql_packages: - "postgresql-server" - "python3-psycopg2" - -pg_hba_location: "/var/lib/pgsql/data/pg_hba.conf" -pg_dir: "/var/lib/pgsql/data" -pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default.yml index 71f1cd46e..af7dfe475 100644 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default.yml +++ b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_db/vars/default.yml @@ -1,7 +1,3 @@ postgresql_packages: - "postgresql-server" - "python-psycopg2" - -pg_hba_location: "/var/lib/pgsql/data/pg_hba.conf" -pg_dir: "/var/lib/pgsql/data" -pg_auto_conf: "{{ pg_dir }}/postgresql.auto.conf" diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/defaults/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/defaults/main.yml deleted file mode 100644 index 5ac314c4b..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/defaults/main.yml +++ /dev/null @@ -1,26 +0,0 @@ -# General: -pg_user: postgres -db_default: postgres - -pg_package_list: -- postgresql -- postgresql-client -- python3-psycopg2 - -packages_to_remove: -- postgresql -- postgresql-client - -# Master specific defaults: -primary_root_dir: '/var/lib/pgsql/primary' -primary_data_dir: '{{ primary_root_dir }}/data' -primary_postgresql_conf: '{{ primary_data_dir }}/postgresql.conf' -primary_pg_hba_conf: '{{ primary_data_dir }}/pg_hba.conf' -primary_port: 5431 - -# Replica specific defaults: -replica_root_dir: '/var/lib/pgsql/replica' -replica_data_dir: '{{ replica_root_dir }}/data' -replica_postgresql_conf: '{{ replica_data_dir }}/postgresql.conf' -replica_pg_hba_conf: '{{ replica_data_dir }}/pg_hba.conf' -replica_port: 5434 diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/handlers/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/handlers/main.yml deleted file mode 100644 index ea230c778..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/handlers/main.yml +++ /dev/null @@ -1,24 +0,0 @@ -- name: Stop services - become: true - become_user: '{{ pg_user }}' - shell: '{{ pg_ctl }} -D {{ item.datadir }} -o "-p {{ item.port }}" -m immediate stop' - loop: - - { datadir: '{{ primary_data_dir }}', port: '{{ primary_port }}' } - - { datadir: '{{ replica_data_dir }}', port: '{{ replica_port }}' } - listen: stop postgresql - -- name: Remove packages - apt: - name: '{{ packages_to_remove }}' - state: absent - listen: cleanup postgresql - -- name: Remove FS objects - file: - state: absent - path: "{{ item }}" - force: true - loop: - - "{{ primary_root_dir }}" - - "{{ replica_root_dir }}" - listen: cleanup postgresql diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/main.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/main.yml deleted file mode 100644 index 4c6421a18..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/main.yml +++ /dev/null @@ -1,13 +0,0 @@ -#################################################################### -# WARNING: These are designed specifically for Ansible tests # -# and should not be used as examples of how to write Ansible roles # -#################################################################### - -# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> -# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) - -# Setup PostgreSQL primary-standby replication into one container: -- import_tasks: setup_postgresql_cluster.yml - when: - - ansible_distribution == 'Ubuntu' - - ansible_distribution_major_version >= '18' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/setup_postgresql_cluster.yml b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/setup_postgresql_cluster.yml deleted file mode 100644 index 2bff42e78..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/tasks/setup_postgresql_cluster.yml +++ /dev/null @@ -1,149 +0,0 @@ -- name: Remove preinstalled packages - apt: - name: '{{ packages_to_remove }}' - state: absent - become: true - -- name: Run autoremove - become: true - apt: - autoremove: true - -- name: Configure Ubuntu 20 for PostgreSQL - when: - - ansible_facts['distribution'] == 'Ubuntu' - - ansible_facts['distribution_major_version'] is version('20', 'ge') - block: - - name: Install wget - package: - name: wget - - - name: Add PGDG repository - lineinfile: - create: true - line: "deb http://apt.postgresql.org/pub/repos/apt {{ ansible_facts['distribution_release'] }}-pgdg main" - path: '/etc/apt/sources.list.d/pgdg.list' - state: 'present' - - - name: Add PGDG GPG key - ansible.builtin.apt_key: - state: present - url: https://www.postgresql.org/media/keys/ACCC4CF8.asc - - - name: Update apt cache - apt: - update_cache: true - -- name: Install apt-utils - apt: - name: apt-utils - -- name: Install packages - apt: - name: '{{ pg_package_list }}' - policy_rc_d: 101 # prevent the service from starting - notify: cleanup postgresql - -- name: Delete postgresql related files - file: - state: absent - path: '{{ item }}' - force: true - loop: - - '{{ primary_root_dir }}' - - '{{ replica_root_dir }}' - - /etc/postgresql - - /var/lib/postgresql - -- name: Create dirs needed - file: - state: directory - recurse: true - path: '{{ item }}' - owner: postgres - group: postgres - mode: '0700' - loop: - - '{{ primary_data_dir }}' - - '{{ replica_data_dir }}' - - /var/lib/postgresql - notify: cleanup postgresql - -- name: Find initdb - shell: find /usr/lib -type f -name "initdb" - register: result - -- name: Set path to initdb - set_fact: - initdb: '{{ result.stdout }}' - -- name: Initialize databases - become: true - become_user: '{{ pg_user }}' - shell: '{{ initdb }} --pgdata {{ item }}' - loop: - - '{{ primary_data_dir }}' - - '{{ replica_data_dir }}' - -- name: Copy config templates - template: - src: '{{ item.conf_templ }}' - dest: '{{ item.conf_dest }}' - owner: postgres - group: postgres - force: true - loop: - - conf_templ: primary_postgresql.conf.j2 - conf_dest: '{{ primary_postgresql_conf }}' - - conf_templ: replica_postgresql.conf.j2 - conf_dest: '{{ replica_postgresql_conf }}' - - conf_templ: pg_hba.conf.j2 - conf_dest: '{{ primary_pg_hba_conf }}' - - conf_templ: pg_hba.conf.j2 - conf_dest: '{{ replica_pg_hba_conf }}' - -- name: Find pg_ctl - shell: find /usr/lib -type f -name "pg_ctl" - register: result - -- name: Set path to initdb - set_fact: - pg_ctl: '{{ result.stdout }}' - -- name: Start primary - become: true - become_user: '{{ pg_user }}' - shell: '{{ pg_ctl }} -D {{ primary_data_dir }} -o "-p {{ primary_port }}" -l {{ primary_data_dir }}/primary.log start' - notify: - - stop postgresql - -- name: Start replica - become: true - become_user: '{{ pg_user }}' - shell: '{{ pg_ctl }} -D {{ replica_data_dir }} -o "-p {{ replica_port }}" -l {{ replica_data_dir }}/replica.log start' - -- name: Check connectivity to the primary and get PostgreSQL version - become: true - become_user: '{{ pg_user }}' - postgresql_ping: - db: '{{ db_default }}' - login_user: '{{ pg_user }}' - login_port: '{{ primary_port }}' - register: result - -- name: Check connectivity to the replica and get PostgreSQL version - become: true - become_user: '{{ pg_user }}' - postgresql_ping: - db: '{{ db_default }}' - login_user: '{{ pg_user }}' - login_port: '{{ replica_port }}' - -- name: Define server version - set_fact: - pg_major_version: '{{ result.server_version.major }}' - pg_minor_version: '{{ result.server_version.minor }}' - -- name: Print PostgreSQL version - debug: - msg: PostgreSQL version is {{ pg_major_version }}.{{ pg_minor_version }} diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/pg_hba.conf.j2 b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/pg_hba.conf.j2 deleted file mode 100644 index 62e05ffc8..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/pg_hba.conf.j2 +++ /dev/null @@ -1,7 +0,0 @@ -local all all trust -local replication logical_replication trust -host replication logical_replication 127.0.0.1/32 trust -host replication logical_replication 0.0.0.0/0 trust -local all logical_replication trust -host all logical_replication 127.0.0.1/32 trust -host all logical_replication 0.0.0.0/0 trust diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/primary_postgresql.conf.j2 b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/primary_postgresql.conf.j2 deleted file mode 100644 index 545769f35..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/primary_postgresql.conf.j2 +++ /dev/null @@ -1,28 +0,0 @@ -# Important parameters: -listen_addresses='*' -port = {{ primary_port }} -wal_level = logical -max_wal_senders = 8 -track_commit_timestamp = on -max_replication_slots = 10 - -# Unimportant parameters: -max_connections=10 -shared_buffers=8MB -dynamic_shared_memory_type=posix -log_destination='stderr' -logging_collector=on -log_directory='log' -log_filename='postgresql-%a.log' -log_truncate_on_rotation=on -log_rotation_age=1d -log_rotation_size=0 -log_line_prefix='%m[%p]' -log_timezone='W-SU' -datestyle='iso,mdy' -timezone='W-SU' -lc_messages='en_US.UTF-8' -lc_monetary='en_US.UTF-8' -lc_numeric='en_US.UTF-8' -lc_time='en_US.UTF-8' -default_text_search_config='pg_catalog.english' diff --git a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/replica_postgresql.conf.j2 b/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/replica_postgresql.conf.j2 deleted file mode 100644 index 206ab2eb3..000000000 --- a/ansible_collections/community/postgresql/tests/integration/targets/setup_postgresql_replication/templates/replica_postgresql.conf.j2 +++ /dev/null @@ -1,28 +0,0 @@ -# Important parameters: -listen_addresses='*' -port = {{ replica_port }} -wal_level = logical -max_wal_senders = 8 -track_commit_timestamp = on -max_replication_slots = 10 - -# Unimportant parameters: -max_connections=10 -shared_buffers=8MB -dynamic_shared_memory_type=posix -log_destination='stderr' -logging_collector=on -log_directory='log' -log_filename='postgresql-%a.log' -log_truncate_on_rotation=on -log_rotation_age=1d -log_rotation_size=0 -log_line_prefix='%m[%p]' -log_timezone='W-SU' -datestyle='iso,mdy' -timezone='W-SU' -lc_messages='en_US.UTF-8' -lc_monetary='en_US.UTF-8' -lc_numeric='en_US.UTF-8' -lc_time='en_US.UTF-8' -default_text_search_config='pg_catalog.english' diff --git a/ansible_collections/community/postgresql/tests/sanity/extra/no-unwanted-files.py b/ansible_collections/community/postgresql/tests/sanity/extra/no-unwanted-files.py index 49806f2e2..85d693c61 100755 --- a/ansible_collections/community/postgresql/tests/sanity/extra/no-unwanted-files.py +++ b/ansible_collections/community/postgresql/tests/sanity/extra/no-unwanted-files.py @@ -2,7 +2,8 @@ # Copyright (c) Ansible Project # GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) """Prevent unwanted files from being added to the source tree.""" -from __future__ import (absolute_import, division, print_function) +from __future__ import absolute_import, division, print_function + __metaclass__ = type import os diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.12.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.12.txt index b9cd1303f..d188d3d68 100644 --- a/ansible_collections/community/postgresql/tests/sanity/ignore-2.12.txt +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.12.txt @@ -1,5 +1,3 @@ -tests/utils/shippable/check_matrix.py replace-urlopen tests/utils/shippable/timing.py shebang plugins/modules/postgresql_db.py use-argspec-type-path plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen -plugins/modules/postgresql_tablespace.py validate-modules:mutually_exclusive-unknown diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.13.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.13.txt index b9cd1303f..d188d3d68 100644 --- a/ansible_collections/community/postgresql/tests/sanity/ignore-2.13.txt +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.13.txt @@ -1,5 +1,3 @@ -tests/utils/shippable/check_matrix.py replace-urlopen tests/utils/shippable/timing.py shebang plugins/modules/postgresql_db.py use-argspec-type-path plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen -plugins/modules/postgresql_tablespace.py validate-modules:mutually_exclusive-unknown diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.14.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.14.txt index b9cd1303f..d188d3d68 100644 --- a/ansible_collections/community/postgresql/tests/sanity/ignore-2.14.txt +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.14.txt @@ -1,5 +1,3 @@ -tests/utils/shippable/check_matrix.py replace-urlopen tests/utils/shippable/timing.py shebang plugins/modules/postgresql_db.py use-argspec-type-path plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen -plugins/modules/postgresql_tablespace.py validate-modules:mutually_exclusive-unknown diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.15.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.15.txt index 58b57c247..c71a79a50 100644 --- a/ansible_collections/community/postgresql/tests/sanity/ignore-2.15.txt +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.15.txt @@ -1,6 +1,4 @@ -tests/utils/shippable/check_matrix.py replace-urlopen tests/utils/shippable/timing.py shebang plugins/modules/postgresql_db.py use-argspec-type-path plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen -plugins/modules/postgresql_tablespace.py validate-modules:mutually_exclusive-unknown plugins/module_utils/version.py pylint:unused-import diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.16.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.16.txt index 58b57c247..c71a79a50 100644 --- a/ansible_collections/community/postgresql/tests/sanity/ignore-2.16.txt +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.16.txt @@ -1,6 +1,4 @@ -tests/utils/shippable/check_matrix.py replace-urlopen tests/utils/shippable/timing.py shebang plugins/modules/postgresql_db.py use-argspec-type-path plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen -plugins/modules/postgresql_tablespace.py validate-modules:mutually_exclusive-unknown plugins/module_utils/version.py pylint:unused-import diff --git a/ansible_collections/community/postgresql/tests/sanity/ignore-2.17.txt b/ansible_collections/community/postgresql/tests/sanity/ignore-2.17.txt new file mode 100644 index 000000000..230ec3421 --- /dev/null +++ b/ansible_collections/community/postgresql/tests/sanity/ignore-2.17.txt @@ -0,0 +1,5 @@ +plugins/modules/postgresql_db.py use-argspec-type-path +plugins/modules/postgresql_db.py validate-modules:use-run-command-not-popen +plugins/module_utils/version.py pylint:unused-import +tests/utils/shippable/timing.py shebang +tests/unit/plugins/module_utils/test_postgres.py pylint:unidiomatic-typecheck diff --git a/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_postgres.py b/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_postgres.py index 975542446..566c78851 100644 --- a/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_postgres.py +++ b/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_postgres.py @@ -1,14 +1,15 @@ # Copyright: (c) 2019, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> # 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) +from __future__ import absolute_import, division, print_function + __metaclass__ = type from os import environ -import pytest - import ansible_collections.community.postgresql.plugins.module_utils.postgres as pg - +import pytest +from ansible_collections.community.postgresql.plugins.module_utils.version import \ + LooseVersion INPUT_DICT = dict( session_role=dict(default=''), @@ -124,7 +125,7 @@ def m_psycopg2(): class DummyPsycopg2(): def __init__(self): - self.__version__ = '2.4.3' + self.__version__ = "2.9.6" self.extras = Extras() self.extensions = Extensions() @@ -155,8 +156,12 @@ class TestEnsureReqLibs(): class Dummym_ansible_module(): def __init__(self): self.params = {'ca_cert': False} + self.warn_msg = '' self.err_msg = '' + def warn(self, msg): + self.warn_msg = msg + def fail_json(self, msg): self.err_msg = msg @@ -164,13 +169,14 @@ class TestEnsureReqLibs(): def test_ensure_req_libs_has_not_psycopg2(self, m_ansible_module): """Test ensure_required_libs() with psycopg2 is None.""" - # HAS_PSYCOPG2 is False by default + # HAS_PSYCOPG is False by default pg.ensure_required_libs(m_ansible_module) assert 'Failed to import the required Python library (psycopg2)' in m_ansible_module.err_msg def test_ensure_req_libs_has_psycopg2(self, m_ansible_module, monkeypatch): """Test ensure_required_libs() with psycopg2 is not None.""" - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) + monkeypatch.setattr(pg, 'HAS_PSYCOPG', True) + monkeypatch.setattr(pg, 'PSYCOPG_VERSION', "2.9") pg.ensure_required_libs(m_ansible_module) assert m_ansible_module.err_msg == '' @@ -180,8 +186,9 @@ class TestEnsureReqLibs(): Test with module.params['ca_cert'], psycopg2 version is suitable. """ m_ansible_module.params['ca_cert'] = True - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) - monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) + monkeypatch.setattr(pg, 'HAS_PSYCOPG', True) + monkeypatch.setattr(pg, 'PSYCOPG_VERSION', LooseVersion("2.9.6")) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) pg.ensure_required_libs(m_ansible_module) assert m_ansible_module.err_msg == '' @@ -191,11 +198,10 @@ class TestEnsureReqLibs(): Test with module.params['ca_cert'], psycopg2 version is wrong. """ m_ansible_module.params['ca_cert'] = True - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) + psycopg = m_psycopg2 + monkeypatch.setattr(pg, 'psycopg', psycopg) # Set wrong psycopg2 version number: - psycopg2 = m_psycopg2 - psycopg2.__version__ = '2.4.2' - monkeypatch.setattr(pg, 'psycopg2', psycopg2) + monkeypatch.setattr(pg, 'PSYCOPG_VERSION', LooseVersion("2.4.2")) pg.ensure_required_libs(m_ansible_module) assert 'psycopg2 must be at least 2.4.3' in m_ansible_module.err_msg @@ -231,10 +237,10 @@ class TestConnectToDb(): """ Namespace for testing connect_to_db() function. - When some connection errors occure connect_to_db() caught any of them + When some connection errors occur connect_to_db() caught any of them and invoke fail_json() or warn() methods of AnsibleModule object depending on the passed parameters. - connect_to_db may return db_connection object or None if errors occured. + connect_to_db may return db_connection object or None if errors occurred. Therefore we must check: 1. Values of err_msg and warn_msg attributes of m_ansible_module mock object. 2. Types of return objects (db_connection and cursor). @@ -242,22 +248,22 @@ class TestConnectToDb(): def test_connect_to_db(self, m_ansible_module, monkeypatch, m_psycopg2): """Test connect_to_db(), common test.""" - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) conn_params = pg.get_conn_params(m_ansible_module, m_ansible_module.params) db_connection, dummy = pg.connect_to_db(m_ansible_module, conn_params) cursor = db_connection.cursor() # if errors, db_connection returned as None: - assert type(db_connection) == DbConnection - assert type(cursor) == Cursor + assert type(db_connection) is DbConnection + assert type(cursor) is Cursor assert m_ansible_module.err_msg == '' # The default behaviour, normal in this case: assert 'Database name has not been passed' in m_ansible_module.warn_msg def test_session_role(self, m_ansible_module, monkeypatch, m_psycopg2): """Test connect_to_db(), switch on session_role.""" - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) m_ansible_module.params['session_role'] = 'test_role' @@ -265,8 +271,8 @@ class TestConnectToDb(): db_connection, dummy = pg.connect_to_db(m_ansible_module, conn_params) cursor = db_connection.cursor() # if errors, db_connection returned as None: - assert type(db_connection) == DbConnection - assert type(cursor) == Cursor + assert type(db_connection) is DbConnection + assert type(cursor) is Cursor assert m_ansible_module.err_msg == '' # The default behaviour, normal in this case: assert 'Database name has not been passed' in m_ansible_module.warn_msg @@ -275,8 +281,7 @@ class TestConnectToDb(): """ Test connect_to_db(), fail_on_conn arg passed as True (the default behavior). """ - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) - monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) m_ansible_module.params['login_user'] = 'Exception' # causes Exception @@ -290,8 +295,7 @@ class TestConnectToDb(): """ Test connect_to_db(), fail_on_conn arg passed as False. """ - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) - monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) m_ansible_module.params['login_user'] = 'Exception' # causes Exception @@ -306,9 +310,9 @@ class TestConnectToDb(): """ Test connect_to_db(), autocommit arg passed as True (the default is False). """ - monkeypatch.setattr(pg, 'HAS_PSYCOPG2', True) # case 1: psycopg2.__version >= 2.4.2 (the default in m_psycopg2) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) conn_params = pg.get_conn_params(m_ansible_module, m_ansible_module.params) @@ -316,8 +320,8 @@ class TestConnectToDb(): cursor = db_connection.cursor() # if errors, db_connection returned as None: - assert type(db_connection) == DbConnection - assert type(cursor) == Cursor + assert type(db_connection) is DbConnection + assert type(cursor) is Cursor assert m_ansible_module.err_msg == '' @@ -327,12 +331,12 @@ class TestGetConnParams(): def test_get_conn_params_def(self, m_ansible_module, m_psycopg2, monkeypatch): """Test get_conn_params(), warn_db_default kwarg is default.""" - monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) assert pg.get_conn_params(m_ansible_module, INPUT_DICT) == EXPECTED_DICT assert m_ansible_module.warn_msg == 'Database name has not been passed, used default database to connect to.' def test_get_conn_params_warn_db_def_false(self, m_ansible_module, m_psycopg2, monkeypatch): """Test get_conn_params(), warn_db_default kwarg is False.""" - monkeypatch.setattr(pg, 'psycopg2', m_psycopg2) + monkeypatch.setattr(pg, 'psycopg', m_psycopg2) assert pg.get_conn_params(m_ansible_module, INPUT_DICT, warn_db_default=False) == EXPECTED_DICT assert m_ansible_module.warn_msg == '' diff --git a/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_saslprep.py b/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_saslprep.py index 62a1704ad..a93bf3f79 100644 --- a/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_saslprep.py +++ b/ansible_collections/community/postgresql/tests/unit/plugins/module_utils/test_saslprep.py @@ -2,13 +2,13 @@ # Copyright: (c) 2019, Andrey Tuzhilin <andrei.tuzhilin@gmail.com> # Copyright: (c) 2020, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> -from __future__ import (absolute_import, division, print_function) +from __future__ import absolute_import, division, print_function + __metaclass__ = type import pytest - -from ansible_collections.community.postgresql.plugins.module_utils.saslprep import saslprep - +from ansible_collections.community.postgresql.plugins.module_utils.saslprep import \ + saslprep VALID = [ (u'', u''), @@ -51,5 +51,5 @@ def test_saslprep_conversions(source, target): @pytest.mark.parametrize('source,exception', INVALID) def test_saslprep_exceptions(source, exception): - with pytest.raises(exception) as ex: + with pytest.raises(exception): saslprep(source) diff --git a/ansible_collections/community/postgresql/tests/unit/plugins/modules/test_postgresql_set.py b/ansible_collections/community/postgresql/tests/unit/plugins/modules/test_postgresql_set.py index a10678202..8850df251 100644 --- a/ansible_collections/community/postgresql/tests/unit/plugins/modules/test_postgresql_set.py +++ b/ansible_collections/community/postgresql/tests/unit/plugins/modules/test_postgresql_set.py @@ -1,12 +1,13 @@ # -*- coding: utf-8 -*- # Copyright: (c) 2021, Andrew Klychkov (@Andersson007) <aaklychkov@mail.ru> -from __future__ import (absolute_import, division, print_function) +from __future__ import absolute_import, division, print_function + __metaclass__ = type import pytest - -from ansible_collections.community.postgresql.plugins.modules.postgresql_set import pretty_to_bytes +from ansible_collections.community.postgresql.plugins.modules.postgresql_set import \ + pretty_to_bytes @pytest.mark.parametrize('input_,expected', [ diff --git a/ansible_collections/community/postgresql/tests/utils/shippable/check_matrix.py b/ansible_collections/community/postgresql/tests/utils/shippable/check_matrix.py index 608db6923..e0115c124 100755 --- a/ansible_collections/community/postgresql/tests/utils/shippable/check_matrix.py +++ b/ansible_collections/community/postgresql/tests/utils/shippable/check_matrix.py @@ -1,6 +1,7 @@ #!/usr/bin/env python """Verify the currently executing Shippable test matrix matches the one defined in the "shippable.yml" file.""" -from __future__ import (absolute_import, division, print_function) +from __future__ import absolute_import, division, print_function + __metaclass__ = type import datetime @@ -15,12 +16,7 @@ try: except ImportError: NoReturn = None -try: - # noinspection PyCompatibility - from urllib2 import urlopen # pylint: disable=ansible-bad-import-from -except ImportError: - # noinspection PyCompatibility - from urllib.request import urlopen +from ansible.module_utils.urls import open_url def main(): # type: () -> None @@ -47,7 +43,7 @@ def main(): # type: () -> None for attempts_remaining in range(4, -1, -1): try: - jobs = json.loads(urlopen('https://api.shippable.com/jobs?runIds=%s' % run_id).read()) + jobs = json.loads(open_url('https://api.shippable.com/jobs?runIds=%s' % run_id).read()) if not isinstance(jobs, list): raise Exception('Shippable run %s data is not a list.' % run_id) diff --git a/ansible_collections/community/postgresql/tests/utils/shippable/shippable.sh b/ansible_collections/community/postgresql/tests/utils/shippable/shippable.sh index b181297f9..818324edd 100755 --- a/ansible_collections/community/postgresql/tests/utils/shippable/shippable.sh +++ b/ansible_collections/community/postgresql/tests/utils/shippable/shippable.sh @@ -59,6 +59,7 @@ else retry pip install "https://github.com/ansible/ansible/archive/stable-${ansible_version}.tar.gz" --disable-pip-version-check fi +# shellcheck disable=SC2153 if [ "${SHIPPABLE_BUILD_ID:-}" ]; then export ANSIBLE_COLLECTIONS_PATHS="${HOME}/.ansible" SHIPPABLE_RESULT_DIR="$(pwd)/shippable" diff --git a/ansible_collections/community/postgresql/tests/utils/shippable/timing.py b/ansible_collections/community/postgresql/tests/utils/shippable/timing.py index fb538271b..c6bf15532 100755 --- a/ansible_collections/community/postgresql/tests/utils/shippable/timing.py +++ b/ansible_collections/community/postgresql/tests/utils/shippable/timing.py @@ -1,5 +1,6 @@ #!/usr/bin/env python3.7 -from __future__ import (absolute_import, division, print_function) +from __future__ import absolute_import, division, print_function + __metaclass__ = type import sys |