PostgreSQL Create roles from list

[Sagan]

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

```
------
# 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

```
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.

[Sagan]

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