create postgresql users with ansible sub nested-list

Question

I am trying to find the best YAML structure to maintain databases & roles/users) for Postgres using ansible, one of the structures I tested is:

---
- databases: 
  - name: database1
    owner: postrgres
    users:
      - name: user1
        pass: secret
        priv: CONNECT,REPLICATION
      - name: user2
        pass: secret
        priv: CONNECT

  - name: database2
    owner: postgres
    users:
      - name: user3
        pass: secret
        priv: CONNECT
      - name: user2    <--- user previously created needs to either create users first implies 
        pass: secret
        priv: CONNECT

But how could I loop and get only a list of users so that I could use them in:

- name: Create users
  postgresql_user:
    name: '{{ item.name }}'
    password: '{{ item.pass }}'

I may split the YAML and have something like:

---
- postgres_users:
  - user: user1
    pass: secret
  - name: user2
    pass: secret

- postgres_databases:
  - name:  db1
    owner: <user> | default('postgres')
    users: 
      - user: user1
        priv: XXX.YYY
      - user: user2
  - name:  db2
    owner: <user> | default('postgres')
    users: 
      - user: user1
        priv: ZZZ
      - user: user2
        priv: XXX

But still wondering how to use in the loop postgres_databases and from there only use users.

Any ideas/tips?

Answer 1

Given the first structure -- and assuming that there's a typo and that databases is not actually a member of a list -- you could write:

- name: create users
  postgresql_user:
    name: "{{ item.1.name }}"
    password: "{{ item.1.pass }}"
  loop: "{{ databases|subelements('users') }}"
  loop_control:
    label: "{{ item.1.name }}"

Here's a complete reproducer; I've wrapped the postgres_user call in a debug task so that I can run it locally:

- hosts: localhost
  gather_facts: false
  vars:
    databases:
    - name: database1
      owner: postrgres
      users:
        - name: user1
          pass: secret
          priv: CONNECT,REPLICATION
        - name: user2
          pass: secret
          priv: CONNECT

    - name: database2
      owner: postgres
      users:
        - name: user3
          pass: secret
          priv: CONNECT
        - name: user2
          pass: secret
          priv: CONNECT

  tasks:
    - name: create users
      debug:
        msg:
          postgresql_user:
            name: "{{ item.1.name }}"
            password: "{{ item.1.pass }}"
      loop: "{{ databases|subelements('users') }}"
      loop_control:
        label: "{{ item.1.name }}"

This outputs:

TASK [create users] *********************************************************************************
ok: [localhost] => (item=user1) => {
    "msg": {
        "postgresql_user": {
            "name": "user1",
            "password": "secret"
        }
    }
}
ok: [localhost] => (item=user2) => {
    "msg": {
        "postgresql_user": {
            "name": "user2",
            "password": "secret"
        }
    }
}
ok: [localhost] => (item=user3) => {
    "msg": {
        "postgresql_user": {
            "name": "user3",
            "password": "secret"
        }
    }
}
ok: [localhost] => (item=user2) => {
    "msg": {
        "postgresql_user": {
            "name": "user2",
            "password": "secret"
        }
    }
}

---

The above will attempt to create user2 twice, but that should be okay; the second attempt won't make any changes because the user already exists. If you wanted a unique list of users you could do something like this:

    - name: get unique list of users
      set_fact:
        all_users: "{{ databases|json_query('[].users[]')|unique }}"

    - name: create users
      debug:
        msg:
          postgresql_user:
            name: "{{ item.name }}"
            password: "{{ item.pass }}"
      loop: "{{ all_users }}"
      loop_control:
        label: "{{ item.name }}"