surtr: postgresql email

1 files changed, 35 insertions, 2 deletions

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;
+        ''}
       '';
     };
   };