Extending Virtual Users And Domains With Postfix, Courier, And SquirrelMail Ubuntu 8.

Discussion in 'HOWTO-Related Questions' started by atjensen11, Jun 7, 2008.

  1. atjensen11

    atjensen11 New Member

    Alright, so I now have a functioning mail server after following this tutorial. I would like to extend the functionality a little bit.

    Primarily, I have added an 'enabled' field to the 'user' table in the database. It is assigned a tiny integer which I store either a 0 for disabled or 1 for enabled.

    I would like Postfix to use this additional field in the query. I would eventually like to do the same thing with entire domains, but my priority right now is at the user level.

    I thought the appropriate file that needed revision to extend the functionality was /etc/postfix/mysql-virtual_mailboxes.cf. I tried both of the following and performed a restart of Postfix. I then tested my login through Squirrelmail after setting the value of the 'enabled' field to 0 on a test account. I could still login which isn't what I was expecting.

    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s' AND enabled=1
    hosts = 127.0.0.1
    
    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
    hosts = 127.0.0.1
    additional_conditions = and enabled=1
    
    Any help would be appreciated.

    Thanks.
     
  2. till

    till Super Moderator

    Squirrelmail authenticates trough imap and not smtp. You will have to add "AND enabled=1" to the querys of the courier imap server too.
     
  3. atjensen11

    atjensen11 New Member

    Thanks Till for that tip.

    I added the following line to the file /etc/courier/authmysqlrc:
    Code:
    MYSQL_WHERE_CLAUSE enabled=1
    Squirrelmail login will now correctly fail if the email account has a zero in the enabled field.

    For SMTP then, which of the six files in the tutorial need to have the WHERE clause added? My initial thought was /etc/postfix/mysql-virtual_mailboxes.cf. Are there others?

    Additionally, do I need to change anything with:
    Code:
    sql_select: select password from users where email = '%u'
    in the file vi /etc/postfix/sasl/smtpd.conf.

    Thanks.
     
  4. till

    till Super Moderator

    Yes, if you want to disable smtp-auth too.
     
  5. atjensen11

    atjensen11 New Member

    Alright, so I added the "and enabled=1" to the sql_select statement. I tested sending email from a remote client. With a zero in the enabled field, the user cannot send email. With a one in the enabled field, email is delivered just fine.

    As I see it, that is two out of three as I don't have Postfix actually configured to use the enabled field yet.

    So I tried adding just the additional line at the bottom of mysql-virtual_mailboxes.cf:
    Code:
    additional_conditions = and enabled = 1
    I sent an email to the account while it was disabled and Postfix still delivered it.

    When I appended "and enabled = 1" to the query line instead and sent an email, I received an undeliverable message back saying the user did not exist. So that worked.

    But why didn't the first way work? I have seen that syntax in many other tutorials.

    Thanks.
     
  6. till

    till Super Moderator

    I guess you can use the additional_conditions setting only together with the select_field and where_field but not with query, as postfix will build the query from the 3 fromer fields, if query is not specified.
     

Share This Page