From a0ef8bb61b78f695128f7574228b2b23acc2f1b1 Mon Sep 17 00:00:00 2001
From: Jonas Gunz <himself@jonasgunz.de>
Date: Sun, 26 Dec 2021 19:54:11 +0100
Subject: postgres: WIP

---
 roles/postgres/Readme.md                | 31 +++++++++++++
 roles/postgres/handlers/main.yml        | 13 ++++++
 roles/postgres/tasks/main.yml           | 78 +++++++++++++++++++++++++++++++++
 roles/postgres/templates/pg_hba.conf.j2 | 28 ++++++++++++
 roles/postgres/templates/pgsql.conf.j2  |  9 ++++
 5 files changed, 159 insertions(+)
 create mode 100644 roles/postgres/Readme.md
 create mode 100644 roles/postgres/handlers/main.yml
 create mode 100644 roles/postgres/tasks/main.yml
 create mode 100644 roles/postgres/templates/pg_hba.conf.j2
 create mode 100644 roles/postgres/templates/pgsql.conf.j2

(limited to 'roles/postgres')

diff --git a/roles/postgres/Readme.md b/roles/postgres/Readme.md
new file mode 100644
index 0000000..7e98f55
--- /dev/null
+++ b/roles/postgres/Readme.md
@@ -0,0 +1,31 @@
+# postgres
+
+sets up postgreSQL database according to variables defines on host
+
+User passwords are expected in `vault_pg_db_users_pw.<hostname>.<user>`
+
+```
+---
+pg_ver: 11
+pg_ins: main
+
+# host:db
+pg_hba:
+ - host: 192.168.1.11/32
+   user: test1
+   db: testdb1
+
+pg_dbs:
+ - testdb1
+ - testdb2
+
+pg_db_users:
+  test1:
+    db: testdb1
+    priv: ALL
+    state: present
+  test2:
+    db: testdb2
+    priv: ALL
+    state: present
+```
diff --git a/roles/postgres/handlers/main.yml b/roles/postgres/handlers/main.yml
new file mode 100644
index 0000000..0dbb274
--- /dev/null
+++ b/roles/postgres/handlers/main.yml
@@ -0,0 +1,13 @@
+---
+- name: Restart pgsql systemd
+  systemd:
+    name: postgresql
+    state: restarted
+  become: yes
+  listen: restart pgsql
+
+- name: Wait 10s for postgres
+  wait_for:
+    timeout: 10
+  delegate_to: localhost
+  listen: restart pgsql
diff --git a/roles/postgres/tasks/main.yml b/roles/postgres/tasks/main.yml
new file mode 100644
index 0000000..433a1cf
--- /dev/null
+++ b/roles/postgres/tasks/main.yml
@@ -0,0 +1,78 @@
+---
+- name: Install packages
+  apt:
+    name:
+      - postgresql
+      - postgresql-contrib
+      - postgresql-client
+      - python3-psycopg2
+    update_cache: yes
+  become: yes
+
+- name: Server configuration
+  template:
+    src: pgsql.conf.j2
+    dest: '/etc/postgresql/{{ pg_ver }}/{{ pg_ins }}/conf.d/deployment.conf'
+  become: yes
+  notify:
+    - restart pgsql
+
+- name: Server configuration
+  template:
+    src: pg_hba.conf.j2
+    dest: '/etc/postgresql/{{ pg_ver }}/{{ pg_ins }}/pg_hba.conf'
+  become: yes
+  notify:
+    - restart pgsql
+
+- name: Generate SSL Certificates
+  include_role:
+    name: signed_certificate
+  vars:
+    owner: postgres
+
+- name: Check for changed cert
+  command: /bin/true
+  when:
+    - cert_changed
+  notify:
+    - restart pgsql
+
+- name: Flush handlers
+  meta: flush_handlers
+
+- name: Database configuration
+  community.postgresql.postgresql_db:
+    name: '{{ item }}'
+    state: present
+    encoding: UTF-8
+    template: template0
+    login_unix_socket: '/var/run/postgresql/'
+  loop: '{{ pg_dbs }}'
+  become_user: postgres
+  become: yes
+
+- name: User configuration
+  community.postgresql.postgresql_user:
+    name: '{{ item.key }}'
+    password: '{{ vault_pg_db_users_pw[ ansible_facts.fqdn ][ item.key ] }}'
+    login_unix_socket: '/var/run/postgresql/'
+  args: '{{ item.value }}'
+  environment:
+    PGOPTIONS: "-c password_encryption=scram-sha-256"
+  with_dict: '{{ pg_db_users }}'
+  become_user: postgres
+  become: yes
+
+- name: Privilege configuration
+  community.postgresql.postgresql_privs:
+    db: postgres
+    roles: PUBLIC
+    privs: ALL
+    type: database
+    objs: 'postgres,{{ pg_dbs | join(",") }}'
+    state: absent
+    login_unix_socket: '/var/run/postgresql/'
+  become_user: postgres
+  become: yes
+
diff --git a/roles/postgres/templates/pg_hba.conf.j2 b/roles/postgres/templates/pg_hba.conf.j2
new file mode 100644
index 0000000..f77641d
--- /dev/null
+++ b/roles/postgres/templates/pg_hba.conf.j2
@@ -0,0 +1,28 @@
+# vi: ft=conf
+
+# DO NOT DISABLE!
+# If you change this first entry you will need to make sure that the
+# database superuser can access the database using some other method.
+# Noninteractive access to all databases is required during automatic
+# maintenance (custom daily cronjobs, replication, and similar tasks).
+#
+# Database administrative login by Unix domain socket
+local   all             postgres                                peer
+
+# TYPE  DATABASE        USER            ADDRESS                 METHOD
+
+# "local" is for Unix domain socket connections only
+local   all             all                                     peer
+# IPv4 local connections:
+host    all             all             127.0.0.1/32            md5
+# IPv6 local connections:
+host    all             all             ::1/128                 md5
+# Allow replication connections from localhost, by a user with the
+# replication privilege.
+local   replication     all                                     peer
+host    replication     all             127.0.0.1/32            md5
+host    replication     all             ::1/128                 md5
+
+{% for host in pg_hba[env]["num" + num] %}
+hostssl {{ host.db }} {{ host.user }} {{ host.host }} scram-sha-256
+{% endfor %}
diff --git a/roles/postgres/templates/pgsql.conf.j2 b/roles/postgres/templates/pgsql.conf.j2
new file mode 100644
index 0000000..beb52d7
--- /dev/null
+++ b/roles/postgres/templates/pgsql.conf.j2
@@ -0,0 +1,9 @@
+# vi: ft=conf
+
+password_encryption = scram-sha-256
+
+listen_addresses = '*'
+
+ssl = on
+ssl_cert_file = '/etc/ssl/certs/{{ ansible_facts.fqdn }}.pem'
+ssl_key_file = '/etc/ssl/private/{{ ansible_facts.fqdn }}.key'
-- 
cgit v1.2.3