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?

Do you have to run the ansible module as the `pgsql` user? eg, add: `sudo: true` and `sudo_user: pgsql` to your command — Steve Lorimer, Oct 09 '14 at 00:48

score 66 · Answer 1 · edited Jul 18 '17 at 09:13

66

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.

edited Jul 18 '17 at 09:13

Nathan Smith

683
1
10
24

answered Mar 18 '15 at 20:48

gitaarik

42,736
12
98
105

score 23 · Answer 2 · answered Feb 15 '16 at 20:44

23

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

answered Feb 15 '16 at 20:44

Most Wanted

6,254
5
53
70

2

Ansible < 1.9: `sudo: yes; sudo_user: postgres` – hudolejev Jul 18 '16 at 14:12

Dylan Pierce · Answer 3 · 2020-06-15T12:49:04.387

19

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

edited Jun 15 '20 at 12:49

answered Sep 08 '16 at 14:52

Dylan Pierce

4,313
3
35
45

2

Thanks for saving hours of my life :) – Amir Rustamzadeh Nov 05 '16 at 05:40
2

No problem! Countless others have done the same for me. Pass it forward. – Dylan Pierce Nov 29 '16 at 14:54
1

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]: https://github.com/ansible/ansible/issues/31125#issuecomment-333847610 – lolcode Nov 20 '19 at 21:25
2

This did not work for me. I needed to install the acl tools as described: https://stackoverflow.com/questions/36646880/ansible-2-1-0-using-become-become-user-fails-to-set-permissions-on-temp-file/36681626 – Andreas Florath Apr 10 '20 at 19:34
1

@DylanPierce Note that you spelled `ansible_pipelining` a bit wrong in your edit (you wrote `piplining`). – Haakon Jun 14 '20 at 20:59
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 – Peter Nerlich May 29 '21 at 14:47

score 5 · Answer 4 · answered Mar 09 '16 at 15:16

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.

score 4 · Answer 5 · answered Feb 25 '17 at 14:28

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

You helped me indirectly. I was using login_host: "{{ item.login_host | default('localhost') }}". Switching to default('omit') solved my issue. — frostymarvelous, Apr 19 '17 at 17:35

score 1 · Answer 6 · answered Jun 06 '19 at 10:40

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.

score 0 · Answer 7 · answered Jul 16 '18 at 09:46

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

score 0 · Answer 8 · answered Aug 14 '20 at 14:09

0

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

answered Aug 14 '20 at 14:09

Léo Benoist

2,511
1
20
18

score 0 · Answer 9 · answered Sep 11 '21 at 13:27

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.

score -4 · Answer 10 · answered Sep 29 '14 at 21:10

-4

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.

answered Sep 29 '14 at 21:10

Laurent B

81
1
5

PostgreSQL failing peer authentication with Ansible

10 Answers10