...

2 files changed, 4 insertions, 2 deletions

diff --git a/hosts/surtr/email/default.nix b/hosts/surtr/email/default.nix
index eb4ec69e..b4ae5ffb 100644
--- a/hosts/surtr/email/default.nix
+++ b/hosts/surtr/email/default.nix
@@ -293,7 +293,7 @@ in {
           args = ${pkgs.writeText "dovecot-sql.conf" ''
             driver = pgsql
             connect = host=localhost dbname=email
-            user_query = SELECT mailbox AS user, quota_rule FROM mailbox WHERE mailbox = '%u'
+            user_query = SELECT user, quota_rule FROM imap_user WHERE user = '%u'
           ''}
           default_fields = uid=dovecot2 gid=dovecot2
         }
diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index 932ff531..e3384fbd 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -34,7 +34,7 @@ in {
             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),
-            quota_rule text GENERATED ALWAYS AS (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) STORED
+            CONSTRAINT mailbox_unique UNIQUE mailbox
           );
           CREATE TABLE mailbox_mapping (
             id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
@@ -47,6 +47,8 @@ in {
 
           CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM virtual_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;
         ''}
       '';