DeutschEnglish

Submenu

 - - - By CrazyStat - - -

25. July 2016

Roundcube: Multiple identities (mail_domains) automatically created by default when user is created

The problem

Roundcube is a popular webmailer written in PHP. Today I configured it on a host where each user has multiple identities, one for each domain: E.g., user bob has the three identities:
bob@example1.org, bob@example2.org and bob@example3.org

The same for all other users.

When the user logs in for the first time in roundcube, all three identities should be automatically created. There is a config variable mail_domain, which can be used like this:

$config['mail_domain'] = 'example1.org';

This will make sure that when user bob logs in, the created identity will be bob@example1.org . You can give an array here, but only one domain per IMAP host. So using mail_domain, it seems that you currently cannot define multiple domains that will create different identities.

The solution

The solution that I found uses the plugin virtuser_query. This plugin lets you query mail identities from an SQL database. But within the SQL query, you can also statically configure all the domains like this:

$config['virtuser_query'] = array('email' => 
'SELECT "%u@example1.org" UNION 
SELECT "%u@example2.org" UNION 
SELECT "%u@example3.org"',
'user' => '', 'host' => '', 'alias' => '');

The plugin replaces %u with the username, e.g. bob. So this query will always return three rows, one for each domain. It is not really querying the database, it is just a workaround to configure multiple default domains in roundcube, as mail_domain currently does not allow us to do this.

For the plugin to work, you need to enable it like this:

$config['plugins'] = array(...,'virtuser_query'); 
// note: there is an underscore after virtuser

In my case the virtuser_query plugin was already included in Roundcube as I have installed the Debian package roundcube-plugins . If it is not already part of your roundcube installation, you can get in here.

I hope this helps somebody to solve this issue faster 🙂
Please leave a comment if it helped you, if you have problems with this, or if you found another way.

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

15. July 2016

Request-Tracker4: Enabling fast fulltext search with Sphinxsearch on Debian Jessie

I just successfully made fulltext search in our request tracker super fast. This is a guide for Debian Jessie and request tracker set up to use MySQL (or MariaDB).

  • Don’t forget to backup your RT database:
    mysqldump -p -u root rtdb > rtdb.sql
  • Log out of request tracker. I had some strange issue because I did not 😉
  • Make sure you are using request-tracker4 version 4.2.12 from jessie-backports. If you don’t already, this is how you update:
    – add this to the end of your /etc/apt/sources.list:

    deb http://ftp.debian.org/debian jessie-backports main

    – run

    apt-get update

    – install request-tracker4 from jessie-backports:

    apt-get -t jessie-backports install request-tracker4

    – follow the update procedure, make sure your database is upgraded
    (We need the package from backports as jessie’s rt4-db-mysql package depends on mysql-client, whereas the backports version depends on virtual-mysql-client and thus can also be easily used with MariaDB.)

  • Second, make sure you are using MariaDB instead of MySQL. MariaDB is a drop-in replacement for MySQL. The big advantage of MariaDB here is that it comes with the SphinxSE storage engine that we will use for the fulltext search. SphinxSE is also available for MySQL, but you would need to compile it yourself whereas MariaDB includes SphinxSE by default. MariaDB has also a lot more advantages. Switching to MariaDB is very simple, no need to convert databases or configuration. I used aptitude and selected to install mariadb-client and maridb-server. Then I checked the dependency resolutions and choose the option to uninstall mysql-client, mysql-server and related packages. Then everything went smooth automatically, I just needed to enter the root password again, everything else was updated automatically.
  • Now check if your RT is still working well with MariaDB.
  • Now enable the SphinxSE storage engine in MariaDB:
    mysql -u root -p
    [enter password]
    INSTALL SONAME 'ha_sphinx';
    SHOW ENGINES;
    EXIT;
  • Now install the Sphinxsearch daemon:
    apt-get install sphinxsearch
  • Now set up indexed full text search in RT:
    rt-setup-fulltext-index --dba root --dba-password secret

    (Let me know if you find a way without passing the password as a parameter)
    This asks you what type of index you want to use. Enter “sphinx”. Then keep the defaults.

  • The command above gave you code for /etc/request-tracker4/RT_SiteConfig.pm – like this:
    Set( %FullTextSearch,
        Enable     => 1,
        Indexed    => 1,
        MaxMatches => '10000',
        Table      => 'AttachmentsIndex',
    );

    Add / adjust this in RT_SiteConfig.pm.

  • Then you need to configure the index in sphinx. The above tool also gave you a config file for this, but it does not work with the current sphinxsearch version of jessie. So here is what works. Create an /etc/sphinxsearch/sphinx.conf like this:
    source rt {
        type            = mysql
    
        sql_host        = localhost
        sql_db          = rtdb
        sql_user        = rtuser
        sql_pass        = RT_PASSWORD
    
        sql_query_pre   = SET NAMES utf8
        sql_query       = \
            SELECT a.id, t.Subject, a.content FROM Attachments a \
            JOIN Transactions txn ON a.TransactionId = txn.id AND txn.ObjectType = 'RT::Ticket' \
            JOIN Tickets t ON txn.ObjectId = t.id \
            WHERE a.ContentType LIKE 'text/%' AND t.Status != 'deleted'
    
    #    sql_query_info  = SELECT * FROM Attachments WHERE id=$id
    }
    
    index rt {
        source                  = rt
        path                    = /var/lib/sphinxsearch/data/rt
        docinfo                 = extern
    #    charset_type            = utf-8
    }
    
    indexer {
        mem_limit               = 32M
    }
    
    searchd {
        listen                  = 3312
        log                     = /var/log/sphinxsearch/searchd.log
        query_log               = /var/log/sphinxsearch/query.log
        read_timeout            = 5
        max_children            = 30
        pid_file                = /var/run/sphinxsearch/searchd.pid
    #    max_matches             = 10000
        seamless_rotate         = 1
        preopen_indexes         = 0
        unlink_old              = 1
        # For sphinx >= 1.10:
        binlog_path             = /var/lib/sphinxsearch/data
    }

    Make sure to adjust the mysql database, user and password.
    Note that there are some differences to the config provided by rt-setup-fulltext-index. Especially, you need to use listen instead of port. If you don’t adjust this, sphinxsearch will start on a different port and RT will not find any results in fulltext searches! If your RT is not behind a firewall, better also set listen to 127.0.0.1:3312 . Also, I used the Debian style paths from the Debian sample. The fields commented out are not necessary anymore in current sphinxsearch.
    UPDATE 26.08.2016: I changed the SQL Query to also index the Subject of the tickets and index all ‘text/%’ types and not only ‘text/plain’. This change is based on a comment by Claes Merin in the Request Tracker Mailing List. Thanks a lot. Without this change, the fulltext search will only search within ticket content, but not within ticket titles… Also, before it would not index text/html attachments.

  • Now run the indexer to build the initial index. This will take some time, for a 6 GB Attachments Table, it took not more than 30 minutes. Maybe run this in a screen in case your ssh connection breaks:
    indexer rt
  • Enable the Sphinx daemon. To do so, adjust START to yes in /etc/default/sphinxsearch
  • Finally, start the sphinxsearch daemon:
    /etc/init.d/sphinxsearch start
  • Now you can log into RT and do a fulltext search by entering in the search field:
    fulltext:search_word
  • In my case, this made the fulltext searches run within a second instead of 5 minutes 🙂
  • Make sure the indexer is run regularily to update the index. To do so, enter
    crontab -e

    And add a line like this:

    13 * * * * /usr/bin/indexer rt --rotate

I hope this helps somebody to setup this a little faster.