SQL structure changes in `vmail.alias` table

Issue #101 resolved
Zhang Huangbin
repo owner created an issue

SQL structure changes in vmail.alias table

Why change this SQL table?

With current sql table, all members and moderators of mail alias account are stored in column alias.goto and alias.moderators, it's not ideal if we want to update them. for example, replace/remove some members/moderators.

The problems of this sql structure design are:

  • slow SQL query performance, especially if we have many mail accounts
  • complex or inconvenience to update them

Sample mail alias account with current SQL structure:

sql> select address,goto,moderators from alias where address='alias01@a.cn';
+--------------+---------------------------+----------------------------+
| address      | goto                      | moderators                 |
+--------------+---------------------------+----------------------------+
| alias01@a.cn | ag@a.cn,xyz@a.cn,zzz@a.cn | postmaster@a.cn,admin@a.cn |
+--------------+---------------------------+----------------------------+

If you changed mail address of alias member xyz@a.cn, you have to query all mail alias accounts which has this user as a member or moderator with SQL command like below:

SELECT address, goto, moderator
  FROM alias
 WHERE goto LIKE '%xyz@a.cn%'
       OR moderator LIKE '%xyz@a.cn%';

Then loop the query result and check whether old email address is in goto or moderators column, re-construct the member/moderator list, update the SQL record.

Problems with this query:

  • The query doesn't use INDEX for best query performance at all.
  • The query result is not accurate. If you want to query xyz@a.cn, above SQL query will match abc-xyz@a.cn, hello-xyz@a.cn and more.

Even worse, if you're deleting a mail domain directly, you have to get all accounts in this domain first, then repeat above SQL query (with LIKE '%<email>%') many times while removing each user under this domain.

[DONE] The new SQL structure

New structure introduces 2 new tables, and (optionally) dropped few columns in old alias table.

For PostgreSQL: please login as vmailadmin user to create these new tables.

su - postgres
psql -U vmailadmin -d vmail

-- After creation, set permission
GRANT SELECT ON TABLE forwardings to vmail;
GRANT SELECT ON TABLE alias_moderators to vmail;
-- Alias moderators.
CREATE TABLE IF NOT EXISTS alias_moderators (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL DEFAULT '',
    moderator VARCHAR(255) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    UNIQUE INDEX (address, moderator),
    INDEX (domain)
) ENGINE=InnoDB;

-- Forwardings. it contains
--  - members of mail alias account
--  - per-account alias addresses
--  - per-user mail forwarding addresses
CREATE TABLE IF NOT EXISTS forwardings (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL DEFAULT '',
    forwarding VARCHAR(255) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    -- defines whether it's a standalone mail alias account. 0=no, 1=yes.
    is_list TINYINT(1) NOT NULL DEFAULT 0,
    -- defines whether it's a mail forwarding address of mail user. 0=no, 1=yes.
    is_forwarding TINYINT(1) NOT NULL DEFAULT 0,
    -- defines whether it's a per-account alias address. 0=no, 1=yes.
    is_alias TINYINT(1) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE INDEX (address, forwarding),
    INDEX (domain),
    INDEX (is_list),
    INDEX (is_alias)
) ENGINE=InnoDB;

New structure will have 2 new tables, and (optionally) drop few columns in existing alias table. With new structure:

  • table alias stores alias profile, no alias members nor moderators. optionally, columns below should be dopped after migration because they're not used anymore:

    • goto
    • moderators
    • islist
    • is_alias
    • alias_to

    Note: with the new structure, alias table is mainly used by management tool like iRedAdmin-Pro. Other softwares like Postfix, Dovecot don't use it at all.

  • NEW table forwardings stores:

    • members of mail alias account (with is_list=1)
    • per-user mail forwarding addresses (with is_forwarding=1)
    • per-account alias addresses (with is_alias=1)
    • per-domain catch-all account (with is_list=0 AND is_forwarding=0 AND is_alias=0)
  • NEW table alias_moderators stores moderators (email addresses) of mail alias account.

How does new SQL structure work?

With new sql tables, one record per one mail alias member, multiple members must be stored in multiple records.

If we changed email address of a mail user, we can simply update forwardings table with below simple SQL command:

UPDATE forwardings
   SET forwarding='<new_email>'
 WHERE address='<old_email>'
       AND is_forwarding=1;

Delete a member of mail alias account:

DELETE FROM forwardings
 WHERE address='<alias_email>'
       AND forwarding='<member_email>'
       AND is_list=1;

Create a mail alias account with few members

INSERT INTO alias (address, name, domain, active)
           VALUES ('new-alias@domain.com',
                   'This is a Testing Alias',
                   'domain.com',
                   1);

INSERT INTO forwardings (address, forwarding, domain, is_list)
                 VALUES ('u@domain.com', 'goto1@xx', 'domain.com', 1),
                        ('u@domain.com', 'goto2@xxx', 'domain.com', 1),
                        ('u@domain.com', 'goto3@xxx', 'domain.com', 1);

Create a per-account alias address

We alraedy have mail user user@domain.com, to assign new alias address (which does not exist on your server) new-address@domain.com to this user:

INSERT INTO forwardings (address, forwarding, domain, is_alias)
                 VALUES ('new-address@domain.com', 'user@domain.com', 'domain.com', 1),

Create a mail forwarding for existing mail user

We alraedy have mail user user@domain.com, to forward all emails received by this user to one Gmail address and 2 other internal users:

INSERT INTO forwardings (address, forwarding, domain, is_forwarding)
                 VALUES ('user@domain.com', 'hello@gmail.com', 'domain.com', 1),
                        ('user@domain.com', 'internal@domain.com', 'domain.com', 1),
                        ('user@domain.com', 'internal2@domain.com', 'domain.com', 1);

If you do NOT want to save a copy of forwarded email:

DELETE FROM forwardings
 WHERE address='user@domain.com'
       AND address='user@domain.com';

Create a per-domain catch-all account

We alraedy have mail domain domain.com, to create a catch-all account:

INSERT INTO forwardings (address, forwarding, domain)
                 VALUES ('domain.com', 'user@some-domain.com', 'domain.com'),
                        ('domain.com', 'user@another-domain.com', 'domain.com');

What additional settings need to be updated

[DONE] Postfix

With this change, we need to update Postfix SQL query files under /etc/postfix/mysql/ (and /etc/postfix/pgsql for PostgreSQL backend), replace the old table name alias by forwardings:

cd /etc/postfix/mysql/
perl -pi -e 's#alias\.address#forwardings.address#g' *.cf
perl -pi -e 's#alias\.goto#forwardings.forwarding#g' *.cf
perl -pi -e 's#alias\.active#forwardings.active#g' *.cf
perl -pi -e 's#alias\.domain#forwardings.domain#g' *.cf
perl -pi -e 's#alias,#forwardings,#g' *.cf

[DONE] iRedAPD

Few iRedAPD plugins must be updated:

  • reject_sender_login_mismatch.py
  • sql_alias_access_policy.py

[DONE] iRedAdmin-Pro

iRedAdmin-Pro must be updated too:

  • manage mail alias accounts
  • manage forwarding addresses of mail user
  • manage per-user alias addresses

[DONE] Script used to migrate vmail.alias table

https://bitbucket.org/zhb/iredmail/src/default/iRedMail/tools/migrate_sql_alias_table.py

[OPTIONAL] After migration

Remove duplicate records:

USE vmail;

-- Remove non-mail-alias account
DELETE FROM alias WHERE islist <> 1;

-- per-domain catch-all account
DELETE FROM alias WHERE address=domain;

Drop SQL columns:

ALTER TABLE alias DROP COLUMN goto;
ALTER TABLE alias DROP COLUMN moderators;
ALTER TABLE alias DROP COLUMN islist;
ALTER TABLE alias DROP COLUMN is_alias;
ALTER TABLE alias DROP COLUMN alias_to;

Comments (17)

  1. Log in to comment