Complete Virtual Mail Server/Postfix to Database
Contents |
Installing Postfix
Postfix installation is quite straight forward. A basic installation instruction can be found here on the wiki for Postfix.
Linking Postfix
Once postfix is working on its most basic level its time to link it to the database.
Gaining access
PostgreSQL
When using postfix on the same host, the local unix socket connection is recommended. For this postfix needs rights on the socket.
root # gpasswd -a postfix postgresAlso a database-user to query the database is required.
root # createuser -U postgres -D -P -R -S postfix
Enter password for new role: $password
Enter it again: $password
Also the postfix user needs some permissions on the tables.
root # psql -U postfixadmin postfix
postfix=>
GRANT SELECT ON alias TO postfix;
GRANT SELECT ON domain TO postfix;
GRANT SELECT ON mailbox TO postfix;
Queering
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.
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 much further explanations.
# virtual_mailbox_domains.cf user = postfix password = $password dbname = postfix #hosts = localhost query = SELECT description 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 postfix
postfix=> SELECT description FROM domain WHERE domain = 'example.com' AND backupmx = '0' AND active = '1';
description
----------------------
An example domain.
(1 row)
# 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 postfix
postfix=> SELECT maildir FROM mailbox WHERE local_part='testuser' AND domain='example.com' AND active='1';
maildir
-------------------
example.com/testuser/
(1 row)
# virtual_alias_maps.cf user = postfix password = $password dbname = postfix #hosts = localhost query = SELECT goto FROM alias WHERE address='%s' AND active='1';
Run there query on the database to verify it's output.
root # psql -U postfix postfix
postfix=> SELECT goto FROM alias WHERE address='testuser@example.com' AND active='1';
goto
------------------
testuser@example.com
(1 row)
Access rights
Only postfix should have access rights to these files, as they contain passwords.
root # chown root:postfix -R /etc/postfix/
root # chmod 640 /etc/postfix/*/virtual_*.cfConnecting postfix to the queries
Postgres
# # Settings required to support virtual mail delivery using lookups in # the Postgres database. # # Set the base address for all virtual mailboxes virtual_mailbox_base = /var/vmail # 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
Testing the database connection
Postfix needs to be restarted to make 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 25
Trying 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.
Mar 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
