*EDIT* what an embarasment - there is no Roundcube 0.4.3, just 0.4.2. Hi, Being stuck on 2.x myself and noticing Roundcube got really good lately I decided to upgrade / switch my webmail solution. While there I didn't find any good solutions for authentication with ISPConfig except for virtuser_file plugin so I decided to do some work to make a plugin for setting real user's name and email into Roundcube settings. Result is very user-friendly experience as there is no "new user identity" settings to be done. Being in serious lack of time here I ask of you, does any of you need a howto / this plugin to be installed so I know to take the time and share it with you? Let me know
Due to extreme interest (3 people ;-) awesome!) here is my ISPConfig 2.x for Roundcube 0.4.2 integration plugin. 1.) Make a MySQL user to access ISPConfig database. I myself prefer to give just the right amount of privileges - in my case I used username "roundcube": Code: GRANT SELECT (web_domain, doc_id, doctype_id) ON `db_ispconfig`.`isp_isp_web` TO 'roundcube'@'localhost' GRANT SELECT (user_username, doc_id, user_emailalias, user_name) ON `db_ispconfig`.`isp_isp_user` TO 'roundcube'@'localhost' GRANT SELECT (child_doctype_id, child_doc_id, parent_doctype_id, parent_doc_id) ON `db_ispconfig`.`isp_dep` TO 'roundcube'@'localhost' Don't forget to set a password! 2.) First make a folder in your Roundcube installation called "ispconfig_addons". 3.) Make two files, "config.inc.php" and "ispconfig_addons.php". 4.) Fill config.inc.php with this code: Code: <?php $rcmail_config['isp_db_name'] = 'db_ispconfig'; // your ISPConfig database $rcmail_config['isp_db_user'] = 'roundcube'; // username for ISPConfig database from step #1 $rcmail_config['isp_db_pass'] = '<password>'; // extremly long, complicated and just impossible to guess password from step #1 ?> 5.) Fill ispconfig_addons.php with this code: Code: <?php /** * ISPConfig v2.x integration for Roundcube 0.4.2 plugin * * Adds ability to authenticate against ISPConfig 2.x user database. * * To use you must have config.inc.php file following settings: * isp_db_name - ISPConfig database name * isp_db_user - user with access to only selected fields in selected tables * isp_db_pass - password for that user * * Code is tested however there is no waranty for it. Use at your own risk. Feedback is welcome! * * @version 0.1 * @author Janez Dolinar s.p., <[email protected]> */ class ispconfig_addons extends rcube_plugin { function init() { $this->add_hook('user_create', array($this, 'usercreate')); $this->add_hook('login_after', array($this, 'loginafter')); $this->load_config(); } function loginafter($query) { $rcmail = rcmail::get_instance(); if (isset($_SESSION['userFullName'])) { $sql = 'UPDATE identities SET name=?, email=? WHERE identity_id = ?'; $rcmail->db->query($sql,$_SESSION['userFullName'], $_SESSION['userEmail'], $rcmail->user->data['user_id']); unset($_SESSION['userFullName']); unset($_SESSION['userEmail']); } return $query; } function usercreate($args) { $rcmail = rcmail::get_instance(); if (($rcmail->config->get('isp_db_name')=='') || ($rcmail->config->get('isp_db_user')=='') || ($rcmail->config->get('isp_db_pass')=='')) { return $args; } else { $sql = "SELECT iiu.user_username, iiu.user_emailalias, iiu.user_name, iid.web_domain FROM isp_isp_user iiu, isp_dep id, isp_isp_web iid " ."WHERE iiu.user_username = '%s' AND iiu.doc_id = id.child_doc_id AND id.child_doctype_id = 1014 AND iid.doc_id = id.parent_doc_id"; $mysql = new MySQLi('localhost', $rcmail->config->get('isp_db_user'), $rcmail->config->get('isp_db_pass'), $rcmail->config->get('isp_db_name')); if ($mysql->connect_error) return false; $result = $mysql->query(sprintf($sql,addslashes($args['user']))); if ($result) { $row = $result->fetch_object(); $userEmail = explode("\n",str_replace("\r","",$row->user_emailalias)); $args['alias'] = $userEmail[0].'@'.$row->web_domain; $_SESSION['userFullName'] = $row->user_name; $_SESSION['userEmail'] = $userEmail[0].'@'.$row->web_domain; $result->close(); } $mysql->close(); } return $args; } } ?> 6.a) make sure it's web-server readable! 7.) Add the plugin to your Roundcube config file /config/main.inc.php : Code: // ---------------------------------- // PLUGINS // ---------------------------------- // List of active plugins (in plugins/ directory) $rcmail_config['plugins'] = array('virtuser_file','ispconfig_addons'); Also don't forget to configure virtuser_file plugin. Add this to the end of config/main.inc.php file: Code: $rcmail_config['virtuser_file'] = '/etc/postfix/virtusertable'; This is valid for Ubuntu / Debian users. This is just a guess, find your location and modify this line accordingly. Code uses MySQLi extension so you'll need that as well however I think it should be no problem changing it to MySQL extension. That should be it! You / your clients can now login with any email set for their account and their password! Their names should already be set to ther ISPConfig full names and default email address. Please note I made this after a long hard working day and was tired. There could be some room for improvement or even some flaws. I ask for your feedback, I'll be glad to fix the code. Oh, and no waranty Enjoy!
Now I'm worried - no replies! Anyone else tried this? Did it work? Was it worth installing? I need feedback people! Re!
test Heloo. I tested your addon on ISPCONFIG 2 with roundcubemail package from ispconfig.com webpage currently 3.1. So i login using email address but have problem that new profile have created as name web2_pok2 with correct email, du u thing that can be corrected ? or if i can test that somehow? i using ispconfig with mysql database and user for connection is same as in script config.inc.php.
Did you use this on your existing profile or a new profile? This solution will work only for new profiles, old ones will still have their settings intact. You will be able to authenticate though.
new test acc Heloo that was new email address and new profile. I dont see any error in roundcubemail log.
Thanks necromncr for your plugin. It was just what we were looking for. However I think we had the same problem as madmucho, and we found the following problems: - The rcmail->db class doesn't seem to like the parameters ( ? ) in the query. - It's trying to update the identities table using identity_id = $rcmail->user->data['user_id'] - The latest version of ISPConfig 2 seems to be using the user_email field insterad of the user_emailalias field So we updated ispconfig_addons.php like this: Code: <?php /** * ISPConfig v2.x integration for Roundcube 0.4.2 plugin * * Adds ability to authenticate against ISPConfig 2.x user database. * * To use you must have config.inc.php file following settings: * isp_db_name - ISPConfig database name * isp_db_user - user with access to only selected fields in selected tables * isp_db_pass - password for that user * * Code is tested however there is no waranty for it. Use at your own risk. Feedback is welcome! * * @version 0.1 * @author Janez Dolinar s.p., <[email protected]> */ class ispconfig_addons extends rcube_plugin { function init() { $this->add_hook('user_create', array($this, 'usercreate')); $this->add_hook('login_after', array($this, 'loginafter')); $this->load_config(); } function loginafter($query) { $rcmail = rcmail::get_instance(); if (isset($_SESSION['userFullName'])) { $sql = "UPDATE identities SET name='" . $_SESSION['userFullName'] . "', email='" . $_SESSION['userEmail'] . "' WHERE user_id = " . $rcmail->user->data['user_id']; $rcmail->db->query($sql); unset($_SESSION['userFullName']); unset($_SESSION['userEmail']); } return $query; } function usercreate($args) { $rcmail = rcmail::get_instance(); if (($rcmail->config->get('isp_db_name')=='') || ($rcmail->config->get('isp_db_user')=='') || ($rcmail->config->get('isp_db_pass')=='')) { return $args; } else { $sql = "SELECT iiu.user_username, iiu.user_email, iiu.user_name, iid.web_domain FROM isp_isp_user iiu, isp_dep id, isp_isp_web iid " ."WHERE iiu.user_username = '%s' AND iiu.doc_id = id.child_doc_id AND id.child_doctype_id = 1014 AND iid.doc_id = id.parent_doc_id"; $mysql = new MySQLi('localhost', $rcmail->config->get('isp_db_user'), $rcmail->config->get('isp_db_pass'), $rcmail->config->get('isp_db_name')); if ($mysql->connect_error) return false; $result = $mysql->query(sprintf($sql,addslashes($args['user']))); if ($result) { $row = $result->fetch_object(); $userEmail = explode("\n",str_replace("\r","",$row->user_email)); $args['alias'] = $userEmail[0].'@'.$row->web_domain; $_SESSION['userFullName'] = $row->user_name; $_SESSION['userEmail'] = $userEmail[0].'@'.$row->web_domain; $result->close(); } $mysql->close(); } return $args; } } ?> Remember to update the permissions of your roundcube MySQL user to grant select on the user_email field instead of the user_emailalias field. We also had to change the following parameters in the roundcube config main.inc.php: Code: $rcmail_config['identities_level'] = 3; before we had Code: $rcmail_config['identities_level'] = 1; Hope this helps someone! / Andreas & Niklas smartcms.se
hello I have just checked the roundcube website and there is a version 0.5.1 out already - can anyone give me a hint if the install instructions for ispconfig 2 are the same as described here, or are there new instructions...? currently I already hvae 04. roundcube running on the server with ispconfig 2 would be happy if anyone could help all best and thx dan
Can't wait to try this out! Wow, thanks necromancr... I've been away for awhile so I wish I saw this earlier. I can't wait to try this out. Plus I already use MySQLite er i mean MySQLi instead of regular mysql for roundcube anyways so I don't have to go the opposite way for a change . I will most definitely give this a try. Much thanks.
