From 74805f291c6f18892165cee1e81fe666d15f681f Mon Sep 17 00:00:00 2001
From: Gregor Kleen <gkleen@yggdrasil.li>
Date: Thu, 5 May 2022 18:03:32 +0200
Subject: surtr: postgresql email

---
 _sources/generated.json    | 18 ++++++++++++++++++
 _sources/generated.nix     | 12 ++++++++++++
 hosts/surtr/postgresql.nix | 37 +++++++++++++++++++++++++++++++++++--
 nvfetcher.toml             |  6 +++++-
 4 files changed, 70 insertions(+), 3 deletions(-)

diff --git a/_sources/generated.json b/_sources/generated.json
index 0f9eb31f..30b7b120 100644
--- a/_sources/generated.json
+++ b/_sources/generated.json
@@ -131,6 +131,24 @@
         },
         "version": "c1219b6ac3ee3de887e6a36ae41a8e478835ae92"
     },
+    "psql-versioning": {
+        "cargoLocks": null,
+        "extract": null,
+        "name": "psql-versioning",
+        "passthru": null,
+        "pinned": false,
+        "src": {
+            "deepClone": false,
+            "fetchSubmodules": false,
+            "leaveDotGit": false,
+            "name": null,
+            "rev": "3e578ff5e5aa6c7e5459dbfa842a64a1b2674b2e",
+            "sha256": "sha256-j+njRssJHTdNV3FbcA3MdUmzCaJxuYBrC0qwtK3HoyY=",
+            "type": "git",
+            "url": "https://gitlab.com/depesz/Versioning"
+        },
+        "version": "3e578ff5e5aa6c7e5459dbfa842a64a1b2674b2e"
+    },
     "uhk-agent": {
         "cargoLocks": null,
         "extract": null,
diff --git a/_sources/generated.nix b/_sources/generated.nix
index 58a1ff0a..30b4aed6 100644
--- a/_sources/generated.nix
+++ b/_sources/generated.nix
@@ -81,6 +81,18 @@
       sha256 = "sha256-+DoKPIulQA3VSeXo8DjoxnPwDfcuCO5YHpXmB+M7EWk=";
     });
   };
+  psql-versioning = {
+    pname = "psql-versioning";
+    version = "3e578ff5e5aa6c7e5459dbfa842a64a1b2674b2e";
+    src = fetchgit {
+      url = "https://gitlab.com/depesz/Versioning";
+      rev = "3e578ff5e5aa6c7e5459dbfa842a64a1b2674b2e";
+      fetchSubmodules = false;
+      deepClone = false;
+      leaveDotGit = false;
+      sha256 = "sha256-j+njRssJHTdNV3FbcA3MdUmzCaJxuYBrC0qwtK3HoyY=";
+    };
+  };
   uhk-agent = {
     pname = "uhk-agent";
     version = "1.5.17";
diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index a34bc675..b5035cb2 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -1,5 +1,7 @@
-{ pkgs, ... }:
-{
+{ pkgs, sources, ... }:
+let
+  versioning = sources.pqsl-versioning;
+in {
   config = {
     services.postgresql = {
       enable = true;
@@ -9,6 +11,37 @@
         CREATE USER "matrix-synapse";
         GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse";
         GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse";
+
+        CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
+        CREATE USER "postfix";
+        GRANT CONNECT ON DATABASE "email" TO "postfix";
+        GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix";
+        CREATE USER "dovecot2";
+        GRANT CONNECT ON DATABASE "email" TO "dovecot2";
+        GRANT SELECT ON ALL TABLES IN SCHEMA public TO "dovecot2";
+      '';
+    };
+
+    systemd.services.postgresql = {
+      postStart = ''
+        psql email postgres -eXf ${pkgs.writeText "email.sql" ''
+          \i ${versioning + "/install.versioning.sql"}
+
+          BEGIN;
+          select _v.register_patch('000-base', null, null);
+
+          create table virtual_mailbox (
+            id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
+            local text,
+            CHECK (local IS DISTINCT FROM ''''),
+            domain text NOT NULL,
+            CHECK (domain <> ''''),
+            mailbox text NOT NULL,
+            CHECK (mailbox <> ''''),
+            UNIQUE(COALESCE(local, ''''), domain)
+          );
+          COMMIT;
+        ''}
       '';
     };
   };
diff --git a/nvfetcher.toml b/nvfetcher.toml
index e82051cd..99db67d0 100644
--- a/nvfetcher.toml
+++ b/nvfetcher.toml
@@ -40,4 +40,8 @@ fetch.github = "gesquive/fast-cli"
 
 [xcompose]
 src.git = "https://github.com/kragen/xcompose"
-fetch.github = "kragen/xcompose"
\ No newline at end of file
+fetch.github = "kragen/xcompose"
+
+[psql-versioning]
+src.git = "https://gitlab.com/depesz/Versioning"
+fetch.git = "https://gitlab.com/depesz/Versioning"
\ No newline at end of file
-- 
cgit v1.2.3