Connecting with SQLGrey

From Zimbra :: Wiki

Jump to: navigation, search
   Article-alert.png  - This article is a community contribution and may include unsupported customizations for network edition customers.



SQLGrey

Download SQLGrey from: http://sqlgrey.sourceforge.net/

Note: This example use SQLite as DB for SQLGrey (you can use MySQL, but SQLite it's so easy)


Before install, you need some packages:

  * perl-Date-Calc (from Red Hat or CentOS CDs)
  * perl-Net-Server (from DAG repository: http://apt.sw.be/redhat/el4/en/i386/RPMS.dag/)
  * perl-IO-Multiplex  (from DAG repository: http://apt.sw.be/redhat/el4/en/i386/RPMS.dag/)
  * perl-DBD-SQLite (from DAG repository: http://apt.sw.be/redhat/el4/en/i386/RPMS.dag/)
  * sqlite (from DAG repository: http://apt.sw.be/redhat/el4/en/i386/RPMS.dag/)


If you have Red Hat 4 or CentOS 4, you can install them using APT (for Red Hat/CentOS 4):

   rpm -Uvh http://apt.sw.be/redhat/el4/en/i386/RPMS.dag/apt-0.5.15lorg3.2-1.el4.rf.i386.rpm
   echo "rpm http://apt.sw.be redhat/el4/en/i386 dag" >> /etc/apt/sources.list.d/os.list
   apt-get update
   apt-get install perl-Net-Server perl-IO-Multiplex perl-DBD-SQLite sqlite -y

Note: before using APT, you must install perl-Date-Calc from Red Hat or CentOS CDs.


Install SQLGrey RPM:

  root# rpm -ivh sqlgrey-x.x.x-x.noarch.rpm


Configure SQLGrey: /etc/sqlgrey/sqlgrey.conf

  conf_dir = /etc/sqlgrey
  user = sqlgrey
  group = sqlgrey
  inet = 2501
  confdir = /etc/sqlgrey
  db_type = SQLite
  db_name = sqlgrey


Start SQLGrey:

  root# service sqlgrey start
  root# chkconfig sqlgrey on


Connecting SQLGrey with Zimbra Postfix

As the zimbra user, update zimbraMtaRestriction. I've added all the other restrictions that Zimbra suggests to add as well, including several Realtime Blackhole Lists (RBL). The important one is at the end. For 3.x Zimbra versions:

   root# su - zimbra
   zimbra$ zmprov mcf  zimbraMtaRestriction "check_policy_service inet:127.0.0.1:2501"

If you're using the lastest (4.x) Zimbra version:

   root# su - zimbra
   zimbra$ zmprov -l
   prov> mcf  zimbraMtaRestriction "check_policy_service inet:127.0.0.1:2501"
   prov> quit


Edit /opt/zimbra/conf/postfix_recipient_restrictions.cf

  ...
  reject_unauth_destination
  %%contains VAR:zimbraMtaRestriction check_policy_service inet:127.0.0.1:2501%%
  permit


Now restart zimbra, and it should all just work!

  root# su - zimbra
  zimbra$ zmcontrol stop
  zimbra$ zmcontrol start


Good luck, and enjoy.


--
Daniel Eugenin M.
IT Linux Ltda.

Enabling SQLGrey Web Interface for Zimbra 4.0.2 on RHEL4ES 32 bit

SSH to your Zimbra server (the main server that serves web pages if you have more than one in your cluster).
You'll need root privileges for some of these steps, I personally su -'ed myself while I did this, you might like to sudo each command where needed instead.

1.) Download sgwi here and untar-gz.

	curl -O http://www.vanheusden.com/sgwi/sqlgreywebinterface-0.6.tgz
	tar zxf sqlgreywebinterface-0.6.tgz
	mv sqlgreywebinterface-0.6 sgwi

2.) At this point I moved the resulting sgwi folder into /opt/zimbra/httpd/htdocs

	mv sgwi /opt/zimbra/httpd/htdocs

Note: you may want to setup a virtual host for sgwi later on after the initial install.

3.) We need to make some edits to the config.inc.php and db.inc.php files to make sgwi use sqlite3 and SQLGrey's working DB:

We change config.inc.php to just contain the following (delete any other lines):

	<?
		$db_db	= "sqlgrey";
		$db_type = "sqlite";
	?>

And we change db.inc.php to look like this (we will address loading the sqlite3.so module in the next step):

	<?
	require "config.inc.php";
	dl('sqlite3.so');
	
	function do_query($query)
	{
			global $db_hostname, $db_user, $db_pass, $db_db, $db_type;
	
			/* Connecting, selecting database */
		if ($db_type == "mysql")
		{
			$link = mysql_connect($db_hostname, $db_user, $db_pass) or die("Could not connect to database");
			mysql_select_db($db_db) or die("Could not select database");
	
			$result = mysql_query($query) or die("Query failed");
	
			/* Closing connection */
			mysql_close($link);
		}
		else if ($db_type == "sqlite")
		{
			$db = $db_db;
			$handle = sqlite3_open($db) or die("Could not open database");
			$result = sqlite3_query($handle, $query) or die("Error in query: ".sqlite_error_string(sqlite_last_error($handle)));
			//sqlite3_close($handle);
		}
	
		else
		{
			$link = pg_connect("host=$db_hostname dbname=$db_db user=$db_user password=$db_pass") or die("Could not connect to database");
	
				$result = pg_query($link, $query) or die("Query failed");
	
			/* Closing connection */
			pg_close($link);
		}
	
			return $result;
	}
	
	function fetch_row($result)
	{
		global $db_type;
	
		if ($db_type == "mysql")
		{
			return mysql_fetch_array($result, MYSQL_ASSOC);
		}
		else if ($db_type == "sqlite")
		{
			return sqlite3_fetch_array($result);
		}
		else
		{
			return pg_fetch_assoc($result);
		}
	}
	?>

4.) Since SQLGrey requires sqlite 3.x we need to build a loadable module for the PHP that comes with Zimbra (5.0.5) since the built-in sqlite support is only for 2.x:

The instructions tell us to use "phpize" to prepare the build information for the module. Since the average regular Zimbra install does not come with the 5.0.5 version of php-devel, we need to install it.

  • Download php-devel 5.0.5 here (or use your own favorite rpm repository)
  • Install php-devel, ignoring dependencies
	rpm -i --nodeps php-devel-5.0.5-2.2.i386.rpm

Note: Make sure to only install version 5.0.5 -- otherwise the API versions of Zimbra's PHP and php-sqlite3 won't match and the module won't load.

Now we can follow the instructions for php-sqlite3:

	cd sqlite3-0.4
	phpize
	./configure --with-sqlite3=/path/to/sqlite3 (ours should be at /usr/local/lib)
	make && make install

We need to move the resulting sqlite3.so module to where Zimbra's PHP will be able to find it, which for some reason is a bit of an odd path that we need to create first (there are reasons I am sure):

	mkdir /opt/zimbra/php-5.0.5/lib/php/extensions/no-debug-non-zts-20041030
	mv /usr/lib/sqlite3.so /opt/zimbra/php-5.0.5/lib/php/extensions/no-debug-non-zts-20041030/

We need to add the sqlite3.so module to /opt/zimbra/conf/php.ini:

	Near:
	;;;;;;;;;;;;;;;;;;;;;;
	; Dynamic Extensions ;
	;;;;;;;;;;;;;;;;;;;;;;
	
	Add:
	extension=sqlite3.so
	
	Leave extension_dir directive as it is:
	extension_dir = "./"
	
	zmapachectl restart

5.) Since sgwi doesn't seem happy when trying to point it to sqlgrey's absolute DB-file path which on my system is /home/sqlgrey/sqlgrey, we'll need to bring the DB file to where sgwi can find it:

	Change /etc/sqlgrey/sqlgrey.conf to change the location of the file:
		db_name = /opt/zimbra/httpd/htdocs/sgwi/sqlgrey

	Shutdown sqlgrey for a brief moment:
		service sqlgrey stop

	Move the current db file:
		mv /home/sqlgrey/sqlgrey /opt/zimbra/httpd/htdocs/sgwi/

	Start sqlgrey back up:
		service sqlgrey start

6.) At this point you should be able to hit sgwi at this URL:

	http://<YOURHOSTNAME>:7780/sgwi/index.php

If everything went right, you should be seeing something like this:


SQLGrey webinterface

waiting (hosts/domains that are greylisted) [33]
greylisted e-mail addresses/domains (hosts/domains that are whitelisted)
optout domain (domains that are always treated as valid)
optout email (see optout domain)
optin domain (these domains are always greylisted unless they are in the optout domain table)
optin email (see optin domain)


And that's it!

Feel free to change/add to these instructions for your platform!
Cheers,

Pepijn Bruienne.


Verified Against: unknown Date Created: 7/20/2006
Article ID: http://wiki.zimbra.com/index.php?title=Connecting_with_SQLGrey Date Modified: 04/28/2008
Personal tools