1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
{ pkgs, sources, config, ... }:
let
versioning = sources.psql-versioning.src;
in {
config = {
services.postgresql = {
enable = true;
package = pkgs.postgresql_14;
};
systemd.services.migrate-postgresql = {
after = [ "postgresql.service" ];
bindsTo = [ "postgresql.service" ];
wantedBy = [ "postgresql.service" ];
serviceConfig = {
Type = "oneshot";
inherit (config.systemd.services.postgresql.serviceConfig) User Group;
RemainAfterExit = true;
};
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);
CREATE TABLE mailbox (
id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true END),
CONSTRAINT mailbox_unique UNIQUE (mailbox)
);
CREATE TABLE mailbox_mapping (
id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''),
domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''),
mailbox uuid REFERENCES mailbox(id),
CONSTRAINT local_domain_unique UNIQUE (local, domain)
);
CREATE UNIQUE INDEX domain_unique ON mailbox_mapping (domain) WHERE local IS NULL;
CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
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;
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;
COMMIT;
BEGIN;
SELECT _v.register_patch('001-lmtp-mapping', ARRAY['000-base'], null);
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;
COMMIT;
BEGIN;
SELECT _v.register_patch('002-citext', ARRAY['000-base'], null);
DROP VIEW virtual_mailbox_domain;
DROP VIEW virtual_mailbox_mapping;
DROP VIEW imap_user;
DROP VIEW lmtp_mapping;
CREATE EXTENSION citext;
ALTER TABLE mailbox ALTER mailbox TYPE citext;
ALTER TABLE mailbox_mapping ALTER local TYPE citext;
ALTER TABLE mailbox_mapping ALTER domain TYPE citext;
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;
CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM mailbox_mapping;
CREATE VIEW virtual_mailbox_mapping (lookup) AS SELECT (CASE WHEN local IS NULL THEN ''' ELSE local END) || '@' || domain AS lookup FROM mailbox_mapping;
CREATE VIEW imap_user ("user", quota_rule) AS SELECT mailbox AS "user", quota_rule FROM mailbox_quota_rule;
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;
COMMIT;
BEGIN;
SELECT _v.register_patch('003-extensions', ARRAY['000-base', '002-citext'], null);
ALTER TABLE mailbox_mapping ADD COLUMN extension citext CHECK (CASE WHEN extension IS NOT NULL THEN extension NOT LIKE '%+%' ELSE true END);
DROP VIEW virtual_mailbox_mapping;
DROP VIEW lmtp_mapping;
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;
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;
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;
COMMIT;
BEGIN;
SELECT _v.register_patch('004-cascade', ARRAY['000-base'], null);
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;
''}
'';
};
};
}
|
