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?

[jogoossens]

We run into the same problem, migration is not easy, we would like an option that the password is updated if not matched.

So we set:

PGOPTIONS: "-c password_encryption=scram-sha-256"

And would expect the password to be updated (even if not changed) if still saved as md5, the same when the new algorithm on the server is already scram-sha-256 and the password are still md5

[Andersson007]

@jogoossens thanks for the feedback! I've put the help wanted label. If someone wants to contribute, here's the quick start dev guide that can help Feels like a relatively easy fix with what @betanummeric suggested. Any volunteers?

[jogoossens]

For now we use this ansible code to reset all passwords to something random, but we can break users which exist already like this :)

- block:
    - name: Get all users without SCRAM-SHA-256 password when postgres is 14 or higher
      community.postgresql.postgresql_query:
        query: >
          SELECT rolname FROM pg_authid
          WHERE rolcanlogin and rolpassword !~* '^SCRAM-SHA-256';
      register: users_to_update
        
    - name: Reset all passwords from users without SCRAM-SHA-256 password when postgres is 14 or higher
      community.postgresql.postgresql_user:
        name: "{{ item.rolname }}"
        password: "{{ lookup('community.general.random_string', length=32) }}"
      loop: "{{ users_to_update.query_result | list }}"
      
  become: true
  become_user: "{{ postgresql_user }}"
  when: postgresql_version is version('14', '>=')

Maybe this idea can be used to check the SCRAM-SHA-256.

[Andersson007]

PR https://github.com/ansible-collections/community.postgresql/pull/764 , please read its description carefully, i also put a comment about testing, thanks

[Andersson007]

Thanks everyone! I'll ping you all here when we release the collection (probably soon)