[2024-feb-29] Sad news: Eric Layton aka Nocturnal Slacker aka vtel57 passed away on Feb 26th, shortly after hospitalization. He was one of our Wiki's most prominent admins. He will be missed.

Welcome to the Slackware Documentation Project

This is an old revision of the document!


Virtual Mail Server Database

This article is under construction and not complete! Please return later…

The database is used to provide configuration and authentication for your virtual mail domains and mail boxes. Email content is not stored in the database in this configuration.

If you have a full Slackware installation with MySQL already working then you may skip to the next section.

If you need to install MySQL (MariaDB) then grab the package from your repo of choice, verify it, then install it with:

installpkg path/to/mariadb-xxxxx.txz
If installing to a Linode.com VPS you may also need to install the libaio package for your Slackware version.

You will then need to initialize the database server and start it. Follow the instructions found in the top comments of the start script, /etc/rc.d/rc.mysqld. Here is a short summary…

mysql_install_db --user=mysql //Initialize database//
...
chmod +x /etc/rc.d/rc.mysqld //Start the server//
/etc/rc.d/rc.mysqld start
...
mysql_secure_installation //Secure the installation and set root mysql user password//

Creating Mail DB User and Database

Your database server should now be running and have a root mysql user password set.

Next we need to create a database, and a user to access it on behalf of our mail server.

We will use the highly original names mailserver for our database, and mailuser for our user.

You may easily create both as follows, as root (provide the root mysql user password when prompted):

mysql -p -e "CREATE DATABASE mailserver"
mysql -p -e "GRANT ALL ON mailserver.* TO mailuser@localhost IDENTIFIED BY 'your-password'"

Remember the mailuser password for postfix and dovecot setup later.

Creating Mail Database Tables

Neither Postfix nor Dovecot place any restrictions on the data structures you should use to support virtual mail functions. Their only requirements are that you must supply a set of queries that return specific fields as needed.

This gives you total flexibility to integrate your mail system into any arbitrary operating environment. It also means that there is no default database structure, so we must provide it ourselves!

Figure 2 below, shows the schema of our virtual mail database.

  • virtual_domains - Contains an entry for each domain name served by virtual email
  • virtual_users - Contains an entry for each virtual mail box served
  • virtual_aliases - Maps aliases (source) to actual mail boxes (destination)

Adding Domain Entries

To add entries to the virtual_domains table use the following query:

mysql> INSERT INTO virtual_domains SET id=NULL, name='my-domain.com'

This table must include entries for each domain served by your mail server.

You should add at least the following rows:

  • The domain name - my-domain.com
  • The hostname - darkstar
  • The FQDN of the host - darkstar.my-domain.com
  • The localhost.domain variant - localhost@my-domain.com

To see the current rows in this table…

mysql> SELECT * FROM virtual_domains
+----+-------------------------+
| id | name                    |
+----+-------------------------+
|  1 | my-domain.com           |
|  2 | darkstar                |
|  3 | darkstar.my-domain.com  |
|  4 | localhost.my-domain.com |
+----+-------------------------+

Note the automatically generated id value for my-domain.com, it will be used to enter user mail boxes and mail box aliases.

Adding User Mail Box Entries

To add entries for each mail box, you will need the domain id from the previous step for the corresponding domain name.

We do not store the user passwords as plain text in the database, instead we will generate an SHA512 hash of the passwords.

To insert a virtual user mailbox use the following query…

INSERT INTO virtual_users SET
id=NULL,
domain_id=1, //From previous step//
password=ENCRYPT('user-password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),
email='me@my-domain.com';

To see currently assigned mail boxes for a domain (with password suppressed), use the following query…

mysql> SELECT U.id, domain_id, email FROM virtual_domains D
JOIN virtual_users U ON(U.domain_id=D.id) WHERE name='my-domain.com';
+----+-----------+-------------------+
| id | domain_id | email             |
+----+-----------+-------------------+
|  1 |         1 | me@my-domain.com  |
|  2 |         1 | you@my-domain.com |
|  3 |         1 | us@my-domain.com  |
+----+-----------+-------------------+

Adding Mail Box Alias Entries

To add entries for each mail box alias, you will again need the domain id from the first step corresponding to the domain name of the alias.

To insert a virtual mailbox alias use the following query…

INSERT INTO virtual_aliases SET
id=NULL,
domain_id=1,
source='alias@my-domain.com',
destination='you@my-domain.com';

In the example given, mail sent to alias@my-domain.com will be accepted and delivered to you@my-domain.com.

To see current aliases in the database, use the following query…

mysql> SELECT A.id, domain_id, source, destination FROM virtual_domains D 
JOIN virtual_aliases A ON(A.domain_id=D.id) WHERE name='my-domain.com';
+----+-----------+---------------------+-------------------+
| id | domain_id | source              | destination       |
+----+-----------+---------------------+-------------------+
|  1 |         1 | alias@my-domain.com | you@my-domain.com |
+----+-----------+---------------------+-------------------+

Return to main article page

 howtos:network_services:postfix_dovecot_mysql:database ()