Unable to set Postgres user password

Question

I use Ansible to create a virtual machine running with Vagrant. I used both the (preferred) VMware provider and VirtualBox for testing, and I get the same result with each.

I am using the following set of tasks to try to create a database called so , with the help and access django user. However, the database password does not appear. If I manually installed this, I can connect, if I try in advance, I always get FATAL: password authentication failed for user "django" .

I have posted the appropriate Ansible configuration section below and the corresponding debug section below ( ansible.verbose = "vvv" in the vagrant configuration).

 # Create Prostgres DB - hosts: all sudo: True sudo_user: postgres vars: dbname: so dbuser: django dbpassword: 4967bKzCegrPxVH4tGgQe6kFn232t7KiFDXfedVi tasks: - name: Ensure database exists postgresql_db: name={{ dbname }} - name: Ensure DB user has access to the DB postgresql_user: db={{ dbname }} name={{ dbuser }} password={{ dbpassword }} priv=ALL state=present # Leave user with ability to create databases. This prividge should be # removed for production, but is required for running tests. postgresql_user: name={{ dbuser }} role_attr_flags=NOSUPERUSER,CREATEDB 

Detailed output:

 TASK: [Ensure DB user has access to the DB] *********************************** <127.0.0.1> ESTABLISH CONNECTION FOR USER: vagrant <127.0.0.1> EXEC ['ssh', '-tt', '-q', '-o', 'ControlMaster=auto', '-o', 'ControlPersist=60s', '-o', 'ControlPath=/Users/danielsgroves/.ansible/cp/ansible-ssh-%h-%p-%r', '-o', 'Port=2222', '-o', 'IdentityFile=/Users/danielsgroves/.vagrant.d/insecure_private_key', '-o', 'KbdInteractiveAuthentication=no', '-o', 'PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey', '-o', 'PasswordAuthentication=no', '-o', 'User=vagrant', '-o', 'ConnectTimeout=10', '127.0.0.1', "/bin/sh -c 'mkdir -p /tmp/ansible-1387481596.93-132175356393082 && chmod a+rx /tmp/ansible-1387481596.93-132175356393082 && echo /tmp/ansible-1387481596.93-132175356393082'"] <127.0.0.1> REMOTE_MODULE postgresql_user name=django role_attr_flags=NOSUPERUSER,CREATEDB <127.0.0.1> PUT /var/folders/2j/n8ng8fdd5gj125w5zswg9kj00000gn/T/tmpvnrb37 TO /tmp/ansible-1387481596.93-132175356393082/postgresql_user <127.0.0.1> EXEC ['ssh', '-tt', '-q', '-o', 'ControlMaster=auto', '-o', 'ControlPersist=60s', '-o', 'ControlPath=/Users/danielsgroves/.ansible/cp/ansible-ssh-%h-%p-%r', '-o', 'Port=2222', '-o', 'IdentityFile=/Users/danielsgroves/.vagrant.d/insecure_private_key', '-o', 'KbdInteractiveAuthentication=no', '-o', 'PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey', '-o', 'PasswordAuthentication=no', '-o', 'User=vagrant', '-o', 'ConnectTimeout=10', '127.0.0.1', "/bin/sh -c 'chmod a+r /tmp/ansible-1387481596.93-132175356393082/postgresql_user'"] <127.0.0.1> EXEC ['ssh', '-tt', '-q', '-o', 'ControlMaster=auto', '-o', 'ControlPersist=60s', '-o', 'ControlPath=/Users/danielsgroves/.ansible/cp/ansible-ssh-%h-%p-%r', '-o', 'Port=2222', '-o', 'IdentityFile=/Users/danielsgroves/.vagrant.d/insecure_private_key', '-o', 'KbdInteractiveAuthentication=no', '-o', 'PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey', '-o', 'PasswordAuthentication=no', '-o', 'User=vagrant', '-o', 'ConnectTimeout=10', '127.0.0.1', '/bin/sh -c \'sudo -k && sudo -H -S -p "[sudo via ansible, key=isnxrvycjudgazbgyciehbcpiiswfczx] password: " -u postgres /bin/sh -c \'"\'"\'echo SUDO-SUCCESS-isnxrvycjudgazbgyciehbcpiiswfczx; /usr/bin/python /tmp/ansible-1387481596.93-132175356393082/postgresql_user\'"\'"\'\''] <127.0.0.1> EXEC ['ssh', '-tt', '-q', '-o', 'ControlMaster=auto', '-o', 'ControlPersist=60s', '-o', 'ControlPath=/Users/danielsgroves/.ansible/cp/ansible-ssh-%h-%p-%r', '-o', 'Port=2222', '-o', 'IdentityFile=/Users/danielsgroves/.vagrant.d/insecure_private_key', '-o', 'KbdInteractiveAuthentication=no', '-o', 'PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey', '-o', 'PasswordAuthentication=no', '-o', 'User=vagrant', '-o', 'ConnectTimeout=10', '127.0.0.1', "/bin/sh -c 'rm -rf /tmp/ansible-1387481596.93-132175356393082/ >/dev/null 2>&1'"] ok: [server] => {"changed": false, "user": "django"} 

Answer 1

Is it because you need to specify the host name when connecting? eg.

 $ psql -U django -h localhost so 

This decision was for me.

EDIT:

The final task in this play will not be performed using ansible - it does not have a hyphen in front of the module name. Change it like this:

  - postgresql_user: name={{ dbuser }} role_attr_flags=NOSUPERUSER,CREATEDB 

or (I prefer to name all tasks)

  - name: Set roles for DB user postgresql_user: name={{ dbuser }} role_attr_flags=NOSUPERUSER,CREATEDB 

and then you can log in as a django user with

 $ psql -U django -h localhost so 

Without the installed roles, the user cannot log in. I think the role of "LOGIN" should be implicit in the roles that are listed there, although I did not confirm this in the PostgreSQL docs.