Complete Virtual Mail Server/Postfix to Database
This article is part of the Complete Virtual Mail Server series, and may require previous parts to have been read or followed.
Installing Postfix
Postfix installation is quite straight forward. A basic installation instruction for Postfix should be followed, form it's article.
Linking Postfix
Once postfix is working on its most basic level, it's time to link it to the database.
Gaining access
PostgreSQL
A database-user to query the database is required:
user $createuser -U postgres --pwprompt postfixEnter password for new role: $password Enter it again: $password
Also, the postfix user needs some permissions on the tables:
user $psql -U postfixadmin postfixpostfix=>GRANT SELECT ON alias TO postfix;
postfix=>GRANT SELECT ON domain TO postfix;
postfix=>GRANT SELECT ON mailbox TO postfix;MySQL
If MySQL is used instead of PostgreSQL, then use the following instructions. Fetch the SQL dump and save it as genericmailsql.sql as it will be needed soon.
First, install MySQL:
root #emerge --ask mysqlCreate an initial database:
root #/usr/bin/mysql_install_dbFollow the on-screen instructions for adding a root password within MySQL as otherwise the database will be wide open.
Start the MySQL database server and create the mailsql database:
root #/etc/init.d/mysql start
root #mysqladmin -u root -p create mailsql
root #mysql -u root -p mailsql < genericmailsql.sqlFire up the mysql client to grant the proper rights to the mailsql database:
root #mysql -u root -p mysqlmysql>GRANT SELECT,INSERT,UPDATE,DELETE ON mailsql.* TO mailsql@localhost IDENTIFIED BY '$password';Query OK, 0 rows affected (0.02 sec)
mysql>FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)
mysql>quitVerify that the new mailsql user can connect to the mysql server:
root #mysql -u mailsql -p mailsqlThe new database has default values and tables set up for two domains. The following tables are included:
- alias - local email alias and mailman alias information.
- relocated - relocated user email address maps
- transport - default mail transport information for all domains you are hosting
- users - all user account information
- virtual - virtual domain email alias maps
id alias destination
1 root foo@bar.com
2 postmaster foo@bar.com
## (Line wrapped for clarity.)
id email clear name uid gid homedir \
maildir quota postfix
10 foo@virt-domain.com $password realname virtid virtid /home/vmail \
/home/vmail/virt-domain.com/foo/.maildir/ y
13 foo@bar.com $password realname localid localid /home/foo \
/home/foo/.maildir/ y
The values of the virtid uid and gid should be those of the vmail user and group:
id domain destination
1 bar.com local:
2 virt-domain.com virtual:
id email destination
3 root@virt-domain.com other@email.address
Querying
All these queries are tested on the database to ensure the queries are written properly. This does require however that the database has been filled to be useful. When postfixadmin is used, this can be done easily from the UI. Otherwise, simple INSERT SQL queries need to be written and executed.
During this entire HOWTO, the user testuser with the password secret is used.
PostgreSQL
Linking postfix to a database isn't that special, postfix just executes predefined SQL routines. These SQL queries will be stored in a file per query in a directory:
root #mkdir /etc/postfix/pgsqlBe aware that any errors in the configuration information in these files can be pretty tricky to track down. Be meticulous with both the contents of the files and their names. Many errors in these files will be announced in the mail log such as "user example@example.com doesn't exist" without any further explanation.
In the following files, the variable 'hosts' is commented on purpose. When no hosts are defined, the local unix socket is used.
/etc/postfix/pgsql/virtual_mailbox_domains.cfvirtual_mailbox_domains# virtual_mailbox_domains.cf
user = postfix
password = $password
dbname = postfix
#hosts = localhost
query = SELECT domain FROM domain WHERE domain = '%s' AND backupmx = '0' AND active = '1';
It's best to test the query on the database (using copy paste) to ensure no typo's exist in the query:
root #psql -U postfix postfixpostfix=>SELECT domain FROM domain WHERE domain = 'example.com' AND backupmx = '0' AND active = '1';domain ---------------------- example.com (1 row)
Postfix does not care about the result in this query, it is more of a yes/no answer.
/etc/postfix/pgsql/virtual_mailbox_maps.cfvirtual_mailbox_maps# virtual_mailbox_maps.cf
user = postfix
password = $password
dbname = postfix
#hosts = localhost
query = SELECT maildir FROM mailbox WHERE local_part='%u' AND domain='%d' AND active='1';
Also here, it's best again to execute the query on the database:
root #psql -U postfix postfixpostfix=>SELECT maildir FROM mailbox WHERE local_part='testuser' AND domain='example.com' AND active='1';maildir ------------------- example.com/testuser/ (1 row)
/etc/postfix/pgsql/virtual_alias_maps.cfvirtual_alias_maps# virtual_alias_maps.cf
user = postfix
password = $password
dbname = postfix
#hosts = localhost
query = SELECT goto FROM alias WHERE address='%s' AND active='1';
Run the query on the database to verify its output:
root #psql -U postfix postfixpostfix=>SELECT goto FROM alias WHERE address='testuser@example.com' AND active='1';goto ------------------ testuser@example.com (1 row)
postfixadmin creates an alias for each user. This is not required for postfix to function properly, it is something needed for postfixadmin.
MySQL
Set up the necessary configurations for postfix to interact with the database for all its other transport needs. Remember to replace each value with the name of the proper user, user id, password, alias, email address, and so on.
/etc/postfix/mysql-aliases.cfuser = mailsql
password = $password
dbname = mailsql
table = alias
select_field = destination
where_field = alias
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-relocated.cfuser = mailsql
password = $password
dbname = mailsql
table = relocated
select_field = destination
where_field = email
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-transport.cf(optional)user = mailsql
password = $password
dbname = mailsql
table = transport
select_field = destination
where_field = domain
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-virtual-gid.cf(optional)user = mailsql
password = $password
dbname = mailsql
table = users
select_field = gid
where_field = email
additional_conditions = and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-virtual-maps.cfuser = mailsql
password = $password
dbname = mailsql
table = users
select_field = maildir
where_field = email
additional_conditions = and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-virtual-uid.cf(optional)user = mailsql
password = $password
dbname = mailsql
table = users
select_field = uid
where_field = email
additional_conditions = and postfix = 'y'
hosts = unix:/var/run/mysqld/mysqld.sock
/etc/postfix/mysql-virtual.cfuser = mailsql
password = $password
dbname = mailsql
table = 'virtual'
select_field = destination
where_field = email
hosts = unix:/var/run/mysqld/mysqld.sock
Access rights
Only postfix should have access rights to these files, as they contain passwords:
root #chown root:postfix -R /etc/postfix/
root #find /etc/postfix/ -name "*.cf" -exec chmod -c 640 '{}' \+Connecting postfix to the queries
Postgres
/etc/postfix/main.cfConnect postfix to postgres#
# Settings required to support virtual mail delivery using lookups in
# the Postgres database.
#
# A list of all virtual domains serviced by this instance of postfix.
virtual_mailbox_domains = pgsql:/etc/postfix/pgsql/virtual_mailbox_domains.cf
# Look up the mailbox location based on the email address received.
virtual_mailbox_maps = pgsql:/etc/postfix/pgsql/virtual_mailbox_maps.cf
# Any aliases that are supported by this system
virtual_alias_maps = pgsql:/etc/postfix/pgsql/virtual_alias_maps.cf
MySQL/MariaDB
/etc/postfix/main.cf## (Ensure that there are no other alias_maps definitions)
alias_maps = mysql:/etc/postfix/mysql-aliases.cf
relocated_maps = mysql:/etc/postfix/mysql-relocated.cf
local_transport = local
local_recipient_maps = $alias_maps $virtual_mailbox_maps unix:passwd.byname
virtual_transport = virtual
## (The domains listed by the mydestination should not be listed in
## the virtual_mailbox_domains parameter)
virtual_mailbox_domains = virt-domain.com, $other-virtual-domain.com
virtual_minimum_uid = 1000
## (Substitute $vmail-gid with the GID of the vmail group)
virtual_gid_maps = static:$vmail-gid
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual.cf
## (Substitute $vmail-uid with the UID of the vmail user)
virtual_uid_maps = static:$vmail-uid
As of Postfix 2.0.x, there were a number of significant changes over the 1.1.x release. Notably the transport, virtual-gid, and virtual-uid tables are no longer necessary. The tables are still included to support potential different use cases.
It is recommended that VIRTUAL_README, included with the postfix docs, is read for more information.
Testing the database connection
Postfix needs to be restarted to have it connect to the database:
root #/etc/init.d/postfix restartCheck for any problems in /var/log/mail.log, such as incorrect permissions.
Using telnet, the mailserver can be tested again to check whether the database is properly used:
user $telnet localhost 25Trying 127.0.0.1... Connected to foo.example.com. Escape character is '^]'. 220 foo.example.com ESMTP Postfix mail from:me@you.com 250 2.1.0 Ok rcpt to:testuser@example.com 250 2.1.5 Ok data 354 End data with <CR><LF>.<CR><LF> Testmail to ensure Postfix is still working. . 250 2.0.0 Ok: queued as EA94F164C7 mail from:me@you.com 250 2.1.0 Ok rcpt to:test.user@example.com 250 2.1.5 Ok data 354 End data with <CR><LF>.<CR><LF> Testmail to ensure Postfix is still really working. . 250 2.0.0 Ok: queued as 3A276164C7 quit 221 2.0.0 Bye Connection closed by foreign host.
/var/log/mail.logVerify test messagesMar 16 19:25:15 foo postfix/smtpd[32321]: connect from unknown[127.0.0.1]
Mar 16 19:25:32 foo postfix/smtpd[32321]: EA94F164C7: client=unknown[127.0.0.1]
Mar 16 19:25:42 foo postfix/cleanup[32330]: EA94F164C7: message-id=<>
Mar 16 19:25:42 foo postfix/qmgr[31681]: EA94F164C7: from=<me@you.com>, size=215, nrcpt=1 (queue active)
Mar 16 19:25:42 foo postfix/virtual[32332]: EA94F164C7: to=<testuser@example.com>, relay=virtual, delay=22, delays=22/0.02/0/0.05, dsn=2.0.0, status=sent (delivered to maildir)
Mar 16 19:25:42 foo postfix/qmgr[31681]: EA94F164C7: removed
Mar 16 19:26:05 foo postfix/smtpd[32321]: 3A276164C7: client=unknown[127.0.0.1]
Mar 16 19:26:14 foo postfix/cleanup[32330]: 3A276164C7: message-id=<>
Mar 16 19:26:14 foo postfix/qmgr[31681]: 3A276164C7: from=<me@you.com>, size=199, nrcpt=1 (queue active)
Mar 16 19:26:15 foo postfix/smtp[32338]: 3A276164C7: to=<test.user@example.com>, orig_to=<test.user@example.com>, relay=virtual, delay=23, delays=22/0.02/0.57/0.39, dsn=2.0.0, status=sent (delivered to maildir)
Mar 16 19:26:15 foo postfix/qmgr[31681]: 3A276164C7: removed
If the
vda USE flag is set, it may happen that test messages get (Soft) bounced due to too overdrawn diskspace quota. There are then two options. Re-merge without the vda USE flag and re-merge with the vda USE flag enabled once doing quota's. Or jump ahead to the quota support bit, follow those steps, then return here.