spm: list/get/patch mailbox mappings

1 files changed, 15 insertions, 0 deletions

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" ''