Postgresql (Peer Authentication Error) with Ansible 2.4

[Bishwajit Samanta]

Hi, i am having a problem in setting up postgresql through ansible.

Error::-

----------

 Peer authentication failed for user "postgres". I checked google, i found people as saying i need to use become and become_user: postgress. But even after doing that i am getting, that those are not valid attribute .

My code is :: 

-----------------

- name: Install Postgress 9.5 Version and its dependencies 

  apt:

    name: "{{ item }}"

    update_cache: yes

    cache_valid_time: 3600

  with_items:

    - postgresql

    - libpq-dev 

    - python-psycopg2

    - postgresql-client-common

    - postgresql-contrib

 

- name: Make change in postgresql.conf file 

  template:

    src: postgresql_conf.jinja2

    dest: /etc/postgresql/9.5/main/postgresql.conf

    mode: 0644

    owner: postgres

    group: postgres 

- name: Daemon-Reload for Postgress

  systemd:

    state: restarted

    daemon-reload: yes 

    name: postgresql

- name: create a new postgresql Database

  postgresql_db:

    name: "{{ my_db_name }}"

    # become: true 

    # become_user: postgres

- name: Create Postgress User 

  postgresql_user:

    name: "{{ my_db_create_name }}"

    password: "{{ my_db_password }}"

    priv: "CONNECT/products:ALL"

    # become: true 

    # become_user: postgres

 

- name: Ensuring no other user have Database Access

  postgresql_privs:

    db: "{{ my_db_create_name }}"

    role: PUBLIC

    type: database 

    priv: ALL

    state: absent  

    # become: true 

    # become_user: postgres

Can anyone help me ?

[Kai Stian Olstad]

On 15.04.2018 11:18, Bishwajit Samanta wrote:
> Hi, i am having a problem in setting up postgresql through ansible.
>
> Error::-
> ----------
>
>
> Peer authentication failed for user "postgres". I checked google, i
> found
> people as saying i need to use become and become_user: postgress. But
> even
> after doing that i am getting, that those are not valid attribute .
>
>

> - name: create a new postgresql Database
> postgresql_db:
> name: "{{ my_db_name }}"
> # become: true
> # become_user: postgres
>
>
> - name: Create Postgress User
> postgresql_user:
> name: "{{ my_db_create_name }}"
> password: "{{ my_db_password }}"
> priv: "CONNECT/products:ALL"
> # become: true
> # become_user: postgres
>
>
> - name: Ensuring no other user have Database Access
> postgresql_privs:
> db: "{{ my_db_create_name }}"
> role: PUBLIC
> type: database
> priv: ALL
> state: absent
> # become: true
> # become_user: postgres
>
>
> Can anyone help me ?

become and become_user is directives for the task not the module, so
they need to be align on the same level as postgres_xxxx and name.

--
Kai Stian Olstad

[Bishwajit Samanta]

Thanks Kai, that problem got solved. but after that 1 more problem created. Below is the error log. Now i am able to create database.

Error::-

---------

TASK [database : Install Postgress 9.5 Version and its dependencies] ********************************************************************

Sunday 15 April 2018  15:46:32 +0530 (0:00:00.036)       0:00:00.036 **********

changed: [192.168.56.121] => (item=[u'postgresql', u'libpq-dev', u'python-psycopg2', u'postgresql-client-common', u'postgresql-contrib'])

TASK [database : Make change in postgresql.conf file] ***********************************************************************************

Sunday 15 April 2018  15:47:00 +0530 (0:00:27.474)       0:00:27.511 **********

ok: [192.168.56.121]

TASK [database : Daemon-Reload for Postgress] *******************************************************************************************

Sunday 15 April 2018  15:47:01 +0530 (0:00:01.188)       0:00:28.699 **********

changed: [192.168.56.121]

TASK [database : create a new postgresql Database] **************************************************************************************

Sunday 15 April 2018  15:47:05 +0530 (0:00:04.637)       0:00:33.337 **********

changed: [192.168.56.121]

TASK [database : Create Postgress User] *************************************************************************************************

Sunday 15 April 2018  15:47:07 +0530 (0:00:01.552)       0:00:34.890 **********

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.ProgrammingError: relation "products" does not exist

fatal: [192.168.56.121]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n  File \"/tmp/ansible_9cE3qn/ansible_module_postgresql_user.py\", line 846, in <module>\n    main()\n  File \"/tmp/ansible_9cE3qn/ansible_module_postgresql_user.py\", line 815, in main\n    changed = grant_privileges(cursor, user, privs) or changed\n  File \"/tmp/ansible_9cE3qn/ansible_module_postgresql_user.py\", line 602, in grant_privileges\n    grant_funcs[type_](cursor, user, name, privileges)\n  File \"/tmp/ansible_9cE3qn/ansible_module_postgresql_user.py\", line 491, in grant_table_privileges\n    cursor.execute(query)\n  File \"/usr/lib/python2.7/dist-packages/psycopg2/extras.py\", line 120, in execute\n    return super(DictCursor, self).execute(query, vars)\npsycopg2.ProgrammingError: relation \"products\" does not exist\n\n", "module_stdout": "", "msg": "MODULE FAILURE", "rc": 1}

[Kai Stian Olstad]

To me is looks like psycopg2 is missing some feature so you probably
needs a new version of it.

--
Kai Stian Olstad