path: root/hosts/surtr/postgresql/default.nix

{ 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;

          BEGIN;
          SELECT _v.register_patch('008-mailbox-mapping-claim', ARRAY['000-base', '002-citext', '003-extensions'], null);
          ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_extension_check;
          ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_extension_check CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' AND extension <> ''' AND local IS DISTINCT FROM ''' ELSE true END);

          ALTER TABLE mailbox_mapping DROP CONSTRAINT local_domain_unique;
          CREATE UNIQUE INDEX local_domain_unique ON mailbox_mapping (local, domain) WHERE extension IS null;

          CREATE UNIQUE INDEX local_extension_domain_unique ON mailbox_mapping (local, extension, domain);
          ALTER TABLE mailbox_mapping ADD CONSTRAINT local_extension_domain_unique UNIQUE USING INDEX local_extension_domain_unique;

          ALTER TABLE mailbox_mapping ADD COLUMN reject bool NOT NULL DEFAULT false;

          CREATE OR REPLACE 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 OR reject THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping;
          COMMIT;

          BEGIN;
          SELECT _v.register_patch('009-spm-update-mapping', ARRAY['000-base', '002-citext', '003-extensions'], null);

          GRANT UPDATE ON "mailbox_mapping" TO "spm";
          COMMIT;

          BEGIN;
          SELECT _v.register_patch('010-spm-delete-mapping', ARRAY['000-base', '002-citext', '003-extensions'], null);

          GRANT DELETE ON "mailbox_mapping" TO "spm";
          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;
        ''}
      '';
    };
  };
}