Ansible Pilot

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

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

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.

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

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 and Twitter to not miss the next episode of the Ansible Pilot.

Academy

Learn the Ansible automation technology with some real-life examples in my

My book Ansible By Examples: 100+ Automation Examples For Linux and Windows System Administrator and DevOps

Access the Complete Book and Learn Quick Ansible by 200+ Examples

Want to keep this project going? Please donate

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

Subscribe not to miss any new releases

June 9, 2022

FREE Top 10 Best Practices

Top 10 Best Practices of Ansible Automation: save time, reduce errors and stress