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
|
{ pkgs, sources, config, ... }:
let
versioning = sources.psql-versioning.src;
in {
config = {
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 = {
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 email postgres -eXf ${pkgs.writeText "email.sql" ''
\i ${versioning + "/install.versioning.sql"}
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;
''}
'';
};
};
}
|
