postgres is not able to login to verify existence of database

[FilBot3]

I'm not sure I understand how or why the postgres user is not able to login to verify the database exists or not, but the following Playbook...

---
- hosts: concourse_db
  roles:
    - role: geerlingguy.postgresql
      tags:
        - concourse_db
      vars:
        postgresql_restarted_state: "restarted"
        postgresql_service_state: started
        postgresql_service_enabled: true
        postgresql_hba_entries:
          - { type: local, database: all, user: postgres, auth_method: peer }
          - { type: local, database: all, user: all, auth_method: peer }
          - { type: host, database: all, user: all, address: '127.0.0.1/32', auth_method: md5 }
          - { type: host, database: all, user: all, address: '::1/128', auth_method: md5 }
        postgresql_locales:
          - 'en_US.UTF-8'
        postgresql_databases:
          - name: concourse
            owner: concourse
            login_user: postgres
            state: present
        postgresql_users:
          - name: concourse
            password: concoursepassword
            encrypted: true
            db: concourse
            login_user: postgres
            state: present
...

Give the following errors:

TASK [geerlingguy.postgresql : Ensure PostgreSQL is started and enabled on boot.] ***************************************************************************************************
ok: [10.144.0.137]

TASK [geerlingguy.postgresql : Ensure PostgreSQL databases are present.] ************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: ProgrammingError: role "concourse" does not exist
failed: [10.144.0.137] (item={'name': 'concourse', 'owner': 'concourse', 'login_user': 'postgres', 'state': 'present'}) => {"changed": false, "item": {"login_user": "postgres", "name": "concourse", "owner": "concourse", "state": "present"}, "msg": "Database query failed: role \"concourse\" does not exist\n"}
	to retry, use: --limit @/Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/playbook_2.retry

I'm not sure if its supposed to be using postgres user or the user I created, concourse in this instance.

Ansible-Galaxy requirements file

---
# https://galaxy.ansible.com/geerlingguy/postgresql
- src: geerlingguy.postgresql
  version: 1.4.5
...

Target System Information

cloud-user@concourse-ci-db.novalocal:~ ( concourse-ci-db.novalocal )
12:38:42 $ uname -a
Linux concourse-ci-db.novalocal 3.10.0-957.1.3.el7.x86_64 #1 SMP Mon Nov 26 17:43:08 PST 2018 x86_64 x86_64 x86_64 GNU/Linux
cloud-user@concourse-ci-db.novalocal:~ ( concourse-ci-db.novalocal )
12:38:44 $ cat /etc/oracle-release
Oracle Linux Server release 7.6

Ansible and Ansible Playbook versions

$ ansible --version && ansible-playbook --version
ansible 2.7.6
  config file = /Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/ansible.cfg
  configured module search path = ['/Users/pd028300/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/pd028300/.pyenv/versions/3.7.2/lib/python3.7/site-packages/ansible
  executable location = /Users/pd028300/.pyenv/versions/3.7.2/bin/ansible
  python version = 3.7.2 (default, Jan 22 2019, 09:35:39) [Clang 9.0.0 (clang-900.0.39.2)]
ansible-playbook 2.7.6
  config file = /Users/pd028300/Documents/development/Ansible/concourse_ci_playbook/ansible.cfg
  configured module search path = ['/Users/pd028300/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/pd028300/.pyenv/versions/3.7.2/lib/python3.7/site-packages/ansible
  executable location = /Users/pd028300/.pyenv/versions/3.7.2/bin/ansible-playbook
  python version = 3.7.2 (default, Jan 22 2019, 09:35:39) [Clang 9.0.0 (clang-900.0.39.2)]

[FilBot3]

Seems to be related to this: PR #31

on these lines:

• https://github.com/geerlingguy/ansible-role-postgresql/blob/master/tasks/main.yml#L21-L23

[FilBot3]

Switching the databases.yml and users.yml yielded new errors.

TASK [geerlingguy.postgresql : Ensure PostgreSQL is started and enabled on boot.] ***************************************************************************************************
ok: [10.144.0.137]

TASK [geerlingguy.postgresql : Ensure PostgreSQL users are present.] ****************************************************************************************************************
failed: [10.144.0.137] (item=None) => {"censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result", "changed": false}
fatal: [10.144.0.137]: FAILED! => {"censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result", "changed": false}

[abdrehma]

@predatorian3 I just tested this in case you're wondering:

This occurs because users are created before databases. So when you create a user and reference a database - there's no database to reference and it errors out

And then you create a database to reference a user - well, there's no user. You have to create a user first with no db reference, then create the db.

[stale]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[IDerr]

Had the same issue today with ansible galaxy version

[stale]

This issue is no longer marked for closure.

[stale]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[ahmedbodi]

Experiencing the same issue too

[stale]

This issue is no longer marked for closure.

[stale]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[ahmedbodi]

not stale

[stale]

This issue is no longer marked for closure.

[stale]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[ahmedbodi]

not stale

[stale]

This issue is no longer marked for closure.

[jpboliv]

I was experiencing a similar issue with the following failure
TASK [geerlingguy.postgresql : Ensure PostgreSQL users are present.] ***************************** failed: [production] (item=None) => {"censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result", "changed": false} fatal: [production]: FAILED! => {"censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result", "changed": false}

I recommend you to try setting postgres_users_no_log: false on your variables file and see what the error is about. In my case, it was related to the dependency psycopg2 (which I was already installing with python3-psycopg2, but was missing ansible_python_interpreter: /usr/bin/python3 on my variables)

[stale]

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

Please read this blog post to see the reasons why I mark issues as stale.

[stale]

This issue has been closed due to inactivity. If you feel this is in error, please reopen the issue or file a new issue with the relevant details.