Learn Mailserver - Virtual Users & Database Backend

#Introduction

Up to episode 6, our mailboxes were tied to Linux system users — for every email account there had to be an OS account. That's impractical for multi-domain: you don't want to create an OS user for every email account, right?

The solution is virtual mailboxes: email accounts that live only in the database and have no system user. Postfix and Dovecot both read the database to know which domains, users, and aliases are valid. This episode builds that foundation with MariaDB.

We'll dissect the concept of virtual users, design the table structure, configure Postfix for virtual domains, then connect Dovecot to SQL for authentication.

#The Virtual Mailbox Concept

The fundamental difference:

• System user — an email account equals an OS account. One OS account per email; mail is stored in the home directory. Not suitable for many accounts or multi-domain.
• Virtual user — an email account is just an entry in the database. Many domains, many accounts, all managed through SQL. Mailboxes are stored in one shared directory, usually /var/mail/vhosts/<domain>/<user>/.

Postfix recognizes virtual domains via virtual_mailbox_domains. Virtual users never log into a shell — they only "exist" as database entries, and that's actually more secure because it doesn't expand the OS attack surface.

#Installing MariaDB

Install and secure the database server:

sudo apt install -y mariadb-server
sudo mysql_secure_installation

Then create a dedicated database and user for mail, with a strong password stored in the configuration file:

sudo mysql -e "CREATE DATABASE maildb;"
sudo mysql -e "CREATE USER 'mail'@'localhost' IDENTIFIED BY 'ganti-password';"
sudo mysql -e "GRANT ALL ON maildb.* TO 'mail'@'localhost';"

mysql -e runs SQL directly from the command line — useful for automation without opening an interactive client.

#Database Structure

Three core tables that almost every mail server tutorial uses:

CREATE TABLE virtual_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);
 
CREATE TABLE virtual_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  domain_id INT NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id)
);
 
CREATE TABLE virtual_aliases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  domain_id INT NOT NULL,
  source VARCHAR(255) NOT NULL,
  destination VARCHAR(255) NOT NULL,
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id)
);

Note: passwords are stored as hashes, not plaintext. Episode 13 will cover how to generate hashes that match Dovecot's authentication mechanisms.

#Postfix and Virtual Domains

Tell Postfix that domains and users are virtual. Postfix doesn't read the database directly — it uses lookup tables generated from SQL queries. For that, the additional parameters virtual_mailbox_domains, virtual_mailbox_maps, and virtual_mailbox_base are needed:

sudo postconf -e 'virtual_mailbox_domains = example.com'
sudo postconf -e 'virtual_mailbox_base = /var/mail/vhosts'
sudo postconf -e 'virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf'
sudo postconf -e 'virtual_transport = virtual'
sudo postfix check
sudo postfix reload

The mysql-virtual-mailbox-maps.cf file contains the connection and query, for example:

user = mail
password = ganti-password
hosts = localhost
dbname = maildb
query = SELECT email FROM virtual_users WHERE email='%s'

Test the lookup directly:

postmap -q user@example.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

If the query returns the same address, Postfix found the right mailbox.

#Dovecot and SQL Auth

Dovecot uses passdb (password verification) and userdb (mailbox location & uid/gid). Both are configured in conf.d/auth-sql.conf.ext:

sudo sed -i 's/#!include auth-sql.conf.ext/!include auth-sql.conf.ext/' /etc/dovecot/conf.d/10-auth.conf

Create /etc/dovecot/dovecot-sql.conf.ext:

driver = mysql
connect = host=localhost dbname=maildb user=mail password=ganti-password
 
password_query = SELECT email AS user, password FROM virtual_users WHERE email='%u'
 
user_query = SELECT 5000 AS uid, 5000 AS gid, '/var/mail/vhosts/%d/%n' AS home, \
             'maildir:/var/mail/vhosts/%d/%n' AS mail

Here %u is the full email, %d is the domain, and %n is the name part before @. Uid/gid 5000 refers to the virtual user we create on the system — one OS user hosts all mailboxes.

#Adding New Users

Adding an account is now just an SQL insert. The password hash can be generated with doveadm pw:

doveadm pw -s SHA512-CRYPT

The resulting hash is then inserted into the table:

INSERT INTO virtual_domains (name) VALUES ('example.com');
INSERT INTO virtual_users (domain_id, email, password)
VALUES (1, 'admin@example.com', '$6$rounds=656000$abc...');

Every database change takes effect immediately without a reload — that's the advantage of a SQL backend over static maps.

#End-to-End Verification

Time to make sure Postfix and Dovecot are "talking" about the same users:

postmap -q admin@example.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
doveadm auth test admin@example.com 'password-rahasia'

doveadm auth test exercises passdb exactly like the IMAP server does during login. If the output says auth succeeded, you're ready to send email to the virtual mailbox — and the mailbox will appear at /var/mail/vhosts/example.com/admin/.

#Conclusion

Episode 7 is done. Key takeaways:

• Virtual users separate email accounts from OS accounts; one vmail user hosts everything.
• The virtual_domains, virtual_users, and virtual_aliases tables are the standard pattern.
• Postfix uses MySQL maps via virtual_mailbox_maps and virtual_transport = virtual.
• Dovecot uses SQL passdb/userdb; %u, %d, %n are important placeholders.
• doveadm auth test and postmap -q are the fastest verification tools.

The user backend is alive. In episode 8 we install the front end: Roundcube — webmail installation and configuration, from PHP and web server preparation to installation via /installer and configuring config.inc.php. See you in episode 8!