PostgreSQL failing peer authentication with Ansible

Question

I am running PostgreSQL 9.3 on FreeBSD. FreeBSD uses pgsql as the default system user for PostgreSQL. My /usr/local/pgsql/data/pg_hba.conf looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             pgsql                                   peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

With this configuration I can connect to the database as pgsql without a password.

$ su pgsql
$ psql template1
template1=# \l
                         List of databases
...

That works as intended.

On a remote machine, I have an Ansible task to create a database on the FreeBSD server.

- name: Create the postgresql database
  postgresql_db: name=mydatabase login_user=pgsql

Executing this task fails with the error Peer authentication failed for user "pgsql".

PLAY [web] ********************************************************************

GATHERING FACTS ***************************************************************
ok: [host.example.org]

TASK: [database | Create the postgresql database] *****************************
failed: [host.example.org] => {"failed": true}
msg: unable to connect to database: FATAL:  Peer authentication failed for user "pgsql"


FATAL: all hosts have already failed -- aborting

Why does this fail when peer authentication for the user pgsql is clearly working?

Jun

Do you have to run the ansible module as the pgsql user? eg, add: sudo: true and sudo_user: pgsql to your command – Mezzotint

Have you eventually find out any solution to this? – Slover

Answer 1

This worked for me:

- name: Create postgres database
  become: true
  become_user: postgres
  postgresql_db:
    name: <database-name>

In your specific case the user might be pgsql, but I think usually the user is postgres.

Osbourn

it showing the below error fatal: [server_1]: FAILED! => { "msg": "Failed to set permissions on the temporary files Ansible needs to create when becoming an unprivileged user (rc: 1, err: chmod: invalid mode: ‘A+user:postgres:rx:allow’\nTry 'chmod --help' for more information.\n}). For information on working around this, see https://docs.ansible.com/ansible-core/2.16/playbook_guide/playbooks_privilege_escalation.html#risks-of-becoming-an-unprivileged-user" } – Needy

For the above issue, this is worked for me github.com/georchestra/ansible/issues/55#issuecomment-588313638 – Needy

Answer 2

Or with slightly different syntax (from Ansible 1.9) and for user creation (might be helpful for someone)

- name: Create postgres user
  postgresql_user: name={{ pg_user }} password={{ pg_password }}
  become: true
  become_user: postgres

Conductive

Ansible < 1.9: sudo: yes; sudo_user: postgres – Mirisola

Answer 3

For those running into "Failed to set permissions on the temporary files Ansible needs to create..." in order to switch to the postgres user with become_user you can leverage pipelining on Ubuntu hosts.

Create a ansible.cfg in your playbook directory and add the following lines:

[ssh_connection]
pipelining=True

Update: according to @lolcode Ansible 2.9.0 has updated to ansible_pipelining

   [ssh_connection]
   ansible_pipelining = true

Update 4/30/2020: for those who still have issues, try installing acl which will cause Ansible to use this acl filesystem to mount module that need to be accessible by the 2nd user instead of making them readable by everyone. Thanks @Andreas Florath

- name: install setfacl support
  become: yes
  apt: pkg=acl

Inaudible

Thanks for saving hours of my life :) – Paramorph

No problem! Countless others have done the same for me. Pass it forward. – Inaudible

The pipelining = True answer worked for me but on ansible 2.9.0 the format has changed to ansible_pipelining = true as per [this ansible issue][1] [1]: github.com/ansible/ansible/issues/31125#issuecomment-333847610 – Tai

This did not work for me. I needed to install the acl tools as described: #36647380 – Spiritualize

@DylanPierce Note that you spelled ansible_pipelining a bit wrong in your edit (you wrote piplining). – Sarena

Thank you so much for the 2020 update. I have no idea about postgresql and stuff, just trying to cobble something together. Without the hint about acl I feel like this would have become a very, very long search... so, much <3 – Igor

Answer 4

I had the same problem. In my case I overlooked that I configured my Ansible-playbook to run as another Linux user than the one with peer access (pgsql in your case). Solution is either run the Ansible play as pgsql:

- name: Create the postgresql database
  remote_user: pgsql
  postgresql_db: name=mydatabase login_user=pgsql
  ...

Or run it as root, and su to pgsql for the command:

- name: Create the postgresql database
  remote_user: root
  become: yes
  become_user: pgsql
  postgresql_db: name=mydatabase login_user=pgsql
  ...

... depending on your access rights via ssh.

This is using Ansible 2.0.

Eusebioeusebius

Answer 5

Another workaround is to connect via host (localhost) rather than the default local peer authentication method:

- name: Create the postgresql database
  postgresql_db:
    name: mydatabase
    login_user: postgres
    login_host: 127.0.0.1

Depending on the settings in pg_hba.conf, you may also need to provide login_password. You can circumvent this by setting

host    all         postgres        127.0.0.1/32            md5

to

host    all         postgres        127.0.0.1/32            trust

Jester

You helped me indirectly. I was using login_host: "{{ item.login_host | default('localhost') }}". Switching to default('omit') solved my issue. – Antechamber

Answer 6

I notice your Postgres version is really out of date (9.3). I had this issue recently when working on an Ubuntu 14 server with Postgres 9.3.

I tried a dozen different things, and finally what worked was installing the acl package via apt. Ansible uses it for navigating some of it's permissions issues. The package is installed by default on newer distros, hence why I've only seen this problem crop up on an old server.

Impressive

Answer 7

Thanks to this threat I made a variant of mdh's post. When I set up a database I generate a password for the postgres user and I store it in a file under the root directory.

I thought why not store it also (or instead) in a .pgpass file for root. So I created a template like this (only last line is important):

#### password file for posgres connection ###

#### *:*:*:*
#### works like
####    *      :      *    :     *     :     *
#### <ip addr> : <port nr> : <db name> : <password>

127.0.0.1:*:*:postgres:{{ new_postgres_pass }}

Store the .pgpass file in the home directory of root. Now you can use the module as root without switching user of having to change the pg_hba.conf:

- name: Ensure postgresql mydatabase
  postgresql_db:
    name: mydatabase
    login_user: postgres
    login_host: 127.0.0.1

Narcoanalysis

Answer 8

If you don't have sudo (debian, etc) but have access to root

- name: Create database
  remote_user: root
  become: yes
  become_method: su
  become_user: postgres
  postgresql_db:
    name: my_db

Fiord

Answer 9

This problem due to postgres authentication methods configured at /etc/postgresql/10/main/pg_hba.conf , the configuration location may vary depending on the version installed, /etc/postgresql//main/pg_hba.conf

sample ansible recipe
---
- name : "Dump PostgreSQL database"
  gather_facts: true
  hosts: posgre_host
  tasks:
    - name: Dump existing PostgreSQL database
      community.postgresql.postgresql_db:
        name: demo
        state: dump
        target: /tmp/backup/backup.sql.gz
      become: true

From

local           all           postgres           peer

To

local           all             postgres        trust

Here trust authentication method anyone connect to the server is authorized to access the database, since ansible is working based on ssh, it is safe to change.

Brooklet

Answer 10

So if I understood well you are on a remote machine, and maybe you should change /usr/local/pgsql/data/pg_hba.conf to allow remote connections 'host all all 0.0.0.0/0 md5' or another specific network address.

Carney