Roundcube/pgsql on FreeBSD

My employer’s current webmail solution is pretty tightly tied to the mail server. We need a webmail solution, but I want to be able to move, change, and upgrade webmail independently of the mail server. I’m testing Roundcube, running on FreeBSD-current/amd64 diskless on KVM.

First install Roundcube. Go to /usr/ports/mail/roundcube and run make config-recursive. You can use MySQL, PostgreSQL, or SQLite. Personally, I’m a Postgres bigot, mainly because Postgres has inflicted less pain than MySQL over the years. (Remember, punishing someone less feels like a reward!) I selected postgres, disabled mysql, then chose other environment-specific options such as LDAP. After configuring all of roundcube’s dependencies, run make all install clean.

No matter the database you prefer, in FreeBSD the client is packaged and built separately from the database server. I built /usr/ports/databases/postgresql84-server. Normally I would enable PAM, to enable database authentication via the operating system authentication, but that’s apparently broken as of this writing.

I want to modify httpd.conf as little as possible, so I do most of my configuration via files in /usr/local/etc/apache/Includes. I create the following files:

vhosts.conf
NameVirtualHost *:80
NameVirtualHost *:443

And of course, we need to acces webmail over SSL. Here’s ssl.conf:

Listen 443
AddType application/x-x509-ca-cert .crt
AddType application/x-pkcs7-crl .crl
SSLPassPhraseDialog builtin
SSLSessionCache "shmcb:/var/run/ssl_scache(512000)"
SSLSessionCacheTimeout 300
AcceptMutex posixsem
SSLMutex "file:/var/run/www/ssl_mutex"

I use RCS for minor stuff like this, so I need to block web site visitors from viewing my RCS files. Here’s blockRCS.conf


Order allow,deny
Deny from all
Satisfy All

Finally, here’s the configuration for the webmail virtual server, webmail.conf. The most interesting thing here is that I automatically redirect HTTP connections to HTTPS. (Perhaps interesting is the wrong word. “Least uninteresting?” Yeah, that’s better.)

AddType application/x-httpd-php .php

ServerName webmail.domain.com
Redirect permanent / https://webmail.domain.com/
ServerAdmin webmail@domain.com
ErrorLog "|/usr/local/sbin/rotatelogs /var/log/httpd/webmail_error_log.%Y-%m-%d-%H_%M_%S 86400 -300"
CustomLog "|/usr/local/sbin/rotatelogs /var/log/httpd/webmail_access_log.%Y-%m-%d-%H_%M_%S 86400 -300" combined
DocumentRoot /usr/local/www/webmail/

Options None
AllowOverride All
Allow from all



ServerAdmin webmail@domain.com
ServerName webmail.domain.com
SSLEngine on
SSLCipherSuite ALL:!ADH:!EXPORT56:RC4+RSA:+HIGH:+MEDIUM:+LOW:+SSLv2:+EXP:+eNULL

SSLOptions +StdEnvVars

BrowserMatch ".*MSIE.*" \
nokeepalive ssl-unclean-shutdown \
downgrade-1.0 force-response-1.0
SSLCertificateFile etc/apache22/certs/webmail.domain.com.crt
SSLCertificateKeyFile etc/apache22/certs/webmail.domain.com.key
ErrorLog "|/usr/local/sbin/rotatelogs /var/log/httpd/webmail_ssl_error_log.%Y-%m-%d-%H_%M_%S 86400 -300"
CustomLog "|/usr/local/sbin/rotatelogs /var/log/httpd/webmail_ssl_access_log.%Y-%m-%d-%H_%M_%S 86400 -300" combined
DocumentRoot /usr/local/www/roundcube/

Options Indexes FollowSymLinks
AllowOverride All
Allow from all

Initialize your postgresql database. I got Postgres help from here.

# /usr/local/etc/rc.d/postgresql initdb

Edit your Postgres config file, /usr/local/pgsql/data/postgresql.conf. Be sure that your database is only listening on the loopback address.

listen_addresses = 'localhost'

If you’re a Postgres guru, make any other changes you like. Then configure loggin in /etc/syslog.conf:

!postgres
*.* /var/log/pg.log

The log files must exist before syslogd will write to them.

# touch /var/log/pg.log
# chown pgsql:pgsql /var/log/pg.log

Now run /usr/local/etc/rc.d/postgresql start and check for errors. It should start without trouble, unless you mucked with your configuration too much.

Normally I would edit pg_hba.conf to tie my account password to PAM, and through PAM to LDAP, but as that’s broken right now, I’ll create a local user.

# su pgsql
$ createuser -sdrP mwlucas
Enter password for new role:
Enter it again:

Now create the Roundcube database, as per the instructions.

$ createuser roundcube
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
$ createdb -O roundcube -E UNICODE roundcubemail
$ psql roundcubemail
psql (8.4.7)
Type "help" for help.

roundcubemail=# alter user roundcube with password 'WeHatesWebmail';
ALTER ROLE
roundcubemail=# \c - roundcube
psql (8.4.7)
You are now connected to database "roundcubemail" as user "roundcube".
roundcubemail=> \i SQL/postgres.initial.sql

After a bunch of SQL spammage, we have a database. Log out with:

roundcubemail=> \q

Roundcube needs two configuration files, db.inc.php and main.inc.php. Go to /usr/local/www/roundcube/config and copy the .dist versions of these files.

Tell Roundcube where to find its database by setting a DSN in db.inc.php.

$rcmail_config['db_dsnw'] = 'pgsql://roundcube:WeHatesWebmail@localhost/roundcubemail';

In main.inc.php, set your mail server. (If you don’t set a mail server, Roundcube will let you connect to any mail server you like. This would confuse my users. Confusion leads to phone calls, something I avidly avoid.)

$rcmail_config['default_host'] = 'mail.domain.com';

Verify that pgsql and Apache are running, and browse to your webmail site. You have a webmail server!

Overall, the setup was pretty straightforward. I’m not saying I’ll keep Roundcube, but my test users are basically content, so it’s a strong candidate.

Stalk me on social media

One Reply to “Roundcube/pgsql on FreeBSD”

Comments are closed.