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/postgresql/default.nix | 239 +++++++++++++++++++++++++++++++++++++ 1 file changed, 239 insertions(+) create mode 100644 hosts/surtr/postgresql/default.nix (limited to 'hosts/surtr/postgresql/default.nix') 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; + ''} + ''; + }; + }; +} -- cgit v1.2.3