SugarCRM, RoundCube and Request Tracker integration on a single domain

In my years of working as a system administrator I’ve done some pretty complex setups and integration solutions, but I don’t think I’ve done anything as twisted as this one recently.  The setup is part of the large and complex client project, built on their infrastructure, with quite a few requirements and a whole array of limitations.  Several systems were integrated together, but in this particular post I’m focusing primarily on the SugarCRM, RoundCube and Request Tracker.  Also, I am not going to cover the integration to full extent – just the email related parts.

Quick overview of the requirements to set the scene:

1. SugarCRM web application used by company with many employees and multiple offices world-wide.  Currently, with access limited by IP address. Later, maybe, with public access.
2. Gmail for Work used with a single primary domain and a few alias domains.  Let’s say, example.com as a primary domain and other.com as an alias.  user@example.com is the same mailbox as user@other.com .
3. Company employees should not know their Gmail account credentials.
4. Individual employee mailboxes and group mailboxes (like support@example.com) should operate on the same domain – no sub-domains or additional domains allowed.
5. Full featured webmail client (folders, templates, signatures, filters, auto-replies, etc) should be available to all employees within the SugarCRM interface.
6. Single sign-on between SugarCRM and webmail client, using SugarCRM user details.  So whoever is logged into the SugarCRM can access webmail without any additional authentication.
7. All incoming and outgoing emails to/from individual employee mailboxes should be imported into the SugarCRM database and linked with related client accounts, so that whoever has the access to see client account in the SugarCRM, can also see the sent and received email communications with this client.
8. The exception to the above should be managerial mailboxes, which should be excluded from such import.
9. A full-featured ticketing/suport system for handling support requests and certain types of workflows/communications should be provided.
10. All incoming emails to group mailboxes (such as support@example.com, sales@example.com, etc) should create a new ticket in the ticketing/support system, also leaving a copy of the message in the Gmail’s mailbox.
11. Ticketing/support system should have single sign-on integration with SugarCRM, using SugarCRM user details.  So whoever is logged into the SugarCRM can access appropriate tickets inside the ticketing/support system.
12. All client-related tickets (based on the primary email address of the client), should be visible inside the SugarCRM to whoever has access to see the client account details.
13. Ticketing/support system should send email responses from appropriate addresses on the @example.com domain, both to external users (“Your request has been received”, etc), as well as internal users (example: employee@example.com – “You have been assigned a new ticket”).
14. All systems can be hosted on the same servers, but they cannot expose more than one application to the outside world.  So, for example, choose either web (ports HTTP/80 or HTTPS/443) or email (SMTP/25).
15. Additional servers can be provided to expose applications to the outside world, but they will have limited communications between them – firewalls, different networks, etc.

The above are by no means all the requirements, just the major ones.

Now, a little bit on why there were these three particular systems.

SugarCRM was the primary part of the project.  If it could solve all the functionality needs, it could have been used on its own, without any additional systems.  By the way, SugarCRM in question is the self-hosted Community Edition version 6.5.20, not professional, enterprise, or cloud variation.

SugarCRM provides both the built-in email client and support/ticketing system, among other things.  However the functionality of both modules is very limited and insufficient for the requirements of the project.

Gmail for Work provides an excellent webmail application.  There is even a way to integrate it with external systems for single sign-on, using the SAML SSO Service.  Here’s a handy illustration from the page:

It wasn’t clear how much time and effort would be needed to so however.  And there was a limitation of embedding Gmail into the SugarCRM interface.  To the best of my knowledge, this is not possible with IFRAME tag (one, two).  The closest option is to build a custom user interface application using Gmail API.  Which is an undertaking of its own.

So, with Gmail being unavailable and SugarCRM being not good enough, a third-party solution was necessary.  RoundCube is a free and Open Source application, written in PHP (same as SugarCRM), providing an excellent array of features, extendable with plugins, and with which we had previous positive experiences.

Now for the ticketing system, the reasons were very similar.  SugarCRM wasn’t good enough and we needed something else.  There are plenty of alternatives to choose from.  However, in-depth expertise with Request Tracker was the deciding factor.  Turned out be more important than the technology stack – Request Tracker is written in Perl, not PHP.  However it is too a free, Open Source solution, with lots of features and extension functionality.

The single sign-on functionality was implemented rather easy.  I’m not going to cover it in this post, but you might want to have a look at some recent related posts talking about this functionality and some of the related issues:

• Single Sign-On with SugarCRM and RoundCube Using Multiple PHP Sessions
• Single Sign-On Between SugarCRM and Request Tracker
• PHP session encoding
• Flexible Feature Control at Instagram
• RT initialdata and Perl’s nested map
• Custom Single Sign-On with Nginx and Auth Request Module

All three systems – SugarCRM, RoundCube, and Request Tracker were hosted on the same Linux server.  It was decided that only the web application (HTTP and HTTPS ports) will be exposed from this server.

So, the MX record for the example.com domain points to the Gmail servers.  This is fine and it makes all emails arrive to all the mailboxes.

RoundCube is used as the web interface by the users to work with individual mailboxes (via IMAP protocol).  A custom plugin has been developed for the RoundCube (using the message_sent hook), so that whenever a user composes and sends an email, a copy of it is passed to the SugarCRM via the API.  SugarCRM then decides whether or not the message needs importing, and if it needs, then does so.  This is the easy part. ;)

Now, we need to get a copy of all the incoming messages, so that we could either import them into the SugarCRM or pass them to Request Tracker for ticketing and support needs.  Gladly, Google for Work supports quite flexible delivery routing.  Here are how to configure dual delivery and some best practices.

An additional Linux server has been setup with Exim as the mail transfer/delivery agent.  Port 25 (SMTP) was exposed from this machine and configured to accept all email for @example.com from Google servers.  Dual delivery configuration on Google was setup to send a copy of each message to this new server.

Unfortunately, this machine was not allowed to connect directly to the server with web applications.  Only the reverse was allowed – the web server could have some limited access to this mail server.

Mkay, no problem.

Dovecot was installed on the mail server to provide POP/IMAP functionality, with all the relevant ports being exposed only to the web server IP address.  Exim was configured on the mail server to check against a list of known group email addresses (support@, sales@, etc) and if matched, then delivery to the local mailbox for Request Tracker to pick up later.  If incoming email is not for the group mailbox, then it is delivered to another mailbox, for the SugarCRM to pick up.  This is still not too difficult, and if you don’t know how to do this, Google search for Exim aliases and catch all address configurations.

We are done with this mail server for now.  Let’s move on to the web server, which is hungry for those emails.

SugarCRM part is the easiest here.  The application already provides the mail checker functionality out of the box.  So we configure it to connect to the mail server via IMAP, authenticate itself and fetch all emails from that dedicated mailbox of the catch all setup.  Done.  Check.

Request Tracker however ships without the mail check functionality.  Probably because most people using it expose the local SMTP port to the world and then use aliases to pipe message to the Request Tracker’s rt-mailgate script.

Well, no problem.  Fetchmail is famous for its mail fetching super powers.  Installing and setting it up takes just a few minutes.  Here’s a configuration to get you started.

Here’s where things get interesting.

We have a number of group addresses and we need to know which email address should go to which queue in Request Tracker.  This information is also needed on two different servers – the mail server that gets all messages and on the web server to deliver the messages to the correct queue.

Obviously, we can update both servers from a single source (Hello, Ansible.  How are you today?).  We can either have multiple mailboxes setup on the mail server – one for each queue – and then use different credentials to deliver messages with fetchmail, or we can use the local Exim on the web server and update its aliases whenever needed.

I didn’t like the approach of creating more and more system users on the mail server.  It’s easy to automate, but it just doesn’t seem elegant.  So I opted for the single mailbox for RT on the mail server and local SMTP delivery on the web server.

Fetchmail provides the –smtphost option of course:

-S <hosts> | --smtphost <hosts>
(Keyword: smtp[host])
Specify a hunt list of hosts to forward mail to (one
or more hostnames, comma-separated). Hosts are tried
in list order; the first one that is up becomes the
forwarding target for the current run. If this option
is not specified, 'localhost' is used as the default.
Each hostname may have a port number following the
host name. The port number is separated from the host
name by a slash; the default port is "smtp". If you
specify an absolute path name (beginning with a /), it
will be interpreted as the name of a UNIX socket
accepting LMTP connections (such as is supported by
the Cyrus IMAP daemon) Example:

--smtphost server1,server2/2525,server3,/var/imap/socket/lmtp

But this introduces a new problem.  Now all messages appear to be from the local system user on the web server, not from the original addresses.

After a bit of poking around fetchmail manual, the following bit was discovered:

Do NOT use an MDA invocation that dispatches on the
contents of To/Cc/Bcc, like "sendmail -i -t" or
"qmail-inject", it will create mail loops and bring
the just wrath of many postmasters down upon your
head. This is one of the most frequent configuration
errors!

Oh, really?  But that’s exactly what I need here, so off I go.  Back to the MDA option with “sendmail -i -t” as the delivery agent.

Now we have messages delivered to the local Exim on the web server, with the original message headers.  Now we configure /etc/aliases with group addresses, each piped to the appropriate Request Tracker queue, as per usual.

Test.  Oops.  Doesn’t work.  Check the configurations again.  And a bit more carefully again. Ah, aliases delivery is used by default only for the local domains.  No problem – we add example.com to local domains, restart Exim service and test again.

Beautiful!  Group emails are coming in with correct headers and are correctly piped to the appropriate queues.  Tickets are created and the world looks beautiful.  For about 30 seconds …

Now we have a new problem to solve.  Request Tracker generates automated responses, which should go to the company employees, who’s emails are on the same example.com domain.  Request Tracker passes these messages to the local SMTP server, where Exim thinks that these are for local delivery, and then … ***BOOM*** Nothing good comes out of it.

Here’s where I have to pause for a second and praise Exim as a beautiful piece of software.  I’ve used for almost two decades now and every time it saves me lots of hair pulling.  There are multiple ways to fix the problem.  One is, for example, to do the following:

1. Remove example.com from local domains.
2. Swap the order of system_aliases and dnslookup routers in /etc/exim/exim.conf
3. Add “domains = example.com : other.com” to system_aliases router.
4. Comment out “domains = ! +local_domains” in the dnslookup router.
5. Restart Exim service

So now, when Exim gets the message, it will first try to delivery it using the /etc/aliases file, provided that the message is for the example.com or other.com domains.  If the message is not for these domains, or if it fails to find the appropriate alias, it will pass the message to the dnslookup router, which will attempt a remote delivery, sending the message off to the Internet.

This will make sure that both scenarios are covered – incoming messages from the outside are delivered to the Request Tracker, and outgoing messages from the Request Tracker are sent out.

Note, however, that I am focusing here purely on the routing and delivery parts of the equation.  You should test and tweak the configuration further to prevent any unwanted relaying and SPAM attacks.

With the above setup, we managed to solve the complex requirements for the project and still keep the sanity.  If you know of any simpler or more elegant ways to do the above, please please please let me know.  I’ll buy you a beer.  Or a few.

P.S.: For those of you still working with the SugarCRM, you might find some of my older posts (from about 8 years ago!) still useful and relevant.