From 427427ec3bcb58eadaa9c62477f70a5f3c6f39e0 Mon Sep 17 00:00:00 2001
From: Bryan Newbold <bnewbold@archive.org>
Date: Wed, 16 Jan 2019 13:24:08 -0800
Subject: mailserver role

---
 roles/mailserver/templates/mailserver.sql.j2 | 57 ++++++++++++++++++++++++++++
 1 file changed, 57 insertions(+)
 create mode 100644 roles/mailserver/templates/mailserver.sql.j2

(limited to 'roles/mailserver/templates/mailserver.sql.j2')

diff --git a/roles/mailserver/templates/mailserver.sql.j2 b/roles/mailserver/templates/mailserver.sql.j2
new file mode 100644
index 0000000..203c3d8
--- /dev/null
+++ b/roles/mailserver/templates/mailserver.sql.j2
@@ -0,0 +1,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 %}
-- 
cgit v1.2.3