From a348ad9cd88d2ae87a879d72453bc6ace03a6590 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Thu, 5 May 2022 19:42:27 +0200 Subject: ... --- hosts/surtr/email/default.nix | 2 +- hosts/surtr/postgresql.nix | 4 +++- 2 files changed, 4 insertions(+), 2 deletions(-) (limited to 'hosts') 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; ''} ''; -- cgit v1.2.3