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
– Steve Lorimer, Commented Oct 9, 2014 at 0:48

Nathan Smith · Accepted Answer · 2017-07-18 09:13:43Z

68

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, 2017 at 9:13

Nathan Smith

6811 gold badge10 silver badges24 bronze badges

answered Mar 18, 2015 at 20:48

gitaarik

47k12 gold badges101 silver badges109 bronze badges

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

2 Comments

Udhayakumar Over a year ago

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" }

Udhayakumar Over a year ago

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

Most Wanted · Accepted Answer · 2016-02-15 20:44:25Z

24

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, 2016 at 20:44

Most Wanted

7,2498 gold badges64 silver badges82 bronze badges

1 Comment

hudolejev Over a year ago

Ansible < 1.9: sudo: yes; sudo_user: postgres

Dylan Pierce · Accepted Answer · 2020-06-15 12:49:04Z

21

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, 2020 at 12:49

answered Sep 8, 2016 at 14:52

Dylan Pierce

4,6883 gold badges41 silver badges49 bronze badges

5 Comments

Dylan Pierce Over a year ago

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

lolcode Over a year ago

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

Andreas Florath Over a year ago

This did not work for me. I needed to install the acl tools as described: stackoverflow.com/questions/36646880/…

Haakon Over a year ago

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

Peter Nerlich Over a year ago

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

René Pijl · Accepted Answer · 2016-03-09 15:16:57Z

5

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.

answered Mar 9, 2016 at 15:16

René Pijl

4,7581 gold badge25 silver badges26 bronze badges

Comments

mdh · Accepted Answer · 2017-02-25 14:28:11Z

4

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

answered Feb 25, 2017 at 14:28

mdh

5,6235 gold badges32 silver badges33 bronze badges

1 Comment

frostymarvelous Over a year ago

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

bugged · Accepted Answer · 2019-06-06 10:40:35Z

1

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.

answered Jun 6, 2019 at 10:40

bugged

631 silver badge7 bronze badges

Comments

Rooie3000 · Accepted Answer · 2018-07-16 09:46:33Z

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

Léo Benoist · Accepted Answer · 2020-08-14 14:09:00Z

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, 2020 at 14:09

Léo Benoist

2,5511 gold badge21 silver badges18 bronze badges

Comments

Srijeyanthan · Accepted Answer · 2021-09-11 13:27:17Z

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.

Laurent B · Accepted Answer · 2014-09-29 21:10:36Z

-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, 2014 at 21:10

Laurent B

811 silver badge5 bronze badges

Collectives™ on Stack Overflow

PostgreSQL failing peer authentication with Ansible

10 Answers 10

2 Comments

1 Comment

5 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

2 Comments

1 Comment

5 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related