summaryrefslogtreecommitdiff
path: root/hosts/surtr/postgresql.nix
diff options
context:
space:
mode:
Diffstat (limited to 'hosts/surtr/postgresql.nix')
-rw-r--r--hosts/surtr/postgresql.nix172
1 files changed, 0 insertions, 172 deletions
diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
deleted file mode 100644
index c10c5084..00000000
--- a/hosts/surtr/postgresql.nix
+++ /dev/null
@@ -1,172 +0,0 @@
1{ pkgs, sources, config, ... }:
2let
3 versioning = sources.psql-versioning.src;
4in {
5 config = {
6 services.postgresql = {
7 enable = true;
8 package = pkgs.postgresql_14;
9 };
10
11 systemd.services.migrate-postgresql = {
12 after = [ "postgresql.service" ];
13 bindsTo = [ "postgresql.service" ];
14 wantedBy = [ "postgresql.service" ];
15
16 serviceConfig = {
17 Type = "oneshot";
18 inherit (config.systemd.services.postgresql.serviceConfig) User Group;
19 RemainAfterExit = true;
20 };
21
22 path = [ config.services.postgresql.package ];
23 script = ''
24 psql postgres postgres -eXf ${pkgs.writeText "schema.sql" ''
25 CREATE DATABASE "matrix-synapse" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
26 CREATE DATABASE "email" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
27 CREATE DATABASE "etebase" WITH TEMPLATE "template0" ENCODING "UTF8" LOCALE "C";
28 ''}
29
30 psql matrix-synapse postgres -eXf ${pkgs.writeText "matrix-synapse.sql" ''
31 \i ${versioning + "/install.versioning.sql"}
32
33 BEGIN;
34 SELECT _v.register_patch('000-matrix-users', null, null);
35
36 CREATE USER "matrix-synapse";
37 GRANT ALL PRIVILEGES ON DATABASE "matrix-synapse" TO "matrix-synapse";
38 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "matrix-synapse";
39 COMMIT;
40 ''}
41
42 psql email postgres -eXf ${pkgs.writeText "email.sql" ''
43 \i ${versioning + "/install.versioning.sql"}
44
45 BEGIN;
46 SELECT _v.register_patch('000-users', null, null);
47
48 CREATE USER "postfix";
49 GRANT CONNECT ON DATABASE "email" TO "postfix";
50 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix";
51 CREATE USER "dovecot2";
52 GRANT CONNECT ON DATABASE "email" TO "dovecot2";
53 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "dovecot2";
54 COMMIT;
55
56 BEGIN;
57 SELECT _v.register_patch('001-spm', null, null);
58
59 CREATE USER "spm";
60 GRANT CONNECT ON DATABASE "email" TO "spm";
61 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES to "spm";
62 COMMIT;
63
64 BEGIN;
65 SELECT _v.register_patch('000-base', null, null);
66
67 CREATE TABLE mailbox (
68 id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
69 mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
70 quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END),
71 CONSTRAINT mailbox_unique UNIQUE (mailbox)
72 );
73 CREATE TABLE mailbox_mapping (
74 id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
75 local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''),
76 domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''),
77 mailbox uuid REFERENCES mailbox(id),
78 CONSTRAINT local_domain_unique UNIQUE (local, domain)
79 );
80 CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL;
81
82 CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
83 CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox;
84
85 CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox;
86 COMMIT;
87
88 BEGIN;
89 SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null);
90
91 CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox.mailbox AS "user", (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule, local, domain FROM mailbox INNER JOIN mailbox_mapping ON mailbox.id = mailbox_mapping.mailbox;
92 COMMIT;
93
94 BEGIN;
95 SELECT _v.register_patch('002-citext', ARRAY['000-base'], null);
96
97 DROP VIEW virtual_mailbox_domain;
98 DROP VIEW virtual_mailbox_mapping;
99 DROP VIEW imap_user;
100 DROP VIEW lmtp_mapping;
101
102 CREATE EXTENSION citext;
103
104 ALTER TABLE mailbox ALTER mailbox TYPE citext;
105 ALTER TABLE mailbox_mapping ALTER local TYPE citext;
106 ALTER TABLE mailbox_mapping ALTER domain TYPE citext;
107
108 CREATE VIEW mailbox_quota_rule (id, mailbox, quota_rule) AS SELECT id, mailbox, (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes END) AS quota_rule FROM mailbox;
109
110 CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
111 CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping;
112 CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule;
113 CREATE VIEW lmtp_mapping ("user", quota_rule, local, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox;
114 COMMIT;
115
116 BEGIN;
117 SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null);
118
119 ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END);
120
121 DROP VIEW virtual_mailbox_mapping;
122 DROP VIEW lmtp_mapping;
123
124 CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || (CASE WHEN extension IS NULL THEN ''' ELSE '+' || extension END) || '@' || domain AS lookup FROM mailbox_mapping WHERE mailbox IS NOT NULL;
125 CREATE 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 THEN 'REJECT' ELSE 'DUNNO' END AS action FROM mailbox_mapping;
126 CREATE VIEW lmtp_mapping ("user", quota_rule, local, extension, domain) AS SELECT mailbox_quota_rule.mailbox AS "user", quota_rule, local, extension, domain FROM mailbox_quota_rule INNER JOIN mailbox_mapping ON mailbox_quota_rule.id = mailbox_mapping.mailbox;
127 COMMIT;
128
129 BEGIN;
130 SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null);
131
132 ALTER TABLE mailbox_mapping DROP CONSTRAINT mailbox_mapping_mailbox_fkey;
133 ALTER TABLE mailbox_mapping ADD CONSTRAINT mailbox_mapping_mailbox_fkey FOREIGN KEY (mailbox) REFERENCES mailbox(id) ON DELETE CASCADE ON UPDATE RESTRICT;
134 COMMIT;
135
136 BEGIN;
137 SELECT _v.register_patch('005-spm', ARRAY['000-base', '002-citext', '003-extensions'], null);
138
139 GRANT INSERT ON "mailbox_mapping" TO "spm";
140 COMMIT;
141
142 BEGIN;
143 SELECT _v.register_patch('006-spm-mailbox', ARRAY['000-base'], null);
144
145 GRANT SELECT ON ALL TABLES IN SCHEMA public TO "spm";
146 COMMIT;
147
148 BEGIN;
149 SELECT _v.register_patch('007-ccert-sender-policy', ARRAY['000-base'], null);
150
151 CREATE USER "postfix-ccert-sender-policy";
152 GRANT CONNECT ON DATABASE "email" TO "postfix-ccert-sender-policy";
153 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "postfix-ccert-sender-policy";
154 GRANT SELECT ON ALL TABLES IN SCHEMA public TO "postfix-ccert-sender-policy";
155 COMMIT;
156 ''}
157
158 psql etebase postgres -eXf ${pkgs.writeText "etebase.sql" ''
159 \i ${versioning + "/install.versioning.sql"}
160
161 BEGIN;
162 SELECT _v.register_patch('000-user', null, null);
163
164 CREATE USER "etebase";
165 GRANT ALL PRIVILEGES ON DATABASE "etebase" TO "etebase";
166 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "etebase";
167 COMMIT;
168 ''}
169 '';
170 };
171 };
172}
generated by cgit v1.2.3 (git 2.25.1) at 2026-09-22 22:32:44 +0000