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
-- -----------------------------------------------------------------------------
-- 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;
