From ba86ae504d8ea9796e43c1b061aa070761cd1323 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Mon, 21 Nov 2022 18:58:56 +0100 Subject: pgbackrest --- hosts/surtr/default.nix | 2 +- hosts/surtr/postgresql.nix | 172 ------------------------ hosts/surtr/postgresql/default.nix | 239 ++++++++++++++++++++++++++++++++++ hosts/surtr/postgresql/pgbackrest.crt | 13 ++ hosts/surtr/postgresql/pgbackrest.key | 26 ++++ hosts/surtr/ruleset.nft | 8 +- hosts/vidhar/default.nix | 2 +- hosts/vidhar/dns/zones/yggdrasil.soa | 5 +- hosts/vidhar/network/ruleset.nft | 6 + hosts/vidhar/pgbackrest/ca/ca.crt | 12 ++ hosts/vidhar/pgbackrest/ca/ca.key | 21 +++ hosts/vidhar/pgbackrest/default.nix | 101 ++++++++++++++ hosts/vidhar/pgbackrest/tls.crt | 12 ++ hosts/vidhar/pgbackrest/tls.key | 26 ++++ 14 files changed, 468 insertions(+), 177 deletions(-) delete mode 100644 hosts/surtr/postgresql.nix create mode 100644 hosts/surtr/postgresql/default.nix create mode 100644 hosts/surtr/postgresql/pgbackrest.crt create mode 100644 hosts/surtr/postgresql/pgbackrest.key create mode 100644 hosts/vidhar/pgbackrest/ca/ca.crt create mode 100644 hosts/vidhar/pgbackrest/ca/ca.key create mode 100644 hosts/vidhar/pgbackrest/default.nix create mode 100644 hosts/vidhar/pgbackrest/tls.crt create mode 100644 hosts/vidhar/pgbackrest/tls.key (limited to 'hosts') diff --git a/hosts/surtr/default.nix b/hosts/surtr/default.nix index 9ac087c3..f6200cf3 100644 --- a/hosts/surtr/default.nix +++ b/hosts/surtr/default.nix @@ -2,7 +2,7 @@ { imports = with flake.nixosModules.systemProfiles; [ tmpfs-root qemu-guest openssh rebuild-machines zfs - ./zfs.nix ./dns ./tls ./http ./bifrost ./matrix ./postgresql.nix + ./zfs.nix ./dns ./tls ./http ./bifrost ./matrix ./postgresql ./prometheus ./email ./vpn ./borg.nix ./etebase ]; diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix deleted file mode 100644 index c10c5084..00000000 --- a/hosts/surtr/postgresql.nix +++ /dev/null @@ -1,172 +0,0 @@ -{ pkgs, sources, config, ... }: -let - versioning = sources.psql-versioning.src; -in { - config = { - services.postgresql = { - enable = true; - package = pkgs.postgresql_14; - }; - - systemd.services.migrate-postgresql = { - after = [ "postgresql.service" ]; - bindsTo = [ "postgresql.service" ]; - wantedBy = [ "postgresql.service" ]; - - serviceConfig = { - Type = "oneshot"; - inherit (config.systemd.services.postgresql.serviceConfig) User Group; - RemainAfterExit = true; - }; - - path = [ config.services.postgresql.package ]; - script = '' - psql postgres postgres -eXf ${pkgs.writeText "schema.sql" '' - CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - CREATE DATABASE "etebase" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; - ''} - - psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-matrix-users', null, null); - - CREATE USER "matrix-synapse"; - GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse"; - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse"; - COMMIT; - ''} - - psql email postgres -eXf ${pkgs.writeText "email.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-users', null, null); - - CREATE USER "postfix"; - GRANT CONNECT ON DATABASE "email" TO "postfix"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix"; - CREATE USER "dovecot2"; - GRANT CONNECT ON DATABASE "email" TO "dovecot2"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('001-spm', null, null); - - CREATE USER "spm"; - GRANT CONNECT ON DATABASE "email" TO "spm"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('000-base', null, null); - - CREATE TABLE mailbox ( - id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), - mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''), - quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END), - CONSTRAINT mailbox_unique UNIQUE (mailbox) - ); - CREATE TABLE mailbox_mapping ( - id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), - local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''), - domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''), - mailbox uuid REFERENCES mailbox(id), - CONSTRAINT local_domain_unique UNIQUE (local, domain) - ); - CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL; - - CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; - CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox; - - CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null); - - CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('002-citext', ARRAY['000-base'], null); - - DROP VIEW virtual_mailbox_domain; - DROP VIEW virtual_mailbox_mapping; - DROP VIEW imap_user; - DROP VIEW lmtp_mapping; - - CREATE EXTENSION citext; - - ALTER TABLE mailbox ALTER mailbox TYPE citext; - ALTER TABLE mailbox_mapping ALTER local TYPE citext; - ALTER TABLE mailbox_mapping ALTER domain TYPE citext; - - CREATE VIEW mailbox_quota_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; - - CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; - CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping; - CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule; - CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null); - - ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END); - - DROP VIEW virtual_mailbox_mapping; - DROP VIEW lmtp_mapping; - - CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup FROM mailbox_mapping WHERE mailbox IS NOT NULL; - CREATE VIEW virtual_mailbox_access (lookup, action) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup, CASE WHEN mailbox IS NULL THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping; - CREATE VIEW lmtp_mapping ("user", quota_rule, local, extension, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, extension, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; - COMMIT; - - BEGIN; - SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null); - - ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey; - ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT; - COMMIT; - - BEGIN; - SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null); - - GRANT INSERT ON "mailbox_mapping" TO "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null); - - GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm"; - COMMIT; - - BEGIN; - SELECT _v.register_patch('007-ccert-sender-policy', ARRAY['000-base'], null); - - CREATE USER "postfix-ccert-sender-policy"; - GRANT CONNECT ON DATABASE "email" TO "postfix-ccert-sender-policy"; - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy"; - GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy"; - COMMIT; - ''} - - psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" '' - \i ${versioning + "/install.versioning.sql"} - - BEGIN; - SELECT _v.register_patch('000-user', null, null); - - CREATE USER "etebase"; - GRANT ALL PRIVILEGES ON DATABASE "etebase" TO "etebase"; - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "etebase"; - COMMIT; - ''} - ''; - }; - }; -} diff --git a/hosts/surtr/postgresql/default.nix b/hosts/surtr/postgresql/default.nix new file mode 100644 index 00000000..9cf494ae --- /dev/null +++ b/hosts/surtr/postgresql/default.nix @@ -0,0 +1,239 @@ +{ pkgs, sources, config, flake, ... }: +let + versioning = sources.psql-versioning.src; +in { + config = { + services.postgresql = { + enable = true; + package = pkgs.postgresql_14; + }; + + services.pgbackrest = { + enable = true; + settings = { + "surtr" = { + pg1-path = config.services.postgresql.dataDir; + + repo1-path = "/var/lib/pgbackrest"; + repo1-retention-full-type = "time"; + repo1-retention-full = 7; + repo1-retention-archive = 2; + + repo2-host-type = "tls"; + repo2-host = "pgbackrest.vidhar.yggdrasil"; + repo2-host-ca-file = toString ../../vidhar/pgbackrest/ca/ca.crt; + repo2-host-cert-file = toString ./pgbackrest.crt; + repo2-host-key-file = config.sops.secrets."pgbackrest.key".path; + repo2-retention-full-type = "time"; + repo2-retention-full = 14; + repo2-retention-archive = 7; + }; + + "global" = { + compress-type = "zst"; + compress-level = 9; + + archive-async = true; + spool-path = "/var/spool/pgbackrest"; + }; + + "global:server" = { + tls-server-address = "2a03:4000:52:ada:1::"; + tls-server-ca-file = toString ../../vidhar/pgbackrest/ca/ca.crt; + tls-server-cert-file = toString ./pgbackrest.crt; + tls-server-key-file = config.sops.secrets."pgbackrest.key".path; + tls-server-auth = ["vidhar.yggdrasil=surtr"]; + }; + + "global:archive-push" = { + process-max = 2; + }; + "global:archive-get" = { + process-max = 2; + }; + }; + + tlsServer.enable = true; + + backups."surtr-daily" = { + stanza = "surtr"; + repo = "1"; + timerConfig.OnCalendar = "daily"; + }; + }; + + sops.secrets."pgbackrest.key" = { + format = "binary"; + sopsFile = ./pgbackrest.key; + owner = "postgres"; + group = "postgres"; + mode = "0400"; + }; + + systemd.tmpfiles.rules = [ + "d /var/lib/pgbackrest 0750 postgres postgres - -" + "d /var/spool/pgbackrest 0750 postgres postgres - -" + ]; + + systemd.services.migrate-postgresql = { + after = [ "postgresql.service" ]; + bindsTo = [ "postgresql.service" ]; + wantedBy = [ "postgresql.service" ]; + + serviceConfig = { + Type = "oneshot"; + inherit (config.systemd.services.postgresql.serviceConfig) User Group; + RemainAfterExit = true; + }; + + path = [ config.services.postgresql.package ]; + script = '' + psql postgres postgres -eXf ${pkgs.writeText "schema.sql" '' + CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; + CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; + CREATE DATABASE "etebase" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C"; + ''} + + psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" '' + \i ${versioning + "/install.versioning.sql"} + + BEGIN; + SELECT _v.register_patch('000-matrix-users', null, null); + + CREATE USER "matrix-synapse"; + GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse"; + GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse"; + COMMIT; + ''} + + psql email postgres -eXf ${pkgs.writeText "email.sql" '' + \i ${versioning + "/install.versioning.sql"} + + BEGIN; + SELECT _v.register_patch('000-users', null, null); + + CREATE USER "postfix"; + GRANT CONNECT ON DATABASE "email" TO "postfix"; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix"; + CREATE USER "dovecot2"; + GRANT CONNECT ON DATABASE "email" TO "dovecot2"; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2"; + COMMIT; + + BEGIN; + SELECT _v.register_patch('001-spm', null, null); + + CREATE USER "spm"; + GRANT CONNECT ON DATABASE "email" TO "spm"; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm"; + COMMIT; + + BEGIN; + SELECT _v.register_patch('000-base', null, null); + + CREATE TABLE mailbox ( + id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), + mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''), + quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END), + CONSTRAINT mailbox_unique UNIQUE (mailbox) + ); + CREATE TABLE mailbox_mapping ( + id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(), + local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''), + domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''), + mailbox uuid REFERENCES mailbox(id), + CONSTRAINT local_domain_unique UNIQUE (local, domain) + ); + CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL; + + CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; + CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox; + + CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; + COMMIT; + + BEGIN; + SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null); + + CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox; + COMMIT; + + BEGIN; + SELECT _v.register_patch('002-citext', ARRAY['000-base'], null); + + DROP VIEW virtual_mailbox_domain; + DROP VIEW virtual_mailbox_mapping; + DROP VIEW imap_user; + DROP VIEW lmtp_mapping; + + CREATE EXTENSION citext; + + ALTER TABLE mailbox ALTER mailbox TYPE citext; + ALTER TABLE mailbox_mapping ALTER local TYPE citext; + ALTER TABLE mailbox_mapping ALTER domain TYPE citext; + + CREATE VIEW mailbox_quota_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox; + + CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping; + CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping; + CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule; + CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; + COMMIT; + + BEGIN; + SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null); + + ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END); + + DROP VIEW virtual_mailbox_mapping; + DROP VIEW lmtp_mapping; + + CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup FROM mailbox_mapping WHERE mailbox IS NOT NULL; + CREATE VIEW virtual_mailbox_access (lookup, action) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup, CASE WHEN mailbox IS NULL THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping; + CREATE VIEW lmtp_mapping ("user", quota_rule, local, extension, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, extension, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox; + COMMIT; + + BEGIN; + SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null); + + ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey; + ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT; + COMMIT; + + BEGIN; + SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null); + + GRANT INSERT ON "mailbox_mapping" TO "spm"; + COMMIT; + + BEGIN; + SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null); + + GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm"; + COMMIT; + + BEGIN; + SELECT _v.register_patch('007-ccert-sender-policy', ARRAY['000-base'], null); + + CREATE USER "postfix-ccert-sender-policy"; + GRANT CONNECT ON DATABASE "email" TO "postfix-ccert-sender-policy"; + ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy"; + GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy"; + COMMIT; + ''} + + psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" '' + \i ${versioning + "/install.versioning.sql"} + + BEGIN; + SELECT _v.register_patch('000-user', null, null); + + CREATE USER "etebase"; + GRANT ALL PRIVILEGES ON DATABASE "etebase" TO "etebase"; + GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "etebase"; + COMMIT; + ''} + ''; + }; + }; +} diff --git a/hosts/surtr/postgresql/pgbackrest.crt b/hosts/surtr/postgresql/pgbackrest.crt new file mode 100644 index 00000000..b4dc4d97 --- /dev/null +++ b/hosts/surtr/postgresql/pgbackrest.crt @@ -0,0 +1,13 @@ +-----BEGIN CERTIFICATE----- +MIIB7zCCAW+gAwIBAgIPQAAAAGN7p/Q5SZ7JU43JMAUGAytlcTAfMR0wGwYDVQQD +DBRwZ2JhY2tyZXN0LnlnZ2RyYXNpbDAeFw0yMjExMjExNjI2MTFaFw0zMjExMjEx +NjMxMTFaMBoxGDAWBgNVBAMMD3N1cnRyLnlnZ2RyYXNpbDAqMAUGAytlcAMhABIl +okEGkov33jgsrF0QA4CKQILbIWkZ2tn+UUhXxxyDo4HGMIHDMB8GA1UdIwQYMBaA +FO+/yfEkwcLr+vNPIsyCW86UwJ3aMB0GA1UdDgQWBBQnVeShLYsqF35OmmzLJEV5 +dfenhjAOBgNVHQ8BAf8EBAMCBeAwDAYDVR0TAQH/BAIwADAdBgNVHSUEFjAUBggr +BgEFBQcDAgYIKwYBBQUHAwEwRAYDVR0RBD0wO4IdcGdiYWNrcmVzdC5zdXJ0ci55 +Z2dkcmFzaWwubGmCGnBnYmFja3Jlc3Quc3VydHIueWdnZHJhc2lsMAUGAytlcQNz +AJqqMDWN1Ym5XANRKWcCh09j0Rej3V64XZlOOP7qFF9Gh4QJXeCvDMjX4LOeRUmi +lB8iosdRN9MSANI4kfwYBnzgn3BNMrvMI4faEOuVnd6X2ulsJdNbJNQzB3hRVsNf +b+QNBV+PpTUgR4k9e1XWX+wwAA== +-----END CERTIFICATE----- diff --git a/hosts/surtr/postgresql/pgbackrest.key b/hosts/surtr/postgresql/pgbackrest.key new file mode 100644 index 00000000..bc2af12d --- /dev/null +++ b/hosts/surtr/postgresql/pgbackrest.key @@ -0,0 +1,26 @@ +{ + "data": "ENC[AES256_GCM,data:Bg4fIAqIGLF1P1P583vQnHhjzrD8fdnS5tA/7SuSdBRJjVaRzB0bieEv+2i9WxgaStG9TTUSmClCVUsbR5gy7MoV6Br4AL17Y++R6wPpJbQJvtMMDJB2xg+THU/Ex61dendcWqPYh73Wn4U9uBE/wC1eVrShXRM=,iv:YG/foZwVcrzi6hdk7Vk0sYZ92LMbmiKg1SbAgPaeUNM=,tag:lAcoxUfQXB4vvc6XnIcA/g==,type:str]", + "sops": { + "kms": null, + "gcp_kms": null, + "azure_kv": null, + "hc_vault": null, + "age": null, + "lastmodified": "2022-11-21T14:30:27Z", + "mac": "ENC[AES256_GCM,data:Dsfc1XrGl4abSnDqRl/IwC11bVy+kHz1RaI0V/nkkaJ3fM/qTXPVc5mMoWCiPn1nz5BTABQRSnrf79qHc0wpZ1WUpn07yOf7JejJ/T/bUC7D8BuoVdWRh1og+NzWCEIwaGXg0Eo04yli+GXisdM3YVM9g3BrxYrSInjnNZFyB+Q=,iv:T5QprwIhB8ZWwmmfWVtxkXqbMB1onW+wX7GPIFMn+z0=,tag:zMi77nMepajhg2Djgz8rBA==,type:str]", + "pgp": [ + { + "created_at": "2022-11-21T14:30:27Z", + "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DXxoViZlp6dISAQdAi3pfg9DA+1v5r5sEijbkdwmOopWh05IuhRJxuy1btyAw\nuo0iV7VpngK8tFcBHnmhx3QsxIJo/gU+xrOwczW3RoSGrWo9tV2FantQPRp6f1aS\n0lwBEJSxmTApD/YDu3M6WhxN49/ZVEXG+KQ/mOdoBo0ITGKa6No0btMolzJ0bCJU\n+/avVdlDdZzfXo9XP0iJUoqh+1yMn+XdnD5deGac8a/QGvXZkxsYQ8KpK9sONA==\n=QyKr\n-----END PGP MESSAGE-----\n", + "fp": "30D3453B8CD02FE2A3E7C78C0FB536FB87AE8F51" + }, + { + "created_at": "2022-11-21T14:30:27Z", + "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DyFKFNkTVG5oSAQdAYU2U/anEJ8JSiG7NBppmsFeogXN3ynOEdq2tHXf+mUww\nIS7kW1pqcGMjnf7RQNuL91Wek5GEk4T498IFadiYDImAfIdS5jeX2w7UvxWLX5OZ\n0lwBlnxOwkYRWZzAhB6jHthmk2zEc+0JKuFolXhrwXqsFwFGoLTO9fctJrV7ry0u\naM9DqXru+/cEUZJDSq5GYDQaxTjyaFMVwLVdfxrtFwc8YMlqU8vVoWTqLaUVYA==\n=Tg80\n-----END PGP MESSAGE-----\n", + "fp": "7ED22F4AA7BB55728B643DC5471B7D88E4EF66F8" + } + ], + "unencrypted_suffix": "_unencrypted", + "version": "3.7.3" + } +} \ No newline at end of file diff --git a/hosts/surtr/ruleset.nft b/hosts/surtr/ruleset.nft index 51fcd498..4993b6b7 100644 --- a/hosts/surtr/ruleset.nft +++ b/hosts/surtr/ruleset.nft @@ -82,6 +82,7 @@ table inet filter { counter submissions-rx {} counter imaps-rx {} counter managesieve-rx {} + counter pgbackrest-rx {} counter established-rx {} @@ -109,6 +110,7 @@ table inet filter { counter submissions-tx {} counter imaps-tx {} counter managesieve-tx {} + counter pgbackrest-tx {} counter tx {} @@ -149,7 +151,7 @@ table inet filter { ct state invalid log level debug prefix "drop invalid input: " counter name invalid-rx drop - + iifname lo counter name rx-lo accept iif != lo ip daddr 127.0.0.1/8 counter name invalid-local4-rx reject @@ -178,6 +180,7 @@ table inet filter { tcp dport 465 counter name submissions-rx accept tcp dport 993 counter name imaps-rx accept tcp dport 4190 counter name managesieve-rx accept + iifname yggdrasil tcp dport 8432 counter name pgbackrest-rx accept ct state {established, related} counter name established-rx accept @@ -222,7 +225,8 @@ table inet filter { tcp sport 465 counter name submissions-tx accept tcp sport 993 counter name imaps-tx accept tcp sport 4190 counter name managesieve-tx accept - + tcp sport 8432 counter name pgbackrest-tx accept + counter name tx } diff --git a/hosts/vidhar/default.nix b/hosts/vidhar/default.nix index fc04d3f5..5c23dea2 100644 --- a/hosts/vidhar/default.nix +++ b/hosts/vidhar/default.nix @@ -4,7 +4,7 @@ with lib; { imports = with flake.nixosModules.systemProfiles; [ - ./zfs.nix ./network ./samba.nix ./dns ./prometheus ./borg + ./zfs.nix ./network ./samba.nix ./dns ./prometheus ./borg ./pgbackrest tmpfs-root zfs initrd-all-crypto-modules default-locale openssh rebuild-machines build-server diff --git a/hosts/vidhar/dns/zones/yggdrasil.soa b/hosts/vidhar/dns/zones/yggdrasil.soa index 3d9d4d83..045e49f8 100644 --- a/hosts/vidhar/dns/zones/yggdrasil.soa +++ b/hosts/vidhar/dns/zones/yggdrasil.soa @@ -1,7 +1,7 @@ $ORIGIN yggdrasil. $TTL 300 @ IN SOA vidhar.yggdrasil. root.yggdrasil.li. ( - 2022101601 ; serial + 2022112101 ; serial 300 ; refresh 300 ; retry 300 ; expire @@ -16,8 +16,11 @@ sif IN AAAA 2a03:4000:52:ada:1:2:: grafana.vidhar IN CNAME vidhar.yggdrasil. prometheus.vidhar IN CNAME vidhar.yggdrasil. +pgbackrest.vidhar IN CNAME vidhar.yggdrasil. nfsroot.vidhar IN CNAME vidhar.lan.yggdrasil. +pgbackrest.surtr IN CNAME surtr.yggdrasil. + vidhar.lan IN A 10.141.0.1 diff --git a/hosts/vidhar/network/ruleset.nft b/hosts/vidhar/network/ruleset.nft index 473f8a20..da3a9048 100644 --- a/hosts/vidhar/network/ruleset.nft +++ b/hosts/vidhar/network/ruleset.nft @@ -87,6 +87,7 @@ table inet filter { counter samba-rx {} counter http-rx {} counter tftp-rx {} + counter pgbackrest-rx {} counter established-rx {} @@ -114,6 +115,7 @@ table inet filter { counter samba-tx {} counter http-tx {} counter tftp-tx {} + counter pgbackrest-tx {} counter tx {} @@ -189,6 +191,8 @@ table inet filter { iifname { lan, mgmt } udp dport 69 counter name tftp-rx accept + iifname yggdrasil tcp dport 8432 counter name pgbackrest-rx accept + ct state {established, related} counter name established-rx accept @@ -235,6 +239,8 @@ table inet filter { udp sport 69 counter name tftp-tx accept udp dport 69 counter name tftp-tx accept + tcp sport 8432 counter name pgbackrest-tx accept + counter name tx } diff --git a/hosts/vidhar/pgbackrest/ca/ca.crt b/hosts/vidhar/pgbackrest/ca/ca.crt new file mode 100644 index 00000000..6be81a1d --- /dev/null +++ b/hosts/vidhar/pgbackrest/ca/ca.crt @@ -0,0 +1,12 @@ +-----BEGIN CERTIFICATE----- +MIIBrjCCAS6gAwIBAgIUXY4sW3OoefBUAZqzZIvlv284d64wBQYDK2VxMB8xHTAb +BgNVBAMMFHBnYmFja3Jlc3QueWdnZHJhc2lsMB4XDTIyMTEyMTE0MTUzMloXDTMy +MTEyMTE0MjAzMlowHzEdMBsGA1UEAwwUcGdiYWNrcmVzdC55Z2dkcmFzaWwwQzAF +BgMrZXEDOgDeYXclDR4mFv4plX6mKS1j5VxF1bFgoQbAuqb/c7KMZe/RxNiiyp82 +ZCAfIaNMIFV3lMMc/j7VkICjYzBhMB8GA1UdIwQYMBaAFO+/yfEkwcLr+vNPIsyC +W86UwJ3aMB0GA1UdDgQWBBTvv8nxJMHC6/rzTyLMglvOlMCd2jAOBgNVHQ8BAf8E +BAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAFBgMrZXEDcwCJifNBFrOgzYYnZtvR6jig +OJp3JQRDCpIeFegO2Hnt8CN/y0dLsGI9OS9LsKq7/2NlHGUfqBpoPACQJcI+DDgd +EZcU+ibTfJmYOu8E3ZbMnbuB22MS7+WPcqAy4Jq/P0C8Ifz83VubogwgcPlLLRiC +FgA= +-----END CERTIFICATE----- diff --git a/hosts/vidhar/pgbackrest/ca/ca.key b/hosts/vidhar/pgbackrest/ca/ca.key new file mode 100644 index 00000000..4c92fb3f --- /dev/null +++ b/hosts/vidhar/pgbackrest/ca/ca.key @@ -0,0 +1,21 @@ +{ + "data": "ENC[AES256_GCM,data:wSkqm/wM9f4HixP3obg6kA1d4cpNOMAnEsfNO5O47LKGZOpAmONTSqfVrLPoL3ZiLacYIuAYWk5hR/n0MkRinrHAmI/HHh/66G4LoIX2HZU2QmdsTJh4sVRbby8S/rfEVAlmJ10JYL2nZvyEt4JANmGC1WARXtR7eIGEU7Cv0SmAdXv9VsDYDxorupU4//gid7CpFj4cjS/5c2Y8,iv:Ix1Zg68ewK5QPqsWj+7Lxeete5AHPJHKWx+M4Z1M4Uk=,tag:aS2kAsTbmF1iYxwdvH528Q==,type:str]", + "sops": { + "kms": null, + "gcp_kms": null, + "azure_kv": null, + "hc_vault": null, + "age": null, + "lastmodified": "2022-11-21T14:20:32Z", + "mac": "ENC[AES256_GCM,data:9iFROHIjheIRb2dTR2VAyZLsM+z6RiPMQPV3qwgGvJfeSGEFWsv9Jg7lBhWAJvWKfEZVptClnGAMbUh2bGTkLbT1JOy10xJsVGk5FrUpPuYT3stJeynNKxfloeoF9WKSIdSLx3blO0bZzqyjmCxR2rJk8FtslWqJUEJsHtYhnyY=,iv:XJT56EroPUlWnWlPIp/vsJIzO3FxZAsZbf0knxXHvuw=,tag:k8zThN9xS6pHq+waAy/HQQ==,type:str]", + "pgp": [ + { + "created_at": "2022-11-21T14:20:31Z", + "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DXxoViZlp6dISAQdA0laIXY2D02+/42Fkyxp/H/4DRxpxKGdqoRfFv5LwhAQw\n3M7DZeg0b8rWgC9BL17w54PY1EekyMzW/IxyRTyV0ffYXmn1IJ9VuqMXMteP+i/A\n0lwBdJIPACe5A0IfAMwcguzAB9kwuIkMykvaE9OjtcR/HFF8VU86GoPM0Gc/kUNS\nPbABAy6OuxFZEvziiT56EJ+gbb7u1JlwIrX7zjVAKWeKxQQyFd2gLDIczlD6uw==\n=gmbw\n-----END PGP MESSAGE-----\n", + "fp": "30D3453B8CD02FE2A3E7C78C0FB536FB87AE8F51" + } + ], + "unencrypted_suffix": "_unencrypted", + "version": "3.7.3" + } +} \ No newline at end of file diff --git a/hosts/vidhar/pgbackrest/default.nix b/hosts/vidhar/pgbackrest/default.nix new file mode 100644 index 00000000..49644e51 --- /dev/null +++ b/hosts/vidhar/pgbackrest/default.nix @@ -0,0 +1,101 @@ +{ config, flake, ... }: + +let + surtrRepoCfg = flake.nixosConfigurations."surtr".config.services.pgbackrest.settings.surtr; +in { + config = { + services.pgbackrest = { + enable = true; + tlsServer = { + enable = true; + + user = "pgbackrest"; + group = "pgbackrest"; + }; + + settings = { + "surtr" = { + pg1-host-type = "tls"; + pg1-host = "pgbackrest.surtr.yggdrasil"; + pg1-host-ca-file = toString ./ca/ca.crt; + pg1-host-cert-file = toString ./tls.crt; + pg1-host-key-file = config.sops.secrets."pgbackrest.key".path; + inherit (surtrRepoCfg) pg1-path; + + # repo1-host-type = "tls"; + # repo1-host = "pgbackrest.surtr.yggdrasil"; + # repo1-host-ca-file = toString ./ca/ca.crt; + # repo1-host-cert-file = toString ./tls.crt; + # repo1-host-key-file = config.sops.secrets."pgbackrest.key".path; + # repo1-retention-full-type = "time"; + # repo1-retention-full = 7; + # repo1-retention-archive = 2; + + repo2-path = "/var/lib/pgbackrest"; + repo2-retention-full-type = "time"; + repo2-retention-full = 14; + repo2-retention-archive = 7; + }; + + "global" = { + compress-type = "zst"; + compress-level = 9; + + archive-async = true; + spool-path = "/var/spool/pgbackrest"; + }; + + "global:server" = { + tls-server-address = "2a03:4000:52:ada:1:1::"; + tls-server-ca-file = toString ./ca/ca.crt; + tls-server-cert-file = toString ./tls.crt; + tls-server-key-file = config.sops.secrets."pgbackrest.key".path; + tls-server-auth = ["surtr.yggdrasil=surtr"]; + }; + + "global:archive-push" = { + process-max = 6; + }; + "global:archive-get" = { + process-max = 6; + }; + }; + + backups."surtr-daily" = { + stanza = "surtr"; + repo = "2"; + user = "pgbackrest"; + group = "pgbackrest"; + timerConfig.OnCalendar = "daily Europe/Berlin"; + }; + }; + + systemd.tmpfiles.rules = [ + "d /var/lib/pgbackrest 0750 pgbackrest pgbackrest - -" + "d /var/spool/pgbackrest 0750 pgbackrest pgbackrest - -" + ]; + + users = { + users.pgbackrest = { + name = "pgbackrest"; + group = "pgbackrest"; + isSystemUser = true; + home = "/var/lib/pgbackrest"; + }; + groups.pgbackrest = {}; + }; + + systemd.services."pgbackrest-tls-server".serviceConfig = { + StateDirectory = [ "pgbackrest" ]; + StateDirectoryMode = "0750"; + }; + + sops.secrets."pgbackrest.key" = { + format = "binary"; + sopsFile = ./tls.key; + owner = "pgbackrest"; + group = "pgbackrest"; + mode = "0400"; + }; + }; +} diff --git a/hosts/vidhar/pgbackrest/tls.crt b/hosts/vidhar/pgbackrest/tls.crt new file mode 100644 index 00000000..e807d423 --- /dev/null +++ b/hosts/vidhar/pgbackrest/tls.crt @@ -0,0 +1,12 @@ +-----BEGIN CERTIFICATE----- +MIIB0jCCAVKgAwIBAgIPQAAAAGN7p+4PBkv3Tn05MAUGAytlcTAfMR0wGwYDVQQD +DBRwZ2JhY2tyZXN0LnlnZ2RyYXNpbDAeFw0yMjExMjExNjI2MDVaFw0zMjExMjEx +NjMxMDVaMBsxGTAXBgNVBAMMEHZpZGhhci55Z2dkcmFzaWwwKjAFBgMrZXADIQDy +Wj+rp1Nvyj5TiIdmVV7HW0LUnX2aIQSd8eh5B54BaaOBqDCBpTAfBgNVHSMEGDAW +gBTvv8nxJMHC6/rzTyLMglvOlMCd2jAdBgNVHQ4EFgQUXU/P0Nq4GmxaL3V8Mq39 +YqggieEwDgYDVR0PAQH/BAQDAgXgMAwGA1UdEwEB/wQCMAAwHQYDVR0lBBYwFAYI +KwYBBQUHAwEGCCsGAQUFBwMCMCYGA1UdEQQfMB2CG3BnYmFja3Jlc3QudmlkaGFy +LnlnZ2RyYXNpbDAFBgMrZXEDcwBa1HCz42U2W8lhL3iFQJp/ZoPGm7Iluibvvnh/ +h8ka4mhIcx8mtYp0L04Lte9JWEx+MgOOso6Tk4Bh7xPjJY1uUkwP9ZwsrsJPqIj1 +1nwtHtUiNr3L4IpJkEo3s/52S41KiaiZ0cXnFE2b8pwLTHIJAwA= +-----END CERTIFICATE----- diff --git a/hosts/vidhar/pgbackrest/tls.key b/hosts/vidhar/pgbackrest/tls.key new file mode 100644 index 00000000..6ab308ac --- /dev/null +++ b/hosts/vidhar/pgbackrest/tls.key @@ -0,0 +1,26 @@ +{ + "data": "ENC[AES256_GCM,data:LnaklO60F6ZXJh0mYwG0e9LTU5qmZWKq2/0YxXeH1QAnEcJIWnrTWwQegL3UJYMf3kOqKJmAcc2VX1nrxe+GRAUUwgVojxS+VFxeSjACNnpe0Zgfgj5ps3GJME3gpmfey+fgnbIFkI8w5UpRtvz7Evj6dJHMGTE=,iv:Q5rIm2GFjJT0ensa+5ILN/yNhjHyxFhZh5q6hh8hDW0=,tag:bCGcF2v+JnWexJb4C35dWA==,type:str]", + "sops": { + "kms": null, + "gcp_kms": null, + "azure_kv": null, + "hc_vault": null, + "age": null, + "lastmodified": "2022-11-21T14:21:06Z", + "mac": "ENC[AES256_GCM,data:OQnaCFEsi5Xka2L7KoC0UX0L+NtihG1hk7koxH51WiiL/JF1NrOs7PpgNbhVzqiAPWlBF1X/2ZhWyEZris9iVZ9RKa1lgF2VXjuwVHZNGA9G9Dr0ipriupOEdQABRA2MM0PlfdW7CdbzxmBcA4uwfL3m4b0uMB87A/cRG8mSm3U=,iv:2yuhHIjWRHipcOx+2hFUx2RJG/L/icGMH0QxR9w+MTM=,tag:pnwNVPzyqu4t6AklWd6HGA==,type:str]", + "pgp": [ + { + "created_at": "2022-11-21T14:21:06Z", + "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DXxoViZlp6dISAQdARaz8S4iFbM7+9cUv/WGQDsbnv51AKznQzs3W31w4Cy0w\nh3UzddwF0lH57GYBnVN6S8h5zEjbtz6tRHVsim6ltnVGmsT+t+fmEbASoPF0mvmc\n0lwB9JoMB9l32cFeCQ6Y1Hxryvu/FeL+iXe+7zouKpW67HQ235+Zx5481xxOg1fy\nwmDb+iZ9R+iNO5twraf1BOG+3y8yrJpZV7SZq4H958Kk35QnHlRiPqDfkx9NEg==\n=GAV2\n-----END PGP MESSAGE-----\n", + "fp": "30D3453B8CD02FE2A3E7C78C0FB536FB87AE8F51" + }, + { + "created_at": "2022-11-21T14:21:06Z", + "enc": "-----BEGIN PGP MESSAGE-----\n\nhF4DbYDvGI0HDr0SAQdAgjL9+LcR5m5vHngB9DWE2zfkjsQDsIKrEw2RLKrKdVMw\nQ5B131gL7QKEfAc0vd+HQDANo/pfB9ArI/lNkVvlBYfbO8paadJWvDt9fdmOtJ9J\n0lwBcT1xLhPxCrUVEY1Clsv4y3liNZ78iOBuqaOx0W1A7CQonM2B9ghTDq4bsEE0\n8CxD/mNCn/D8WOqu4dJg6wvIzkk6faSBCbxBjmzTcJ6oDj9RdnnnZ6M/uNWw7g==\n=jZqN\n-----END PGP MESSAGE-----\n", + "fp": "A1C7C95E6CAF0A965CB47277BCF50A89C1B1F362" + } + ], + "unencrypted_suffix": "_unencrypted", + "version": "3.7.3" + } +} \ No newline at end of file -- cgit v1.2.3