postgres: WIP

7 files changed, 249 insertions, 0 deletions

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'
diff --git a/roles/postgres_backup/tasks/main.yml b/roles/postgres_backup/tasks/main.yml
new file mode 100644
index 0000000..62dfae3
--- /dev/null
+++ b/roles/postgres_backup/tasks/main.yml
@@ -0,0 +1,88 @@
+---
+- name: Check for BORG
+  command: which borg
+  register: borg_check
+  ignore_errors: yes
+
+# Kinda hacky but saves time
+- name: Install BORG
+  apt:
+    name:
+      - borgbackup
+  become: yes
+  when: not borg_check.rc == 0
+
+- name: Create BORG repo
+  file:
+    path: '{{ borg_repo_dir }}'
+    state: directory
+    owner: server
+    group: server
+    mode: 'u=rwx,g=,o='
+      #    recurse: yes
+  become: yes
+
+- name: Initialize BORG repo
+  command:
+    cmd: borg init --encryption=repokey
+    creates: '{{ borg_repo_dir }}/config'
+  environment:
+    BORG_REPO: '{{ borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ vault_db_backup_key[ env ] }}'
+
+- name: Create tempdir
+  file:
+    path: /tmp/postgres
+    state: directory
+    owner: postgres
+    group: postgres
+    mode: 'u=rwx,g=,o='
+  become: yes
+
+- name: Dump databases
+  community.postgresql.postgresql_db:
+    name: '{{ item }}'
+    state: dump
+    target: '/tmp/postgres/{{ item }}.sql'
+    login_unix_socket: '/var/run/postgresql/'
+  loop: '{{ pg_dbs }}'
+  become_user: postgres
+  become: yes
+
+- name: Create tempdir
+  file:
+    path: /tmp/postgres
+    state: directory
+    owner: server
+    group: server
+    recurse: yes
+    mode: 'u=rwx,g=,o='
+  become: yes
+
+- name: Create BORG backup
+  command: 'borg create --compression lz4 --verbose ::{hostname}-{now} /tmp/postgres'
+  environment:
+    BORG_REPO: '{{ borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ vault_db_backup_key[ env ] }}'
+  register: borg_output
+
+- name: Borg Output
+  debug:
+    var: borg_output.stderr
+
+- name: Delete TEMP files
+  file:
+    path: /tmp/postgres
+    state: absent
+  become: yes
+
+- name: Prune BORG backup
+  command: 'borg prune --list --keep-last 3 --keep-daily 7 --keep-weekly 4 --keep-monthly 6'
+  environment:
+    BORG_REPO: '{{ borg_repo_dir }}'
+    BORG_PASSPHRASE: '{{ vault_pg_db_backup_key[ env ] }}'
+  register: borg_prune
+
+- name: Prune Output
+  debug:
+    var: borg_prune.stderr
diff --git a/roles/postgres_backup/vars/main.yml b/roles/postgres_backup/vars/main.yml
new file mode 100644
index 0000000..0a33854
--- /dev/null
+++ b/roles/postgres_backup/vars/main.yml
@@ -0,0 +1,2 @@
+---
+borg_repo_dir: /var/backup/borg_postgres/