pgbackrest

1 files changed, 0 insertions, 172 deletions

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