Create a PostgreSQL User or Role - Ansible module postgresql_user
How to automate the creation of a “myuser” user/role on PostgreSQL using Ansible Playbook and postgresql_user module.
How to Create 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 Create a PostgreSQL User/Role
community.postgresql.postgresql_user- Create, alter, or remove a user (role) from a PostgreSQL server instance
Let’s talk about the Ansible module postgresql_user.
The full name is community.postgresql.postgresql_user, 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 create, alter, or remove a user (role) from a PostgreSQL server instance.
This module uses psycopg2, a Python PostgreSQL User library. You must ensure that python3-psycopg2 is installed on the host before using this module.
Parameters
- name string - Name of User
- state string - present/absent - The user (role) state
- password string - Password cleartext or MD5-hashed
- db string - Grant user permission to the database
Let me summarize the main parameters of the module postgresql_user.
Ansible supposes that PostgreSQL is in the target node.
The only required parameter is name, the name of the user to interact with.
The parameter state specify the desired user (role) state. The option “present” means that the user/role should be created. The option absent means that the user/role should be deleted.
You could specify the desired password in the password parameter in cleartext or MD5-hashed format.
You could also specify a database parameter to specify the name of the database to connect to and where the user’s permissions are granted. You could also perform the same operation using the postgresql_privs Ansible 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
demo
Let’s jump into a real-life Ansible Playbook to Create a PostgreSQL User now called Role.
I’m going to show you how to create the myuser user (role) in the current PostgreSQL server.
code
---
- name: postgresql demo
hosts: all
become: true
vars:
db_user: myuser
db_password: MySecretPassword123
tasks:
- name: Utility present
ansible.builtin.package:
name: python3-psycopg2
state: present
- name: Create db user
community.postgresql.postgresql_user:
state: present
name: "{{ db_user }}"
password: "{{ db_password }}"
become: true
become_user: postgres
execution
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_create.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Create db user] *************************************************************************************
changed: [demo.example.com]
PLAY RECAP ************************************************************************************************
demo.example.com : ok=3 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
idempotency
$ ansible-playbook -i virtualmachines/demo/inventory postgresql/user_create.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [demo.example.com]
TASK [Utility present] ************************************************************************************
ok: [demo.example.com]
TASK [Create 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
Only postgres user/role present.
$ ssh [email protected]
[[email protected] ~]$ sudo su
[[email protected] devops]# sudo - postgres
sudo: -: command not found
[[email protected] devops]# su - postgres
Last login: Wed Jun 8 15:39:34 UTC 2022 on pts/0
[[email protected] ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \q
[[email protected] ~]$
after execution
The user/role list has: postgres and myuser.
$ ssh [email protected]
[[email protected] ~]$ sudo su
[[email protected] devops]# su - postgres
Last login: Thu Jun 9 15:17:16 UTC 2022 on pts/0
[[email protected] ~]$ psql
psql (10.21)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
myuser | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \q
[[email protected] ~]$
Recap
Now you know how to Create 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
