HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   HOWTO-Related Questions (http://www.howtoforge.com/forums/forumdisplay.php?f=2)
-   -   Extending Virtual Users And Domains With Postfix, Courier, And SquirrelMail Ubuntu 8. (http://www.howtoforge.com/forums/showthread.php?t=23977)

atjensen11 7th June 2008 20:35

Extending Virtual Users And Domains With Postfix, Courier, And SquirrelMail Ubuntu 8.
 
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.

till 7th June 2008 23:12

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 7th June 2008 23:34

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.

till 7th June 2008 23:52

Quote:

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 8th June 2008 00:48

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.

till 8th June 2008 13:09

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.


All times are GMT +2. The time now is 07:58.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.