Planned SQL structure change to `vmail.alias` table

Issue #49 closed
Zhang Huangbin
repo owner created an issue

Planned SQL structure change to vmail.alias table.

Q: Why change this table?

A: With current sql table, all members of a mail alias account are stored in column alias.goto, it's not ideal if we want to replace or remove some members. For example:

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

if we changed email address of a mail user (or a mail alias account), and it's member of some mail alias accounts, to update mail alias account, we have to:

1: Query all mail alias accounts which has this member with SQL command like below:

sql> SELECT address, goto FROM alias WHERE goto LIKE '%old_email%';

Be careful, if you want to query a@domain.com, this query will match aa@domain.com, ba@domain.com, it's not accurate.

2: To update this mail alias with new member, you must make sure old member is one of existing members first (because above query is not accurate), then update mail alias with re-contructed alias members.

sql> UPDATE alias SET goto='new_member_list' WHERE address='alias@domain';

This procedure may cause performance issue. And same operation required to delete a member.

Q: How do new tables work?

A: With new sql tables, one record for one mail alias member, multiple members must be stored in multiple records. Check SQL structure below.

if we changed email address of a mail user, and it's member of some mail alias accounts, we just update alias_members table with below simple SQL command:

UPDATE alias_members SET goto='new_email' WHERE goto='old_email';

To delete a member:

DELETE FROM alias_members WHERE address='alias@domain' AND goto='member_email';

Old SQL table

CREATE TABLE IF NOT EXISTS alias (
    address VARCHAR(255) NOT NULL DEFAULT '',
    goto TEXT,
    ...
)

New SQL tables

New structure will have 2 tables: alias_profiles, alias_members.

  • table alias_profiles stores alias profile, no alias members.
  • table alias_members stores membership mapping, including:

    • membership of mail alias account
    • user mail forwarding
    • user alias address

NOTES:

  • alias_members.goto stores a single email, not multiple emails.
  • multiple alias members must be stored in multiple sql records like this:
insert into alias_members (mail, goto, ...)
                   values ('u@domain', 'goto1@xx', ...)
                          ('u@domain', 'goto2@xxx', ...)
                          ('u@domain', 'goto3@xxx', ...)
  • alias_members.alias_type stores alias types. we use digital numbers for different alias types:

    • 0 -> none
    • 1 -> mail alias account
    • 2 -> user mail forwarding. Including mail=goto.
    • 3 -> user alias address
-- Alias account profiles
CREATE TABLE IF NOT EXISTS alias_profiles (
    address VARCHAR(255) NOT NULL DEFAULT '',
    name VARCHAR(255) NOT NULL DEFAULT '',
    moderators TEXT,
    accesspolicy VARCHAR(30) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    active TINYINT(1) NOT NULL DEFAULT 1,
    created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    expired DATETIME NOT NULL DEFAULT '9999-12-31 00:00:00',
    PRIMARY KEY (address),
    INDEX (domain),
    INDEX (expired),
    INDEX (active)
) ENGINE=MyISAM;

-- Alias members
CREATE TABLE IF NOT EXISTS alias_members (
    address VARCHAR(150) NOT NULL DEFAULT '',
    goto VARCHAR(150) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    alias_type TINYINT(1) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    modified DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    expired DATETIME NOT NULL DEFAULT '9999-12-31 00:00:00',
    PRIMARY KEY (address, goto),
    INDEX (domain),
    INDEX (active),
    INDEX (alias_type),
    INDEX (expired)
) ENGINE=MyISAM;

What additional settings need to be updated

Postfix

With this change, we need to update Postfix SQL query files under /etc/postfix/mysql/, replace the old table name alias by alias_members:

# cd /etc/postfix/mysql/
# perl -pi -e 's#alias\.#alias_members.#g' *.cf
# perl -pi -e 's#alias\,#alias_members,#g' *.cf

iRedAPD

iRedAPD plugin sql_alias_access_policy.py queries alias table, must be updated to query new SQL tables.

iRedAdmin-Pro

iRedAdmin-Pro manages mail alias accounts, must be updated to fit this change.

Script used to extract alias.goto to alias_members

import web

db = web.database(dbn='mysql', db='vmail', user='root', pw='password')

records = db.select('alias', what='address,goto,domain,active,islist')

for r in records:
    is_list = False
    alias_type = 0
    if r.islist == 1:
        # mail alias account
        is_list = True
        alias_type = 1
    else:
        # user forwarding
        alias_type = 2

    address = str(r.address)
    members = set(r.goto.split(','))
    active = int(r.active)
    domain = str(r.domain)

    for m in members:
        if m:
            inserts = {}
            inserts['address'] = address
            inserts['goto'] = m
            inserts['domain'] = domain
            inserts['alias_type'] = alias_type
            inserts['active'] = active

            try:
                db.insert('alias_members', **inserts)
            except Exception, e:
                if e[0] == 1062:
                    # Duplicate record
                    pass
                else:
                    print m, e

Comments (14)

  1. Zhang Huangbin reporter

    Will start working on user alias support soon, sponsored by one iRedAdmin-Pro customer.

    Purposes

    • Offers per-user alias address support in SQL backends. (iRedMail has this feature for OpenLDAP backend for years.)
    • No need to use mail alias account for this goal, so that you won't see many mail alias accounts under this domain, it's a little confused.
    • It will be able to assign email addresses under other domain(s) as user alias address, controlled by a global switch/setting. Maybe a per-domain setting in the future.

    Possible changes in iRedMail

    • New SQL column in vmail.alias table: isuseralias=1 (or 0). SQL structure:
    sql> USE vmail;
    sql> ALTER TABLE alias ADD COLUMN isuseralias TINYINT(1) NOT NULL DEFAULT 0;
    

    Sample usage:

    -- Create a user alias address:
    --    * alias.islist=0
    --    * alias.isuseralias=1
    --    * alias.domain=[domain name of value in `alias.goto`]
    -- under same domain
    sql> INSERT INTO alias (address, goto, domain, islist, isuseralias)
                    VALUES ('alias@domain.com', 'user@domain.com', 'domain.com', 0, 1);
    
    -- under different domain
    sql> INSERT INTO alias (address, goto, domain, islist, isuseralias)
                    VALUES ('alias@other_domain.com', 'user@domain.com', 'domain.com', 0, 1);
    
    -- Comparison: create a mail alias account
    --    * alias.islist=1
    --    * alias.isuseralias=0
    --    * alias.domain=[domain name of value in `alias.address`]
    sql> INSERT INTO alias (address, goto, domain, islist, isuseralias)
                    VALUES ('alias@domain.com', 'user@domain.com', 'domain.com', 1, 0);
    
    • No changes required in Postfix/Dovecot and other components.

    Possible changes in iRedAdmin-Pro

    • New tab in user profile page: Aliases, used to manage per-user alias addresses.
    • New setting in iRedAdmin-Pro config file (settings.py): ALLOW_CROSS_DOMAIN_USER_ALIAS = True (or False), With ALLOW_CROSS_DOMAIN_USER_ALIAS = True, domain admin is able to assign any (non-exist) email address of local hosted mail domains to one (existing) mail user.
  2. Log in to comment