From 6f49d8632e6ceccb0399764e7da86cc4cba9ab04 Mon Sep 17 00:00:00 2001 From: Gregor Kleen Date: Fri, 16 Dec 2022 20:29:28 +0100 Subject: spm: list/get/patch mailbox mappings --- hosts/surtr/postgresql/default.nix | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'hosts/surtr') diff --git a/hosts/surtr/postgresql/default.nix b/hosts/surtr/postgresql/default.nix index 9cf494ae..907c652d 100644 --- a/hosts/surtr/postgresql/default.nix +++ b/hosts/surtr/postgresql/default.nix @@ -220,6 +220,21 @@ in { ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy"; GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy"; COMMIT; + + BEGIN; + SELECT _v.register_patch('008-mailbox-mapping-claim', ARRAY['000-base', '002-citext', '003-extensions'], null); + ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_extension_check; + ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_extension_check CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' AND extension <> ''' AND local IS DISTINCT FROM ''' ELSE true END); + + ALTER TABLE mailbox_mapping DROP CONSTRAINT local_domain_unique; + ALTER TABLE mailbox_mapping ADD CONSTRAINT local_domain_unique UNIQUE (local, domain) WHERE extension IS null; + + ALTER TABLE mailbox_mapping ADD CONSTRAINT local_extension_domain_unique UNIQUE (local, extension, domain); + + ALTER TABLE mailbox_mapping ADD COLUMN reject bool NOT NULL DEFAULT false; + + CREATE OR REPLACE 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 OR reject THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping; + COMMIT; ''} psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" '' -- cgit v1.2.3