65 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
67 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
66 ); |
68 ); |
67 -- Insert default (non) quota limit |
69 -- Insert default (non) quota limit |
68 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
70 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
69 |
71 |
|
72 CREATE TABLE service_set ( |
|
73 ssid bigint NOT NULL DEFAULT nextval('service_set_id'), |
|
74 smtp boolean NOT NULL DEFAULT TRUE, |
|
75 pop3 boolean NOT NULL DEFAULT TRUE, |
|
76 imap boolean NOT NULL DEFAULT TRUE, |
|
77 managesieve boolean NOT NULL DEFAULT TRUE, |
|
78 CONSTRAINT pkey_service_set PRIMARY KEY (ssid), |
|
79 CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, managesieve) |
|
80 ); |
|
81 -- Insert all possible service combinations |
|
82 COPY service_set (smtp, pop3, imap, managesieve) FROM stdin; |
|
83 TRUE TRUE TRUE TRUE |
|
84 FALSE TRUE TRUE TRUE |
|
85 TRUE FALSE TRUE TRUE |
|
86 FALSE FALSE TRUE TRUE |
|
87 TRUE TRUE FALSE TRUE |
|
88 FALSE TRUE FALSE TRUE |
|
89 TRUE FALSE FALSE TRUE |
|
90 FALSE FALSE FALSE TRUE |
|
91 TRUE TRUE TRUE FALSE |
|
92 FALSE TRUE TRUE FALSE |
|
93 TRUE FALSE TRUE FALSE |
|
94 FALSE FALSE TRUE FALSE |
|
95 TRUE TRUE FALSE FALSE |
|
96 FALSE TRUE FALSE FALSE |
|
97 TRUE FALSE FALSE FALSE |
|
98 FALSE FALSE FALSE FALSE |
|
99 \. |
|
100 |
70 CREATE TABLE domain_data ( |
101 CREATE TABLE domain_data ( |
71 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
102 gid bigint NOT NULL DEFAULT nextval('domain_gid'), |
|
103 qid bigint NOT NULL DEFAULT 1, -- default quota limit |
|
104 ssid bigint NOT NULL DEFAULT 1, -- default service_set |
72 tid bigint NOT NULL DEFAULT 1, -- default transport |
105 tid bigint NOT NULL DEFAULT 1, -- default transport |
73 qid bigint NOT NULL DEFAULT 1, -- default quota limit |
|
74 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
106 domaindir varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294 |
75 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
107 CONSTRAINT pkey_domain_data PRIMARY KEY (gid), |
|
108 CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) |
|
109 REFERENCES quotalimit (qid), |
|
110 CONSTRAINT fkey_domain_data_ssid_service_set FOREIGN KEY (ssid) |
|
111 REFERENCES service_set (ssid), |
76 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
112 CONSTRAINT fkey_domain_data_tid_transport FOREIGN KEY (tid) |
77 REFERENCES transport (tid), |
113 REFERENCES transport (tid) |
78 CONSTRAINT fkey_domain_data_qid_quotalimit FOREIGN KEY (qid) |
|
79 REFERENCES quotalimit (qid) |
|
80 ); |
114 ); |
81 |
115 |
82 CREATE TABLE domain_name ( |
116 CREATE TABLE domain_name ( |
83 domainname varchar(255) NOT NULL, |
117 domainname varchar(255) NOT NULL, |
84 gid bigint NOT NULL, |
118 gid bigint NOT NULL, |
93 passwd varchar(270) NOT NULL, |
127 passwd varchar(270) NOT NULL, |
94 name varchar(128) NULL, |
128 name varchar(128) NULL, |
95 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
129 uid bigint NOT NULL DEFAULT nextval('users_uid'), |
96 gid bigint NOT NULL, |
130 gid bigint NOT NULL, |
97 mid bigint NOT NULL DEFAULT 1, |
131 mid bigint NOT NULL DEFAULT 1, |
|
132 qid bigint NOT NULL DEFAULT 1, |
|
133 ssid bigint NOT NULL DEFAULT 1, |
98 tid bigint NOT NULL DEFAULT 1, |
134 tid bigint NOT NULL DEFAULT 1, |
99 qid bigint NOT NULL DEFAULT 1, |
|
100 smtp boolean NOT NULL DEFAULT TRUE, |
|
101 pop3 boolean NOT NULL DEFAULT TRUE, |
|
102 imap boolean NOT NULL DEFAULT TRUE, |
|
103 managesieve boolean NOT NULL DEFAULT TRUE, |
|
104 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
135 CONSTRAINT pkey_users PRIMARY KEY (local_part, gid), |
105 CONSTRAINT ukey_users_uid UNIQUE (uid), |
136 CONSTRAINT ukey_users_uid UNIQUE (uid), |
106 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
137 CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid) |
107 REFERENCES domain_data (gid), |
138 REFERENCES domain_data (gid), |
108 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
139 CONSTRAINT fkey_users_mid_maillocation FOREIGN KEY (mid) |
109 REFERENCES maillocation (mid), |
140 REFERENCES maillocation (mid), |
|
141 CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) |
|
142 REFERENCES quotalimit (qid), |
|
143 CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid) |
|
144 REFERENCES service_set (ssid), |
110 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
145 CONSTRAINT fkey_users_tid_transport FOREIGN KEY (tid) |
111 REFERENCES transport (tid), |
146 REFERENCES transport (tid) |
112 CONSTRAINT fkey_users_qid_quotalimit FOREIGN KEY (qid) |
|
113 REFERENCES quotalimit (qid) |
|
114 ); |
147 ); |
115 |
148 |
116 CREATE TABLE userquota_11 ( |
149 CREATE TABLE userquota_11 ( |
117 uid bigint NOT NULL, |
150 uid bigint NOT NULL, |
118 path varchar(16) NOT NULL, |
151 path varchar(16) NOT NULL, |
142 |
175 |
143 CREATE OR REPLACE VIEW dovecot_password AS |
176 CREATE OR REPLACE VIEW dovecot_password AS |
144 SELECT local_part || '@' || domain_name.domainname AS "user", |
177 SELECT local_part || '@' || domain_name.domainname AS "user", |
145 passwd AS "password", smtp, pop3, imap, managesieve |
178 passwd AS "password", smtp, pop3, imap, managesieve |
146 FROM users |
179 FROM users |
147 LEFT JOIN domain_name USING (gid); |
180 LEFT JOIN domain_name USING (gid) |
|
181 LEFT JOIN service_set USING (ssid); |
148 |
182 |
149 CREATE OR REPLACE VIEW dovecot_user AS |
183 CREATE OR REPLACE VIEW dovecot_user AS |
150 SELECT local_part || '@' || domain_name.domainname AS userid, |
184 SELECT local_part || '@' || domain_name.domainname AS userid, |
151 uid, gid, domain_data.domaindir || '/' || uid AS home, |
185 uid, gid, domain_data.domaindir || '/' || uid AS home, |
152 mailboxformat.format || ':~/' || maillocation.directory AS mail |
186 mailboxformat.format || ':~/' || maillocation.directory AS mail |