📨 Please read the previous part first!
This post is the fifth article in the "Building a Mail Server on Linux" series.
In the previous part, we analyzed 4 essential configuration files for Dovecot. If you haven't read it yet, I recommend checking it out first.
👉 Previous Part: Analysis of Essential Dovecot Configuration Files

🔄 Order Adjustment Notice
In the last part, I announced that the next article would cover the auth-sql.conf.ext configuration, however,
while writing the article, I concluded that it would be more logical to explain the DB table design first.

Therefore, in this article, we will cover the concept of Dovecot virtual users and PostgreSQL table design,
and in the next part, we will explain the auth-sql.conf.ext configuration for integrating Dovecot with PostgreSQL.

🎯 What are Dovecot Virtual Users? Why are they needed?

There are mainly two methods for authenticating users in Dovecot.

1. System account-based authentication
2. Authentication using Linux's /etc/passwd and /etc/shadow files
3. Utilization of regular system user accounts

4. Account management can be cumbersome, making it difficult to create mail-specific accounts

5. Virtual user-based authentication

6. Authentication using a separate database (PostgreSQL, MySQL, etc.)
7. Independent management of mail-specific users from system accounts
8. Highly secure and scalable, suitable for large-scale mail services

We are planning to build a virtual user environment using PostgreSQL.
This means that we can authenticate by retrieving user information from the database without creating actual Linux accounts.

🔹 Advantages of a Virtual User Environment

• High security as they are separate from system accounts
• Easy addition and removal of user accounts
• Support for multiple domains (management of several mail domains on a single server)
• Excellent scalability and ease of management

🛠️ Table Design in PostgreSQL

To manage virtual users properly in Dovecot, a minimum of 3 tables is required.

1️⃣ Essential Table Composition

1. mail_domain - List of domains managed by the mail server
2. mail_users - Actual mail user information
3. mail_alias - Management of email aliases (forwarding)

The reason for separating these is for scalability and ease of maintenance.
It is very useful when operating multiple mail domains or forwarding specific emails to other addresses.

2️⃣ Table Schema and SQL Examples

📌 1. mail_domain (Domain List Management)
This table manages the domains allowed on the mail server.
In other words, it defines mail domains like example.com or mydomain.com.

CREATE TABLE mail_domain (
    id SERIAL PRIMARY KEY,
    domain_name VARCHAR(255) NOT NULL UNIQUE,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW()
);

🔹 Description
- domain_name → Mail domain (e.g., example.com)
- active → Whether to use this domain (can be deactivated)
- created_at → Creation date

Sample data insertion example

INSERT INTO mail_domain (domain_name) VALUES ('example.com');
INSERT INTO mail_domain (domain_name) VALUES ('mydomain.com');

📌 2. mail_users (Mail User Accounts)
This stores user account information that will actually use mail.
Each user must belong to a specific domain.

CREATE TABLE mail_users (
    id SERIAL PRIMARY KEY,
    domain_id INT NOT NULL REFERENCES mail_domain(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password TEXT NOT NULL,
    home_directory TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

🔹 Description
- domain_id → Links to the id of the mail_domain table (the domain to which the mail user belongs)
- email → User email address (user@example.com)
- password → Stores encrypted password
- home_directory → Mail storage path (/var/mail/vmail/user@example.com)
- created_at → Account creation date

Sample data insertion example

INSERT INTO mail_users (domain_id, email, password, home_directory)
VALUES (1, 'user@example.com', 'hashed_password_here', '/var/mail/vmail/user@example.com');

📌 3. mail_alias (Email Aliases / Forwarding) Needed for forwarding a mail address to another address or using multiple aliases for a single address.

CREATE TABLE mail_alias (
    id SERIAL PRIMARY KEY,
    domain_id INT NOT NULL REFERENCES mail_domain(id) ON DELETE CASCADE,
    source_email VARCHAR(255) NOT NULL,
    destination_email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

🔹 Description

• source_email → Original email address (e.g., contact@example.com)
• destination_email → Email address to which the mail will be forwarded (e.g., admin@example.com)
• This performs the function of automatically forwarding a specific email address to another address.

Sample data insertion example

INSERT INTO mail_alias (domain_id, source_email, destination_email)
VALUES (1, 'contact@example.com', 'admin@example.com');

Now, any mail sent to contact@example.com will automatically be forwarded to admin@example.com.

🔍 Recommended Tables for Future Expansion

In addition to basic mail service operations, additional features can be expanded. Below are examples of tables that can be expanded in the future.

🏁 Summary and Next Steps

✅ Topics covered in this article
- The concept and necessity of Dovecot virtual users
- PostgreSQL table design (mail_domain, mail_users, mail_alias)
- Recommendations for additional tables for email management expansion

Next part preview: auth-sql.conf.ext configuration and password hashing methods
Now that the database tables are ready, in the next part I will explain how Dovecot retrieves user information from PostgreSQL for authentication,
and discuss the password hashing methods to be used for mail user accounts.

In particular, I will compare the characteristics and security differences of MD5, SHA-256, and BCRYPT,
and specifically examine how to apply each method in Dovecot and PostgreSQL.

👉 Next part: auth-sql.conf.ext configuration and password hashing methods (Coming Soon!)