5 files changed, 86 insertions, 12 deletions
diff --git a/ansible/roles/postgres/handlers/main.yml b/ansible/roles/postgres/handlers/main.yml
index 73fffe3..5f01c13 100644
--- a/ansible/roles/postgres/handlers/main.yml
+++ b/ansible/roles/postgres/handlers/main.yml
@@ -1,4 +1,13 @@
-- name: Restart postgres.
+- name: Restart the postgres service
   service:
     name: '{{ postgres_daemon }}'
     state: "restarted"
+  tags:
+    - role::postgres
+
+- name: Reload the postgres service
+  service:
+    name: '{{ postgres_daemon }}'
+    state: reloaded
+  tags:
+    - role::postgres
diff --git a/ansible/roles/postgres/tasks/main.yml b/ansible/roles/postgres/tasks/main.yml
index 034ff9c..ea6565b 100644
--- a/ansible/roles/postgres/tasks/main.yml
+++ b/ansible/roles/postgres/tasks/main.yml
@@ -18,16 +18,55 @@
     - role::postgres
 
 - name: Add postgres users
-  community.postgresql.postgresql_user: "{{ item }}"
-  with_items: "{{ postgres_users }}"
   become: true
   become_user: "{{ postgres_user }}"
+  community.postgresql.postgresql_user:
+    name: "{{ item.name }}"
+    password: "{{ item.password }}"
+    state: present
+  loop_control:
+    label: "{{ item.name }}"
+  loop: "{{ postgres_users }}"
+  environment:
+    PGOPTIONS: "-c password_encryption=scram-sha-256"
   tags:
     - role::postgres
 
 - name: Add postgres databases
-  community.postgresql.postgresql_db: "{{ item }}"
-  with_items: "{{ postgres_databases }}"
+  become: true
+  become_user: "{{ postgres_user }}"
+  community.postgresql.postgresql_db:
+    name: "{{ item.name }}"
+    owner: "{{ item.owner }}"
+    state: present
+  loop: "{{ postgres_databases }}"
+  tags:
+    - role::postgres
+
+- name: Set host based authentication rules for all postgres users at once
+  ansible.builtin.blockinfile:
+    path: /etc/postgresql/{{ postgres_version }}/main/pg_hba.conf
+    insertafter: "# Put your actual configuration here"
+    marker: "# {mark} ANSIBLE MANAGED HBA CONF BLOCK"
+    block: |
+      {% for db in postgres_databases %}
+      host    {{ db.name }}    {{ db.owner }}    all    scram-sha-256
+      {% endfor %}
+  loop: "{{ postgres_databases }}"
+  notify:
+    - Reload the postgres service
+  tags:
+    - role::postgres
+
+- name: Grant specified roles to users
+  community.postgresql.postgresql_membership:
+    groups: "{{ user.roles }}"
+    target_role: "{{ user.name }}"
+  loop: "{{ postgres_users }}"
+  when: user.roles != None
+  loop_control:
+    loop_var: user
+    label: "{{ user.name }}"
   become: true
   become_user: "{{ postgres_user }}"
   tags:
diff --git a/ansible/roles/postgres/vars/main.yml b/ansible/roles/postgres/vars/main.yml
deleted file mode 100644
index 7f482b0..0000000
--- a/ansible/roles/postgres/vars/main.yml
+++ /dev/null
@@ -1,7 +0,0 @@
-postgres_version: "15"
-postgres_daemon: "postgresql@{{ postgres_version }}-main"
-postgres_user: "postgres"
-
-postgres_users: []
-
-postgres_databases: []
diff --git a/ansible/roles/postgres/vars/main/db_passwords.yml b/ansible/roles/postgres/vars/main/db_passwords.yml
new file mode 100644
index 0000000..6c31909
--- /dev/null
+++ b/ansible/roles/postgres/vars/main/db_passwords.yml
@@ -0,0 +1,15 @@
+$ANSIBLE_VAULT;1.1;AES256
+33376564336164313533613136396638396332383132366634373361303361643631353663646538
+6132653061306166356238396636656538356164343765380a316134626534363566363237373162
+66383963323931646230353265613764313062616466616465653066613636633233613038626239
+3133373631626531330a656330336466616165623161303963376538616331343032376461663237
+32306664356464376437623138393530643436303465353336613465353963356665303032366134
+65373166376339666138393835383863326138663536383039373438356634373534306332656231
+30656531623561353161616334323739373533363034663936376430316132316166396665376537
+61623131383037633432356538616434333139646465383432366132653636306233653865633134
+34663633386266323634366134356166346634333063323230336666373366343134353733303732
+39653137646132613635623531343464653931323262333534393766623132613938303763646636
+35343137656262313236343434393462383464393664333736383861383363313861363636653739
+31653439336365316466613065623063306635653336336565323539343061616163323235336463
+64303335623465303462613332303330666433333538316165333561663537386635653130663432
+6437633131326534656664353332623163653664373965633330
diff --git a/ansible/roles/postgres/vars/main/main.yml b/ansible/roles/postgres/vars/main/main.yml
new file mode 100644
index 0000000..f532863
--- /dev/null
+++ b/ansible/roles/postgres/vars/main/main.yml
@@ -0,0 +1,18 @@
+postgres_version: "15"
+postgres_daemon: "postgresql@{{ postgres_version }}-main"
+postgres_user: "postgres"
+
+postgres_users:
+  - name: pinnwand
+    password: "{{ vault_postgres_user_passwords.pinnwand }}"
+    roles:
+
+  - name: blackbox
+    password: "{{ vault_postgres_user_passwords.blackbox }}"
+    roles:
+      - pg_read_all_data
+
+
+postgres_databases:
+  - name: pinnwand
+    owner: pinnwand