2 files changed, 51 insertions, 7 deletions

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