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

I'm going to show you a live Playbook 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.

Links

  • [community.postgresql.postgresql_pg_hba](https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_pg_hba_module.html)

Playbook

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

``yaml

---

  • name: postgresql Playbook

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

``bash

$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_md5.yml

PLAY [postgresql Playbook] **

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 un