Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail

Discussion in 'HOWTO-Related Questions' started by gu35t, Feb 2, 2011.

  1. gu35t

    gu35t New Member

    hi

    i m setting up postfix with virtual users & domains using falko`s HowTo and other manuals.

    i probably made a syntax error or i misunderstood something.
    os:
    freebsd

    main error is(/var/log/maillog):
    Code:
    Feb  2 00:54:23 tonic postfix/smtpd[8336]: disconnect from [...][xxx.xxx.xxx.xxx]
    Feb  2 00:55:03 tonic postfix/smtpd[8336]: connect from [...][xxx.xxx.xxx.xxx]
    Feb  2 00:55:03 tonic postfix/smtpd[8336]: warning: SASL authentication failure: Password verification failed
    Feb  2 00:55:03 tonic postfix/smtpd[8336]: warning: [...][xxx.xxx.xxx.xxx]: SASL PLAIN authentication failed: authentication failure
    Feb  2 00:55:03 tonic postfix/smtpd[8336]: warning: [...][xxx.xxx.xxx.xxx]: SASL LOGIN authentication failed: authentication failure
    Feb  2 00:55:03 tonic postfix/smtpd[8336]: NOQUEUE: reject: RCPT from [...][xxx.xxx.xxx.xxx]: 554 5.7.1 <[email protected]>: Relay access denied; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<hostname>
    /var/log/debug.log
    Code:
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: commit transaction
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin Parse the username [email protected]
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin try and connect to a host
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin trying to open db 'mail' on host 'localhost'
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin Parse the username [email protected]
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin try and connect to a host
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin trying to open db 'mail' on host 'localhost'
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: begin transaction
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin create statement from userPassword dawid mydomain.pl
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin doing query select password from users where email='dawid';
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin create statement from cmusaslsecretPLAIN dawid mydomain.pl
    Feb  2 00:56:20 tonic postfix/smtpd[8336]: sql plugin doing query select password from users where email='dawid';
    
    mysql-general log:
    Code:
    110202  0:56:20    65 Connect   mail_admin@localhost on mail
                       65 Quit
                       66 Connect   mail_admin@localhost on mail
                       66 Query     START TRANSACTION
                       66 Query     select password from users where email='dawid'
                       66 Query     select password from users where email='dawid'
                       66 Query     COMMIT
                       66 Quit
                       67 Connect   mail_admin@localhost on mail
                       67 Quit
                       68 Connect   mail_admin@localhost on mail
                       68 Query     START TRANSACTION
                       68 Query     select password from users where email='dawid'
                       68 Query     select password from users where email='dawid'
                       68 Query     COMMIT
                       68 Quit
                       69 Connect   mail_admin@localhost on mail
                       69 Quit
                       70 Connect   mail_admin@localhost on mail
                       70 Query     START TRANSACTION
                       70 Query     select password from users where email='dawid'
                       70 Query     select password from users where email='dawid'
                       70 Query     COMMIT
                       70 Quit
                       71 Connect   mail_admin@localhost on mail
                       71 Quit
    
    config files:
    sasl:
    Code:
    cat /usr/local/lib/sasl2/smtpd.conf
    pwcheck_method: saslauthd
    mech_list: plain login
    auxprop_plugin: sql
    sql_engine: mysql
    sql_user: mail_admin
    sql_passwd: pass
    sql_hostnames: localhost
    sql_database: mail
    sql_select: select password from users where email='%u'
    sql_verbose: yes
    log_level: 7
    
    pam.d/smtp
    Code:
    cat /usr/local/etc/pam.d/smtp
    auth required pam_mysql.so user=mail_admin passwd=pass host=/tmp/mysql.sock db=mail table=users usercolumn=email passwdcolumn=password crypt=1
    account sufficient pam_mysql.so user=mail_admin passwd=pass host=/tmp/mysql.sock db=mail table=users usercolumn=email passwdcolumn=password crypt=1
    postfix-mysql users table:
    Code:
    mysql> select * from users;
    +-----------------------+--------------------------+----------+
    | email                         | ENCRYPT(password)         | quota      |
    +-----------------------+--------------------------+----------+
    | [email protected]     | xxxxxxxx                         | 10485760 |
    | [email protected]        | xxxxxxxx                         | 10485760 |
    +-----------------------+--------------------------+----------+
    2 rows in set (0.00 sec)
    
    as you can see pam_mysql send only first part of email('dawid') in sql query but should send full username(mysql email cell -> '[email protected]')

    what couses this error? how to fix it ?

    kind regards
    gu35t
     
  2. falko

    falko Super Moderator ISPConfig Developer

    Can you post the mysql*.cf files from the /etc/postfix/ directory?
     
  3. gu35t

    gu35t New Member

    cat mysql-virtual_mailboxes.cf
    Code:
     
    user = mail_admin
    password = pass
    dbname = mail
    #query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
    table = users
    select_field = CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
    where_field = email
    hosts = localhost
    
    cat mysql-virtual_mailbox_limit_maps.cf
    Code:
    user = mail_admin
    password = pass
    dbname = mail
    query = SELECT quota FROM users WHERE email='%s'
    hosts = localhost
    
    cat mysql-virtual_domains.cf
    Code:
    user = mail_admin
    password = pass
    dbname = mail
    query = SELECT domain AS virtual FROM domains WHERE domain='%s'
    hosts = localhost
    cat mysql-virtual_email2email.cf
    Code:
    user = mail_admin
    password = pass
    dbname = mail
    query = SELECT email FROM users WHERE email='%s'
    hosts = localhost
    cat mysql-virtual_forwardings.cf
    Code:
     
    user = mail_admin
    password = pass
    dbname = mail
    query = SELECT destination FROM forwardings WHERE source='%s'
    hosts = localhost
    cat mysql-virtual_transports.cf
    Code:
    user = mail_admin
    password = pass
    dbname = mail
    query = SELECT transport FROM transport WHERE domain='%s'
    hosts = localhost
     
  4. falko

    falko Super Moderator ISPConfig Developer

    I guess mysql-virtual_mailboxes.cf is the crucial file. Which tutorial exactly (URL) did you use?
     
  5. gu35t

    gu35t New Member

    mainly i was using this HowTo: http://www.howtoforge.com/virtual-users-domains-postfix-courier-mysql-squirrelmail-ubuntu9.04-p2

    /var/log/auth.log:
    Code:
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - option debug is set to ""
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_close_db() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_sm_authenticate() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_open_db() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_open_db() returning 0.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_check_passwd() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_format_string() called
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_quick_escape() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - SELECT password FROM users WHERE email = 'dawid'
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - SELECT returned no result.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_check_passwd() returning 1.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_sql_log() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_sql_log() returning 0.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_converse() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_open_db() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_check_passwd() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_format_string() called
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_quick_escape() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - SELECT password FROM users WHERE email = 'dawid'
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - SELECT returned no result.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_check_passwd() returning 1.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_sql_log() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_sql_log() returning 0.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_sm_authenticate() returning 13.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_release_ctx() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_destroy_ctx() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: pam_mysql - pam_mysql_close_db() called.
    Feb  3 17:36:37 tonic saslauthd[16734]: do_auth         : auth failure: [user=dawid] [service=smtp] [realm=mydomain.pl] [mech=pam] [reason=PAM auth error]
    do_auth function send user=dawid but as i said should send 'user@realm'. still do not know why this is happening ;-/
     
  6. gu35t

    gu35t New Member

    ok i got it :)

    In FreeBSD you have to add saslauthd_flags in /etc/rc.conf:
    cat /etc/rc.conf |grep saslauthd
    Code:
    saslauthd_enable="YES"
    saslauthd_flags="-a pam -r"
    
    '-r' is the winner :) :
    man saslauthd:
    Code:
     
    [...]
    -r             Combine the realm with the login before passing to authentication mechanism
                     Ex. login: "foo" realm: "bar" will get passed as login: "foo@bar"
                     The realm name is passed untouched.
    [...]
    
    By default saslauthd rc script (/usr/local/etc/rc.d/saslauthd) run saslauthd with '-a pam' argument. There is no saslauthd main config file. Only auth via LDAP use config file:
    man saslauthd:
    Code:
    [....]
    FILES
         /var/run/saslauthd/mux  The default communications socket.
    
         /usr/local/etc/saslauthd.conf
                                 The default configuration file for ldap support.
    [....]
    
    it was just too simple :)

    regards
    David
     

Share This Page