community.postgresql.postgresql_user: Create & Manage PostgreSQL Users

How to Create 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.

See also: Configure PostgreSQL with Ansible: User Access and Service Management

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.

See also: community.postgresql.postgresql_db: Create & Manage PostgreSQL Databases

Links

• community.postgresql.postgresql_user

## Playbook 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 Playbook
  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 Playbook] ************************************************************************************
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 Playbook] ************************************************************************************
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 devops@demo.example.com
[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@Playbook ~]$ 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@Playbook ~]$

after execution

The user/role list has: postgres and myuser.

$ ssh devops@demo.example.com
[devops@demo ~]$ sudo su
[root@demo devops]# su - postgres
Last login: Thu Jun  9 15:17:16 UTC 2022 on pts/0
[postgres@Playbook ~]$ 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@Playbook ~]$

code with ❤️ in GitHub

Conclusion

Now you know how to Create a PostgreSQL User/Role with Ansible.

See also: Install PostgreSQL in Debian-like systems - Ansible modules apt, stat, shell, service

Related Articles

• sudo and become in Ansible playbooks • Ansible Inventory Guide • the Ansible roles overview