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

[atjensen11]

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.

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

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.

[till]

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

[atjensen11]

Thanks Till for that tip.

I added the following line to the file /etc/courier/authmysqlrc:

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:

sql_select: select password from users where email = '%u'

in the file vi /etc/postfix/sasl/smtpd.conf.

Thanks.

[till]

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.

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

[atjensen11]

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:

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.

[till]

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.