Postfix with MySQL Backend for Virtual Domains and Users on Ubuntu 22.04

Introduction

In a real production mail server, we usually need to host multiple domains and many email accounts.
Creating local Linux system accounts for each mail user is not practical.
Instead, we can use MySQL backend with Postfix to manage virtual domains and virtual users.
This setup is widely used by web hosting companies and ISPs.

What Are Virtual Domains and Users?

• Virtual Domain: A domain that is hosted on the mail server but does not exist as a system domain. Example: bitscentric.com
• Virtual User: An email account stored in a database, not as a Linux system user. Example: user1@bitscentric.com

With this method, Postfix uses MySQL queries to check if a domain or user exists, instead of relying on /etc/passwd.

Step 1: Install Required Packages

sudo apt update
sudo apt install -y postfix postfix-mysql mysql-server

Step 2: Create MySQL Database for Mail

Login to MySQL:

sudo mysql -u root -p

Create database and user:

CREATE DATABASE mailserver;
CREATE USER 'mailuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
GRANT ALL PRIVILEGES ON mailserver.* TO 'mailuser'@'localhost';
FLUSH PRIVILEGES;

Step 3: Create Tables for Domains and Users

USE mailserver;

CREATE TABLE virtual_domains (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE virtual_users (
  id INT NOT NULL AUTO_INCREMENT,
  domain_id INT NOT NULL,
  password VARCHAR(150) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
);

CREATE TABLE virtual_aliases (
  id INT NOT NULL AUTO_INCREMENT,
  domain_id INT 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
);

Step 4: Add Example Domain and Users

INSERT INTO virtual_domains (name) VALUES ('bitscentric.com');

INSERT INTO virtual_users (domain_id, password, email)
VALUES (1, ENCRYPT('Password123', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user1@bitscentric.com');

INSERT INTO virtual_aliases (domain_id, source, destination)
VALUES (1, 'admin@bitscentric.com', 'user1@bitscentric.com');

Step 5: Configure Postfix to Use MySQL

Edit /etc/postfix/main.cf:

virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-users.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-aliases.cf

Step 6: Create MySQL Lookup Files

Create /etc/postfix/mysql-virtual-domains.cf:

user = mailuser
password = StrongPassword123
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'

Create /etc/postfix/mysql-virtual-users.cf:

user = mailuser
password = StrongPassword123
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'

Create /etc/postfix/mysql-virtual-aliases.cf:

user = mailuser
password = StrongPassword123
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

Step 7: Secure Files

sudo chmod 640 /etc/postfix/mysql-virtual-*.cf
sudo chown root:postfix /etc/postfix/mysql-virtual-*.cf

Step 8: Restart Postfix

sudo systemctl restart postfix

Step 9: Testing the Setup

Test domain lookup:

postmap -q bitscentric.com mysql:/etc/postfix/mysql-virtual-domains.cf

Test user lookup:

postmap -q user1@bitscentric.com mysql:/etc/postfix/mysql-virtual-users.cf

Test alias lookup:

postmap -q admin@bitscentric.com mysql:/etc/postfix/mysql-virtual-aliases.cf

Best Practices for Production

• Always use strong passwords for MySQL accounts.
• Use encrypted passwords (SHA512) for mail users.
• Integrate with Dovecot for IMAP/POP3 access to mailboxes.
• Enable SSL/TLS for secure mail delivery.
• Set proper DNS records (MX, SPF, DKIM, DMARC) for good inbox delivery.
• Backup the MySQL database regularly.

Conclusion

We have successfully integrated Postfix with MySQL backend on Ubuntu 22.04.
Now, we can manage virtual domains and users from a database instead of system accounts.
This method is scalable, easy to maintain, and suitable for production-level mail hosting environments.