Email Server/Database
Views:
Part of the Email Server documentation
Contents |
Introduction
Running an email server with virtual users and virtual domains needs a database for easy administration and flexibility. This page details a database schema that should be useful and flexible enough for almost all situations.
The Model
Clients
Everything starts with a client, e.g. a company, group or organisation. Some space is provided for contact information.
Each client can have multiple domains
Domains
Domain names are associated with clients. Domain names can refer to other domain names to become aliases.
Mailboxes
Each mailbox is associated with a domain. Mailboxes are defined by details such as a password to access the mailbox and information regarding the filesystem.
Aliases
Alias names for mailboxes that refer to both mailboxes and domains.
Transports
A transport is the mechanism to for moving email. The transport could be local or remote. The default transport used is the default one configured in the email server.
Accounts
Accounts are the actual email accounts. They are made up from a domain and mailbox to provide details of filesystem paths, email address, password, quota and permissions.
Alias Maps
Alias maps are a simple abstraction of all possible aliased email addresses and their real email address. Alias Maps are made from domains, aliases and mailboxes
The Schema
DDL for MySQL - should be easy to convert in to something a little more suitable for other databases.
quota attributes are defined as int, this means that you can only store up to a 32bit number, e.g. 2GB if you want that column to represent bytes. The important thing to remember here is not how large a number your database server can handle, but how large a number your email software can handle. Specifically, any components that access the mailboxes and which are expect to honor quotas.
Maildrop for example can only accept a signed 32bit number as the quota. If you want to set larger quotas here, make sure your mail software is capable of handling it.
The uid and gid attributes in the account_auth view have been preset. You will probably need different values on your system. In most cases they will need to be set to the uid and gid of the processes that will be accessing the mail boxes, i.e. the delivery agent and imap server.
The maildir attribute in the account_auth view is fixed as 'Maildir', you may prefer to use something else as a standard. I can't see any reason why some one would want to make this vary between accounts but you could always make this an attribute of the mailbox entity.
CREATE TABLE `clients` (
`id` int(11) NOT NULL auto_increment,
`quota` int(11) default NULL,
`company` tinytext NOT NULL,
`contact` tinytext NOT NULL,
`telephone` tinytext NOT NULL,
`disabled` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `domains` (
`id` int(11) NOT NULL auto_increment,
`domainId` int(11) default NULL,
`clientId` int(11) NOT NULL,
`quota` int(11) default NULL,
`domain` varchar(200) NOT NULL,
`disabled` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `domain` (`domain`),
KEY `clientId` (`clientId`),
KEY `domainId` (`domainId`),
CONSTRAINT `domains_ibfk_2` FOREIGN KEY (`domainId`) REFERENCES `domains` (`id`),
CONSTRAINT `domains_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `mailboxes` (
`id` int(11) NOT NULL auto_increment,
`domainId` int(11) NOT NULL,
`mailbox` varchar(200) NOT NULL,
`clearpw` varchar(200) NOT NULL,
`cryptpw` varchar(200) NOT NULL,
`quota` int(11) default NULL,
`name` tinytext,
`disabled` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `mailbox` (`mailbox`,`domainId`),
KEY `domainId` (`domainId`),
KEY `mailbox_2` (`mailbox`),
CONSTRAINT `mailboxes_ibfk_1` FOREIGN KEY (`domainId`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `aliases` (
`id` int(11) NOT NULL auto_increment,
`domainId` int(11) NOT NULL,
`mailboxId` int(11) NOT NULL,
`alias` varchar(200) default NULL,
`disabled` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `domainId` (`domainId`),
KEY `mailboxId` (`mailboxId`),
KEY `alias` (`alias`),
KEY `disabled` (`disabled`),
CONSTRAINT `aliases_ibfk_2` FOREIGN KEY (`mailboxId`) REFERENCES `mailboxes` (`id`),
CONSTRAINT `aliases_ibfk_1` FOREIGN KEY (`domainId`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `transports` (
`id` int(11) NOT NULL auto_increment,
`domainId` int(11) NOT NULL,
`transport` text,
`disabled` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `domainId` (`domainId`),
CONSTRAINT `transports_ibfk_1` FOREIGN KEY (`domainId`) REFERENCES `domains` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE VIEW `account_auth` AS
SELECT
concat(`mailboxes`.`mailbox`,'@',`domains`.`domain`) AS `username`,
`mailboxes`.`cryptpw` AS `cryptpw`,`mailboxes`.`clearpw` AS `clearpw`,
'101' AS `uid`,
'102' AS `gid`,
concat(`domains`.`domain`,'/',`mailboxes`.`mailbox`) AS `home`,
'Maildir' AS `maildir`,
IF (
`mailboxes`.`quota` IS NOT NULL,
`mailboxes`.`quota`,
IF(
`domains`.`quota` IS NOT NULL,
`domains`.`quota`,
IF(
`clients`.`quota` IS NOT NULL,
`clients`.`quota`,
""
)
)
) AS `quota`,
`mailboxes`.`name` AS `name`,_utf8'' AS `options`
FROM
((`mailboxes` join `domains`) join `clients`)
WHERE
(
(`clients`.`disabled` = 0)
AND
(`domains`.`disabled` = 0)
AND
(`mailboxes`.`disabled` = 0)
AND
(`mailboxes`.`domainId` = `domains`.`id`)
AND
(`domains`.`clientId` = `clients`.`id`)
);
CREATE VIEW `virtual_domains` AS
SELECT `domains`.* FROM `domains` WHERE ISNULL(`domainId`) AND `disabled` = 0;
CREATE VIEW `alias_domains` AS
SELECT * FROM `domains` WHERE !ISNULL(`domainId`) AND `disabled` = 0;
CREATE VIEW `alias_maps` AS
-- Virtual Mailboxes, Virtual Domains
SELECT
CONCAT(`aliases`.`alias`,_utf8'@',`virtual_domains`.`domain`) AS `alias`,
CONCAT(`mailboxes`.`mailbox`,_utf8'@',`domains`.`domain`) AS `username`
FROM
(
(
(
(
`aliases` JOIN `mailboxes` ON
(
(`aliases`.`mailboxId` = `mailboxes`.`id`)
)
)
JOIN `domains` ON
(
(`mailboxes`.`domainId` = `domains`.`id`)
)
)
JOIN `clients` ON
(
(`domains`.`id` = `clients`.`id`)
)
)
JOIN `virtual_domains` ON
(
(`virtual_domains`.`id` = `aliases`.`domainId`)
)
)
WHERE
(
(`clients`.`disabled` = 0)
AND
(`mailboxes`.`disabled` = 0)
AND
(`aliases`.`disabled` = 0)
)
-- Virtual Aliases, Virtual Domains, Alias Domains
UNION
SELECT
CONCAT(`aliases`.`alias`,_utf8'@',`alias_domains`.`domain`) AS `alias`,
CONCAT(`mailboxes`.`mailbox`,_utf8'@',`domains`.`domain`) AS `username`
FROM
(
(
(
(
`aliases` JOIN `mailboxes` ON
(
(`aliases`.`mailboxId` = `mailboxes`.`id`)
)
)
JOIN `domains` ON
(
(`mailboxes`.`domainId` = `domains`.`id`)
)
)
JOIN `clients` ON
(
(`domains`.`id` = `clients`.`id`)
)
)
JOIN `alias_domains` ON
(
(`alias_domains`.`domainId` = `aliases`.`domainId`)
)
)
WHERE
(
(`clients`.`disabled` = 0)
AND
(`mailboxes`.`disabled` = 0)
AND
(`aliases`.`disabled` = 0)
)
-- Virtual Mailboxes, Alias Domains
UNION
SELECT
CONCAT(`mailbox`, _utf8'@', `alias_domains`.`domain`) AS alias,
CONCAT(`mailbox`, _utf8'@', `domains`.`domain`) AS `username`
FROM `alias_domains`
LEFT JOIN `domains` ON `alias_domains`.`domainId` = `domains`.`id`
INNER JOIN `mailboxes` ON `mailboxes`.`domainId` = `domains`.`id`
INNER JOIN `clients` ON `domains`.`clientId` = `clients`.`id`
WHERE
(
`alias_domains`.`disabled` = 0
AND
`domains`.`disabled` = 0
AND
`mailboxes`.`disabled` = 0
AND
`clients`.`disabled` = 0
);
Passwords
Clear passwords are easy to store in the database but you'll only really want to do that if you are going to offer your users CRAM-MD5 authentication so that their password is not transmitted over a clear text internet connection.
Personally, I'd prefer people to authenticate over an encrypted network connection, sending a clear text password. This way you don't need to store a clear text version of the password in the database. The clear text password can be crypted and compared to a copy of the password which has already been crypted and stored in the database.
These two functions will help you to create crypted passwords for the database and make sure that they are compatible with any software you use with your database.
Once they are available in the database you can use them in combination with the ENCRYPT() function: ... SET cryptpw = ENCRYPT('my-password', SALT()) WHERE ...;
to compare passwords use: ...WHERE cryptpw = ENCRYPT('my-password', cryptpw);
FUNCTION `SALT_CHAR`() RETURNS VARCHAR(1) -- generates a (fairly) random character to use as salt in an MD5 cyrpt
BEGIN
DECLARE c VARCHAR(1);
DECLARE i INT(2);
SET i = (FLOOR(1+(RAND()*3)));
IF i = 1 THEN
SET c = CHAR(FLOOR(46+( RAND()*11)) USING utf8); -- valid salt character are numbers 0 to 9 . and /
ELSEIF i = 2 THEN
SET c = CHAR(FLOOR(65+(RAND()*25)) USING utf8); -- ... uppercase letters A to Z
else
SET c = CHAR(FLOOR(97+(RAND() * 25)) USING utf8); -- ... and lower case letters a to z
END IF;
RETURN c;
END
CREATE FUNCTION `SALT`() RETURNS VARCHAR(12) -- generates salt for an MD5 crypt
BEGIN
DECLARE i INT;
DECLARE salt VARCHAR(12);
SET i = 1;
SET salt = '$1$'; -- marks this as being MD5 salt
WHILE i <=8 DO
SET salt = CONCAT(salt, SALT_CHAR());
SET i = i + 1;
END WHILE;
SET salt = CONCAT(salt, '$'); -- marks end of salt
RETURN salt;
END
