postgresql_user applying changes in check mode

[elmanolito]

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]

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

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

Correct?

[elmanolito]

@Andersson007 yes, that is correct.

[Andersson007]

1. 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]

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]

@elmanolito thanks for checking!
Could you also please:

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

Thank you

[ghost]

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]

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

Could you please:

1. Provide the full task
2. 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]

@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]

@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]

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]

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]

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

[Andersson007]

@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!