Mail Alias Stores in mySQL Not working

[Bill Shirley]

I see two problems:
1) you have two user_querys, there should only be one
2) your 2nd user_query is not limiting the SQL search with a WHERE clause

Here is my setup:
MySQL (root at localhost) [sys_mail]> SHOW COLUMNS FROM `Virtual_User`;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| vu_id              | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| Name               | varchar(50)      | NO   |     | NULL    |                |
| Domain             | varchar(128)     | NO   | MUL | NULL    |                |
| User               | varchar(32)      | NO   | MUL | NULL    |                |
| Password_Cleartext | varchar(128)     | NO   |     | NULL    |                |
| Password_Crypt     | varchar(128)     | YES  |     | NULL    |                |
| Retrieve_Mail      | enum('No','Yes') | NO   |     | Yes     |                |
| Receive_Mail       | enum('No','Yes') | NO   |     | Yes     |                |
| Accept_SMTP        | enum('No','Yes') | NO   |     | Yes     |                |
| uid                | int(11) unsigned | NO   |     | 399     |                |
| gid                | int(11) unsigned | NO   |     | 399     |                |
| homedir            | varchar(75)      | NO   |     | NULL    |                |
| Maildir            | varchar(32)      | NO   |     | Maildir |                |
+--------------------+------------------+------+-----+---------+----------------+
Note: Not all fields are used.

and the .conf:
password_query = SELECT LOWER(CONCAT_WS('@',`User`,`Domain`)) AS `user` \
         , IF(`Password_Crypt` IS NOT NULL, CONCAT('{crypt}',`Password_Crypt`),`Password_Cleartext`) AS `password` \
         FROM `Virtual_User` \
         WHERE LOWER(`User`) = LOWER('%n') \
         AND LOWER(`Domain`) = LOWER('%d') \
         AND `Retrieve_Mail` = 'Yes'

# vmail = 399
user_query = SELECT CONCAT_WS('/','/home/vmail/domains',LOWER(`Domain`),LOWER(`User`)) AS `home` \
         , 'maildir:~/Maildir' AS `mail` \
         , 399 AS `uid` \
         , 399 AS `gid` \
         FROM `Virtual_User` \
         WHERE LOWER(`User`) = LOWER('%n') \
         AND LOWER(`Domain`) = LOWER('%d') \
         AND `Retrieve_Mail` = 'Yes'
The directory structure is:
/home/vmail/domains/example.com/bob/Maildir

HTH,
Bill


On 10/9/2017 10:34 PM, jason hirsh wrote:
> This apparently was a problem that i was not aware since i redid my mail server configuration to make use of Postfixadmin. I have update the conf files using the postfix document fromthe down load. The download document seems to be fpr an early version of dovecot (I am using 2.2.4) as it focuse of dovecot.conf which is now broken into file in the conf.d subdirectory I get "dovecot: auth-worker(30555): sql(abuse at examplei.com): unknown user "
> I imagine this is a conf problem and deals with reading the sql daya I have confirmed the dayta is there
> The major file apperas to be dovecot-sql.conf.ext
> Mine reads as follows
> driver = mysql
> connect = host=127.0.0.1 dbname=postfix user=postfix password=postfixadmin
> password_query = SELECT username, password FROM mailbox WHERE username='%u' and active ="
> default_pass_scheme = MD5-CRYPT
> user_query = SELECT maildir, 5000 AS uid, 5000 AS gid FROM mailbox WHERE username = '%u'
> user_query = SELECT CONCAT('/var/vmail/mail/', maildir) AS home, 5000 AS uid, 5000 AS gid
> Since normal mail is habdled by dovecoy It would see somthing is wrong wihh the first user_query since it appears to be the one that should fine the alias user
> Any hellp or suggestions would be appreciate