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.


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.
Links
The Best Resources For Ansible
Video Course
Printed Book
eBooks
- Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
- Ansible For Windows By Examples: 50+ Automation Examples For Windows System Administrator And DevOps
- Ansible For Linux by Examples: 100+ Automation Examples For Linux System Administrator and DevOps
- Ansible Linux Filesystem By Examples: 40+ Automation Examples on Linux File and Directory Operation for Modern IT Infrastructure
- Ansible For Containers and Kubernetes By Examples: 20+ Automation Examples To Automate Containers, Kubernetes and OpenShift
- Ansible For Security by Examples: 100+ Automation Examples to Automate Security and Verify Compliance for IT Modern Infrastructure
- Ansible Tips and Tricks: 10+ Ansible Examples to Save Time and Automate More Tasks
- Ansible Linux Users & Groups By Examples: 20+ Automation Examples on Linux Users and Groups Operation for Modern IT Infrastructure
- Ansible For PostgreSQL by Examples: 10+ Examples To Automate Your PostgreSQL database
- Ansible For Amazon Web Services AWS By Examples: 10+ Examples To Automate Your AWS Modern Infrastructure
- Ansible Automation Platform By Example: A step-by-step guide for the most common user scenarios
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]
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Thu Jun 9 15:19:14 UTC 2022 on pts/0
[postgres@demo ~]$ psql -h localhost -U myuser
psql: FATAL: Ident authentication failed for user "myuser"
[postgres@demo ~]$
after execution
$ ssh [email protected]
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
[postgres@demo ~]$ psql -h localhost -U myuser
Password for user myuser:
psql: FATAL: database "myuser" does not exist
[postgres@demo ~]$
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.
Academy
Learn the Ansible automation technology with some real-life examples in my
My book Ansible By Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Donate
Want to keep this project going? Please donate