Configure Squirrelmail to Store Prefs and Address Books in Mysql

Update Toaster-Watcher Config

Note: I'm not sure what exactly this does - can someone else comment on this?

1. Edit toaster-watcher.conf.

2. Enable the Squirrelmail SQL setting:

install_squirrelmail_sql           = 1       # use MySQL for user prefs and address book?

3. Save and exit the file.

Create the Database

1. Log into mysql:

mysql -u root -p

2. Create the database:

CREATE DATABASE squirrelmail;

3. Create the database user:

Note: Set sqpassword to a password of your choosing.

GRANT select,insert,update,delete ON squirrelmail.*
TO squirreluser@localhost IDENTIFIED BY 'sqpassword';

Create tables

4. Create the table that will store user's address books:

CREATE TABLE address (
  owner varchar(128) DEFAULT '' NOT NULL,
  nickname varchar(16) DEFAULT '' NOT NULL,
  firstname varchar(128) DEFAULT '' NOT NULL,
  lastname varchar(128) DEFAULT '' NOT NULL,
  email varchar(128) DEFAULT '' NOT NULL,
  label varchar(255),
  PRIMARY KEY (owner,nickname),
  KEY firstname (firstname,lastname)
);

5. Create the table that will store the global address book:

CREATE TABLE global_abook (
  owner varchar(128) DEFAULT '' NOT NULL,
  nickname varchar(16) DEFAULT '' NOT NULL,
  firstname varchar(128) DEFAULT '' NOT NULL,
  lastname varchar(128) DEFAULT '' NOT NULL,
  email varchar(128) DEFAULT '' NOT NULL,
  label varchar(255),
  PRIMARY KEY (owner,nickname),
  KEY firstname (firstname,lastname)
);

6. Create the table that will store user preferences:

CREATE TABLE userprefs (
  user varchar(128) DEFAULT '' NOT NULL,
  prefkey varchar(64) DEFAULT '' NOT NULL,
  prefval BLOB DEFAULT '' NOT NULL,
  PRIMARY KEY (user,prefkey)
);

Configure Squirrelmail

1. Run the Squirrelmail configuration program:

cd /usr/local/www/squirrelmail
./configure

2. Configure Squirrelmail to use the database:

a. From the main menu, go to Database (Option 9).

b. Set all of the DSN's (1, 3 and 8) to the following:

Note: Change sqpassword to the one you configured when you created the database.

mysql://squirreluser:sqpassword@localhost/squirrelmail

c. Press S to save the configuration.

3. Press Q to quit the configuration utility.

Test!

General Squirrelmail Test

Browse to http://full.dns.of.toaster/squirrelmail/src/configtest.php. Review the page - you should see a section similar to:

Checking database functions...
     PHP Pear DB support is present.
    mysql database support present.
    preferences database connect successful.
    mysql database support present.
    addressbook database connect successful.
    mysql database support present.
    global addressbook database connect successful.

User Test

1. Log into Squirrelmail as a user.

2. Create an address book entry.

3. Log into mysql on the server.

4. Show the contents of the address table:

use squirrelmail;
select * from address;

5. You should see the entry that you just created.

External Links

"Using Database Backends" - Squirrelmail Docs.