Allow md5 Connection for a PostgreSQL User / Role - Ansible module postgresql_pg_hba

How to automate the creation of a PostgreSQL authentication file pg_hba.conf to allow the “myuser” user/role to connect using md5 authentication on PostgreSQL using Ansible Playbook and postgresql_pg_hba module.

June 9, 2022

Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons

#ansible #database #module #postgresql_user #authentication #postgresql #user #role #md5

How to Allow md5 Connection for a PostgreSQL User / Role with Ansible?

I’m going to show you a live demo with some simple Ansible code. I’m Luca Berton and welcome to today’s episode of Ansible Pilot

Ansible Allow md5 Connection for a PostgreSQL User / Role

community.postgresql.postgresql_pg_hba
Add, remove or modify a rule in a pg_hba file

Let’s talk about the Ansible module postgresql_pg_hba. The full name is community.postgresql.postgresql_pg_hba, which means that is part of the collection of modules “community.postgresql” maintained by the Ansible Community to interact with PostgreSQL. The collection is tested with ansible-core version 2.11+, prior versions such as 2.9 or 2.10 are not supported. The purpose of the module is to Add, remove or modify a rule in a pg_hba file. This module uses psycopg2, a Python PostgreSQL User library. You must ensure that python3-psycopg2 is installed on the host before using this module.

demo

Let’s jump into a real-life Ansible Playbook to Allow md5 Connection for a PostgreSQL User / Role now called Role. I’m going to show you how to create a pg_hba.conffile to allow themyuser user/role to connect to the current PostgreSQL server using md5 authentication.

code

---
- name: postgresql demo
  hosts: all
  become: true
  vars:
    db_user: myuser

  tasks:
    - name: Utility present
      ansible.builtin.package:
        name: python3-psycopg2
        state: present

    - name: Allow md5 connection for the db user
      community.postgresql.postgresql_pg_hba:
        dest: "~/data/pg_hba.conf"
        contype: host
        databases: all
        method: md5
        users: "{{ db_user }}"
        create: true
      become: true
      become_user: postgres
      notify: Restart service

  handlers:
    - name: Restart service
      ansible.builtin.service:
        name: postgresql
        state: restarted

execution

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_md5.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Allow md5 connection for the db user] ***************************************************************
changed: [demo.example.com]
RUNNING HANDLER [Restart service] *************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=4    changed=2    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

idempotency

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_md5.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Allow md5 connection for the db user] ***************************************************************
ok: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com           : ok=3    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

before execution

$ ssh [email protected]
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Thu Jun  9 15:19:14 UTC 2022 on pts/0
[[email protected] ~]$ psql -h localhost -U myuser
psql: FATAL:  Ident authentication failed for user "myuser"
[[email protected] ~]$

after execution

$ ssh [email protected]
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
[[email protected] ~]$ psql -h localhost -U myuser
Password for user myuser: 
psql: FATAL:  database "myuser" does not exist
[[email protected] ~]$

code with ❤️ in GitHub

Recap

Now you know how to Allow md5 Connection for a PostgreSQL User / Role with Ansible. Subscribe to the YouTube channel, Medium, Website, Twitter, and Substack to not miss the next episode of the Ansible Pilot.