4 files changed, 61 insertions, 17 deletions

diff --git a/flake.lock b/flake.lock
index 344614d9..66a9c19a 100644
--- a/flake.lock
+++ b/flake.lock
@@ -64,11 +64,11 @@
         ]
       },
       "locked": {
-        "lastModified": 1651886851,
-        "narHash": "sha256-kbXOJSf1uho0/7P54nZkJdJY3oAelIjyc6tfiRhaXJI=",
+        "lastModified": 1652214259,
+        "narHash": "sha256-kbribVik1m3SU6QNpZ3euybljqs0CEQ0lEEz7MN+u8U=",
         "owner": "nix-community",
         "repo": "home-manager",
-        "rev": "882bd8118bdbff3a6e53e5ced393932b351ce2f6",
+        "rev": "f735a8502b098962ae965c2600c7be9f7711b814",
         "type": "github"
       },
       "original": {
@@ -80,11 +80,11 @@
     },
     "nixpkgs": {
       "locked": {
-        "lastModified": 1652107188,
-        "narHash": "sha256-6CVG9pABO7FB1qP/d7gwuP166COGHv3WC1AQ5r/n1ds=",
+        "lastModified": 1652424998,
+        "narHash": "sha256-6rqAwXEVlnXzCcju+ZcxZnLNql6bdiG9deREbBAb2Pc=",
         "owner": "NixOS",
         "repo": "nixpkgs",
-        "rev": "403d21a9416b865c9f1da016a110c2610610b4c5",
+        "rev": "d999ca3e08b053b80d4e52e700a4627e692479eb",
         "type": "github"
       },
       "original": {
diff --git a/flake.nix b/flake.nix
index 163e7bd5..4893cd19 100644
--- a/flake.nix
+++ b/flake.nix
@@ -210,10 +210,10 @@
               system = {
                 path = deploy-rs.lib.${self.nixosConfigurations.${hostname}.config.nixpkgs.system}.activate.nixos self.nixosConfigurations.${hostname};
               };
-            } // (mapAttrs (_user: usercfg: {
-              user = usercfg.home.username;
-              path = activateHomeManager (self.nixosConfigurations.${hostname}.config.nixpkgs.system) usercfg.home;
-            }) self.nixosConfigurations.${hostname}.config.home-manager.users);
+            }; # // (mapAttrs (_user: usercfg: {
+            #   user = usercfg.home.username;
+            #   path = activateHomeManager (self.nixosConfigurations.${hostname}.config.nixpkgs.system) usercfg.home;
+            # }) self.nixosConfigurations.${hostname}.config.home-manager.users);
           }) (nixImport { dir = ./hosts; _import = (_path: name: name); });
           overrides = if pathExists ./deploy then nixImport { dir = ./deploy; _import = path: _name: import (./deploy + "/${path}") inputs; } else {};
           filterEnabled = attrs: mapAttrs (_n: v: filterAttrs (n: _v: n != "enabled") v) (filterAttrs (_n: v: v.enabled or true) attrs);
diff --git a/hosts/surtr/email/default.nix b/hosts/surtr/email/default.nix
index 2ddff519..57883864 100644
--- a/hosts/surtr/email/default.nix
+++ b/hosts/surtr/email/default.nix
@@ -37,7 +37,7 @@ in {
     services.postfix = {
       enable = true;
       hostname = "surtr.yggdrasil.li";
-      recipientDelimiter = "+";
+      recipientDelimiter = "";
       setSendmail = true;
       postmasterAlias = ""; rootAlias = ""; extraAliases = "";
       destination = [];
@@ -100,6 +100,11 @@ in {
           "reject_unauth_pipelining"
           "reject_non_fqdn_recipient"
           "reject_unknown_recipient_domain"
+          "check_recipient_access pgsql:${pkgs.writeText "check_recipient_access.cf" ''
+            hosts = postgresql:///email
+            dbname = email
+            query = SELECT action FROM virtual_mailbox_access WHERE lookup = '%s'
+          ''}"
           "permit_mynetworks"
           "check_ccert_access ${relay_ccert}"
           "reject_non_fqdn_helo_hostname"
@@ -156,7 +161,8 @@ in {
           dbname = email
           query = SELECT 1 FROM virtual_mailbox_mapping WHERE lookup = '%s'
         ''}'';
-        virtual_transport = "lmtp:unix:/run/postfix/dovecot-lmtp";
+        dvlmtp_destination_recipient_limit = "1";
+        virtual_transport = "dvlmtp:unix:/run/postfix/dovecot-lmtp";
       };
       masterConfig = {
         smtps = {
@@ -174,6 +180,12 @@ in {
             "-o" ''smtpd_milters=${config.services.opendkim.socket}''
           ];
         };
+        dvlmtp = {
+          command = "lmtp";
+          args = [
+            "flags=DORX"
+          ];
+        };
       };
     };
 
@@ -375,7 +387,7 @@ in {
             args = ${pkgs.writeText "dovecot-sql.conf" ''
               driver = pgsql
               connect = dbname=email
-              user_query = SELECT DISTINCT ON (local IS NULL) "user", quota_rule, 'dovecot2' as uid, 'dovecot2' as gid FROM lmtp_mapping WHERE (local = '%n' AND domain = '%d') OR (local IS NULL AND domain = '%d') ORDER BY (local IS NULL) ASC
+              user_query = SELECT DISTINCT ON (extension IS NULL, local IS NULL) "user", quota_rule, 'dovecot2' as uid, 'dovecot2' as gid FROM lmtp_mapping WHERE CASE WHEN extension IS NOT NULL AND local IS NOT NULL THEN ('%n' :: citext) = local || '+' || extension AND domain = ('%d' :: citext) WHEN local IS NOT NULL THEN (local = ('%n' :: citext) OR ('%n' :: citext) ILIKE local || '+%%') AND domain = ('%d' :: citext) WHEN extension IS NOT NULL THEN ('%n' :: citext) ILIKE '%%+' || extension AND domain = ('%d' :: citext) ELSE domain = ('%d' :: citext) END ORDER BY (extension IS NULL) ASC, (local IS NULL) ASC
             ''}
 
             skip = never
@@ -387,7 +399,8 @@ in {
         mail_plugins = $mail_plugins quota
         mailbox_list_index = yes
         postmaster_address = postmaster@yggdrasil.li
-        recipient_delimiter = +
+        recipient_delimiter = 
+        auth_username_chars = abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-+_@
 
         service lmtp {
           vsz_limit = 1G
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;
         ''}
       '';
     };