Postfix 05 - Postfix + Cyrus + MySQL

category: learnz/postfix · date: 2009-03-04 · source: old.linuxor.sk/MAIL/POSTFIX-HOWTOs/user17/howto-postfix-mysql.txt · theme: github

Postfix Mail Server Learning · Previous: Postfix + Dovecot + SquirrelMail + MailScanner + ClamAV · Next: body_checks and header_checks

The Slovak original of this document: Postfix 05 - Postfix + Cyrus + MySQL (slovensky).

asciiart

#######################################################
## CYRUS

# apt-get install cyrus-common-2.2 cyrus-clients-2.2 cyrus-admin-2.2 libsasl2-modules-sql libsasl2-modules
# apt-get install libsasl2-2 libsasl2-modules libsasl2-modules-sql sasl2-bin libpam-mysql openssl

#######################################################
## POSTFIX

# apt-get install postfix-mysql postfix-tls

## editing /etc/postfix/main.cf

# nano /etc/postfix/main.cf

 --------------- paste the main.cf listing here at the end, once it works !!!!!!!!!!!!

##

# cd /etc/postfix

# touch mysql_virtual_sender.cf
# nano mysql_virtual_sender.cf

user = postfix
password = <postfix_db_password>
hosts = 127.0.0.1
dbname = mail
table = mailbox
select_field = username
where_field = username

########################################
# touch mysql_virtual_alias_maps.cf
# nano mysql_virtual_alias_maps.cf

user = mail
password = <mail_db_password>
hosts = localhost
dbname = mail
table = alias
select_field = goto
where_field = address
additional_conditions = and active = '1'
#query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

########################################
# touch mysql_virtual_domains_maps.cf
# nano mysql_virtual_domains_maps.cf

user = mail
password = <mail_db_password>
hosts = localhost
dbname = mail
table = domain
select_field = domain
where_field = domain
additional_conditions = and backupmx = '0' and active = '1'
#query = SELECT domain FROM domain WHERE domain='%s' AND backupmx = '0' AND active = '1'

########################################
# touch mysql_virtual_mailbox_limit_maps.cf
# nano mysql_virtual_mailbox_limit_maps.cf

user = mail
password = <mail_db_password>
hosts = localhost
dbname = mail
table = mailbox
select_field = quota
where_field = username
additional_conditions = and active = '1'
#query = SELECT quota FROM mailbox WHERE username='%s' AND active = '1'

########################################
# touch mysql_virtual_mailbox_maps.cf
# nano mysql_virtual_mailbox_maps.cf

user = mail
password = <mail_db_password>
hosts = localhost
dbname = mail
table = mailbox
select_field = CONCAT(domain,'/',maildir)
where_field = username
additional_conditions = and active = '1'
#query = SELECT CONCAT(domain,'/',maildir) FROM mailbox WHERE username='%s' AND active = '1'

## setting the permissions on the configuration files

# chown root:postfix *.cf
# chmod 644 *.cf

## creating the mail user

# useradd -r -u 150 -g mail -d /var/vmail -s /sbin/nologin -c "Virtual mailbox" vmail
# mkdir /var/vmail
# chmod 770 /var/vmail/
# chown vmail:mail /var/vmail

## copying aliases

# cp /etc/aliases* /etc/postfix/
# newaliases

#######################################################
## MYSQL && APACHE2 && PHP

# apt-get install mysql-server-5.0 mysql-client-5.0 phpmyadmin apache2 libapache2-mod-php5 php5 php5-mysql

## creating the DB

# mysql -u root -p
# mysql> CREATE DATABASE mail;
# mysql> GRANT ALL PRIVILEGES ON mail.* TO 'mail'@'localhost' IDENTIFIED BY '<mail_db_password>';
# mysql> FLUSH PRIVILEGES;
# mysql> quit

## setting the password for the mail user

# mysql -u root -p
# mysql> use mysql;
# mysql> update user set password=PASSWORD("NEWPASSWORD") where User='mail';
# mysql> flush privileges;
# mysql> quit

## creating the tables

# mysql -u mail -p
# mysql> use mail;

# mysql> CREATE TABLE domain ( domain varchar(255) NOT NULL default '', description varchar(255) NOT NULL default '', aliases int(10) NOT NULL default '0', mailboxes int(10) NOT NULL default '0', maxquota int(10) NOT NULL default '0', transport varchar(255) default NULL, backupmx tinyint(1) NOT NULL default '0', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (domain), KEY domain (domain) ) TYPE=MyISAM COMMENT=' Virtual Domains';
# mysql> CREATE TABLE mailbox ( username varchar(255) NOT NULL default '', password varchar(255) NOT NULL default '', name varchar(255) NOT NULL default '', maildir varchar(255) NOT NULL default '', quota int(10) NOT NULL default '0', domain varchar(255) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (username), KEY username (username) ) TYPE=MyISAM COMMENT='Virtual Mailboxes';
# mysql> CREATE TABLE alias ( address varchar(255) NOT NULL default '', goto text NOT NULL, domain varchar(255) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', modified datetime NOT NULL default '0000-00-00 00:00:00', active tinyint(1) NOT NULL default '1', PRIMARY KEY (address), KEY address (address) ) TYPE=MyISAM COMMENT='Virtual Aliases';
# mysql> quit

#######################################################
## DOVECOT

# apt-get install dovecot-common

## editing dovecot-sql.conf

# nano /etc/dovecot/dovecot-sql.conf

driver = mysql
default_pass_scheme = plain
#connect = host=/var/run/mysqld/mysqld.sock dbname=mail user=root password=<mysql_root_password>
# Alternatively you can connect to localhost as well:
connect = host=localhost dbname=mail user=mail password=<mail_db_password>
password_query = SELECT password FROM mailbox WHERE username = '%u'
user_query = SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail, 150 AS uid, 8 AS gid, concat('dirsize:storage=',quota) AS quota FROM mailbox WHERE username ='%u' AND active ='1'

## editing dovecot.conf

# nano /etc/dovecot/dovecot.conf

base_dir = /var/run/dovecot/
protocols = imap pop3 imaps pop3s
listen = [::]
login_dir = /var/run/dovecot-login
mail_location = maildir:/var/vmail/%d/%n
mbox_read_locks = fcntl
log_timestamp = "%Y-%m-%d %H:%M:%S "
log_path = /var/log/maillog
mail_extra_groups = mail
first_valid_uid = 150
last_valid_uid = 150
maildir_copy_with_hardlinks = yes
userdb sql {
args = /etc/dovecot/dovecot-sql.conf
}
passdb sql {
args = /etc/dovecot/dovecot-sql.conf
}

## setting the permissions

chmod 600 /etc/dovecot/*.conf
chown vmail /etc/dovecot/*.conf

#######################################################
## ADDING A DOMAIN AND A USER

# mysql -u mail -p

mysql> USE mail;
mysql> INSERT INTO domain (domain,description,aliases,mailboxes,maxquota,transport,backupmx,active) VALUES ('domenanejaka.sk','Virtual domain','10','10', '0','virtual', '0','1');
mysql> INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('uzivatel1@domenanejaka.sk','<password_1>', 'Meno Uzivatela1','uzivatel1/', '0','domenanejaka.sk','1');
mysql> INSERT INTO mailbox (username,password,name,maildir,quota,domain,active) VALUES ('uzivatel2@domenanejaka.sk','<password_2>', 'Meno Uzivatela2','uzivatel2/', '0','domenanejaka.sk','1');
mysql> quit

#######################################################
## LINKS

http://wiki.sharlaan.net/us:howto:postfix:debian

http://www.howtoforge.com/isp-mailserver-with-virtual-users-domains-postfix-dovecot-mysql-centos5.0-p2

http://johnny.chadda.se/2007/04/15/mail-server-howto-postfix-and-dovecot-with-mysql-and-tlsssl-postgrey-and-dspam/

http://deblueconfuse.perbanas.ac.id/blogs/full-mail-server-solution-w-virtual-domains-users-debian-etch-postfix-mysql-dovecot-dspam-clamav-postgrey-rbl.html

Current practice (checked 2026-10)

noteThe article above is kept as it was written in 2009. This section lists what has changed since and what to do instead today.

Cleartext passwords in the database: default_pass_scheme = plain and INSERT ... '<password_1>' store every mailbox password readable in the mailbox table. Store hashes produced by doveadm pw (ARGON2ID, BLF-CRYPT or SHA512-CRYPT, in that order of preference per the Dovecot documentation), with the {SCHEME} prefix in the column or a matching default scheme.
World-readable database credentials: chmod 644 *.cf lets every local user read the MySQL password in the Postfix map files. Use chown root:postfix with chmod 640, and do not use passwords equal to the account name (mail/mail, postfix/postfix).
Legacy query parameters: select_field, where_field, table and additional_conditions have been deprecated since Postfix 2.2 and may be removed. Use the query = SELECT ... line that the article already carries as a comment.
MySQL account statements: GRANT ... IDENTIFIED BY for creating a user, the PASSWORD() function and editing mysql.user by hand were removed in MySQL 8.0. Create the account with CREATE USER and change the password with ALTER USER, then GRANT only what is needed: the Postfix and Dovecot lookups need SELECT, not ALL PRIVILEGES.
Table definitions: TYPE=MyISAM is no longer accepted; the option is ENGINE=, and InnoDB is the engine to use. The default '0000-00-00 00:00:00' columns are rejected under the strict SQL mode that is the default in current MySQL.
Dovecot configuration: /etc/dovecot/dovecot-sql.conf with connect =, password_query and user_query, and userdb sql { args = ... }, are Dovecot 1.x/2.3 syntax. Dovecot 2.4 puts the SQL settings into the main configuration (sql_driver = mysql, a mysql block, passdb sql { query = ... }) and uses %{user} style variables.
Packages: postfix-tls, mysql-server-5.0, php5, libapache2-mod-php5 and cyrus-*-2.2 no longer exist; TLS support is part of the postfix package. Despite the title, Cyrus is only installed here and the IMAP/POP3 server that is configured is Dovecot.
SMTP AUTH: libsasl2-modules-sql and libpam-mysql are installed for Cyrus SASL against MySQL. With Dovecot already present, smtpd_sasl_type = dovecot reuses the same SQL passdb and avoids a second authentication stack.
TLS: imaps and pop3s are enabled without any certificate settings and the notes configure no TLS in Postfix. Both need a certificate clients can validate, and plain imap/pop3 logins without TLS should stay disabled.

bash

$ # doveadm pw -s ARGON2ID

Sources: