pg_user doesn't take password_encryption into account when checking if a password should be updated

[dirkcuys]

SUMMARY

When the current password is stored as a md5 hash, but PGOPTIONS: "-c password_encryption=scram-sha-256" is passed, the new password will still be hashed with md5.

https://github.com/ansible-collections/community.postgresql/blob/0bc4754d88a4343a19b97e76e022e9a93fc1fdef/plugins/modules/postgresql_user.py#L473

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

ansible [core 2.16.5]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/ZZZ/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/ZZZ/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.12 (main, Nov 20 2023, 15:14:05) [GCC 11.4.0] (/usr/bin/python3)
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

# /usr/lib/python3/dist-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.4.0  

STEPS TO REPRODUCE

Create a user

- name: create postgres user
  postgresql_user:
    name: user
    password: password
    login_host: "127.0.0.1"

Update the playbook to use SCRAM

- name: create postgres user
  postgresql_user:
    name: user
    password: password
    login_host: "127.0.0.1"
  environment:
    PGOPTIONS: "-c password_encryption=scram-sha-256"

And re-run the playbook. The password is reported as unchanged.

EXPECTED RESULTS

The password is updated using scram-sha-256

ACTUAL RESULTS

The password is unchanged

[betanummeric]

Hi @dirkcuys Although the password_encryption variable may be changed, the password remains the same. Currently the module does not attempt to change the hashing algorithm while keeping the same password. If you change the password, the module will use the then-specified password_encryption.

[dirkcuys]

Thanks, that helps me work around the problem in my specific case! I ran into problems while migrating a db from postgres 11 to postgres 15. In postgres 15, the default encryption is set as scram-sha-256.

If I'm reading the code correctly, the password is updated using the following SQL

ALTER USER "user" WITH ENCRYPTED PASSWORD %(password)s

And the password passed as a parameter to the query:

cursor.execute(statement, query_password_data)

It doesn't seem like the encryption scheme is available within the function that checks if a password should be changed or where it is updated.

Could it be determined by checking the environment? Then something like

elif (password.startswith('md5') and 'PGOPTIONS' in os.environ and 'password_encryption=scram-sha-256' in os.environ['PGOPTIONS']:
    pwchanging = True

could be added here

[betanummeric]

Yes, the password is set with ALTER USER.

The password_encryption variable can also be set in other ways, so checking the environment is not sufficient. But we can simply run the query show password_encryption;.

Do you want to submit a pull request? What issues did you run into during the upgrade to pg 15?

[dirkcuys]

Do you want to submit a pull request?

If I run into this again I'll try to get a PR together, but for my use-case changing the password was easy enough

What issues did you run into during the upgrade to pg 15?

The issue was that the default for encryption for passwords changed between pg 11 and 15

[Andersson007]

Thanks for discussing the issue, folks! Can we close it? What do you think?