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.

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