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
|
-- If tables are not dropped, have to truncate before insert or use "insert or replace" (not postgres compatible)
DROP TABLE IF EXISTS "virtual_users";
DROP TABLE IF EXISTS "virtual_aliases";
DROP TABLE IF EXISTS "virtual_domains";
CREATE TABLE IF NOT EXISTS "virtual_domains" (
"id" SERIAL,
"name" TEXT NOT NULL,
PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX name_idx ON virtual_domains (name);
CREATE TABLE IF NOT EXISTS "virtual_users" (
"id" SERIAL,
"domain_id" int NOT NULL,
"password" TEXT NOT NULL,
"email" TEXT NOT NULL UNIQUE,
PRIMARY KEY ("id"),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX email_idx ON virtual_users (email);
CREATE TABLE IF NOT EXISTS "virtual_aliases" (
"id" SERIAL,
"domain_id" int NOT NULL,
"source" TEXT NOT NULL,
"destination" TEXT NOT NULL,
PRIMARY KEY ("id"),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
);
CREATE INDEX source_idx ON virtual_aliases (source);
{% for virtual_domain in mail_virtual_domains %}
INSERT INTO "virtual_domains" ("id", "name")
VALUES ('{{ virtual_domain.pk_id }}', '{{ virtual_domain.name }}');
{% endfor %}
{% for virtual_user in mail_virtual_users %}
INSERT INTO "virtual_users" ("domain_id", "password" , "email")
VALUES (
'{{ virtual_user.domain_pk_id }}',
'{{ virtual_user.password | doveadm_pw_hash }}',
'{{ virtual_user.account }}@{{ virtual_user.domain }}'
);
{% endfor %}
{% if mail_virtual_aliases is defined %}
{% for virtual_alias in mail_virtual_aliases %}
INSERT INTO "virtual_aliases" ("domain_id", "source", "destination")
VALUES ('{{ virtual_alias.domain_pk_id }}', '{{ virtual_alias.source }}', '{{virtual_alias.destination }}');
{% endfor %}
{% endif %}
|
