aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--roles/apache/README.md3
-rw-r--r--roles/mysql_backup/README.md18
-rw-r--r--roles/mysql_backup/defaults/main.yml8
-rw-r--r--roles/mysql_backup/tasks/main.yml81
-rw-r--r--roles/postgres/Readme.md31
-rw-r--r--roles/postgres/handlers/main.yml13
-rw-r--r--roles/postgres/tasks/main.yml78
-rw-r--r--roles/postgres/templates/pg_hba.conf.j228
-rw-r--r--roles/postgres/templates/pgsql.conf.j29
-rw-r--r--roles/postgres_backup/tasks/main.yml88
-rw-r--r--roles/postgres_backup/vars/main.yml2
11 files changed, 359 insertions, 0 deletions
diff --git a/roles/apache/README.md b/roles/apache/README.md
index 6cc82ee..12f7d7c 100644
--- a/roles/apache/README.md
+++ b/roles/apache/README.md
@@ -3,6 +3,8 @@
Example config
+PHP-Modules and apache mods in example config are always needed.
+
```
---
php_versions:
@@ -15,6 +17,7 @@ php_extensions:
apache_mods:
- ssl
- rewrite
+ - proxy_fcgi
apache_rproxies:
proxy-sso:
diff --git a/roles/mysql_backup/README.md b/roles/mysql_backup/README.md
new file mode 100644
index 0000000..9ee05cf
--- /dev/null
+++ b/roles/mysql_backup/README.md
@@ -0,0 +1,18 @@
+# mysql_backup
+
+Archives SQL Database dumps in an encrypted BORG repo.
+
+compatible with kompetenzbolzen.stuff.mariadb configuration for databases
+
+Example config
+
+```
+---
+sql_backup:
+ borg_repo_dir: /var/backup/borg_mysql/
+ borg_repo_key: 'changeme'
+ keep_last: 3
+ keep_daily: 7
+ keep_weekly: 4
+ keep_monthly: 6
+```
diff --git a/roles/mysql_backup/defaults/main.yml b/roles/mysql_backup/defaults/main.yml
new file mode 100644
index 0000000..ade30ea
--- /dev/null
+++ b/roles/mysql_backup/defaults/main.yml
@@ -0,0 +1,8 @@
+---
+sql_backup:
+ borg_repo_dir: /var/backup/borg_mysql/
+ borg_repo_key: 'changeme'
+ keep_last: 3
+ keep_daily: 7
+ keep_weekly: 4
+ keep_monthly: 6
diff --git a/roles/mysql_backup/tasks/main.yml b/roles/mysql_backup/tasks/main.yml
new file mode 100644
index 0000000..7ce7748
--- /dev/null
+++ b/roles/mysql_backup/tasks/main.yml
@@ -0,0 +1,81 @@
+---
+- 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: '{{ sql_backup.borg_repo_dir }}'
+ state: directory
+ mode: 'u=rwx,g=,o='
+ become: yes
+
+- name: Initialize BORG repo
+ command:
+ cmd: borg init --encryption=repokey
+ creates: '{{ sql_backup.borg_repo_dir }}/config'
+ environment:
+ BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+ BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+ become: yes
+
+- name: Create tempdir
+ file:
+ path: /tmp/sql
+ state: directory
+ owner: server
+ group: server
+ mode: 'u=rwx,g=,o='
+ become: yes
+
+- name: Dump databases
+ community.mysql.mysql_db:
+ state: dump
+ name: '{{ item }}'
+ target: '/tmp/sql/{{ item }}.sql'
+ login_unix_socket: /var/run/mysqld/mysqld.sock
+ loop: '{{ dbs }}'
+ become: yes
+
+- name: Create BORG backup
+ command: 'borg create --compression lz4 --verbose ::{hostname}-{now} /tmp/sql'
+ environment:
+ BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+ BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+ register: borg_output
+ become: yes
+
+- name: Borg Output
+ debug:
+ var: borg_output.stderr
+
+- name: Delete TEMP files
+ file:
+ path: /tmp/sql
+ state: absent
+ become: yes
+
+- name: Prune BORG backup
+ command: 'borg prune --list
+ --keep-last {{ sql_backup.keep_last }}
+ --keep-daily {{ sql_backup.keep_daily }}
+ --keep-weekly {{ sql_backup.keep_weekly }}
+ --keep-monthly {{ sql_backup.keep_monthly }}'
+ environment:
+ BORG_REPO: '{{ sql_backup.borg_repo_dir }}'
+ BORG_PASSPHRASE: '{{ sql_backup.borg_repo_key }}'
+ register: borg_prune
+ become: yes
+
+- name: Prune Output
+ debug:
+ var: borg_prune.stderr
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/
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-21 19:29:33 +0000