Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > HOWTO-Related Questions

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 7th June 2008, 20:35
atjensen11 atjensen11 is offline
Senior Member
 
Join Date: Dec 2007
Posts: 199
Thanks: 9
Thanked 6 Times in 6 Posts
Default 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.
Reply With Quote
Sponsored Links
  #2  
Old 7th June 2008, 23:12
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,788
Thanks: 840
Thanked 5,612 Times in 4,423 Posts
Default

Squirrelmail authenticates trough imap and not smtp. You will have to add "AND enabled=1" to the querys of the courier imap server too.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #3  
Old 7th June 2008, 23:34
atjensen11 atjensen11 is offline
Senior Member
 
Join Date: Dec 2007
Posts: 199
Thanks: 9
Thanked 6 Times in 6 Posts
Default

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.
Reply With Quote
  #4  
Old 7th June 2008, 23:52
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,788
Thanks: 840
Thanked 5,612 Times in 4,423 Posts
Default

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.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #5  
Old 8th June 2008, 00:48
atjensen11 atjensen11 is offline
Senior Member
 
Join Date: Dec 2007
Posts: 199
Thanks: 9
Thanked 6 Times in 6 Posts
Default

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.
Reply With Quote
  #6  
Old 8th June 2008, 13:09
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 36,788
Thanks: 840
Thanked 5,612 Times in 4,423 Posts
 
Default

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.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail -Ubuntu 8.04 c4rdinal HOWTO-Related Questions 112 23rd August 2011 11:49
Virtual Users And Domains With Postfix, Courier, And SquirrelMail Ubuntu 8.04 flipb18b HOWTO-Related Questions 16 10th June 2008 05:27
Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail -Ubuntu 8.04 tonkyman HOWTO-Related Questions 4 9th June 2008 20:09
Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail Ubuntu 8.04 paulodani HOWTO-Related Questions 7 4th June 2008 01:46
Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 8.04 mtzcesar HOWTO-Related Questions 3 1st June 2008 11:57


All times are GMT +2. The time now is 22:27.


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