Here’s a small script I’ve implemented as a MySQL stored procedure that allows you to include a virtual domain mapping scheme for the Version 3.0 of ISPConfig.
ISPConfig – see www.ispconfig.org – is an excellent tool for managing virtual domains on a shared mail/webserver. The thing that I found missing was that if a customer has multiple domains and wants to make sure that an email to a named user of his company will arrive in that user’s inbox will actually come out in his inbox regardless of the domain to which the email was sent, we need to manage multiple domain aliases automatically, as we obviously do not want to manage each user account in what may be 10 different domains.
On the file system side, we’re creating symlinks for the different subdirectories in /var/vmail, so that the mails are automagically available regardless of the domain the user is going to work with.
There also is a cleanup script – and you by the way need to make sure that if you do not have aliases for one given domain, you still add an entry to the alias table mapping the domain to itself – or the cleanup script will just remove all the members of that domain (or you change the cleanup script).
I’m giving the tool in one block, so have fun with it. I did submit it to the ISP developers, as well.
M
|
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 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 |
-- ----------------------------------------------------------------------------- -- Management for Virtual Domains for ISP / PostFix Management -- -- (C) 2009 Matthias Nott, SAP -- -- ----------------------------------------------------------------------------- -- This script uses a table mail_domain_mappings that is defined as -- -- +--------+--------------+------+-----+---------+-------+ -- | Field | Type | Null | Key | Default | Extra | -- +--------+--------------+------+-----+---------+-------+ -- | domain | varchar(255) | NO | | NULL | | -- | alias | varchar(255) | NO | | NULL | | -- +--------+--------------+------+-----+---------+-------+ -- -- While ISP manages its mail addresses through the mail_user table, we -- want to avoid having to manage userx@domaina if the same user exists -- as userx@domainb. We enter domaina in the above table into the first -- column (domain), and a matching alias domain into the second column. -- domaina can exist multiple times, so that we can more than just one -- alias domain. -- -- The stored procedure addVirtualDomains adds, to the mail_user table, -- records for the virtual domains for all users that are already there -- and of which the domains are defined in the domain column above. -- -- The stored procedure deleteVirtualDomains removes all of these users -- and at the same time cleans up all virtual mappings that are not any -- more needed as the master record (userx@domaina) was deleted in ISP. -- -- Also, to make this work we map all of -- -- /var/vmail/domainb -> /var/vmail/domaina -- -- using symbolic links. -- ----------------------------------------------------------------------------- DROP PROCEDURE IF EXISTS deleteVirtualDomains; DELIMITER / CREATE PROCEDURE deleteVirtualDomains() BEGIN delete from mail_user where substr(email, locate('@', email)+1) not in (select domain from mail_domain_mappings); END / DELIMITER ; DROP PROCEDURE IF EXISTS addVirtualDomains; DELIMITER / CREATE PROCEDURE addVirtualDomains () READS SQL DATA BEGIN -- --------------------------------------------------------------------------- -- Declare our variables -- --------------------------------------------------------------------------- DECLARE v_done int DEFAULT FALSE; -- Loop Exit Handler DECLARE v_domain varchar(255); -- Current virtual domain DECLARE v_user varchar(255); -- Current virtual user DECLARE v_adomain varchar(255); -- Current alias domain DECLARE v_auser varchar(255); -- Current alias user DECLARE v_test varchar(255); -- Test whether alias exists -- --------------------------------------------------------------------------- -- Declare variables we're going to duplicate -- --------------------------------------------------------------------------- DECLARE v_mailuser_id int(11); DECLARE v_sys_groupid int(11); DECLARE v_sys_perm_user varchar(5); DECLARE v_sys_perm_group varchar(5); DECLARE v_sys_perm_other varchar(5); DECLARE v_server_id int(11); DECLARE v_password varchar(255); DECLARE v_name varchar(128); DECLARE v_uid int(10) unsigned; DECLARE v_gid int(10) unsigned; DECLARE v_maildir varchar(255); DECLARE v_quota int(11); DECLARE v_homedir varchar(255); DECLARE v_autoresponder char(1); DECLARE v_autoresponder_text tinytext; DECLARE v_custom_mailfilter text; DECLARE v_postfix char(1); DECLARE v_access char(1); DECLARE v_disableimap char(1); DECLARE v_disablepop3 char(1); -- --------------------------------------------------------------------------- -- Declare our cursors -- --------------------------------------------------------------------------- DECLARE c_vdomain CURSOR FOR select distinct domain from mail_domain_mappings; DECLARE c_vuser CURSOR FOR select mailuser_id, sys_groupid, sys_perm_user, sys_perm_group, sys_perm_other, server_id, substr(email, 1, locate('@', email)-1), password, name, uid, gid, maildir, quota, homedir, autoresponder, autoresponder_text, custom_mailfilter, postfix, access, disableimap, disablepop3 from mail_user where substr(email, locate('@', email)+1) = v_domain; DECLARE c_adomain CURSOR FOR select alias from mail_domain_mappings where domain = v_domain; DECLARE c_auser CURSOR FOR select email from mail_user where email = concat(concat(v_user, '@'), v_adomain); DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done := TRUE; -- --------------------------------------------------------------------------- -- Loop over all domains -- --------------------------------------------------------------------------- OPEN c_vdomain; l_cvdomain: LOOP FETCH c_vdomain INTO v_domain; IF v_done THEN CLOSE c_vdomain; SET v_done := false; LEAVE l_cvdomain; END IF; -- ------------------------------------------------------------------------- -- For each v_domain, loop over all users for that domain -- ------------------------------------------------------------------------- OPEN c_vuser; l_cvuser: LOOP FETCH c_vuser into v_mailuser_id, v_sys_groupid, v_sys_perm_user, v_sys_perm_group, v_sys_perm_other, v_server_id, v_user, v_password, v_name, v_uid, v_gid, v_maildir, v_quota, v_homedir, v_autoresponder, v_autoresponder_text, v_custom_mailfilter, v_postfix, v_access, v_disableimap, v_disablepop3; IF v_done THEN CLOSE c_vuser; SET v_done := FALSE; LEAVE l_cvuser; END IF; -- ----------------------------------------------------------------------- -- For each v_user, loop over all alias domains -- ----------------------------------------------------------------------- OPEN c_adomain; l_cadomain: LOOP FETCH c_adomain into v_adomain; IF v_done THEN CLOSE c_adomain; SET v_done := FALSE; LEAVE l_cadomain; END IF; -- --------------------------------------------------------------------- -- For each alias domain, see if alias user it is already registered -- --------------------------------------------------------------------- select email into v_test from mail_user where email = concat(concat(v_user, '@'), v_adomain); IF v_done THEN SET v_done := FALSE; set v_test := null; END IF; -- --------------------------------------------------------------------- -- If we are missing a user entry, we add it -- --------------------------------------------------------------------- IF v_test is null THEN -- select concat(concat(v_user, '@'), v_adomain); insert into mail_user ( sys_groupid, sys_perm_user, sys_perm_group, sys_perm_other, server_id, email, password, name, uid, gid, maildir, quota, homedir, autoresponder, autoresponder_text, custom_mailfilter, postfix, access, disableimap, disablepop3 ) values ( v_sys_groupid, v_sys_perm_user, v_sys_perm_group, v_sys_perm_other, v_server_id, concat(concat(v_user, '@'), v_adomain), v_password, v_name, v_uid, v_gid, v_maildir, v_quota, v_homedir, v_autoresponder, v_autoresponder_text, v_custom_mailfilter, v_postfix, v_access, v_disableimap, v_disablepop3 ); END IF; END LOOP l_cadomain; END LOOP l_cvuser; END LOOP l_cvdomain; END; / DELIMITER ; call deleteVirtualDomains; call addVirtualDomains; |