configure_postgresql.yml

---
- name: configure postgres dbs
  become: true
  hosts: all
  tasks:
    # enable PostgreSQL service
    - name: ensure postgres service is enabled and running
      service:
        name:postgresql
        state: started
        enabled: yes

    # create databases and logins for the users
    - name: create databases
      postgresql_db:
        name: "instance_{{ item.key }}"
        encoding: "UTF-8"
        lc_collate: de_DE.UTF-8
        lc_ctype: de_DE.UTF-8
        template: "template0"
        state: present
      with_dict: "{{ otree_users }}"
      when: item.value.override

    - name: ensure users have access to the databases
      postgresql_user:
        db: "instance_{{ item.key }}"
        name: "{{ item }}"
        name: "{{ item }}"
        priv: ALL
        state: present
      with_dict: "{{ otree_users }}"
      when: item.value.override

    - name: ensure users do not have unnecessary priviliges
      postgresql_user:
        role_attr_flags: NOSUPERUSER,NOCREATEDB
      when: item.value.override