mail_users.sql
2.04 KB
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
CREATE OR REPLACE VIEW mail_users
AS
SELECT
users.login || '@' || domains.name as username,
users.crypted_password as crypted_password,
as passwd,
'' as clearpasswd,
5000 as uid,
5000 as gid,
'/home/vmail/' || domains.name as home,
users.login as maildir,
NULL as quota,
profiles.name as fullname,
'' as options,
users.crypted_password as pam_passwd
from users
JOIN profiles on
(profiles.user_id = users.id and
profiles.type = 'Person')
JOIN environments on
(environments.id = profiles.environment_id)
JOIN domains on
(
(
profiles.preferred_domain is null and
domains.owner_id = environments.id and
domains.owner_type = 'Environment'
)
OR
(
profiles.preferred_domain_id is not null and
domain.owner_id = profiles.id and
domains.owner_type = 'Profile'
)
)
WHERE
users.password_type = 'crypt'
AND domains.is_default
AND users.enable_email;
CREATE OR REPLACE VIEW mail_aliases
AS
SELECT
users.login || '@' || domains_from.name as source,
users.login || '@' || domains_to.name as destination
from users
JOIN profiles on
(profiles.user_id = users.id and
profiles.type = 'Person')
JOIN environments on
(environments.id = profiles.environment_id)
JOIN domains domains_from on
(domains_from.owner_id = environments.id and
domains_from.owner_type = 'Environment' and
not domains_from.is_default)
JOIN domains domains_to on
(domains_to.owner_id = environments.id and
domains_to.owner_type = 'Environment' and
domains_to.is_default)
WHERE
users.enable_email;