search

ANXS / postgresql

Fairly full featured Ansible role for Postgresql.
http://anxs.io/
MIT License
850 stars 573 forks source link

"Make sure the PostgreSQL users are present" reports a change when upgrading to Ansible 2.6 #397

Closed thbar closed 5 years ago

thbar commented 5 years ago

For an installation where ANXS.postgresql was used to configure a database with Ansible v2.5, the first time I run the role with Ansible v2.6, "Make sure the PostgreSQL users are present" will report a change.

Subsequent runs do not show the change anymore during my test.

I suspect a change in the way the password is crypted/salted, but could not yet pinpoint this out fully, so I'm reporting this as is.

thbar commented 5 years ago

I must double check if this isn't instead caused by the upgrade I made to this very role in my setup. Will report back.

gclough commented 5 years ago

Thanks @thbar . Keep us updated. I run the role (slightly tweaked like you) with Ansible v2.7.2, but in both cases it uses the built-in postgresql_user module ( https://docs.ansible.com/ansible/latest/modules/postgresql_user_module.html ), so I'm hoping when you check again with the standard role you will find no issues.

thbar commented 5 years ago

@gclough I figured things out.

It is postgresql_user itself which has changed of behaviour with Ansible 2.6:

encrypted (default to yes) ... Whether the password is stored hashed in the database. Passwords can be passed already hashed or unhashed, and postgresql ensures the stored password is hashed when encrypted is set. Note: Postgresql 10 and newer doesn't support unhashed passwords. Previous to Ansible 2.6, this was no by default.

Upgrading to 2.6 resulted into encrypted move to yes by default.

As described a bit below in the documentation around password:

Password can be passed unhashed or hashed (MD5-hashed). Unhashed password will automatically be hashed when saved into the database if encrypted parameter is set, otherwise it will be save in plain text format.

So what happens here is that the unhashed password I'm providing is 1/ detected as unhashed (because the string it doesn't start by md5$) and 2/ automatically hashed.

I've verified that with select * from pg_shadow; (before and after Ansible 2.6), which confirms that the password is now hashed inside the database.

I've also verified (by temporarily allowing external md5 connections and connecting using psql remotely to ensure I wouldn't rely on the trust method) that the old password was still working.

So in summary: nothing problematic as far as this role is concerned, which is great news!

I'm closing this.

PS: thanks for maintaining this role, much appreciated! I'll help whenever I feel I can reliably can.

gclough commented 5 years ago

@thbar , thanks for sorting this out and posting a great summary!