1 files changed, 239 insertions, 0 deletions

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;
+        ''}
+      '';
+    };
+  };
+}