postgresql_user applying changes in check mode

elmanolito commented 3 years ago

SUMMARY

I am currently creating a playbook to configure a postgresdb running on kubernetes and am facing unwanted behaviour with the dry run of the user provisioning.

ISSUE TYPE

Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

ansible [core 2.11.2]
  config file = /Users/manuelgall/projects/devops/platform-ansible/ansible.cfg
  configured module search path = ['/Users/manuelgall/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/ansible
  ansible collection location = /Users/manuelgall/.ansible/collections:/usr/share/ansible/collections
  executable location = /Library/Frameworks/Python.framework/Versions/3.9/bin/ansible
  python version = 3.9.2 (v3.9.2:1a79785e3e, Feb 19 2021, 09:06:10) [Clang 6.0 (clang-600.0.57)]
  jinja version = 3.0.1
  libyaml = True

COLLECTION VERSION

# /Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.3.0

# /Users/manuelgall/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.4.0

CONFIGURATION

ANSIBLE_NOCOWS(env: ANSIBLE_NOCOWS) = True
INTERPRETER_PYTHON(/Users/manuelgall/projects/devops/platform-ansible/ansible.cfg) = /Library/Frameworks/Python.framework/Versions/3.9/bin/python3.9

OS / ENVIRONMENT

macOS 11.2.3 Kubernetes v1.16.3 on Ubuntu 20.04.2 LTS

STEPS TO REPRODUCE

have postgresdb running on kubernetes
port-forward to postgresdb service
run playbook with --check flag

check for users with \du

command to run playbook

ansible-playbook -i inifile.ini yamlfile.yml --vault-password-file .vault.password -vvv --diff --tags "createuser" --check --connection=local

example playbook

name: provision user no_log: false postgresql_user: login_db: dbname name: "{{ 'user' + item.name }}" password: "{{ user_password }}" login_user: "{{ admin_user }}" login_password: "{{ admin_password }}" login_host: "{{ host }}" port: "{{ port }}" tags: createuser loop: "{{ users }}"

EXPECTED RESULTS

I expect the output to be a dry run and no changes to be applied. The users to be provisioned should not be listed when checking with \du.

ACTUAL RESULTS

Changes are applied and users are provisioned to database. \du is showing the provisioned users.

changed: [localhost] => (item=None) => {
    "censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result",
    "changed": true
}
changed: [localhost] => {
    "censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result",
    "changed": true
}
META: role_complete for localhost
META: ran handlers
META: ran handlers

PLAY RECAP *********************************************************************************************************************************************************************
localhost                  : ok=2    changed=1    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Andersson007 commented 3 years ago

@elmanolito hi, thanks for reporting this! So, just to be sure I understand correctly, according to the description:

You didn't have the user in the DB
You ran the playbook with --check
The user was actually created

Correct?

elmanolito commented 3 years ago

@Andersson007 yes, that is correct.

Andersson007 commented 3 years ago

I ran this (community.postgresql 1.4.0 and i don't think we changed something related):
```
psql (13.3)
Type "help" for help.
```

postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | Superuser | {}


2. Ran the playbook twice with `--check`. Reported that the state has been changed in both the cases as expected.

[aklychko@localhost ansible]$ ansible-playbook pg-test.yml -u aklychko -k -K -i "127.0.0.1," -e "ansible_python_interpreter=/usr/bin/python3" --check [WARNING]: You are running the development version of Ansible. You should only run Ansible from "devel" if you are modifying the Ansible engine, or trying out features under development. This is a rapidly changing source of code and can become unstable at any point. SSH password: BECOME password[defaults to SSH password]:

PLAY [all] **

TASK [Test] ***** changed: [127.0.0.1]

PLAY RECAP ** 127.0.0.1 : ok=1 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0

[aklychko@localhost ansible]$ ansible-playbook pg-test.yml -u aklychko -k -K -i "127.0.0.1," -e "ansible_python_interpreter=/usr/bin/python3" --check [WARNING]: You are running the development version of Ansible. You should only run Ansible from "devel" if you are modifying the Ansible engine, or trying out features under development. This is a rapidly changing source of code and can become unstable at any point. SSH password: BECOME password[defaults to SSH password]:

PLAY [all] **

TASK [Test] ***** changed: [127.0.0.1]

PLAY RECAP ** 127.0.0.1 : ok=1 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0


3. Ran the command 1 again

postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | Superuser | {}


4. Did the same with loop - same result.

5. I don't think login params affect somehow because they are used only in the connection function but I also checked the above specifying all the params that we can see in the description - same result (admin was added and used as `login_user`):

postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- admin | Superuser | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test | Superuser | {}



So I failed to reproduce it. Any ideas from anyone why this can happen?

elmanolito commented 3 years ago

Thank you so far for looking into it.

We now also tried with another team-member with the same outcome. running
ansible-playbook -i inifile.ini yamlfile.yml --vault-password-file .vault.password -vvv --diff --tags "createuser" --check --connection=local
and
ansible-playbook -i inifile.ini yamlfile.yml --vault-password-file .vault.password -vvv --tags "createuser" --check --connection=local
both result in the users getting created.

ansible --version:

ansible 2.9.18
  config file = /Users/secondUser/repos/platform-ansible/ansible.cfg
  configured module search path = ['/Users/secondUser/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.9.5 (default, May  4 2021, 03:36:27) [Clang 12.0.0 (clang-1200.0.32.29)]

ansible-galaxy collection list community.postgresql:

usage: ansible-galaxy collection [-h] COLLECTION_ACTION ...
ansible-galaxy collection: error: argument COLLECTION_ACTION: invalid choice: 'list' (choose from 'init', 'build', 'publish', 'install')

ansible-config dump --only-changed also does not work for him.

Andersson007 commented 3 years ago

@elmanolito thanks for checking! Could you also please:

Try to use check_mode: yes with this task instead of the CLI arg
Is there a chance to test this with the latest ansible-core?

Thank you

ghost commented 3 years ago

I also have the same issue. Based on my test and in my situation, it happened to any pgsql user whose privilege is set to a value other than ALL.

For example, in my case, the pg user which keeps reporting changed state is the one with privilege = pg_stat_database:SELECT.

my ENV: community.postgresql version 1.4.0 (I also test in old verion 1.1.1, the reason why I upgraded in the hope to resolve this issue but not) Ansible version 2.10.5 Postgresql 9.6

Andersson007 commented 3 years ago

@zyitingftnt hi, thanks for the feedback! Sorry for the late response, was afk throughout last week.

Could you please:

Provide the full task
Elaborate a bit more on the details

As we can see @elmanolito doesn't pass any priv:. Do I understand correctly that you get this (the user will be actually created in check_mode) only when you pass the priv: blabla parameter and which is not set to ALL? If you don't pass priv, will everything work as expected?

I'd be happy to reproduce this in integration tests but failed to do it using @elmanolito 's task.

ghost commented 3 years ago

@Andersson007. correct. If I set priv: ALL, then problem goes away. If I set priv to other value other than ALL, then it keeps showing "changed".

Here is my playbook

master_playbook.yml
---
- name: update postgresql users on standalone or global master
  hosts: XXXXXXXX
  gather_facts: true
  remote_user: root
  tasks:
    - include_role:
        name: postgresql
        tasks_from: postgresql_manage_user

the playbook is calling another task postgresql_manage_user from a role called postgresql, the task file of the role is below:

postgresql_manage_user.yml in the role postgresql
---
- name: update PostgreSQL users
  postgresql_user:
    name: "{{ item.name }}"
    password: "{{ item.password | default(omit) }}"
    encrypted: "{{ item.encrypted | default(omit) }}"
    priv: "{{ item.priv | default(omit) }}"
    role_attr_flags: "{{ item.role_attr_flags | default(omit) }}"
    db: "{{ item.db | default(omit) }}"
    login_host: "{{ item.login_host | default('localhost') }}"
    login_user: "{{ item.login_user | default(postgresql_user) }}"
    login_password: "{{ item.login_password | default(postgresql_pass) }}"
    login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories) }}"
    port: "{{ item.port | default(omit) }}"
    state: "{{ item.state | default('present') }}"
  with_items: "{{ postgresql_users }}"
  no_log: "{{ postgres_users_no_log }}"
  become: true
  become_user: XXXXX
  vars:
    ansible_ssh_pipelining: true

and the variable is defined in the group_vars as below, the user1 stay idempotent, but not user2. If I change priv to ALL in user2, it resolves the problem.

postgresql_users:
  - name: user1
    password: XXXX
    encrypted: yes
    db: postgres
    priv: ALL
    state: present
  - name: user2
    password: XXXX
    encrypted: yes
    db: postgres
    priv: pg_stat_database:SELECT   <---------------------------------------------------------
    state: present

my ansible version is 2.10.5 my ansble-galaxy collection community.postgresql is 1.4.0 (I also tried with old 1.1.1 and it had same problem) my postgresql version is 9.6 my system on which is running the postgresql is Centos 7

Andersson007 commented 3 years ago

@zyitingftnt thanks for the feedback! Can this be another issue? If i understand correctly, the issue author originally reported that after he/she ran the module in check_mode, the user was actually created in the database. If this is another issue, could you please create a separate one?

elmanolito commented 3 years ago

Hey, thank you all for the feedback. Unfortunately I didn't have any time to further try anything you suggested as I was also forced to continue with this issue still being around. Good thing it was not a show-stopper and just an inconvenience.

I will definitely try and report back. Thanks guys and gals.

ghost commented 3 years ago

Ok, I will raise a bug report then. But for me, this is a critical issue since this breaks the idempotency which is the key principe of Ansible tool.

RealGreenDragon commented 1 year ago

postgresql_user module 'priv' parameter is now deprecated and will be removed soon. postgresql_privs module is the right choice to manage privileges.

Andersson007 commented 1 year ago

@RealGreenDragon thanks for putting the comment here, I'll add this issue link to the issue, so that we won't forget to close this. Thanks!

ansible-collections / community.postgresql