1 files changed, 34 insertions, 3 deletions

diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index abd2cb26..a5e93ecf 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -1,4 +1,4 @@
-{ pkgs, sources, ... }:
+{ pkgs, sources, config, ... }:
 let
   versioning = sources.psql-versioning.src;
 in {
@@ -22,8 +22,19 @@ in {
       '';
     };
 
-    systemd.services.postgresql = {
-      postStart = ''
+    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 email postgres -eXf ${pkgs.writeText "email.sql" ''
           \i ${versioning + "/install.versioning.sql"}
 
@@ -78,6 +89,26 @@ in {
           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;
         ''}
       '';
     };