Database preparation:

After installing all the required packages, I suggest dedicating some time to preparing the database as a first step.

We use SQLite as our database because it is less resource-intensive and does a great job for SOHO servers. Wikipedia has a great page about SQLite you can refer to get more info.

I researched SQLite and estimate that, on a small VPS for a SOHO mail server (max 50 mailboxes), it can handle up to 10,000 queries per second, making it ideal for our purpose.

SQLite is file-based, the entire database is stored in a single file located in the Dovecot configuration directory /etc/dovecot which we will call mailserver.db.

# sqlite3 mailserver.db
SQLite version 3.43.2 2023-10-10 13:08:14
Enter ".help" for usage hints.
sqlite>

So with the above we created a mailserver.db file in the directory you are (so don’t forget to cd to /etc/dovecot) and the command line client is patiently waiting for our commands, and it’s what it’s what it get now:

CREATE TABLE `smtp_authorized` (
  `id` INTEGER PRIMARY KEY,
  `email` TEXT NOT NULL);
CREATE TABLE `virtual_aliases` (
  `id` INTEGER PRIMARY KEY,
  `domain_id` INTEGER NOT NULL,
  `source` TEXT NOT NULL,
  `destination` TEXT NOT NULL,
  CONSTRAINT `virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
);
CREATE TABLE `virtual_domains` (
  `id` INTEGER PRIMARY KEY,
  `name` TEXT NOT NULL
);
CREATE TABLE `virtual_users` (
  `id` INTEGER PRIMARY KEY,
  `domain_id` INTEGER NOT NULL,
  `email` TEXT UNIQUE NOT NULL,
  `password` TEXT NOT NULL,
  CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
);

The code above will produce the output below:

sqlite> CREATE TABLE `virtual_aliases` (
(x1...>   `id` INTEGER PRIMARY KEY,
(x1...>   `domain_id` INTEGER NOT NULL,
(x1...>   `source` TEXT NOT NULL,
(x1...>   `destination` TEXT NOT NULL,
(x1...>   CONSTRAINT `virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
(x1...> );
sqlite> 
sqlite> CREATE TABLE `virtual_domains` (
(x1...>   `id` INTEGER PRIMARY KEY,
(x1...>   `name` TEXT NOT NULL
(x1...> );
sqlite> 
sqlite> CREATE TABLE `virtual_users` (
(x1...>   `id` INTEGER PRIMARY KEY,
(x1...>   `domain_id` INTEGER NOT NULL,
(x1...>   `email` TEXT UNIQUE NOT NULL,
(x1...>   `password` TEXT NOT NULL,
(x1...>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
(x1...> );
sqlite>

Above I ran the CREATE query that creates the tables we need to contains our server’s information. If you type .schema:

sqlite> .schema
CREATE TABLE `virtual_aliases` (
  `id` INTEGER PRIMARY KEY,
  `domain_id` INTEGER NOT NULL,
  `source` TEXT NOT NULL,
  `destination` TEXT NOT NULL,
  CONSTRAINT `virtual_aliases_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
);
CREATE TABLE `virtual_domains` (
  `id` INTEGER PRIMARY KEY,
  `name` TEXT NOT NULL
);
CREATE TABLE `virtual_users` (
  `id` INTEGER PRIMARY KEY,
  `domain_id` INTEGER NOT NULL,
  `email` TEXT UNIQUE NOT NULL,
  `password` TEXT NOT NULL,
  CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
);
sqlite>

Youl’ll get back the structure of the database you just created. These are the valid commands to replicate the database in another file if needed (I did the same!).

Remember that if you need to exit from SQLite command prompt, when you’re done just type:

.exit

And you quit. Don’t do it now, let’s dig a bit on the structure itself.

Table virtual_domains

Table virtual_domains contains the name of the domains you’re about to handle with your server and it’s index, for example:

sqlite> INSERT INTO virtual_domains (name) VALUES ('example.com');
sqlite> SELECT * FROM virtual_domains;
+----+-------------+
| id |    name     |
+----+-------------+
| 1  | example.com |
+----+-------------+

(Nice output, right? To get it, don’t forget to type .mode table command on the SQLite prompt!)

The above command should be straightforward for anyone so let me skip about; just remember that the id field is auto-incrementing.

Table virtual_users

All the information about your users, for example:

sqlite> INSERT INTO virtual_users (domain_id,email,password) VALUES (1,'franck@example.com','{BLF-CRYPT}$2y$05$1D/aOoBha6lCuRi1Y9LGuOCv3sLCwkhuN9pIcael8SrVjGZztyudS');
sqlite> SELECT * FROM virtual_users;
1|1|franck@example.com|{BLF-CRYPT}$2y$05$1D/aOoBha6lCuRi1Y9LGuOCv3sLCwkhuN9pIcael8SrVjGZztyudS

(I used the ugly formatting to keep the SELECT result on a single line – The structure of the output should be clear however!)

INSERT and SELECT commands are straightforward; other fields:

• id: Single id of each user; not declared on the INSERT query so auto-incrementing;
• domain_id: Numeric domain id as created by the query on the virtual_domains table; because example.com has id = 1 so we indicate 1 here! If you handle multiple domains, insert the domain number conforming to the email address you declare on the email field;
• email: email field of your user;
• password: The encrypted password of your user. How to generate it? See below.

Table virtual_aliases

This is a nice feature: If you want to declare a postmaster user and deliver all it’s emails to franck@ without creating another email address, this is the way! I used to declare a family@ address and delivers all the emails for this address to me and to my wife! Because my daughter is still minor, I created a new mail address with her name and I forwarded everything to both the parents! When she’ll be 18, she already have it’s own email address.

Let’s do the magic:

sqlite> INSERT INTO virtual_aliases (domain_id,source,destination) VALUES (1,'postmaster@example.com','franck@example.com');
sqlite> SELECT * FROM virtual_aliases;
+----+-----------+------------------------+--------------------+
| id | domain_id |         source         |     destination    |
+----+-----------+------------------------+--------------------+
| 1  | 1         | postmaster@example.com | franck@example.com |
+----+-----------+------------------------+--------------------+

I think the mechanic is clear here!

How to generate the password for your users

When you installed dovecot, you installed also some utilities like doveadm that can be used to generate the encrypted passwords.

# doveadm pw -s BLF-CRYPT
Enter new password: 
Retype new password: 
{BLF-CRYPT}$2y$05$1D/aOoBha6lCuRi1Y9LGuOCv3sLCwkhuN9pIcael8SrVjGZztyudS

Enter new password and Retype new password are clear! Enter twice your password; the doveadm pw is the command itself; the -s BLF-CRYPT is the encryption scheme, bcrypt in this case. bcrypt is the most secure option available in Dovecot. We also have SHA512-CRYPT, which is faster but less secure, and two other options that are not worth considering (MD5 encryption, which is obsolete, and PLAIN, which stores passwords in plaintext).

Let’s dig a bit on the output:

• {BLF-CRYPT} is the encryption scheme;
• $2y$ : Algorithm version;
• 05 : Rounds when encryption was done; higher means harder but also slower; default is 5, paranoid is 10-12; if you want to specify the rounds number use the switch: -r 12 where 12 means twelve rounds; you have a minimum of 4 rounds and a maximum of 31: Make your thoughts;
• $1D/aOoBha6lC…GZztyudS : The salt and the encrypted password.

More info about doveadm pw command? https://doc.dovecot.org/main/core/man/doveadm-pw.1.html

That’s all for now!

Next article will setup postfix to query the database for user’s data; stay tuned.