From a0598d39a79bc8d7e44fdf0a8163e545764dbcb5 Mon Sep 17 00:00:00 2001 From: Jonas Gunz Date: Sat, 25 Dec 2021 22:37:47 +0100 Subject: Readme --- roles/apache/README.md | 3 +++ 1 file changed, 3 insertions(+) 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: -- cgit v1.2.3 From 5166e2e4666f5450b26b4ef3e70ed2c42518b486 Mon Sep 17 00:00:00 2001 From: Jonas Gunz Date: Sat, 25 Dec 2021 23:24:38 +0100 Subject: mysql_backup: WIP --- roles/mysql_backup/README.md | 18 ++++++++ roles/mysql_backup/defaults/main.yml | 8 ++++ roles/mysql_backup/tasks/main.yml | 81 ++++++++++++++++++++++++++++++++++++ 3 files changed, 107 insertions(+) create mode 100644 roles/mysql_backup/README.md create mode 100644 roles/mysql_backup/defaults/main.yml create mode 100644 roles/mysql_backup/tasks/main.yml 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 -- cgit v1.2.3 From a0ef8bb61b78f695128f7574228b2b23acc2f1b1 Mon Sep 17 00:00:00 2001 From: Jonas Gunz 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 ++++ roles/postgres_backup/tasks/main.yml | 88 +++++++++++++++++++++++++++++++++ roles/postgres_backup/vars/main.yml | 2 + 7 files changed, 249 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 create mode 100644 roles/postgres_backup/tasks/main.yml create mode 100644 roles/postgres_backup/vars/main.yml 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..` + +``` +--- +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/ -- cgit v1.2.3