From a453ac31f3428614cceb99027f8efbdb9258a40b Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Tue, 14 May 2024 22:03:01 +0200 Subject: Adding upstream version 2.10.7+merged+base+2.10.8+dfsg. Signed-off-by: Daniel Baumann --- .../incidental_postgresql_user/tasks/main.yml | 7 + .../tasks/postgresql_user_general.yml | 741 +++++++++++++++++++++ .../tasks/postgresql_user_initial.yml | 153 +++++ .../tasks/test_no_password_change.yml | 167 +++++ .../tasks/test_password.yml | 336 ++++++++++ 5 files changed, 1404 insertions(+) create mode 100644 test/integration/targets/incidental_postgresql_user/tasks/main.yml create mode 100644 test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_general.yml create mode 100644 test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_initial.yml create mode 100644 test/integration/targets/incidental_postgresql_user/tasks/test_no_password_change.yml create mode 100644 test/integration/targets/incidental_postgresql_user/tasks/test_password.yml (limited to 'test/integration/targets/incidental_postgresql_user/tasks') diff --git a/test/integration/targets/incidental_postgresql_user/tasks/main.yml b/test/integration/targets/incidental_postgresql_user/tasks/main.yml new file mode 100644 index 00000000..d59ae635 --- /dev/null +++ b/test/integration/targets/incidental_postgresql_user/tasks/main.yml @@ -0,0 +1,7 @@ +# Initial CI tests of postgresql_user module +- import_tasks: postgresql_user_initial.yml + when: postgres_version_resp.stdout is version('9.4', '>=') + +# General tests: +- import_tasks: postgresql_user_general.yml + when: postgres_version_resp.stdout is version('9.4', '>=') diff --git a/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_general.yml b/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_general.yml new file mode 100644 index 00000000..963f58ac --- /dev/null +++ b/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_general.yml @@ -0,0 +1,741 @@ +# Copyright: (c) 2019, Andrew Klychkov (@Andersson007) +# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) +# Integration tests for postgresql_user module. + +- 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' + task_parameters: &task_parameters + become_user: '{{ pg_user }}' + become: yes + register: result + pg_parameters: &pg_parameters + login_user: '{{ pg_user }}' + login_db: postgres + + block: + # + # Common tests + # + - name: Create role in check_mode + <<: *task_parameters + check_mode: yes + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: check that the user doesn't exist + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 0 + + - name: Create role in actual mode + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: check that the user exists + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 1 + + - name: Add a comment on the user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '{{ test_comment1 }}' + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON ROLE \"{{ test_user }}\" IS '{{ test_comment1 }}'"] + + - 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 == '{{ test_comment1 }}' + + - name: Try to add the same comment on the user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '{{ test_comment1 }}' + + - assert: + that: + - result is not changed + + - name: Try to add another comment on the user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + comment: '{{ test_comment2 }}' + + - assert: + that: + - result is changed + - result.queries == ["COMMENT ON ROLE \"{{ test_user }}\" IS '{{ test_comment2 }}'"] + + - 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 == '{{ test_comment2 }}' + + - name: Try to create role again in check_mode + <<: *task_parameters + check_mode: yes + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: check that the user exists + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 1 + + - name: Try to create role again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: check that the user exists + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 1 + + - name: Drop role in check_mode + <<: *task_parameters + check_mode: yes + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + state: absent + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: check that the user actually exists + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 1 + + - name: Drop role in actual mode + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + state: absent + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: check that the user doesn't exist + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_roles WHERE rolname = '{{ test_user }}'" + + - assert: + that: + - result.rowcount == 0 + + - name: Try to drop role in check mode again + <<: *task_parameters + check_mode: yes + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + state: absent + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: Try to drop role in actual mode again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + state: absent + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + # + # password, no_password_changes, encrypted, expires parameters + # + + - name: Create role with password, passed as hashed md5 + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + password: md59543f1d82624df2b31672ec0f7050460 + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: Check that the user exist with a proper password + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'md59543f1d82624df2b31672ec0f7050460'" + + - assert: + that: + - result.rowcount == 1 + + - name: Test no_password_changes + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + password: u123 + no_password_changes: yes + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + + - name: Check that nothing changed + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'md59543f1d82624df2b31672ec0f7050460'" + + - assert: + that: + - result.rowcount == 1 + + # Storing unencrypted passwords is not available from PostgreSQL 10 + - name: Change password, passed as unencrypted + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + password: myunencryptedpass + encrypted: no + when: postgres_version_resp.stdout is version('10', '<') + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + when: postgres_version_resp.stdout is version('10', '<') + + - name: Check that the user exist with the unencrypted password + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword = 'myunencryptedpass'" + when: postgres_version_resp.stdout is version('10', '<') + + - assert: + that: + - result.rowcount == 1 + when: postgres_version_resp.stdout is version('10', '<') + + - name: Change password, explicit encrypted=yes + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + password: myunencryptedpass + encrypted: yes + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: Check that the user exist with encrypted password + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: "SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' and rolpassword != 'myunencryptedpass'" + + - assert: + that: + - result.rowcount == 1 + + - name: Change rolvaliduntil attribute + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + expires: 'Jan 31 2020' + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: Check the prev step + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolvaliduntil::text like '2020-01-31%' + + - assert: + that: + - result.rowcount == 1 + + - name: Try to set the same rolvaliduntil value again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + expires: 'Jan 31 2020' + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: Check that nothing changed + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolvaliduntil::text like '2020-01-31%' + + - assert: + that: + - result.rowcount == 1 + + # + # role_attr_flags + # + - name: Set role attributes + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + role_attr_flags: CREATEROLE,CREATEDB + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: Check the prev step + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolcreaterole = 't' and rolcreatedb = 't' + + - assert: + that: + - result.rowcount == 1 + + - name: Set the same role attributes again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + role_attr_flags: CREATEROLE,CREATEDB + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: Check the prev step + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolcreaterole = 't' and rolcreatedb = 't' + + - name: Set role attributes + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + role_attr_flags: NOCREATEROLE,NOCREATEDB + + - assert: + that: + - result is changed + - result.user == '{{ test_user }}' + + - name: Check the prev step + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolcreaterole = 'f' and rolcreatedb = 'f' + + - assert: + that: + - result.rowcount == 1 + + - name: Set role attributes + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + role_attr_flags: NOCREATEROLE,NOCREATEDB + + - assert: + that: + - result is not changed + - result.user == '{{ test_user }}' + + - name: Check the prev step + <<: *task_parameters + postgresql_query: + <<: *pg_parameters + query: > + SELECT rolname FROM pg_authid WHERE rolname = '{{ test_user }}' + AND rolcreaterole = 'f' and rolcreatedb = 'f' + + # + # priv + # + - name: Create test table + <<: *task_parameters + postgresql_table: + <<: *pg_parameters + name: '{{ test_table }}' + columns: + - id int + + - name: Insert data to test table + <<: *task_parameters + postgresql_query: + query: "INSERT INTO {{ test_table }} (id) VALUES ('1')" + <<: *pg_parameters + + - name: Check that test_user is not allowed to read the data + <<: *task_parameters + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + session_role: '{{ test_user }}' + query: 'SELECT * FROM {{ test_table }}' + ignore_errors: yes + + - assert: + that: + - result is failed + - "'permission denied' in result.msg" + + - name: Grant privileges + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + priv: '{{ test_table }}:SELECT' + + - assert: + that: + - result is changed + + - name: Check that test_user is allowed to read the data + <<: *task_parameters + postgresql_query: + db: postgres + login_user: '{{ pg_user }}' + session_role: '{{ test_user }}' + query: 'SELECT * FROM {{ test_table }}' + + - assert: + that: + - result.rowcount == 1 + + - name: Grant the same privileges again + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + priv: '{{ test_table }}:SELECT' + + - assert: + that: + - result is not changed + + - name: Remove test table + <<: *task_parameters + postgresql_table: + <<: *pg_parameters + name: '{{ test_table }}' + state: absent + + # + # fail_on_user + # + - name: Create role for test + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user2 }}' + + - name: Create test table, set owner as test_user + <<: *task_parameters + postgresql_table: + <<: *pg_parameters + name: '{{ test_table }}' + owner: '{{ test_user2 }}' + + - name: Test fail_on_user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user2 }}' + state: absent + ignore_errors: yes + + - assert: + that: + - result is failed + - result.msg == 'Unable to remove user' + + - name: Test fail_on_user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ test_user }}' + fail_on_user: no + + - assert: + 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: yes + + - 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 + + - 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 }}' + + - 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 + + always: + # + # Clean up + # + - name: Drop test table + <<: *task_parameters + postgresql_table: + <<: *pg_parameters + name: '{{ test_table }}' + state: absent + + - name: Drop test user + <<: *task_parameters + postgresql_user: + <<: *pg_parameters + name: '{{ item }}' + state: absent + loop: + - '{{ test_user }}' + - '{{ test_user2 }}' + - '{{ test_group1 }}' + - '{{ test_group2 }}' diff --git a/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_initial.yml b/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_initial.yml new file mode 100644 index 00000000..ccd42847 --- /dev/null +++ b/test/integration/targets/incidental_postgresql_user/tasks/postgresql_user_initial.yml @@ -0,0 +1,153 @@ +# +# Create and destroy user, test 'password' and 'encrypted' parameters +# +# unencrypted values are not supported on newer versions +# do not run the encrypted: no tests if on 10+ +- set_fact: + encryption_values: + - 'yes' + +- set_fact: + encryption_values: '{{ encryption_values + ["no"]}}' + when: postgres_version_resp.stdout is version('10', '<=') + +- include_tasks: test_password.yml + vars: + encrypted: '{{ loop_item }}' + db_password1: 'secretù' # use UTF-8 + loop: '{{ encryption_values }}' + loop_control: + loop_var: loop_item + +# BYPASSRLS role attribute was introduced in PostgreSQL 9.5, so +# we want to test attribute management differently depending +# on the version. +- set_fact: + bypassrls_supported: "{{ postgres_version_resp.stdout is version('9.5.0', '>=') }}" + +# test 'no_password_change' and 'role_attr_flags' parameters +- include_tasks: test_no_password_change.yml + vars: + no_password_changes: '{{ loop_item }}' + loop: + - 'yes' + - 'no' + loop_control: + loop_var: loop_item + +### TODO: fail_on_user + +# +# Test login_user functionality +# +- name: Create a user to test login module parameters + become: yes + become_user: "{{ pg_user }}" + postgresql_user: + name: "{{ db_user1 }}" + state: "present" + encrypted: 'yes' + password: "password" + role_attr_flags: "CREATEDB,LOGIN,CREATEROLE" + login_user: "{{ pg_user }}" + db: postgres + +- name: Create db + postgresql_db: + name: "{{ db_name }}" + state: "present" + login_user: "{{ db_user1 }}" + login_password: "password" + login_host: "localhost" + +- name: Check that database created + become: yes + become_user: "{{ pg_user }}" + shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres + register: result + +- assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + +- name: Create a user + postgresql_user: + name: "{{ db_user2 }}" + state: "present" + encrypted: 'yes' + password: "md55c8ccfd9d6711fc69a7eae647fc54f51" + db: "{{ db_name }}" + login_user: "{{ db_user1 }}" + login_password: "password" + login_host: "localhost" + +- name: Check that it was created + become: yes + become_user: "{{ pg_user }}" + shell: echo "select * from pg_user where usename='{{ db_user2 }}';" | psql -d postgres + register: result + +- assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + +- name: Grant database privileges + postgresql_privs: + type: "database" + state: "present" + roles: "{{ db_user2 }}" + privs: "CREATE,connect" + objs: "{{ db_name }}" + db: "{{ db_name }}" + login: "{{ db_user1 }}" + password: "password" + host: "localhost" + +- name: Check that the user has the requested permissions (database) + become: yes + become_user: "{{ pg_user }}" + shell: echo "select datacl from pg_database where datname='{{ db_name }}';" | psql {{ db_name }} + register: result_database + +- assert: + that: + - "result_database.stdout_lines[-1] == '(1 row)'" + - "db_user2 ~ '=Cc' in result_database.stdout" + +- name: Remove user + postgresql_user: + name: "{{ db_user2 }}" + state: 'absent' + priv: "ALL" + db: "{{ db_name }}" + login_user: "{{ db_user1 }}" + login_password: "password" + login_host: "localhost" + +- name: Check that they were removed + become: yes + become_user: "{{ pg_user }}" + shell: echo "select * from pg_user where usename='{{ db_user2 }}';" | psql -d postgres + register: result + +- assert: + that: + - "result.stdout_lines[-1] == '(0 rows)'" + +- name: Destroy DB + postgresql_db: + state: absent + name: "{{ db_name }}" + login_user: "{{ db_user1 }}" + login_password: "password" + login_host: "localhost" + +- name: Check that database was destroyed + become: yes + become_user: "{{ pg_user }}" + shell: echo "select datname from pg_database where datname = '{{ db_name }}';" | psql -d postgres + register: result + +- assert: + that: + - "result.stdout_lines[-1] == '(0 rows)'" diff --git a/test/integration/targets/incidental_postgresql_user/tasks/test_no_password_change.yml b/test/integration/targets/incidental_postgresql_user/tasks/test_no_password_change.yml new file mode 100644 index 00000000..c296c0ea --- /dev/null +++ b/test/integration/targets/incidental_postgresql_user/tasks/test_no_password_change.yml @@ -0,0 +1,167 @@ +- vars: + task_parameters: &task_parameters + become_user: "{{ pg_user }}" + become: yes + register: result + postgresql_parameters: ¶meters + db: postgres + name: "{{ db_user1 }}" + login_user: "{{ pg_user }}" + + block: + + - name: Create a user with all role attributes + <<: *task_parameters + postgresql_user: + <<: *parameters + state: "present" + role_attr_flags: "SUPERUSER,CREATEROLE,CREATEDB,INHERIT,LOGIN{{ bypassrls_supported | ternary(',BYPASSRLS', '') }}" + no_password_changes: '{{ no_password_changes }}' # no_password_changes is ignored when user doesn't already exist + + - name: Check that the user has the requested role attributes + <<: *task_parameters + shell: "echo \"select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin {{ bypassrls_supported | ternary(\", 'bypassrls:'||rolbypassrls\", '') }} from pg_roles where rolname='{{ db_user1 }}';\" | psql -d postgres" + + - assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + - "'super:t' in result.stdout_lines[-2]" + - "'createrole:t' in result.stdout_lines[-2]" + - "'create:t' in result.stdout_lines[-2]" + - "'inherit:t' in result.stdout_lines[-2]" + - "'login:t' in result.stdout_lines[-2]" + + - block: + - name: Check that the user has the requested role attribute BYPASSRLS + <<: *task_parameters + shell: "echo \"select 'bypassrls:'||rolbypassrls from pg_roles where rolname='{{ db_user1 }}';\" | psql -d postgres" + + - assert: + that: + - "not bypassrls_supported or 'bypassrls:t' in result.stdout_lines[-2]" + when: bypassrls_supported + + - name: Modify a user to have no role attributes + <<: *task_parameters + postgresql_user: + <<: *parameters + state: "present" + role_attr_flags: "NOSUPERUSER,NOCREATEROLE,NOCREATEDB,noinherit,NOLOGIN{{ bypassrls_supported | ternary(',NOBYPASSRLS', '') }}" + no_password_changes: '{{ no_password_changes }}' + + - name: Check that ansible reports it modified the role + assert: + that: + - result is changed + + - name: "Check that the user doesn't have any attribute" + <<: *task_parameters + shell: "echo \"select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin from pg_roles where rolname='{{ db_user1 }}';\" | psql -d postgres" + + - assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + - "'super:f' in result.stdout_lines[-2]" + - "'createrole:f' in result.stdout_lines[-2]" + - "'create:f' in result.stdout_lines[-2]" + - "'inherit:f' in result.stdout_lines[-2]" + - "'login:f' in result.stdout_lines[-2]" + + - block: + - name: Check that the user has the requested role attribute BYPASSRLS + <<: *task_parameters + shell: "echo \"select 'bypassrls:'||rolbypassrls from pg_roles where rolname='{{ db_user1 }}';\" | psql -d postgres" + + - assert: + that: + - "not bypassrls_supported or 'bypassrls:f' in result.stdout_lines[-2]" + when: bypassrls_supported + + - name: Try to add an invalid attribute + <<: *task_parameters + postgresql_user: + <<: *parameters + state: "present" + role_attr_flags: "NOSUPERUSER,NOCREATEROLE,NOCREATEDB,noinherit,NOLOGIN{{ bypassrls_supported | ternary(',NOBYPASSRLS', '') }},INVALID" + no_password_changes: '{{ no_password_changes }}' + ignore_errors: yes + + - name: Check that ansible reports failure + assert: + that: + - result is not changed + - result is failed + - "result.msg == 'Invalid role_attr_flags specified: INVALID'" + + - name: Modify a single role attribute on a user + <<: *task_parameters + postgresql_user: + <<: *parameters + state: "present" + role_attr_flags: "LOGIN" + no_password_changes: '{{ no_password_changes }}' + + - name: Check that ansible reports it modified the role + assert: + that: + - result is changed + + - name: Check the role attributes + <<: *task_parameters + shell: echo "select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin from pg_roles where rolname='{{ db_user1 }}';" | psql -d postgres + + - assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + - "'super:f' in result.stdout_lines[-2]" + - "'createrole:f' in result.stdout_lines[-2]" + - "'create:f' in result.stdout_lines[-2]" + - "'inherit:f' in result.stdout_lines[-2]" + - "'login:t' in result.stdout_lines[-2]" + + - block: + - name: Check the role attribute BYPASSRLS + <<: *task_parameters + shell: echo "select 'bypassrls:'||rolbypassrls from pg_roles where rolname='{{ db_user1 }}';" | psql -d postgres + + - assert: + that: + - "( postgres_version_resp.stdout is version('9.5.0', '<')) or 'bypassrls:f' in result.stdout_lines[-2]" + when: bypassrls_supported + + - name: Check that using same attribute a second time does nothing + <<: *task_parameters + postgresql_user: + <<: *parameters + state: "present" + role_attr_flags: "LOGIN" + no_password_changes: '{{ no_password_changes }}' + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - name: Check there isn't any update reported + assert: + that: + - result is not changed + + - name: Cleanup the user + <<: *task_parameters + postgresql_user: + <<: *parameters + state: 'absent' + no_password_changes: '{{ no_password_changes }}' # user deletion: no_password_changes is ignored + + - name: Check that user was removed + <<: *task_parameters + shell: echo "select * from pg_user where usename='{{ db_user1 }}';" | psql -d postgres + + - assert: + that: + - "result.stdout_lines[-1] == '(0 rows)'" + + always: + - name: Cleanup the user + <<: *task_parameters + postgresql_user: + <<: *parameters + state: 'absent' diff --git a/test/integration/targets/incidental_postgresql_user/tasks/test_password.yml b/test/integration/targets/incidental_postgresql_user/tasks/test_password.yml new file mode 100644 index 00000000..be033a55 --- /dev/null +++ b/test/integration/targets/incidental_postgresql_user/tasks/test_password.yml @@ -0,0 +1,336 @@ +- vars: + task_parameters: &task_parameters + become_user: "{{ pg_user }}" + become: yes + register: result + postgresql_parameters: ¶meters + db: postgres + name: "{{ db_user1 }}" + login_user: "{{ pg_user }}" + + block: + - name: 'Check that PGOPTIONS environment variable is effective (1/2)' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '{{ db_password1 }}' + ignore_errors: true + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - name: 'Check that PGOPTIONS environment variable is effective (2/2)' + assert: + that: + - "{{ result is failed }}" + + - name: 'Create a user (password encrypted: {{ encrypted }})' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '{{ db_password1 }}' + encrypted: '{{ encrypted }}' + environment: + PGCLIENTENCODING: 'UTF8' + + - block: &changed # block is only used here in order to be able to define YAML anchor + - name: Check that ansible reports it was created + assert: + that: + - "{{ result is changed }}" + + - name: Check that it was created + <<: *task_parameters + shell: echo "select * from pg_user where usename='{{ db_user1 }}';" | psql -d postgres + + - assert: + that: + - "result.stdout_lines[-1] == '(1 row)'" + + - name: Check that creating user a second time does nothing + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '{{ db_password1 }}' + encrypted: '{{ encrypted }}' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - block: ¬_changed # block is only used here in order to be able to define YAML anchor + - name: Check that ansible reports no change + assert: + that: + - "{{ result is not changed }}" + + - name: 'Define an expiration time' + <<: *task_parameters + postgresql_user: + <<: *parameters + expires: '2025-01-01' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + - name: 'Redefine the same expiration time' + <<: *task_parameters + postgresql_user: + expires: '2025-01-01' + <<: *parameters + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - block: + + - name: 'Using MD5-hashed password: check that password not changed when using cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '{{ db_password1 }}' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: "Using MD5-hashed password: check that password not changed when using md5 hash with 'ENCRYPTED'" + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "md5{{ (db_password1 ~ db_user1) | hash('md5')}}" + encrypted: 'yes' + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: "Using MD5-hashed password: check that password not changed when using md5 hash with 'UNENCRYPTED'" + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "md5{{ (db_password1 ~ db_user1) | hash('md5')}}" + encrypted: 'no' + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Redefine the same expiration time and password (encrypted)' + <<: *task_parameters + postgresql_user: + <<: *parameters + encrypted: 'yes' + password: "md5{{ (db_password1 ~ db_user1) | hash('md5')}}" + expires: '2025-01-01' + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using MD5-hashed password: check that password changed when using another cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: 'prefix{{ db_password1 }}' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + - name: "Using MD5-hashed password: check that password changed when using another md5 hash with 'ENCRYPTED'" + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "md5{{ ('prefix1' ~ db_password1 ~ db_user1) | hash('md5')}}" + encrypted: 'yes' + + - <<: *changed + + - name: "Using MD5-hashed password: check that password changed when using md5 hash with 'UNENCRYPTED'" + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "md5{{ ('prefix2' ~ db_password1 ~ db_user1) | hash('md5')}}" + encrypted: 'no' + register: change_pass_unencrypted + failed_when: + - change_pass_unencrypted is failed + # newer version of psycopg2 no longer supported unencrypted password, we ignore the error + - '"UNENCRYPTED PASSWORD is no longer supported" not in change_pass_unencrypted.msg' + + - <<: *changed + + - name: 'Using MD5-hashed password: check that password changed when clearing the password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + - name: 'Using MD5-hashed password: check that password not changed when clearing the password again' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using cleartext password: check that password not changed when clearing the password again' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using MD5-hashed password: check that password changed when using a cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '{{ db_password1 }}' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + when: encrypted == 'yes' + + - block: + + - name: 'Using cleartext password: check that password not changed when using cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "{{ db_password1 }}" + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Redefine the same expiration time and password (not encrypted)' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "{{ db_password1 }}" + encrypted: 'no' + expires: '2025-01-01' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using cleartext password: check that password changed when using another cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "changed{{ db_password1 }}" + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + - name: 'Using cleartext password: check that password changed when clearing the password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + - name: 'Using cleartext password: check that password not changed when clearing the password again' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using MD5-hashed password: check that password not changed when clearing the password again' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: '' + encrypted: 'yes' + environment: + PGCLIENTENCODING: 'UTF8' + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + - name: 'Using cleartext password: check that password changed when using cleartext password' + <<: *task_parameters + postgresql_user: + <<: *parameters + password: "{{ db_password1 }}" + encrypted: 'no' + environment: + PGCLIENTENCODING: 'UTF8' + + - <<: *changed + + when: encrypted == 'no' + + - name: Remove user + <<: *task_parameters + postgresql_user: + state: 'absent' + <<: *parameters + + - <<: *changed + + - name: Check that they were removed + <<: *task_parameters + shell: echo "select * from pg_user where usename='{{ db_user1 }}';" | psql -d postgres + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - assert: + that: + - "result.stdout_lines[-1] == '(0 rows)'" + + - name: Check that removing user a second time does nothing + <<: *task_parameters + postgresql_user: + state: 'absent' + <<: *parameters + environment: + PGOPTIONS: '-c default_transaction_read_only=on' # ensure 'alter user' query isn't executed + + - <<: *not_changed + + always: + - name: Remove user + <<: *task_parameters + postgresql_user: + state: 'absent' + <<: *parameters -- cgit v1.2.3