Offering webmail access

This step is optional but most email service providers offer their users a way to read emails in a browser. Luckily this is easy. The package you need is called "squirrelmail" - a web mail system that can use any IMAP server - just like the Dovecot IMAP server we use here. To set it up you first need to add its configuration to your Apache configuration:

$> ln -s /etc/squirrelmail/apache.conf /etc/apache2/conf.d/squirrelmail.conf
$> apache2ctl restart

You need to change one configuration option at least so please run the configuration utility:

$> squirrelmail-configure

Select option 3 (Folder Defaults) and set option 1 (Default Folder Prefix) to 'none'. You may also want to browse through the other menu options to e.g. set your organisation's name right.

Point your browser at http://YOUR-MAIL-SERVER/squirrelmail and you should see the webmail interface. Login as 'john@example.com' with password 'summersun' and you should be able to read John's email. It couldn't be simpler.

In case you are unhappy with the http://my.server/squirrelmail URL and prefer to use http://my.server instead you should read the users will prefer a simple URL section in the /etc/squirrelmail/apache.conf file.

27 Comments

Adding the Change_sqlpass plugin to squirrelmail

I hope this will be of help to others!

I wanted to add the capability of my users changing passwords on their accounts. Squirrelmail seemed to offer the best option through the Change_sqlpass plugin. I spent some time adapting the config.php to work with Christoph's excellent tutorial, and I wanted to pass the information on to other users.

First you must locate the change_sqlpass plugin, which at the time of this writing is a little hard to find due to the troubles caused at the Squirrelmail website. I am including a link that I think will be of help:

ftp://ftp.internat.freebsd.org/pub/FreeBSD/distfiles/squirrelmail/change_sqlpass-3.3-1.2.tar.gz

cd /usr/share/squirrelmail/plugins

wget ftp://ftp.internat.freebsd.org/pub/FreeBSD/distfiles/squirrelmail/change_sqlpass-3.3-1.2.tar.gz

tar -xvzf change_sqlpass-3.3-1.2.tar.gz

This plugin call for some other options to be installed:

aptitude install php-pear

Now you have to enable pear database access:

pear install DB

Next, the plugin calls for another plugin to be installed, the compatibility plugin:

wget ftp://ftp.internat.freebsd.org/pub/FreeBSD/distfiles/squirrelmail/compatibility-2.0.14-1.0.tar.gz

tar -xvzf compatibility-2.0.14-1.0.tar.gz

Now you must edit the config.php for the change_sqlpass plugin:

cd change_sqlpass

Use your favorite editor:

nano config.php

I am now including the changes I made, remember that in this file you MUST have a user created that has ALL privileges because they are making changes to your tables:

   // csp_dsn
   //
   // Theoretically, any SQL database supported by Pear should be supported
   // here.  The DSN (data source name) must contain the information needed
   // to connect to your database backend. A MySQL example is included below.
   // For more details about DSN syntax and list of supported database types,
   // please see:
   //   http://pear.php.net/manual/en/package.database.db.intro-dsn.php
   //
   $csp_dsn = 'mysqli://user:password@localhost/database';

   // where 'mysqli' is the sql version used.
   //    dbase  -> dBase
   //    fbsql  -> FrontBase (functional since DB 1.7.0)
   //    ibase  -> InterBase (functional since DB 1.7.0)
   //    ifx    -> Informix
   //    msql   -> Mini SQL (functional since DB 1.7.0)
   //    mssql  -> Microsoft SQL Server (NOT for Sybase. Compile PHP --with-mssql)
   //    mysql  -> MySQL (for MySQL <= 4.0)
   //    mysqli -> MySQL (for MySQL >= 4.1) (requires PHP 5) (since DB 1.6.3)
   //    oci8   -> Oracle 7/8/9
   //    odbc   -> ODBC (Open Database Connectivity)
   //    pgsql  -> PostgreSQL
   //    sqlite -> SQLite
   //    sybase -> Sybase
   // Where 'user' is a user capable of making changes to your virtual_users table,
   // where 'password' is the password of the above user,
   // where 'database' , in the case of the tutorial is mailserver


   // lookup_password_query
   //
   // This plugin will always verify the user's old password
   // against their login password, but an extra check can also
   // be done against the database for more security if you
   // desire.  If you do not need the extra password check,
   // make sure this setting is empty.
   //
   // This is a query that returns a positive value if a user
   // and password pair are found in the database.
   //
   // This query should return one value (one row, one column), the
   // value being ideally a one or a zero, simply indicating that
   // the user/password pair does in fact exist in the database.
   $lookup_password_query = 'SELECT 1 FROM virtual_users WHERE email = "%1" AND password = %4';

   // The above query is specific to the Workaround tutorial and returns a 1


   // password_update_queries
   //     
   // An array of SQL queries that will all be executed
   // whenever a password change attempt is made.
   //     
   // Any number of queries may be included here.
   // The queries will be executed in the order given here.
   $password_update_queries = array('UPDATE virtual_users SET password = %4 WHERE email = "%1"');

   // The above is the actual update sql, again specific to the workaround tutorial


   // password_encryption
   //
   // What encryption method do you use to store passwords
   // in your database?  Please use one of the following,
   // exactly as you see it:
   //     
   //   NONE          Passwords are stored as plain text only
   //   MYSQLPWD      Passwords are stored using the MySQL password() function
   //   MYSQLENCRYPT  Passwords are stored using the MySQL encrypt() function
   //   PHPCRYPT      Passwords are stored using the PHP crypt() function
   //   MD5CRYPT      Passwords are stored using encrypted MD5 algorithm
   //   MD5           Passwords are stored as MD5 hash
   //
   $password_encryption = 'MD5';

   // This is self explanatory if you followed the tutorial -):


   // csp_salt_static
   //
   // Encryption types that need a salt need to know where to get
   // that salt.  If you have a constant, known salt value, you
   // should define it in $csp_salt_static.  Otherwise, leave that
   // value empty and define a value for the $csp_salt_query.
   //
   // Leave both values empty if you do not need (or use) salts
   // to encrypt your passwords.
   //$csp_salt_static = 'LEFT(crypt_password, 2)';
   //$csp_salt_static = '"a4"';  // use this format with MYSQLENCRYPT
   //$csp_salt_static = '$2$blowsomefish$';  // use this format with PHPCRYPT
   $csp_salt_static = '';

   //$csp_salt_query = 'SELECT SUBSTRING_INDEX(crypt_password, '$', 1) FROM users WHERE username =$
   //$csp_salt_query = 'SELECT SUBSTRING(crypt_password, (LENGTH(SUBSTRING_INDEX(crypt_password, '$
   //$csp_salt_query = 'SELECT salt FROM users WHERE username = "%1"';
   $csp_salt_query = '';

   // This is where I had to pay close attention as the tutorial does not use a                               // salt and the default inserts a salt query

At this point, save your work and exit.

Next you must enable both the compatibility and change_sqlpass plugins in Squirrelmail:

cd ../../config/

./conf.pl

SquirrelMail Configuration : Read: config.php (1.4.0)
---------------------------------------------------------
Main Menu --
1.  Organization Preferences
2.  Server Settings
3.  Folder Defaults
4.  General Options
5.  Themes
6.  Address Books
7.  Message of the Day (MOTD)
8.  Plugins
9.  Database
10. Languages

D.  Set pre-defined settings for specific IMAP servers

C   Turn color on
S   Save data
Q   Quit

Command >>
 <-- 8


SquirrelMail Configuration : Read: config.php (1.4.0)
---------------------------------------------------------
Plugins
  Installed Plugins

  Available Plugins:
    1. abook_take
    2. administrator
    3. bug_report
    4. calendar
    5. change_sqlpass
    6. compatibility
    7. delete_move_next
    8. demo
    9. filters
    10. fortune
    11. info
    12. listcommands
    13. mail_fetch
    14. message_details
    15. newmail
    16. sent_subfolders
    17. spamcop
    18. squirrelspell
    19. test
    20. translate

R   Return to Main Menu
C   Turn color on
S   Save data
Q   Quit

Command >>
 <-- 6 (or whatever number the compatibility plugin has - it's needed by the change_sqlpass plugin)


SquirrelMail Configuration : Read: config.php (1.4.0)
---------------------------------------------------------
Plugins
  Installed Plugins
    1. compatibility

  Available Plugins:
    2. abook_take
    3. administrator
    4. bug_report
    5. calendar
    6. change_sqlpass
    7. delete_move_next
    8. demo
    9. filters
    10. fortune
    11. info
    12. listcommands
    13. mail_fetch
    14. message_details
    15. newmail
    16. sent_subfolders
    17. spamcop
    18. squirrelspell
    19. test
    20. translate

R   Return to Main Menu
C   Turn color on
S   Save data
Q   Quit

Command >>
 <-- 6 (the number of the change_sqlpass plugin)


SquirrelMail Configuration : Read: config.php (1.4.0)
---------------------------------------------------------
Plugins
  Installed Plugins
    1. compatibility
    2. change_sqlpass

  Available Plugins:
    3. abook_take
    4. administrator
    5. bug_report
    6. calendar
    7. delete_move_next
    8. demo
    9. filters
    10. fortune
    11. info
    12. listcommands
    13. mail_fetch
    14. message_details
    15. newmail
    16. sent_subfolders
    17. spamcop
    18. squirrelspell
    19. test
    20. translate

R   Return to Main Menu
C   Turn color on
S   Save data
Q   Quit

Command >> S

Data saved in config.php
Press enter to continue...
<-- press some key


SquirrelMail Configuration : Read: config.php (1.4.0)
---------------------------------------------------------
Plugins
  Installed Plugins
    1. compatibility
    2. change_sqlpass

  Available Plugins:
    3. abook_take
    4. administrator
    5. bug_report
    6. calendar
    7. delete_move_next
    8. demo
    9. filters
    10. fortune
    11. info
    12. listcommands
    13. mail_fetch
    14. message_details
    15. newmail
    16. sent_subfolders
    17. spamcop
    18. squirrelspell
    19. test
    20. translate

R   Return to Main Menu
C   Turn color on
S   Save data
Q   Quit

Command >>
 <-- Q

This will save the needed changes and quit the Squirrelmail configuration.

I am still trying to find a way to secure the config.php file for this plugin since it contains a cleartext password to a database super user. If you have suggestions, please forward step up!!

Now, test and enjoy!!

 

using database super user in config.php

There's no need to use the super user. In fact: Never do that when not needed !
Only grant permissions that are needed for the purpose. So if anybody gets in, they only have limited rights.

Here's the query to setup such a user:

GRANT select,update ON mailserver.virtual_users
TO <some-user>@localhost IDENTIFIED BY '<some-password>';
FLUSH PRIVILEGES;

Tested this on my install and it works.
(I used this tutorial for similar CentOS5.4 setup)
 

Error displaying the inbox on the left hand

SquirrelMail displays the following error on the left side (where normaly the inbox and other folders can be selected):

ERROR: Could not complete request.
Query: CREATE "Sent"
Reason Given: Unknown namespace.

If I change Default Folder Prefix to "none" (without quotes) the same error appears.

My OS is CentOS not Lenny

Regards Carsten

Security?

Yes, Roundcube is far nicer than Squirrelmail. But it got temporarily removed from Debian due to serious security issues. So there is no official Lenny version - although there are backports.

I wonder though what was so complicated in getting Squirrelmail running. I found it dead simple. :) Besides it has the avelsieve plugin allowing the users to edit their server-side filters which Roundcube doesn't AFAIK.

Cool

Nice. Thanks for the pointer. As squirrelmail isn't exactly beautiful it may be time to give it another try. :)

RoundCube instead of SquirrelMail

I started with SquirrelMail, too... but the GUI is not that user-friendly I think. I wonder why SquirrelMail doesn't use any AJAX-Features to improve the web interface. Roundcube is great and very easy to install.

Just to make this clear: You have to migrate your server side sieve filters manually!

As you already mentioned, there is no stable debian package. I decided not to use the unstable one... that's the reson why I downloaded the general tarball from http://roundcube.net/download.

As roundcube is nothing more than a php application which needs/uses a database this workaroung is ok, I think.

 

Kind regards

Oliver

Migration

I've migrated my roundcube version to the lenny backport version without having any problems. I can recommend this solution.

Kind regards

Oliver

Squirrelmail can send

I'm very thankful for this tutorial, you have done a great job here, it is very easy to follow...but it looks like I've made a mistake somewhere.  Everything appears to work fine throughout my entire setup, except I can't send email through Squirrelmail.

I recieve the following message:

Transaction failed
554 5.7.1 <oncedivine@hotmail.com>: Relay access denied

I've tried adding the subnet my mail server is in using the command:

postconf -e mynetworks=xxx.xxx.xxx.xxx/XX

but this didn't change anything.  Email clients are not having any problems...this seems odd, have you come
 passed anything like this before?  I did look around before sending this message, but I'm stumped.

Differences between my setup and your tutorial:

I used the GRS mail manager

Have not setup the following:

Sieve

quotas

DNS

or SPF to avoid spoofing

Thanks for any suggestions you might have,

Brad

Restrictions problem?

Nice to hear you liked the tutorial.

Please double-check your smtpd_*_restrictions. Most likely the order of your smtpd_recipient_restrictions or a missing statement. If you are not sure then tell us what you set it to.

smtpd_*_restrictions

Christoph, thank you for your quick reply.

I've copied from /etc/postfix/main.cf (I hope this is what you were looking for):

smtpd_sasl_type = dovecot

smtpd_sasl_path = private/auth

smtpd_sasl_auth_enable = yes

smtpd_recipient_restrictions = permit_mynetworks,permit_sasl_authenticated,reject_unauth_destination

I've checked for spelling mistakes but it all seems fine (I think), I've also checked the "Mynetworks" statement and I'm satisfied this is correct.  I'm curious if I added 127.0.0.0/8 to "mynetworks" if it would work and what possible security issues would this cause?

22:38:32 mail postfix/smtpd[2457]: connect from localhost[127.0.0.1]

22:38:32 mail postfix/smtpd[2457]: NOQUEUE: reject: RCPT from localhost[127.0.0.1]: 554 5.7.1 <********@yahoo.com.au>: Relay access denied; from=<brad@mydomain.com> to=<********@yahoo.com.au> proto=ESMTP helo=<mydomain.com>

22:38:32 mail postfix/smtpd[2457]: lost connection after RCPT from localhost[127.0.0.1]

22:38:32 mail postfix/smtpd[2457]: disconnect from localhost[127.0.0.1]
 

I should also add that I can send within my own domain without a problem using Squirrelmail, the problem only exists when I try and send outside of my domain.  What possible causes would allow an email client to work but not allow SquirrelMail to work?

 

Cheers,

Brad.

Possible cause, but how to fix

I've been using the testing steps throughout this tutorial and found an error.  I can't say that I remember doing this step whilst doing the initial build, my appologies for not picking this up sooner. 


mail:/var/log# telnet local smtp
Trying ***.***.***.***...
Connected to local.mydomain.com.
Escape character is '^]'.
220 mail.mydomain.com ESMTP Postfix (Debian/GNU)
ehlo mydomain.com
250-mail.mydomain.com
250-PIPELINING
250-SIZE 102400000
250-VRFY
250-ETRN
250-STARTTLS
250-AUTH PLAIN LOGIN
250-ENHANCEDSTATUSCODES
250-8BITMIME
250 DSN
auth plain **************(I used the instruction on Base64-encode for this)*******************
535 5.7.8 Error: authentication failed:

 

This has caused the following to be displayed in /var/log/mail.log

Apr  4 23:16:34 mail postfix/smtpd[2419]: connect from localhost[127.0.0.1]
Apr  4 23:16:51 mail postfix/smtpd[2419]: warning: localhost[127.0.0.1]: SASL plain authentication failed:
Apr  4 23:16:54 mail postfix/smtpd[2419]: disconnect from localhost[127.0.0.1]
 

I'm guessing I'm on the right track here, but I'm not sure what I've missed to fix this problem.

 

I used


Cheers,

Brad

band-aid fix

I've added 127.0.0.0/8 to mynetworks in /etc/main.cf and I'm no longer receiving the errors, I realise this does not fix the underlying issue but it is now in a working state.

Is anyone aware of any security issues I may now have due to my "band-aid fix"

No problem

It's totally correct to put your loopback addresses into the $mynetworks setting. On my server at home I use both 127.0.0.0/8 and my local network address.

Thanks for your help

Christoph,

Excellent, thank you for all of your help.  My mail server appears to work in all senarios I want it to. 

I've learnt a lot from this project, it won't be long until I can break these shackles Microsoft hold over me.

My next steps will be to move MySQL database (/var/lib/mysql) and mail directories (/var/vmail) to my file-server over FTP, this should be fairly easy...I hope. 

Cheers,

Brad

some further explanation?

I had the same issue... everything going outbound (using Squirrelmail) was being rejected, but as a relay, and not just an outbound message.

 

Does someone have the knowledge and time to expound on why this is, and how this solution is affecting the server?  I'm icthing to understand what I have here.

 

--qab

You should go and setup the rest

First asure that the error about 503... are gone.

Next: if You want to not be relay:

- setup DNS

- setup SPF

- in your network should not be another mail server with the same name as the one used in this setup.

Hmmm - help

http://www.delaere.nl/blog/2009/roundcube-workaroundorg-password-change/

I am trying to make raoundcube users change their own passwords!!!!!

found this, but i am unable to make the "And now then, ofcourse the Stored procedure":

 

CREATE PROCEDURE `sp_password_update`(passwordold char(40),passwordnew char(50),username char(50),domainname char(40))
BEGIN
insert into `password_changes` (`domainname`, `username`, `passwordold`, `passwordnew`,`changedate`)
values (domainname, username, md5(passwordold), md5(passwordnew), now());
update virtual_users
set password = md5(passwordnew)
where user = username
and password = md5(passwordold)
and domain_id = (select id from virtual_domains where name = domainname);

END$$

 

 

i get: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 

 

mysql version 5.0.51a-24+lenny3

 

Ideas :(

What worked for

What worked for me:

activate password plugin

ind /roundcube/plugins/password/config.inc.php

$rcmail_config['password_driver'] = 'sql';

$rcmail_config['password_db_dsn'] = 'mysql://MAILUSER:MAILPASSS@127.0.0.1/MAILDB';

$rcmail_config['password_query'] = 'UPDATE virtual_users SET password = %n WHERE email = %u LIMIT 1';

$rcmail_config['password_hash_algorithm'] = 'md5';

I did not use stored procedures.. so you are on your own..

 

insert into `password_changes`  ?

I do not have a table password changes.. 




 

work's

thanks for the help

i did it with this line in the password config line

$rcmail_config['password_query'] = 'UPDATE virtual_users SET password=MD5(%p) WHERE email = %u AND password=MD5(%o) LIMIT 1';

without $rcmail_config['password_hash_algorithm'] = 'md5';

works perfect :)