Server administration Setting Up a Mail Server from Scratch – Part 2: Creating Databases

[D•Jass]

Creating Databases and Users​

We'll create a database to store virtual domains, users, and aliases, as well as a separate one for Roundcube.

2.1. Creating Databases and Service Users​

Log in to MariaDB as root:

mysql -u root -p

Then run the following SQL commands:
Warning: Replace 'StrongPostfixPass' and 'StrongRoundcubePass' with your own strong, unique passwords! Use a secure generator (e.g., openssl rand -base64 32) and store them safely.

-- Create databases
CREATE DATABASE postfix_accounts CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE roundcube CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create users for services (localhost-only access)
CREATE USER 'postfix_admin'@'localhost' IDENTIFIED BY 'StrongPostfixPass';
CREATE USER 'roundcube_admin'@'localhost' IDENTIFIED BY 'StrongRoundcubePass';

-- Grant privileges
GRANT ALL PRIVILEGES ON postfix_accounts.* TO 'postfix_admin'@'localhost';
GRANT ALL PRIVILEGES ON roundcube.* TO 'roundcube_admin'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

2.2. Creating Tables for Mail Data​

Now let's define the table structure for domains, mailboxes, and aliases.

USE postfix_accounts;

CREATE TABLE domains_table (
DomainId INT NOT NULL AUTO_INCREMENT,
DomainName VARCHAR(100) NOT NULL,
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (DomainId),
UNIQUE KEY (DomainName)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE accounts_table (
AccountId INT NOT NULL AUTO_INCREMENT,
DomainId INT NOT NULL,
Email VARCHAR(255) NOT NULL,
PasswordHash VARCHAR(255) NOT NULL,
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
QuotaBytes BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (AccountId),
UNIQUE KEY (Email),
FOREIGN KEY (DomainId) REFERENCES domains_table(DomainId) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE alias_table (
AliasId INT NOT NULL AUTO_INCREMENT,
DomainId INT NOT NULL,
Source VARCHAR(255) NOT NULL,
Destination TEXT NOT NULL,
IsActive BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (AliasId),
FOREIGN KEY (DomainId) REFERENCES domains_table(DomainId) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2.3. Adding a Domain and Test Mailboxes​

Add your main domain and a couple of example mailboxes.
Warning: Replace 'AdminPass' and 'UnsortedPass' with your own strong passwords. These are the passwords users will use to log in to their mailboxes!

-- Add a domain (replace example.com with your domain)
INSERT INTO domains_table (DomainName) VALUES ('example.com');

-- Add user admin@example.com
INSERT INTO accounts_table (DomainId, Email, PasswordHash)
VALUES (
(SELECT DomainId FROM domains_table WHERE DomainName = 'example.com'),
'admin@example.com',
ENCRYPT('AdminPass', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))
);

-- Add user unsorted@example.com (for catch-all)
INSERT INTO accounts_table (DomainId, Email, PasswordHash)
VALUES (
(SELECT DomainId FROM domains_table WHERE DomainName = 'example.com'),
'unsorted@example.com',
ENCRYPT('UnsortedPass', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16)))
);

2.4. (Optional) Catch-All Alias Setup​

This alias will forward all emails sent to non-existent addresses under example.com to unsorted@example.com.

INSERT INTO alias_table (DomainId, Source, Destination)
VALUES (
(SELECT DomainId FROM domains_table WHERE DomainName = 'example.com'),
'@example.com',
'unsorted@example.com'
);

EXIT;