After upgrade dovecot unkown user

Discussion in 'ISPConfig 3 Priority Support' started by 30uke, Apr 15, 2021.

  1. 30uke

    30uke Member HowtoForge Supporter

    Hello,
    After upgrading ISPConfig, some user are unable to receive mail.
    I did test a few accounts (as I know the credentials) and I did notice the following:
    Code:
    Apr 15 15:38:10 s1 dovecot: auth-worker(11117): sql([email protected],::1,<RMNk+QLAKogAAAAAAAAAAAAAAAAAAAAB>): unknown user
    I am not sure how to fix this. It looks like there's something wrong with retrieving the credentials from the MySQL database?
     
  2. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Does that user exist on the server, or is it a alias address?
     
  3. 30uke

    30uke Member HowtoForge Supporter

    Hi Th0m,
    These are real existing users. The previous one was obfuscated.
    Another example (with real user) is this one:
    Code:
    Apr 15 15:49:47 s1 dovecot: auth-worker(11966): sql([email protected],::1,<KhqzIgPApIsAAAAAAAAAAAAAAAAAAAAB>): unknown user
    Apr 15 15:49:49 s1 dovecot: imap-login: Disconnected (auth failed, 1 attempts in 6 secs): user=<[email protected]>, method=PLAIN, rip=::1, lip=::1, secured, session=<KhqzIgPApIsAAAAAAAAAAAAAAAAAAAAB>
     
    Last edited: Apr 15, 2021
  4. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Could you check in the database that this user exists?
    Can you share the content of /etc/dovecot/dovecot-sql.conf (remove the password)?
     
  5. 30uke

    30uke Member HowtoForge Supporter

    Code:
    # http://wiki.dovecot.org/AuthDatabase/SQL
    #
    # CREATE TABLE users (
    #     userid VARCHAR(128) NOT NULL,
    #     password VARCHAR(64) NOT NULL,
    #     home VARCHAR(255) NOT NULL,
    #     uid INTEGER NOT NULL,
    #     gid INTEGER NOT NULL,
    #     active CHAR(1) DEFAULT 'Y' NOT NULL
    # );
    
    driver = mysql
    connect = host=localhost dbname=dbispconfig user=ispconfig password=snip-snip-snip port=3306
    default_pass_scheme = CRYPT
    
    # password-query with prefetch
    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' AND NOT EXISTS (SELECT domain_id FROM mail_domain WHERE domain = '%d' AND active = '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'
    
    # The iterate_query is required for the doveadm command only and works only on dovecot 2 servers.
    # Do not enable it on Dovecot 1.x servers
    iterate_query = SELECT email as user FROM mail_user WHERE server_id = '1'
     
  6. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Alright. This looks good to me. Please verify the user exists in the database and is activated.
    Do you use custom login names or the email address?
     
  7. 30uke

    30uke Member HowtoForge Supporter

    I'll check the database later (I think this evening).
     
  8. 30uke

    30uke Member HowtoForge Supporter

    I was able to quickly verify this via phpmyadmin on table "mail_user" of database "dbispconfig". The user exists in the database.
    The e-mail addresses have some aliasses but I do not use custom login names. It's straight forward "email" == "login". I did verify this by comparing both (columns) in the database.
     
  9. Jesse Norell

    Jesse Norell ISPConfig Developer Staff Member ISPConfig Developer

    can you post the corresponding mail_user and mail_domain entries?
     
  10. 30uke

    30uke Member HowtoForge Supporter

    This has been resolved...
    ...and I feel a bit uncomfortable to tell you guys how I did resolve this issue...
    Although this feels a bit stupid: I still don't know how the domain became inactivated. Ah well... it's resolved now. Thanks.

    Code:
    E-mail > Domain > [domain.ext]: Active
    
    [​IMG]
     
    Kaarl and Th0m like this.
  11. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Glad to hear.
     
    Kaarl likes this.

Share This Page