How to manage virtual mail domain aliases with ISPConfig 3

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;

Share