Connecting with SQLGrey

scacchicgardenSoftware and s/w Development

Dec 13, 2013 (3 years and 9 months ago)

151 views

Connecting with 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
1/5

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.
root# su - zimbra
zimbra$ zmprov mcf +zimbraMtaRestriction "check_policy_service
inet:127.0.0.1:2501"
Edit /opt/zimbra/conf/postfix_recipient_restrictions.cf
...
%%contains VAR:zimbraMtaRestriction check_policy_service inet:127.0.0.1:2501%%
reject_unauth_destination
permit
Copy and modify the name of archives (/etc/sqlgrey/):
clients_fqdn_whitelist
clients_ip_whitelist
to
clients_fqdn_whitelist.local
clients_ip_whitelist.local
Now restart Zimbra MTA, and it should all just work!
root# su - zimbra
zimbra$ zmmtactl stop
zimbra$ zmmtactl start

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).<br> 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 [
http://http://www.vanheusden.com/sgwi/
here] and untar-gz. <pre>
curl -O http://www.vanheusden.com/sgwi/sqlgreywebinterface-0.6.tgz
tar zxf sqlgreywebinterface-0.6.tgz
mv sqlgreywebinterface-0.6 sgwi

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

</pre>
Note: you may want to setup a virtual host for sgwi later on after the
initial install.
<br><br> 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:<br>
2/5
We change config.inc.php to just contain the following (delete any other lines): <pre>
<?
$db_db = "sqlgrey";
$db_type = "sqlite";
?>

</pre> And we change db.inc.php to look like this (we will address loading the sqlite3.so module in the next
step): <pre>
<?
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);
3/5
/opt/zimbra/php-5.0.5/lib/php/extensions/no-debug-non-zts-20041030/

</pre>
We need to add the sqlite3.so module to /opt/zimbra/conf/php.ini: <pre>
Near:
;;;;;;;;;;;;;;;;;;;;;;
; Dynamic Extensions ;
;;;;;;;;;;;;;;;;;;;;;;
Add:
extension=sqlite3.so
Leave extension_dir directive as it is:
extension_dir = "./"
zmapachectl restart

</pre>
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: <pre>
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

</pre> 6.) At this point you should be able to hit sgwi at this URL: <pre>
http://<YOURHOSTNAME>:7780/sgwi/index.php

</pre> 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)
5/5