unable to create a postgres database using ansible

Question

this is my code:

- name: Instalation of postgresql-9.6
  apt:
    name: postgresql-9.6

- name: start postgresql service
  service: name=postgresql state=restarted enabled=yes

- name: create a database
  become_user: postgres
  postgresql_db:
    name: managys
    encoding: UTF-8
    lc_collate: de_DE.UTF-8
    template: template0
    state: present

- name: crate user for database
  become: yes
  become_user: postgres
  postgresql_user:
    db: template0
    name: Odoo
    password: Odoo
    priv: ALL
    state: present

and this is the error after executing the main playbook:

The full traceback is:

Traceback (most recent call last):
  File "/tmp/ansible_postgresql_db_payload_ce8a5D/__main__.py", line 421, in main
    db_connection = psycopg2.connect(database=maintenance_db, **kw)
  File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
OperationalError: FATAL:  Peer authentication failed for user "postgres"


fatal: [172.17.0.2]: FAILED! => {
    "changed": false, 
    "invocation": {
        "module_args": {
            "db": "managys", 
            "encoding": "UTF-8", 
            "lc_collate": "de_DE.UTF-8", 
            "lc_ctype": "", 
            "login_host": "", 
            "login_password": "", 
            "login_unix_socket": "", 
            "login_user": "postgres", 
            "maintenance_db": "postgres", 
            "name": "managys", 
            "owner": "", 
            "port": 5432, 
            "ssl_mode": "prefer", 
            "ssl_rootcert": null, 
            "state": "present", 
            "target": "", 
            "target_opts": "", 
            "template": "template0"
        }
    }, 
    "msg": "unable to connect to database: FATAL:  Peer authentication failed for user \"postgres\"\n"
}

Florian Neumann · Accepted Answer · 2022-12-09 16:35:51Z

2

The become_user: postgres will only work when the user with which ansible is getting executed is in the sudoers file of the target server. If not then we have to update the config file of the postgres.

The below code worked for me

---
- name: test
  hosts: localhost
  tasks:
    - name: Instalation of postgresql-9.6
      apt:
      name: postgresql-9.6

    - name: start postgresql service
      service: name=postgresql state=restarted enabled=yes

    - name: create a database
      postgresql_db:
        name: managys
        encoding: UTF-8
        template: template0
        state: present
      become_user: postgres
      become: yes

edited Dec 9, 2022 at 16:35

Florian Neumann

5,8951 gold badge45 silver badges51 bronze badges

answered Mar 19, 2019 at 13:30

error404

2,8532 gold badges17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

iyadhalaoui Over a year ago

fatal: [172.17.0.2]: FAILED! => { "changed": false, "module_stderr": "Shared connection to 172.17.0.2 closed.\r\n", "module_stdout": "/bin/sh: 1: sudo: not found\r\n", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 127 }

Collectives™ on Stack Overflow

unable to create a postgres database using ansible

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related