3

I have setup a mail server with Postfix, Dovecot and MySQL. Dovecot works perfectly. Postfix sends mail, but I have a problem with receiving mail.

After 2 days of debugging and tweaking configurations, I'm now able to receive mails successfuly. The only problem is Postfix saves the mails in the wrong place. I want mails to be saved in /var/mail/vhosts/domain.tld/user/ directory for user@domain.tld but mails get saved in /var/mail/vhosts/user@domain.tld file.

/ v a r / m a i l / v h o s t s / u s e r @ d o m a i n . t l d
-------------------------------   -----------------------------
^    virtual_mailbox_base     ^   ^     sql result from       ^
                                  |   virtual_mailbox_maps    |

--

Postfix Configuration

# (postconf -d; postconf -d; postconf -n;) | sort | uniq -u
alias_maps = hash:/etc/aliases
append_dot_mydomain = no
biff = no
broken_sasl_auth_clients = yes
local_recipient_maps = $virtual_mailbox_maps
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
mydestination = localhost.localdomain, localhost
myhostname = {censored}
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
myorigin = /etc/mailname
process_id = 6297
process_id = 6298
readme_directory = no
recipient_delimiter = +
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
smtpd_recipient_restrictions = permit_sasl_authenticated,permit_mynetworks,reject_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_path = private/auth
smtpd_sasl_tls_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_tls_CAfile = /etc/ssl/certs/cacert.pem
smtpd_tls_cert_file = /etc/ssl/certs/mail.roofworkshop.com.crt
smtpd_tls_key_file = /etc/ssl/private/mail.roofworkshop.com.key
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_security_level = may
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_tls_note_starttls_offer = yes
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf
virtual_gid_maps = static:5000
virtual_mailbox_base = /var/mail/vhosts
virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_uid_maps = static:5000

mysql-virtual-mailbox-domains.cf

user = mailuser
password = {censored}
hosts = 127.0.0.1
dbname = mailserver
query = SELECT name FROM virtual_domains WHERE name='%s'

mysql-virtual-mailbox-maps.cf

user = mailuser
password = {censored}
hosts = 127.0.0.1
dbname = mailserver
query = SELECT email FROM virtual_users WHERE email='%s'
Hkan
  • 173
  • 6

2 Answers2

5

Another method is using mysql expansions feature in postfix. See man 5 mysql_table.

Here the alternative version of your query

query = SELECT '%d/%u/' FROM virtual_users WHERE email='%s';

When input was user@example.com, string %d/%u/ will expand to example.com/user/. This part WHERE email='%s' will limit this query to valid username only.

See also this blog post to get the example of this expansions.

The relevant excerpt from documentation page

query The SQL query template used to search the database, where %s is a substitute for the address Postfix is trying to resolve, e.g. query = SELECT replacement FROM aliases WHERE mailbox = '%s'

This parameter supports the following '%' expansions:

%% This is replaced by a literal '%' character.

%s This is replaced by the input key. SQL quoting is used to make sure that the input key does not add unexpected metacharacters.

%u When the input key is an address of the form user@domain, %u is replaced by the SQL quoted local part of the address. Otherwise, %u is replaced by the entire search string. If the localpart is empty, the query is suppressed and returns no results.

%d When the input key is an address of the form user@domain, %d is replaced by the SQL quoted domain part of the address. Otherwise, the query is suppressed and returns no results.

Community
  • 1
masegaloeh
  • 17,978
  • 9
  • 56
  • 104
  • I just tried this and, yes, it works. And since it looks much cleaner than my solution, I will stick with this now. Thanks! – Hkan Sep 14 '15 at 14:55
4

I did a little trick to solve the problem. I changed mysql-virtual-mailbox-maps.cf file as following:

user = mailuser
password = {censored}
hosts = 127.0.0.1
dbname = mailserver
query = SELECT CONCAT(SUBSTRING(email, LOCATE('@', email) + 1), '/', SUBSTRING_INDEX(email, '@', 1), '/') AS `domain` FROM virtual_users WHERE email='%s'

I still would like to know if there are any better methods than this cheap workaround.

Hkan
  • 173
  • 6