# PostgreSQL Create roles from list

**URL:** <https://forum.ansible.com/t/postgresql-create-roles-from-list/37045>\
**Category:** Ansible Project\
**Created:** [August 29, 2022, 4:06pm UTC](https://forum.ansible.com/t/postgresql-create-roles-from-list/37045 "2022-08-29T16:06:00Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sagan](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@Sagan](https://forum.ansible.com/u/Sagan)\
**Post date:** [August 29, 2022, 4:06pm UTC](https://forum.ansible.com/t/postgresql-create-roles-from-list/37045/1 "2022-08-29T16:06:00Z")

</div>

Objective:

- Create a file under a common format (yml, json)
- Create a PostgreSQL role for each user un file

Description:  
I’m trying to create alot of users and inserting them into their respective groups, the structure isn’t very complicated, but my current issue, is trying to find documentation or a solution to achieve the objectives here. I don’t know how to create this list, but indulge me in considering this:

example users.yml|json

```auto
------
# Classe roles
name: classeA
db: database
can_login: no

name: classeA
db: database
can_login: no

# Student roles
name: student1
db: database
login: yes
password: xxxx
member_of: classeA

name: student2
db: database
login: yes
db: school
password: xxxx
member_of: classeA

name: student3
db: database
login: yes
db: school
password: xxxx
member_of: classeA

```

Now, ansible, provides a way to create postgresql users like so

```auto
postgresql_user:
db: acme
name: django
password: ceec4eif7ya
priv: "CONNECT/products:ALL"
expires: "Jan 31 2020"

```

I’ve considered the following idea:

- using ansible.builtin.include\_vars to read the yml file, insert data into a variable LIST, and foreach element in that list, call the postgresql\_user module to create the user.

I would welcome any help regarding this.

---

<div class="post-metadata">

**Author:** ![Sagan](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@Sagan](https://forum.ansible.com/u/Sagan)\
**Post date:** [September 5, 2022, 2:24pm UTC](https://forum.ansible.com/t/postgresql-create-roles-from-list/37045/2 "2022-09-05T14:24:00Z")

</div>

if anyone founds this thread and has the same issue:

first of all your user list must be either a dictionnary or a list which in my example above, is neither. here is what it should look like

static\_roles:  
admin:  
canlogin: true  
member\_of:   
superuser: true  
valid\_until: ‘’  
eleves\_suisse:  
canlogin: false  
member\_of:   
superuser: false  
valid\_until: ‘’  
eleves\_canada:  
canlogin: false  
member\_of:   
superuser: false  
valid\_until: ‘’  
eleve1:  
canlogin: true  
member\_of: [eleves\_suisse]  
superuser: false  
valid\_until: ‘’  
eleve2:  
canlogin: true  
member\_of: [eleves\_suisse]  
superuser: false  
valid\_until: ‘’  
eleve3:  
canlogin: true  
member\_of: [eleves\_canada]  
superuser: false  
valid\_until: ‘’  
eleve4:  
canlogin: true  
member\_of: [eleves\_canada]  
superuser: false  
valid\_until: ‘’  
eleve5:  
canlogin: true  
member\_of: [eleves\_canada]  
superuser: false  
valid\_until: ‘’  
eleve6:  
canlogin: true  
member\_of: [eleves\_suisse]  
superuser: false  
valid\_until: ‘’  
eleve7:  
canlogin: true  
member\_of: [eleves\_canada]  
superuser: false  
valid\_until: ‘’  
postgres:  
canlogin: true  
member\_of:   
superuser: true  
valid\_until: ‘’

now regarding the creation of the user you’ll wanna run a task that looks something like this:

- name: “create roles”  
become: true  
become\_user: postgres  
postgresql\_user:  
name: “{{ item.key }}”  
role\_attr\_flags: “{{ ‘LOGIN’ if item.value.canlogin else ‘NOLOGIN’ }}”  
loop: “{{ static\_roles|dict2items }}”
- name: “add users to groups”  
become: true  
become\_user: postgres  
community.postgresql.postgresql\_membership:  
group: “{{ item.value.member\_of }}”  
target\_roles:
- “{{ item.key }}”  
loop: “{{ static\_roles|dict2items }}”  
when: item.value.member\_of

Has you can see in the first task i test if the user can login and i display it in the attributes. So that way it will know if it’s a user or a group.  
Then i have another task that actually puts the members in the respective groups.

Don’t hesitate to ask questions