Lesson 004 · Linux MTA Operations Learning Path

MariaDB and PostfixAdmin-Style Virtual Mail Database

Anil Jalela · Published Sep 3, 2026 · 10 min read

A virtual-mail database is an authorization system, not just an address book. Postfix asks whether a domain or recipient exists and where an address routes. Dovecot asks whether a user may authenticate and which UID, GID, home and mailbox apply. If every daemon connects as a database administrator, a single configuration disclosure becomes full database compromise. This lesson builds explicit tables and narrow read-only service accounts before either daemon depends on them.

Prerequisites and inherited lab checkpoint

Begin with the Lesson 3 checkpoint: Postfix is reachable only on the intended SMTP path, relay trust is still narrow, and submission/mailbox ports remain closed. Use a separate lab database host where practical. If MariaDB shares the mail host, bind it to a private interface or local socket and document the combined failure boundary.

This course demonstrates a PostfixAdmin-style data model but does not require its web interface. If an administration UI is later installed, patching, TLS, multifactor access, session security and its write-capable database account become separate responsibilities. Mail daemons still receive read-only accounts.

Install and prepare the required components

sudo dnf install -y mariadb-server mariadb
sudo systemctl enable --now mariadb
sudo systemctl status mariadb --no-pager
sudo ss -lntp | grep ':3306 ' || true
sudo mariadb -e 'SELECT VERSION();'

• Use supported RHEL modules and repositories. Record the exact MariaDB stream and lifecycle before production adoption; changing a database stream is not an ordinary package update.
• A local Unix-socket administrative login avoids placing a root password in shell history. Remote administration requires a separate named identity and protected network path.
• Do not expose TCP/3306 publicly. Database access belongs on localhost or an explicitly controlled private network with host and network firewall evidence.
• Run the distribution hardening workflow deliberately: remove test objects and remote administrative access after confirming automation and recovery will not depend on them.

Create a normalized schema and separate lookup identities

The following lab schema records active domains, mailboxes and aliases. Production schemas can add quota, transport, ownership and audit fields, but each mail query should return one defined value. Store canonical lowercase domains and addresses at the application boundary so lookup behavior is predictable.

CREATE DATABASE mailserver CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE TABLE mailserver.virtual_domains (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL UNIQUE,
  active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE mailserver.virtual_users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  domain_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(320) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id)
);
CREATE TABLE mailserver.virtual_aliases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  domain_id BIGINT UNSIGNED NOT NULL,
  source VARCHAR(320) NOT NULL,
  destination TEXT NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  INDEX (source),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id)
);

Create two local service accounts with generated unique secrets delivered through a protected secret mechanism. The literal placeholders below must never be deployed:

CREATE USER 'postfix_lookup'@'localhost' IDENTIFIED BY 'REPLACE_WITH_SECRET';
CREATE USER 'dovecot_lookup'@'localhost' IDENTIFIED BY 'REPLACE_WITH_DIFFERENT_SECRET';
GRANT SELECT ON mailserver.virtual_domains TO 'postfix_lookup'@'localhost';
GRANT SELECT ON mailserver.virtual_users TO 'postfix_lookup'@'localhost';
GRANT SELECT ON mailserver.virtual_aliases TO 'postfix_lookup'@'localhost';
GRANT SELECT ON mailserver.virtual_domains TO 'dovecot_lookup'@'localhost';
GRANT SELECT ON mailserver.virtual_users TO 'dovecot_lookup'@'localhost';
FLUSH PRIVILEGES;

Dovecot needs password hashes, not recoverable passwords. Select a scheme supported by the installed Dovecot build, generate hashes through a protected input channel and plan migration away from weak hashes. Never put a password after -p on a shared command line.

Verify the working path

sudo mariadb -e 'SHOW DATABASES;'
sudo mariadb mailserver -e 'SHOW TABLES;'
sudo mariadb -e "SHOW GRANTS FOR 'postfix_lookup'@'localhost';"
sudo mariadb -e "SHOW GRANTS FOR 'dovecot_lookup'@'localhost';"
sudo mariadb mailserver -e 'CHECK TABLE virtual_domains,virtual_users,virtual_aliases;'
sudo ss -lntp | grep ':3306 ' || true
sudo firewall-cmd --list-all

• Grant output must show only the required SELECT privileges. A global grant, GRANT OPTION, FILE or write privilege fails this checkpoint.
• If the database listens on TCP, prove that only the intended private address and clients can reach it. Local-only designs should prefer the Unix socket.
• Insert lab records in a transaction, verify exact and inactive lookups, then remove them or preserve them as the documented next-lesson fixture.
• CHECK TABLE is not a backup or restore test. A successful isolated restore is the evidence that recovery material is usable.

Production decisions before continuing

Define database truth without making it a single silent dependency

The database becomes authoritative for virtual recipient and authentication decisions. That makes query correctness, latency and failure semantics part of SMTP availability. A recipient query that fails must not be interpreted as “recipient does not exist” without deliberation; temporary database failure should normally produce a temporary SMTP failure rather than discard or permanently reject legitimate mail.

Postfix and Dovecot do not need the same columns or privileges. Postfix queries return domain existence, recipient existence and alias destinations. Dovecot returns one authentication record and stable mailbox attributes. Avoid SELECT *, write access and shared credentials. Root-owned query files must not be readable by ordinary users.

PostfixAdmin can manage a compatible schema, but the course focuses on the data contract. Before installing any UI, verify its expected schema rather than pointing it at a handcrafted database and hoping versions agree.

Understand the component before configuring it

Build it step by step

1. Classify data. Separate domain, mailbox, alias, quota and authentication responsibilities before creating tables.
2. Create constraints. Use uniqueness, foreign keys and explicit active state so invalid relationships fail early.
3. Create service identities. Give each daemon only SELECT on its required tables and restrict connection origin.
4. Load a reserved fixture. Add one active domain, two active users, one disabled user and one alias for deterministic tests.
5. Test both answers. Verify exact success, unknown address, disabled address, ambiguous result and database-unavailable behavior.
6. Protect secrets. Deliver generated values outside shell history and source control; set restrictive file ownership later.
7. Back up and restore. Restore into an isolated database and compare schema, row counts and test queries.

Operate and inspect the component

sudo mariadb mailserver
sudo mariadb-dump --single-transaction --routines --events mailserver > /root/mailserver-lab.sql
sudo install -d -m 0700 /root/mail-db-restore-test
mysql --protocol=socket -u postfix_lookup -p -e 'SELECT name FROM mailserver.virtual_domains WHERE active=1;'
doveadm pw -l
doveadm pw -s ARGON2ID

• The dump path is illustrative. Production backup files require encryption, access control, integrity evidence, off-host retention and restore testing.
• Prompt for service passwords interactively during a lab; do not include them in command arguments, terminal recordings or process listings.
• Confirm the exact password schemes shown by doveadm pw -l. A hash label unsupported by the deployed Dovecot will cause every login to fail.
• A consistent SQL snapshot still needs coordination with mailbox data when recovery objectives require both to represent the same point.

Evidence and acceptance criteria

A database outage must not become a permanent recipient rejection

During maintenance, the mail database stops accepting connections. A poorly written recipient map treats query failure like an empty result and Postfix returns a permanent unknown-user response. Remote senders generate bounces for valid customers. Reconnecting the database cannot recover messages that were never accepted.

The corrected design uses supported Postfix SQL maps whose lookup errors remain errors, bounded connection timeouts and monitoring that distinguishes an unknown recipient from an unavailable lookup. A controlled outage test expects a 4xx temporary response. Once SQL recovers, the same address is accepted and follows the normal delivery path.

Troubleshooting by symptom

Unsafe operations and recovery boundaries

• Unsafe: copying plaintext database credentials from a working server or reference file into course content, Git or chat exposes the entire lookup boundary.
• Unsafe: granting mail daemons administrative or write privileges turns a configuration compromise into data modification or destruction.
• Unsafe: running an unreviewed PostfixAdmin schema installer against an existing database can alter tables expected by live services.
• Unsafe: treating lookup failure as “not found” creates permanent delivery errors during a temporary dependency outage.

Rewritten knowledge checks

Why use separate Postfix and Dovecot database accounts?

Each daemon receives only the tables and operations it needs, limiting disclosure and simplifying rotation and audit.

Should mail services store recoverable passwords?

No. Store modern password hashes supported by the authenticating service.

Why is a unique email constraint useful?

It prevents ambiguous recipient or authentication results for the same canonical address.

How should temporary SQL failure affect SMTP recipient validation?

It should normally defer with a 4xx result rather than permanently reject a valid recipient.

Does <code>CHECK TABLE</code> prove recoverability?

No. Only an isolated restore followed by query and integrity tests proves the backup is usable.

Why avoid <code>SELECT *</code> in service maps?

It exposes unnecessary data and makes the contract vulnerable to schema changes.

What privilege should a lookup identity normally have?

SELECT on the exact required tables or views, from the intended connection origin.

Can a database dump alone restore a mail service?

Usually not; mailbox data, Sieve state, configurations, TLS and signing keys may also be required.

What is a disabled-record test for?

It proves deprovisioning changes the lookup result without deleting audit-relevant identity data.

Why is an administration UI a separate security boundary?

It writes identity data and introduces web authentication, session, patch and authorization risks not needed by daemon lookups.

Cumulative lab checkpoint

1. Install MariaDB and prove it is not reachable from an untrusted network.
2. Create the lab schema and constraints using reserved identities.
3. Create separate SELECT-only Postfix and Dovecot accounts without recording their secrets.
4. Load active, disabled, missing and alias fixtures; document every expected query result.
5. Simulate database unavailability and specify the required temporary SMTP behavior for Lesson 5.
6. Perform an isolated restore test and retain a secret-free schema, grant and acceptance report as the Lesson 4 checkpoint.

Primary references

• Dovecot CE documentation
• Dovecot SQL authentication
• Dovecot LMTP with Postfix