1 SET client_encoding = 'UTF8'; |
1 SET client_encoding = 'UTF8'; |
2 SET client_min_messages = warning; |
2 SET client_min_messages = warning; |
3 |
3 |
4 |
4 -- --- |
5 -- --- |
5 -- Create the new service_set table and insert all possible combinations |
6 -- Make room for sha512-crypt.hex hashed passwords |
6 -- -- |
7 -- --- |
7 CREATE SEQUENCE service_set_id; |
|
8 |
|
9 CREATE TABLE service_set ( |
|
10 ssid bigint NOT NULL DEFAULT nextval('service_set_id'), |
|
11 smtp boolean NOT NULL DEFAULT TRUE, |
|
12 pop3 boolean NOT NULL DEFAULT TRUE, |
|
13 imap boolean NOT NULL DEFAULT TRUE, |
|
14 sieve boolean NOT NULL DEFAULT TRUE, |
|
15 CONSTRAINT pkey_service_set PRIMARY KEY (ssid), |
|
16 CONSTRAINT ukey_service_set UNIQUE (smtp, pop3, imap, sieve) |
|
17 ); |
|
18 |
|
19 COPY service_set (smtp, pop3, imap, sieve) FROM stdin; |
|
20 TRUE TRUE TRUE TRUE |
|
21 FALSE TRUE TRUE TRUE |
|
22 TRUE FALSE TRUE TRUE |
|
23 FALSE FALSE TRUE TRUE |
|
24 TRUE TRUE FALSE TRUE |
|
25 FALSE TRUE FALSE TRUE |
|
26 TRUE FALSE FALSE TRUE |
|
27 FALSE FALSE FALSE TRUE |
|
28 TRUE TRUE TRUE FALSE |
|
29 FALSE TRUE TRUE FALSE |
|
30 TRUE FALSE TRUE FALSE |
|
31 FALSE FALSE TRUE FALSE |
|
32 TRUE TRUE FALSE FALSE |
|
33 FALSE TRUE FALSE FALSE |
|
34 TRUE FALSE FALSE FALSE |
|
35 FALSE FALSE FALSE FALSE |
|
36 \. |
|
37 |
|
38 -- --- |
|
39 -- Make room for different mailbox formats and longer password hashes. |
|
40 -- --- |
|
41 DROP VIEW dovecot_user; |
8 DROP VIEW dovecot_password; |
42 DROP VIEW dovecot_password; |
9 |
|
10 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270); |
|
11 |
|
12 CREATE VIEW dovecot_password AS |
|
13 SELECT local_part || '@' || domain_name.domainname AS "user", |
|
14 passwd AS "password", smtp, pop3, imap, sieve |
|
15 FROM users |
|
16 LEFT JOIN domain_name USING (gid); |
|
17 |
|
18 -- --- |
|
19 -- Make room for different mailbox formats. |
|
20 -- --- |
|
21 DROP VIEW dovecot_user; |
|
22 DROP VIEW postfix_maildir; |
43 DROP VIEW postfix_maildir; |
23 DROP VIEW vmm_domain_info; |
44 DROP VIEW vmm_domain_info; |
24 |
45 |
25 CREATE SEQUENCE mailboxformat_id; |
46 CREATE SEQUENCE mailboxformat_id; |
26 CREATE SEQUENCE quotalimit_id; |
47 CREATE SEQUENCE quotalimit_id; |
43 ADD COLUMN fid bigint NOT NULL DEFAULT 1, |
64 ADD COLUMN fid bigint NOT NULL DEFAULT 1, |
44 ADD COLUMN extra varchar(1024); |
65 ADD COLUMN extra varchar(1024); |
45 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
66 ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat |
46 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
67 FOREIGN KEY (fid) REFERENCES mailboxformat (fid); |
47 |
68 |
|
69 ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270); |
48 |
70 |
49 -- --- |
71 -- --- |
50 -- Add quota stuff |
72 -- Add quota stuff |
51 -- --- |
73 -- --- |
52 CREATE TABLE quotalimit ( |
74 CREATE TABLE quotalimit ( |
57 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
79 CONSTRAINT ukey_quotalimit UNIQUE (bytes, messages) |
58 ); |
80 ); |
59 -- Insert default (non) quota limit |
81 -- Insert default (non) quota limit |
60 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
82 INSERT INTO quotalimit(bytes, messages) VALUES (0, 0); |
61 |
83 |
62 -- Adjust tables |
84 -- Adjust tables (quota) |
63 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
85 ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
64 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
86 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit |
65 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
87 FOREIGN KEY (qid) REFERENCES quotalimit (qid); |
66 |
88 |
67 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
89 ALTER TABLE users ADD COLUMN qid bigint NOT NULL DEFAULT 1; |
113 $$ LANGUAGE plpgsql; |
135 $$ LANGUAGE plpgsql; |
114 |
136 |
115 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota |
137 CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota |
116 FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); |
138 FOR EACH ROW EXECUTE PROCEDURE merge_userquota(); |
117 |
139 |
|
140 -- Adjust tables (services) |
|
141 ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
|
142 ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set |
|
143 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
144 |
|
145 ALTER TABLE users ADD COLUMN ssid bigint NOT NULL DEFAULT 1; |
|
146 -- save current service sets |
|
147 UPDATE users u |
|
148 SET ssid = ss.ssid |
|
149 FROM service_set ss |
|
150 WHERE ss.smtp = u.smtp |
|
151 AND ss.pop3 = u.pop3 |
|
152 AND ss.imap = u.imap |
|
153 AND ss.sieve = u.sieve; |
|
154 |
|
155 ALTER TABLE users DROP COLUMN smtp; |
|
156 ALTER TABLE users DROP COLUMN pop3; |
|
157 ALTER TABLE users DROP COLUMN imap; |
|
158 ALTER TABLE users DROP COLUMN sieve; |
|
159 ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set |
|
160 FOREIGN KEY (ssid) REFERENCES service_set (ssid); |
|
161 |
118 -- --- |
162 -- --- |
119 -- Restore views |
163 -- Restore views |
120 -- --- |
164 -- --- |
121 CREATE VIEW dovecot_user AS |
165 CREATE VIEW dovecot_user AS |
122 SELECT local_part || '@' || domain_name.domainname AS userid, |
166 SELECT local_part || '@' || domain_name.domainname AS userid, |
125 FROM users |
169 FROM users |
126 LEFT JOIN domain_data USING (gid) |
170 LEFT JOIN domain_data USING (gid) |
127 LEFT JOIN domain_name USING (gid) |
171 LEFT JOIN domain_name USING (gid) |
128 LEFT JOIN maillocation USING (mid) |
172 LEFT JOIN maillocation USING (mid) |
129 LEFT JOIN mailboxformat USING (fid); |
173 LEFT JOIN mailboxformat USING (fid); |
|
174 |
|
175 CREATE OR REPLACE VIEW dovecot_password AS |
|
176 SELECT local_part || '@' || domainname AS "user", |
|
177 passwd AS "password", smtp, pop3, imap, sieve |
|
178 FROM users |
|
179 LEFT JOIN domain_name USING (gid) |
|
180 LEFT JOIN service_set USING (ssid); |
130 |
181 |
131 CREATE VIEW postfix_maildir AS |
182 CREATE VIEW postfix_maildir AS |
132 SELECT local_part || '@' || domain_name.domainname AS address, |
183 SELECT local_part || '@' || domain_name.domainname AS address, |
133 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
184 domain_data.domaindir||'/'||uid||'/'||maillocation.directory||'/' |
134 AS maildir |
185 AS maildir |