postgresql_user is not idempotent when password is a SCRAM hash

[benformosa]

SUMMARY

When providing the password parameter as a SCRAM-256 hash, postgresql_user will always give a result of changed.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_user

ANSIBLE VERSION

ansible [core 2.12.2]
  config file = /home/ben/.ansible.cfg
  configured module search path = ['/home/ben/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.8/site-packages/ansible
  ansible collection location = /home/ben/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.8.12 (default, Sep 16 2021, 10:46:05) [GCC 8.5.0 20210514 (Red Hat 8.5.0-3)]
  jinja version = 2.10.3
  libyaml = True

COLLECTION VERSION

# /home/ben/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 2.1.5

CONFIGURATION

DEFAULT_HOST_LIST(/home/ben/.ansible.cfg) = ['/home/ben/git/ansible/inventory.ini']
DEFAULT_ROLES_PATH(/home/ben/.ansible.cfg) = ['/home/ben/.ansible/roles', '/home/ben/git/ansible/roles', '/home/ben/git', '/home/ben/git/ansible']
GALAXY_SERVER_LIST(/home/ben/.ansible.cfg) = ['galaxy']

OS / ENVIRONMENT

RHEL 8.6

PostgreSQL 13.7 (RHEL App Stream postgresql:13)

STEPS TO REPRODUCE

Create a user with a task like:

- name: Create user
  community.postgresql.postgresql_user:
    name: scrampassword
    password: SCRAM-SHA-256$4096:XkAEs3QAEZNaIZ49QgluOw==$P62LBGrtmFn7FuWxNJKt3ZlfuteVVV2AZmETo5EkFEI=:dJyvi8FA4emhPhsH/ND7AxSxIc2URCiGUspmdV5MGW8=
  become: true
  become_user: postgres

Full example playbook:
https://gist.github.com/benformosa/e6b95b09f0d55377738a6ff85d4189c9#file-play_postgres_user-yml

EXPECTED RESULTS

The second time the postgresql_user task is run, it should not show as changed.

ACTUAL RESULTS

The result is changed both times the task is run.

TASK [Create users] ******************************************************************************************************************************************
changed: [localhost] => (item=clearpassword)
changed: [localhost] => (item=scrampassword)

TASK [Create users - again] **********************************************************************************************************************************
ok: [localhost] => (item=clearpassword)
changed: [localhost] => (item=scrampassword)

Full log:
https://gist.github.com/benformosa/e6b95b09f0d55377738a6ff85d4189c9#file-play_postgres_user-log

Full log with -vvv:
https://gist.github.com/benformosa/e6b95b09f0d55377738a6ff85d4189c9#file-play_postgres_user_vvv-log

Possible solution

A test could be added to check if the stored password is the same as the password parameter, similar to what is done if the given password starts with md5:

community.postgresql/plugins/modules/postgresql_user.py

Lines 463 to 465 in 3d6d9a7

	elif (password.startswith('md5') and len(password) == 32 + 3) or encrypted == 'UNENCRYPTED':
	if password != current_role_attrs['rolpassword']:
	pwchanging = True

[matthbakeredb]

This would be useful addition. Currently doing exactly this in a local fork. Thanks for submitting this!

[Andersson007]

@matthbakeredb thanks for the feedback, i think we'll merge the PR this week, there's one thing that needs to be added