My dovecot-sql.conf file is default:
users WHERE userid = '%u'
driver = mysql
connect = host=localhost dbname=dbispconfig user=ispconfig
password=06549e2a867ee50a107098f424073acd port=3306
default_pass_scheme = CRYPT
password_query = SELECT email as user, password, maildir as userdb_home,
CONCAT( maildir_format, ':', maildir, '/',
IF(maildir_format='maildir','Maildir',maildir_format)) as userdb_mail, uid
as userdb_uid, gid as userdb_gid, CONCAT('*:storage=', quota, 'B') AS
userdb_quota_rule, CONCAT(maildir, '/.sieve') as userdb_sieve FROM
mail_user WHERE (login = '%u' OR email = '%u') AND `disable%Ls` = 'n' AND
server_id = '1'
user_query = SELECT email as user, maildir as home, CONCAT( maildir_format,
':', maildir, '/', IF(maildir_format='maildir','Maildir',maildir_format))
as mail, uid, gid, CONCAT('*:storage=', quota, 'B') AS quota_rule,
CONCAT(maildir, '/.sieve') as sieve FROM mail_user WHERE (login = '%u' OR
email = '%u') AND `disable%Ls` = 'n' AND server_id = '1'
iterate_query = SELECT email as user FROM mail_user WHERE server_id = '1'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
How did You create this nice mysql table in console?
Btw - in dovecot.conf - is this line properly configured:
ssl_protocols = !SSLv2 !SSLv3 !TLSv1 !TLSv1.1
2017-02-24 4:07 GMT+01:00 Bill Shirley <b...@knoxvillechristian.org>:
> Sounds like you have a problem in /etc/dovecot/dovecot-sql.conf
>
> Did you ever post it? For virtual users, mine is:
> driver = mysql
>
> # Currently supported schemes include PLAIN, PLAIN-MD5, DIGEST-MD5, and
> CRYPT.
> default_pass_scheme = PLAIN
>
> # Database options
> connect = host=/var/lib/mysql/mysql.sock dbname=sys_mail user=sys_mail
> password=hunter2
>
> 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 table:
> MySQL (root@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
> | |
> | quota | tinytext | NO | | NULL
> | |
> | imap | enum('Y','N') | NO | | N
> | |
> | virus_check | enum('Y','N') | NO | | Y
> | |
> | spam_check | enum('Y','N') | NO | | Y
> | |
> +--------------------+------------------+------+-----+------
> ---+----------------+
>
>
> Dovecot can be made to log the MySQL transaction. I did that a long long
> time ago but don't remember how.
>
> Virtual user db:
> # ------------------------------------------------------------
> ------------------
> # ----------------------------------- MySQL ------------------------------
> ------
> # ------------------------------------------------------------
> ------------------
> first_valid_uid = 399
> passdb {
> driver = sql
> args = /etc/dovecot/dovecot-mysql.conf
> }
>
> userdb {
> driver = sql
> args = /etc/dovecot/dovecot-mysql.conf
> }
>
> Hope this helps,
> Bill
>
>
--
*Pozdrawiam / Best Regards*
*Piotr Bracha*
*tel. 534 555 877*
*ser...@poliman.pl <ser...@poliman.pl>*
