SQL-Backed Virtual Users for Postfix and Dovecot: One Store, Zero Reloads
Stop editing flat files and reloading two daemons every time someone orders a mailbox. Three normalized SQL tables become the single source of truth that Postfix and Dovecot both query live, so provisioning collapses to a single INSERT with ARGON2ID hashing and a SELECT-only DB role.
EvilMail TeamJuly 12, 202610 min read
Here is the flat-file loop every mail admin knows by heart: someone orders a mailbox, you open /etc/postfix/vmailbox, add a line, run postmap, then open Dovecot's passwd-file, add a matching line, then reload both daemons. Do it fifty times and the two views drift — a mailbox exists in Postfix that Dovecot won't authenticate, or an alias points at an address Postfix never learned about. The flat file is a cache pretending to be a database, and you are the replication process.
Replace all of it with one SQL store that both daemons read live. Postfix looks up domains, mailboxes, and aliases against it; Dovecot authenticates and resolves Maildir paths against the same rows. Adding a mailbox becomes a single INSERT — no postmap, no reload, no drift. Both daemons hold persistent connections and see the new row on their next query.
This guide targets Postfix 3.8/3.9, Dovecot 2.3/2.4, and PostgreSQL 16 (MariaDB 10.11 works with trivial dialect changes). Mail lives under /var/mail/vhosts/%d/%n, owned by a single vmail
SQL-Backed Virtual Users for Postfix + Dovecot (2026 Guide) — EvilMail Blog
user, uid/gid 5000. One stance up front: the copy-pasted
default_pass_scheme = PLAIN
tutorials still circulating in 2026 are wrong. We hash with ARGON2ID and bind the database to a SELECT-only role on 127.0.0.1.
One schema both daemons agree on
Three tables. Domains own users and aliases; the foreign keys and cascade handle cleanup, so you never leave orphaned rows behind.
sql
CREATE TABLE virtual_domains (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX idx_domains_name ON virtual_domains(name);
CREATE TABLE virtual_users (
id SERIAL PRIMARY KEY,
domain_id INT NOT NULL REFERENCES virtual_domains(id) ON DELETE CASCADE,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
quota_bytes BIGINT NOT NULL DEFAULT 0,
active BOOLEAN NOT NULL DEFAULT true
);
CREATE UNIQUE INDEX idx_users_email ON virtual_users(email);
CREATE TABLE virtual_aliases (
id SERIAL PRIMARY KEY,
domain_id INT NOT NULL REFERENCES virtual_domains(id) ON DELETE CASCADE,
source VARCHAR(255) NOT NULL,
destination VARCHAR(255) NOT NULL
);
CREATE INDEX idx_aliases_source ON virtual_aliases(source);
Two columns earn their place beyond the obvious. quota_bytes lets Dovecot enforce a per-mailbox limit straight from the row. active lets you suspend an account — for non-payment, abuse, or a security hold — without deleting a byte of mail. Postfix and Dovecot both filter on active = true, so flipping one boolean takes a mailbox offline within a query and flipping it back restores it. The unique index on email is not optional: it is what stops a race in your signup form from creating two rows for the same address.
Wiring Postfix to the rows
Three Postfix parameters map to three query files. Each .cf file is a small contract: connection details plus one SQL statement.
The virtual_transport line is the one people skip, and it matters. It hands final delivery to Dovecot over LMTP instead of letting Postfix write the Maildir itself. That keeps quota accounting, Sieve filtering, and the active check all on Dovecot's side of the fence — one process owns the mailbox, and Postfix just routes.
The domain map is the simplest. It answers one question: do we accept mail for this domain at all?
ini
# /etc/postfix/sql/virtual-mailbox-domains.cf
user = mailuser
password = <db-password>
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
The mailbox map decides whether a specific address is a real, active mailbox; the alias map returns forwarding destinations:
ini
# virtual-mailbox-maps.cf
query = SELECT 1 FROM virtual_users WHERE email='%s' AND active=true
# virtual-alias-maps.cf
query = SELECT destination FROM virtual_aliases WHERE source='%s'
Postfix escapes %s before substituting it, so a crafted recipient address can't break out of the string. Don't treat that as your only defense: the mailuser role must still be granted SELECT and nothing else, so even a hypothetical injection has no reach. Least privilege and input escaping are layers, not alternatives.
Dovecot: auth and userdb from the same table
Dovecot asks two different questions, and the split matters. The passdb answers "can this person log in" — it needs the address and the password hash. The userdb answers "where does their mail live and as which system user" — it returns the home directory, uid, gid, and Maildir path. Both read the same table.
ini
# /etc/dovecot/dovecot-sql.conf.ext
driver = pgsql
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=<db-password>
default_pass_scheme = ARGON2ID
password_query = \
SELECT email AS user, password \
FROM virtual_users WHERE email='%u' AND active=true
user_query = \
SELECT '/var/mail/vhosts/%d/%n' AS home, \
5000 AS uid, 5000 AS gid, \
'maildir:/var/mail/vhosts/%d/%n' AS mail \
FROM virtual_users WHERE email='%u'
Enable the SQL backend in 10-auth.conf with !include auth-sql.conf.ext, and set the global fallback in 10-mail.conf:
ini
mail_location = maildir:/var/mail/vhosts/%d/%n
Dovecot's variables differ from Postfix's: %u is the full address, %d the domain, %n the local part. So [email protected] resolves her home to /var/mail/vhosts/evilmail.pro/alice, owned by uid/gid 5000. Every domain gets its own directory tree under one system user — no per-user OS accounts, no /etc/passwd churn.
Hashing done right — kill PLAIN
If a tutorial tells you to set default_pass_scheme = PLAIN and store passwords as-is, close the tab. A dump of that table hands an attacker every mailbox in cleartext. SHA512-CRYPT was defensible a decade ago; in 2026 it is a fast hash on modern GPUs. Use ARGON2ID — memory-hard, tunable, and native in Dovecot 2.3+. BLF-CRYPT (bcrypt) is an acceptable fallback on older builds.
Store the full {SCHEME} prefix in the password column. That prefix is what lets you rotate schemes per row: an old account can carry {SHA512-CRYPT} while new signups get {ARGON2ID}, and Dovecot picks the right verifier per hash. No flag day, no forced mass reset — you re-hash accounts as users next log in. default_pass_scheme only applies to rows with no prefix, so keeping the prefix in the column is strictly safer.
Provisioning in one statement
This is the whole payoff. Adding a mailbox is one shell line — hash the password, insert the row:
bash
HASH=$(doveadm pw -s ARGON2ID -p 'secret')
psql -U provisioner mailserver -c \
"INSERT INTO virtual_users(domain_id,email,password)
VALUES((SELECT id FROM virtual_domains WHERE name='evilmail.pro'),
'[email protected]','$HASH');"
That's it. No postmap, no systemctl reload. Postfix and Dovecot each hold their own persistent pool of connections to PostgreSQL and see the new row on the very next lookup. A forward is one row in virtual_aliases. Suspending an account is UPDATE virtual_users SET active=false WHERE email='[email protected]', and re-enabling is the same flip in reverse.
Because provisioning is a plain INSERT, your signup form and a new mailbox are the same operation. Wire the web app's registration handler to that statement — through the provisioner role, which holds INSERT/UPDATE — and the entire "order a mailbox" workflow collapses into one database call.
Verify before you trust it
Never assume the wiring works — prove each layer independently. Postfix ships postmap -q precisely so you can query a map by hand:
If postmap -q returns nothing, the .cf connection or query is wrong — check that the daemon can reach 127.0.0.1 and that the role can SELECT. If doveadm auth test fails but doveadm user succeeds, the problem is the password hash or default_pass_scheme, not the plumbing. Once all four pass, send a real test message and check the target Maildir:
bash
ls -la /var/mail/vhosts/evilmail.pro/alice/Maildir/new/
A file appearing there means Postfix routed over LMTP and Dovecot wrote the message as uid 5000. Now you can trust it.
Lock it down — checklist
SELECT-only role for the daemons.GRANT SELECT ON ALL TABLES IN SCHEMA public TO mailuser; and nothing more. Postfix and Dovecot never write.
Separate role for provisioning. The web app or CLI uses a provisioner role with INSERT/UPDATE, never the credentials the daemons hold.
Bind the DB to localhost.listen_addresses = 'localhost' in postgresql.conf (or bind-address = 127.0.0.1 for MariaDB). The mail store has no business on the network.
Protect the `.cf` files. They contain DB passwords: chmod 640, chown root:postfix. Same for dovecot-sql.conf.ext — never world-readable.
Constraints and indexes.UNIQUE on email, an index on virtual_domains(name) and virtual_aliases(source), and ON DELETE CASCADE from domains so removing a domain drops its users and aliases atomically.
Watch for orphaned Maildirs. Deleting a row cascades in SQL but does not remove /var/mail/vhosts/... on disk. Reap orphaned directories on a schedule, or archive them.
Back up the database, not just the mail./var/mail is worthless without the rows that map addresses to it. Dump both, together.
The win isn't that the config is elegant. It's that a signup submission and a provisioned mailbox are now the same INSERT — one row, no reloads, no drift between two daemons that finally agree on where the truth lives.
