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?

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.