Use this script to update the amavisnewsql database with email users stored in the ispCP database. It's based loosely on this How-To: http://www.uno-code.com/?q=node/27 When executed it syncronizes the Amavis DB with email users stored in the ispCP DB, adding or removing users in the Amavis DB. It should be run whenever there are changes in the ispCP DB, i.e. when email accounts are addedd/removed in the ispCP frontend. I added an entry to the ispcp-serv-mngr script in the MTA section, but I am sure there are cleaner ways to do this.

updateamavisusers.php:

#!/usr/bin/php
<?
 
DEFINE ("BASEINCLUDE", "/var/www/ispcp/gui/tools/webmail/plugins/amavisnewsql/");
include(BASEINCLUDE."config.php");
include(BASEINCLUDE."amavisnewsql.class.php");
 
#Here you need to specify the ispCP database to be used.
 
$DBserver       = "localhost";
$DBname         = "ispcp";
$DBuser         = "amavis";
$DBpassword     = preg_replace("/@.*/","",preg_replace("/mysql:\/\/.*:/","",$CONFIG["dsn"]));
 
 
$dbfp = new AmavisNewSQL($CONFIG);
$dbfp->connect();
 
$amavisusers = getAmavisUsers();
$domains = array();
$ispcpusers = array();
 
 
if(!($connect = mysql_connect($DBserver, $DBuser, $DBpassword))) {
        echo "Error Connecting to the Database.";
        //echo mysql_error();
        exit();
}
$DB             = mysql_select_db($DBname);
$sql            = "SELECT * FROM mail_users WHERE mail_acc <> ''";
if(!$rs = mysql_query($sql,$connect)) {
        echo "Database Error";
        exit();
}
 
# Check for and add new users to the amavis db
 
while($row = mysql_fetch_object($rs)) {
        $email  = $row->mail_acc;
	$email_forward = $row->mail_forward;
	$domain_id = $row->domain_id;
 
	if ($email_forward == "_no_" ) {
 
		if (!$rs3 = mysql_query("SELECT * FROM domain WHERE domain_id = $domain_id",$connect)) {
	        	echo "Database Error";
        		exit();
		}
 
		$row3 = mysql_fetch_object($rs3);
		$domain = $row3->domain_name;
		$fullemail = "$email@$domain";
 
		array_push($ispcpusers,$fullemail);
 
		if (!in_array($fullemail,$amavisusers) ) {
	       		system(BASEINCLUDE."utils/addamavisuser.php $fullemail ' ' $fullemail");
		} 
 
	}
}
 
# Remove non-existing users form the amavis db
 
for ($i=0; $i < count($amavisusers); $i++) {
	if( !in_array($amavisusers[$i], $ispcpusers) ) {
		system(BASEINCLUDE."utils/deleteamavisuser.php $amavisusers[$i]");
	}
}
 
mysql_free_result($rs);
mysql_free_result($rs3);
 
print("The database was updated.\n"); 
 
function getAmavisUsers() {
   global $dbfp, $CONFIG;
 
   $users = array();
 
      $q = "select username from $dbfp->users_table where username <> ''";
 
      if(!$res = $dbfp->sqlRead($q, __FILE__, __LINE__)) die ($dbfp->error);
 
      if($res->numRows() != 0) {
         for($i=0; $i < $res->numRows(); $i++) {
             $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
             array_push($users, $row[username]);
         }
      }
 
   return $users;
 
}
 
?>