How to specify target directory in Postfix with MySQL?

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'

Answer

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.