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
Certifications
- Coursera Pro - Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs—all included in your subscription
Video Course
Printed Book
-
Ansible For VMware by Examples
-
Ansible for Kubernetes by Example
-
Hands-on Ansible Automation
-
Red Hat Ansible Automation Platform
eBooks
- Ansible by Examples: 200+ Automation Examples For Linux and Windows System Administrator and DevOps
- Ansible Cookbook: A Comprehensive Guide to Unleashing the Power of Ansible via Best Practices, Troubleshooting, and Linting Rules with Luca Berton
- Terraform By Example: A Practical Approach for Beginners to Learn Cloud Infrastructure with Terraform
- 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 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 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]
[devops@demo ~]$ sudo su
[root@demo devops]# sudo - postgres
sudo: -: command not found
[root@demo devops]# su - postgres
Last login: Wed Jun 8 15:39:34 UTC 2022 on pts/0
[postgres@demo ~]$ 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
[postgres@demo ~]$
after execution
The user/role list has: postgres
and myuser
.
$ ssh [email protected]
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Thu Jun 9 15:17:16 UTC 2022 on pts/0
[postgres@demo ~]$ 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
[postgres@demo ~]$
Recap
Now you know how to Create 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