nsc0AK6X

1CREATE TABLE IF NOT EXISTS virtual_domains (
2id SERIAL PRIMARY KEY,
3name varchar(100) NOT NULL);
4
5CREATE TABLE IF NOT EXISTS virtual_users (
6id SERIAL PRIMARY KEY,
7domain_id int NOT NULL,
8email varchar(100) NOT NULL,
9password varchar(100) NOT NULL,
10constraint email unique(email),
11FOREIGN KEY (domain_id) REFERENCES virtual_domains (id) ON DELETE CASCADE);
12
13CREATE TABLE IF NOT EXISTS virtual_aliases (
14id SERIAL PRIMARY KEY,
15domain_id int NOT NULL,
16source varchar(100) NOT NULL,
17destination varchar(100) NOT NULL,
18FOREIGN KEY (domain_id) REFERENCES virtual_domains (id) ON DELETE CASCADE);
19
20-- DELETE FROM mailserver.virtual_domains WHERE name='example.org'; 
21
22CREATE USER mailuser WITH PASSWORD 'mailuser';
23GRANT SELECT ON TABLE virtual_domains TO mailuser;
24GRANT SELECT ON TABLE virtual_users TO mailuser;
25GRANT SELECT ON TABLE virtual_aliases TO mailuser;
26
27CREATE DATABASE mailadmin;
28
29-- Here are data fixtures
30INSERT INTO virtual_domains (id,name) VALUES ('1','example.org');
31INSERT INTO virtual_users (id,domain_id,password,email)
32VALUES ('1', '1', '{SHA256-CRYPT}$5$M/GWzmtjsLroRWIf$0qDt7IgXCoCQyvQZO7v.NC46rXzFVBSkZcilijlwgL7', 'john@example.org');
33INSERT INTO virtual_aliases (id,domain_id,source,destination)
34VALUES ('1', '1', 'jack@example.org', 'john@example.org');