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.![](/articles/ansible_postgresql_user_md5.png)
![Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons](/banner-video.jpg)
YouTube Video
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
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, and Website, X (formerly 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: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
Donate
Want to keep this project going? Please donate