2 files changed, 14 insertions, 1 deletions

diff --git a/hosts/surtr/email/default.nix b/hosts/surtr/email/default.nix
index aa901f6f..265207cc 100644
--- a/hosts/surtr/email/default.nix
+++ b/hosts/surtr/email/default.nix
@@ -349,7 +349,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 (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)
             ''}
 
             skip = never
diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index 28ccd8c4..0352c27f 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -60,10 +60,23 @@ in {
           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 VIWE 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_quote_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;
         ''}
       '';