Mail Alias Stores in mySQL Not working

[Bill Shirley]

In an earlier post you said your user_query was:

user_query = SELECT CONCAT('/var/vmail/mail/', maildir) AS home, 5000 AS uid, 5000 AS gid, CONCAT('*:bytes=', quota) AS 
quota_rule FROM mailbox WHERE username = '%u' AND active=‘1'

Last post you said:

mail is stored at /var/mail/vhosts

and:

mail_location = maildir:/var/mail/vhosts/%d/%n

These don't agree with your user_query.

Run a MySQL query:
SELECT `username`, `domain`, `maildir` FROM `mailbox` LIMIT 5;
Let's see the value of maildir

Also, lets see the output of:
ls -l /var/mail/vhosts/*

Bill

On 10/11/2017 8:31 AM, jason hirsh wrote:
> "With that query, it appears everyone is sharing the same mail directory.? It, of course, depends
> on the value of `maildir`.? It doesn't look right.”
>
> stored by domain or at least it was until i started messing with mysql
>
> "Have you checked your MySQL log file to see the actual query?”
>
> hmm  found out i had never enabled that log…. another tangent I need to follow
>
> "We don't know your mailbox format.? Post your 'doveconf -n’.”
>
> My bad
>
> # OS: FreeBSD 11.0-RELEASE-p9 amd64  ufs
> auth_debug = yes
> auth_debug_passwords = yes
> auth_mechanisms = plain login
> auth_verbose = yes
> auth_verbose_passwords = yes
> base_dir = /var/run/dovecot/
> disable_plaintext_auth = no
> mail_location = maildir:/var/mail/vhosts/%d/%n
> mail_privileged_group = vmail
> namespace inbox {
>    inbox = yes
>    location =
>    mailbox Drafts {
>      special_use = \Drafts
>    }
>    mailbox Junk {
>      special_use = \Junk
>    }
>    mailbox Sent {
>      special_use = \Sent
>    }
>    mailbox "Sent Messages" {
>      special_use = \Sent
>    }
>    mailbox Trash {
>      special_use = \Trash
>    }
>    prefix =
> }
> passdb {
>    args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
>    driver = sql
> }
> pop3_uidl_format = %v.%u
> postmaster_address = postmaster at theoceanwindow.com
> service auth-worker {
>    user = vmail
> }
> service auth {
>    unix_listener /var/spool/postfix/private/auth {
>      group = postfix
>      mode = 0660
>      user = postfix
>    }
>    unix_listener auth-userdb {
>      mode = 0600
>      user = vmail
>    }
>    user = dovecot
> }
> service director {
>    unix_listener director-userdb {
>      mode = 0600
>    }
> }
> service imap-login {
>    inet_listener imaps {
>      port = 993
>      ssl = yes
>    }
> }
> service lmtp {
>    unix_listener /var/spool/postfix/private/dovecot-lmtp {
>      group = postfix
>      mode = 0600
>      user = postfix
>    }
> }
> service pop3-login {
>    inet_listener pop3s {
>      port = 995
>      ssl = yes
>    }
> }
> ssl_ca = </usr/local/etc/keys/certs/ca.crt
> ssl_cert = </usr/local/etc/keys/certs/domain.crt
> ssl_key =  # hidden, use -P to show it
> userdb {
>    args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
>    driver = sql
> }
> protocol imap {
>    mail_max_userip_connections = 30
> }
>
>
>
> We don't know what your table layout looks like.? Post your 'SHOW COLUMNS FROM `mailbox`’.
>
>
> +------------+--------------+------+-----+---------------------+-------+
> | Field      | Type         | Null | Key | Default             | Extra |
> +------------+--------------+------+-----+---------------------+-------+
> | username   | varchar(255) | NO   | PRI | NULL                |       |
> | password   | varchar(255) | NO   |     | NULL                |       |
> | name       | varchar(255) | NO   |     | NULL                |       |
> | maildir    | varchar(255) | NO   |     | NULL                |       |
> | quota      | bigint(20)   | NO   |     | 0                   |       |
> | local_part | varchar(255) | NO   |     | NULL                |       |
> | domain     | varchar(255) | NO   | MUL | NULL                |       |
> | created    | datetime     | NO   |     | 2000-01-01 00:00:00 |       |
> | modified   | datetime     | NO   |     | 2000-01-01 00:00:00 |       |
> | active     | tinyint(1)   | NO   |     | 1                   |       |
> +------------+--------------+------+-----+---------------------+-------+
>
> What is the on-disk directory layout for mail?
>
> mail is stored at  /var/mail/vhosts