Virtual Domains and Mailboxes

Aside from us the system administrators, there are no human users working interactively on the server. The typical user works on his own device and accesses services on the server. The server contains websites, delivers mails, synchronizes files and delivers instant messages under the hosted domain names and various accounts on each domain. This concept is known as virtual domains and users. Virtual becuase unlike real users, they don’t work on the server- system interactively like on their own devices.

In the case of virtual mail, the mailbox-accounts are referenced in a database instead of the system user-profiles and the incoming mails are stored in a virtual mailbox directory instead of the users home-directory.

Virtual Mail User and Group

Following we create a system user profile and group to access the virtual mailbox directory.

$ sudo addgroup --gid 5000 vmail
$ sudo adduser  --system --uid=5000 --ingroup vmail --home /var/vmail vmail

This creates the user and group vmail and also the directory /var/vmail where mails will be stored.

Mail Server Database

Preparing the database

Create and store a password for postfix to access the database.

Create the mail-server database:

$ mysqladmin -u root -p create mailserver

Create a database user for the mail-server to access the database:

$ mysql -u root -p mailserver

GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1'
    IDENTIFIED BY '********';

FLUSH PRIVILEGES;
EXIT;

Create a SQL file mailserver-tables.sql with the following contents:

#
# Create database tables for virtual mail domains for use in Postfixand Dovecot
#
CREATE TABLE IF NOT EXISTS `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(32) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `virtual_aliases` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `source` varchar(100) NOT NULL,
  `destination` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Add the tables to the mail-server database:

$ mysql -u root -p mailserver < mailserver-tables.sql

Adding a Domain

$ mysql -u root -p mailserver

INSERT INTO `mailserver`.`virtual_domains` (
  `id` ,
  `name`
)
VALUES (
  '1', 'example.org'
);

Adding a Mailbox

$ mysql -u root -p mailserver

INSERT INTO `mailserver`.`virtual_users` (
        `id` ,
        `domain_id` ,
        `password` ,
        `email`
    )
    VALUES (
        '1',
        '1',
        ENCRYPT (
            '********', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))
        ),
        'email1@example.net'
);

The above encrypts the password with 512-bit SHA-2 before it is stored in the database.

Adding an Alias

$ mysql -u root -p mailserver

INSERT INTO `mailserver`.`virtual_aliases` (
  `id`,
  `domain_id`,
  `source`,
  `destination`
)
VALUES (
  '1', '1', 'jack@example.org', 'john@example.org'
);