Fatal error when creating Postgresql role (Unencrypted passwords issue)

Jim · December 13, 2018, 4:15pm

Hi,
I haven’t installed the server package in quite a while, so I figured I would create a new installation to see what has changed, w/out risking my existing older setup.

I followed the documentation I had created for customizing the new remote install, and when I ran ansible, all ran fine, until it got to here:

TASK [postgresql : install packages] ***********************************************************************************************
changed: [newusbrocaar] => (item=python-psycopg2)
changed: [newusbrocaar] => (item=postgresql)

TASK [postgresql : start postgresql on boot] ***************************************************************************************
ok: [newusbrocaar]

TASK [postgresql : create databases] ***********************************************************************************************
included: /home/biff/BrocaarInstall/NewUS/roles/postgresql/tasks/create_db.yml for newusbrocaar
included: /home/biff/BrocaarInstall/NewUS/roles/postgresql/tasks/create_db.yml for newusbrocaar

TASK [postgresql : create role] ****************************************************************************************************
fatal: [newusbrocaar]: FAILED! => {“censored”: “the output has been hidden due to the fact that ‘no_log: true’ was specified for this result”, “changed”: false}
to retry, use: --limit @/home/biff/BrocaarInstall/NewUS/full_deploy.retry

PLAY RECAP *************************************************************************************************************************
newusbrocaar : ok=21 changed=15 unreachable=0 failed=1

I’ve created multiple servers in the past using my old configuration instructions, so I am assuming that something has changed and my documentation needs updated.

thanks

Jim

brocaar · December 17, 2018, 1:45pm

You need to uncomment this line to see the error:

github.com

brocaar/chirpstack-setup/blob/master/roles/postgresql/tasks/create_db.yml#L7


- name: create role
  postgresql_user:
    name: "{{ item.user }}"
    password: "{{ item.password }}"
    role_attr_flags: LOGIN
  become_user: postgres


- name: create database
  postgresql_db:
    name: "{{ item.name }}"
    owner: "{{ item.user }}"
  become_user: postgres


- name: create trgm extension
  postgresql_ext:
    db: "{{ item.name }}"
    name: pg_trgm

This line is there so that passwords are not logged to the console when creating a role.

Jim · December 17, 2018, 2:58pm

OK,
I enabled logging, and can now see the problem. Is there a reason unencrypted passwords are being used?
This error suggests that if encrypted ones were used - things would work properly…

TASK [postgresql : create databases] ***************************************************************************************************************
included: /home/biff/BrocaarInstall/NewUS/roles/postgresql/tasks/create_db.yml for usbrocaar
included: /home/biff/BrocaarInstall/NewUS/roles/postgresql/tasks/create_db.yml for usbrocaar

TASK [postgresql : create role] ********************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: HINT: Remove UNENCRYPTED to store the password in encrypted form instead.
fatal: [usbrocaar]: FAILED! => {“changed”: false, “module_stderr”: “Traceback (most recent call last):\n File “/tmp/ansible_Ymq15g/ansible_module_postgresql_user.py”, line 855, in \n main()\n File “/tmp/ansible_Ymq15g/ansible_module_postgresql_user.py”, line 816, in main\n role_attr_flags, encrypted, expires, conn_limit)\n File “/tmp/ansible_Ymq15g/ansible_module_postgresql_user.py”, line 270, in user_add\n cursor.execute(query, query_password_data)\n File “/usr/lib/python2.7/dist-packages/psycopg2/extras.py”, line 144, in execute\n return super(DictCursor, self).execute(query, vars)\npsycopg2.NotSupportedError: UNENCRYPTED PASSWORD is no longer supported\nLINE 1: CREATE USER “loraserver_as” WITH UNENCRYPTED PASSWORD 'loras…\n ^\nHINT: Remove UNENCRYPTED to store the password in encrypted form instead.\n\n”, “module_stdout”: “”, “msg”: “MODULE FAILURE”, “rc”: 1}
to retry, use: --limit @/home/biff/BrocaarInstall/NewUS/full_deploy.retry

PLAY RECAP *****************************************************************************************************************************************

Jim · December 18, 2018, 11:00am

I fixed the error locally based on advice given here:
unencrypted passwords issue

Edit roles/postgresql/tasks/create_db.yml
Add the encrypted flag to the user, since ansible defaults to unencrypted:

name: create role
postgresql_user:
name: “{{ item.user }}”
password: “{{ item.password }}”
encrypted: yes
role_attr_flags: LOGIN
become_user: postgres
no_log: true

brocaar · December 18, 2018, 1:07pm

This seems to be introduced by PostgreSQL 10.x.