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

---
 hosts/surtr/postgresql.nix | 37 +++++++++++++++++++++++++++++++++++--
 1 file changed, 35 insertions(+), 2 deletions(-)

(limited to 'hosts')

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;
+        ''}
       '';
     };
   };
-- 
cgit v1.2.3