From e23823d812de7df0eb1c3f8c9df63956158802c6 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Fri, 6 May 2022 00:10:16 +0200 Subject: surtr: ... --- hosts/surtr/email/default.nix | 2 +- hosts/surtr/postgresql.nix | 13 +++++++++++++ 2 files changed, 14 insertions(+), 1 deletion(-) 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; ''} ''; -- cgit v1.2.3