Configure PostgreSQL with Ansible: User Access and Service Management — Video Tutorial

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 the`myuser` 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 unreachable=0 faile