Ansible - problem with adding Postgresql user

August 15, 2023

Today I strugled a bit with adding a PostgreSQL user using Ansible. The fault was on my side so I am making this note to avoid it in the future.

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 user postgres was created during the installation of the Postresql and Ansible was supposed to use it to create a new account, here called “mynewuser”.

But when I ran the playbook, I kept getting this error:

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

And these errors in the /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

The problem was caused by my assuption that Ansible uses local connection (using a Unix socket). I’ve already modified the line bellow in the pg_hba.conf and the trust authentication method was supposed to make adding the user possible, but it didn’t.

local   all     postgres        trust

As should be clear from the messages in the postgresql-11-main.log, that assumption was wrong, Ansible uses host connetion (TCP/IP connection from localhost) and the relevant lines are these:

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

So to be able to create the Postresql user, I have to first set these authentication methods to trust, do my changes and than revert them back to the md5.

For instance the part that changes the authentication 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

As often lately, the awesome ChatGPT helped a lot :-)