Ansible: Problem with Adding a PostgreSQL User

2023-08-15

I struggled to add a PostgreSQL user using Ansible. The fault was mine. This note is here so I don’t repeat it.

The task looked like this:

- name: Create PostgreSQL user
  become: yes
  become_user: postgres
  postgresql_user:
    name: mynewuser 
    password: 'mystrongpassword'
    encrypted: yes
    login_host: localhost
    login_user: postgres

The postgres user was created when PostgreSQL was installed, and Ansible was supposed to use it to create a new account, mynewuser.

But the playbook kept failing with:

TASK [postgresql_configure : Create PostgreSQL user] **
fatal: [test.mensik.net]: FAILED! => changed=false 
  msg: |-
    unable to connect to database: fe_sendauth: no password supplied

The following errors also appeared in /var/log/postgresql/postgresql-11-main.log:

2023-08-15 13:35:26.060 CEST [8392] postgres@postgres FATAL:  password authentication failed for user "postgres"
2023-08-15 13:35:26.060 CEST [8392] postgres@postgres DETAIL:  User "postgres" has no password assigned.
	Connection matched pg_hba.conf line 78: "host	all	all	::1/128	md5"

My /etc/postgresql/11/main/pg_hba.conf looked like this:

local   replication     all     peer
local   all     postgres        trust
local   all     all     md5
host    replication     all     127.0.0.1/32    md5
host    replication     all     ::1/128 md5
host    all     all     127.0.0.1/32            md5
host    all     all     ::1/128                 md5

I assumed Ansible used a local connection over a Unix socket. I had set the line below to trust, expecting that to let me add the user. It didn’t:

local   all     postgres        trust

The log makes the reason clear: Ansible connects over TCP/IP from localhost, so these are the relevant lines:

host    all     all     127.0.0.1/32            md5
host    all     all     ::1/128                 md5

So to create the user, I first set these to trust, make my changes, then revert them to md5.

The part that switches the methods to trust looks like this:

- name: Set trust authentication for localhost
  lineinfile:
    dest: /etc/postgresql/11/main/pg_hba.conf
    regexp: '^(host|local)\s+all\s+all\s+::1/128'
    line: 'host    all     all     ::1/128                 trust'
    state: present
  become: yes

- name: Set trust authentication for 127.0.0.1/32
  lineinfile:
    dest: /etc/postgresql/11/main/pg_hba.conf
    regexp: '^(host|local)\s+all\s+all\s+127.0.0.1/32'
    line: 'host    all     all     127.0.0.1/32            trust'
    state: present
  become: yes