Question 246981 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Peer authentication failed when using Ansible to create a database user

I’m trying to use Ansible to create a PostgreSQL database user, but I’m encountering the following error:

TASK [Create db user] ********************************************************************** fatal: [test]: FAILED! => {"changed": false, "msg": "unable to connect to database: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres""}



Here is my Ansible playbook:


```yaml
---
- name: postgresql demo
  hosts: all
  become: true
  become_method: sudo
  vars:
    db_user: myuser
    db_password: MySecretPassword123

  tasks:
    - name: Utility present
      ansible.builtin.package:
        name: python3-psycopg2
        state: present

    - name: Create db user
      community.postgresql.postgresql_user:
        state: present
        name: "{{ db_user }}"
        password: "{{ db_password }}"

I’ve tried various solutions, including changing the become_user, but the issue persists. How can I resolve the "Peer authentication failed" error when using Ansible to create a PostgreSQL user?

Tags:

2

Answers


  1. 0

    The Peer authentication failed problem frequently happens because PostgreSQL by default use the peer authentication technique for local connections. In order to use this technique, a PostgreSQL username must match the operating system username. PostgreSQL makes an attempt to authenticate you as the postgres user when you perform tasks as that user in your Ansible playbook, which results in the error.

    You can either alter the authentication process or explicitly specify a host for the connection in order to fix this. If you decide to specify a host, making it localhost requires Ansible to use TCP/IP rather than the Unix domain socket to connect to PostgreSQL, thereby disabling the Peer authentication technique. As demonstrated in the previous response, you may accomplish this by include the PGHOST environment variable in your Ansible task. Another option is to change the pg_hba.conf file in PostgreSQL to allow peer authentication for the postgres user without a password. However, exercise caution while making changes to the pg_hba.conf file since they may affect the security of your PostgreSQL system.

    Login or Signup to reply.
  2. 0

    I don’t know what the operating system user is that Ansible is running under; let’s call it ansible. Peer authentication fails because the name of the operating system user is different from the name of the database user.

    You can work around that by specifying a mapping. In pg_hba.conf change the peer line to

    # TYPE  DATABASE      USER        ADDRESS       METHOD
local   all           all                       peer map=foransible

    Then add the following to pg_ident.conf:

    # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
foransible      ansible                 postgres
foransible      postgres                postgres

    Don’t forget to reload PostgreSQL after changing the files. Then operating system user ansible can connect as database user postgres.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search