From 84f2affd66a0ff3947b91a30308cb8e6a8ff7594 Mon Sep 17 00:00:00 2001
From: Gregor Kleen <gkleen@yggdrasil.li>
Date: Thu, 19 May 2022 22:05:02 +0200
Subject: surtr: ...

---
 hosts/surtr/postgresql.nix | 62 +++++++++++++++++++++++++++++++++++-----------
 1 file changed, 48 insertions(+), 14 deletions(-)

(limited to 'hosts/surtr/postgresql.nix')

diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index a5e93ecf..66ce60eb 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -6,20 +6,6 @@ in {
     services.postgresql = {
       enable = true;
       package = pkgs.postgresql_14;
-      initialScript = pkgs.writeText "schema.sql" ''
-        CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
-        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";
-        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix";
-        CREATE USER "dovecot2";
-        GRANT CONNECT ON DATABASE "email" TO "dovecot2";
-        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2";
-      '';
     };
 
     systemd.services.migrate-postgresql = {
@@ -35,9 +21,45 @@ in {
 
       path = [ config.services.postgresql.package ];
       script = ''
+        psql postgres postgres -eXf ${pkgs.writeText "schema.sql" ''
+          CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
+          CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
+        ''}
+
+        psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" ''
+          \i ${versioning + "/install.versioning.sql"}
+
+          BEGIN;
+          SELECT _v.register_patch('000-matrix-users', null, null);
+
+          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";
+          COMMIT;
+        ''}
+
         psql email postgres -eXf ${pkgs.writeText "email.sql" ''
           \i ${versioning + "/install.versioning.sql"}
 
+          BEGIN;
+          SELECT _v.register_patch('000-users', null, null);
+
+          CREATE USER "postfix";
+          GRANT CONNECT ON DATABASE "email" TO "postfix";
+          ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix";
+          CREATE USER "dovecot2";
+          GRANT CONNECT ON DATABASE "email" TO "dovecot2";
+          ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2";
+          COMMIT;
+
+          BEGIN;
+          SELECT _v.register_patch('001-spm', null, null);
+
+          CREATE USER "spm";
+          GRANT CONNECT ON DATABASE "email" TO "spm";
+          ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm";
+          COMMIT;
+
           BEGIN;
           SELECT _v.register_patch('000-base', null, null);
 
@@ -109,6 +131,18 @@ in {
           ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey;
           ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT;
           COMMIT;
+
+          BEGIN;
+          SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null);
+
+          GRANT INSERT ON "mailbox_mapping" TO "spm";
+          COMMIT;
+
+          BEGIN;
+          SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null);
+
+          GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm";
+          COMMIT;
         ''}
       '';
     };
-- 
cgit v1.2.3