Install the database firewall GreenSQL on Debian

This howto is tested on:

• Debian 6.0 Squeeze

Warning

GreenSQL is a powerfull tool to block SQL injections. However, it is very restrictive. Its implementation is complex and labor intensive to create whitelist rules per application.

GreenSQL is an open-source software up to version 1.3.0 witch is used in this guide. The installation of the last free version of this tool need to register on the editor website.

Prerequisites

This howto needs the mysql-tools script available in the howto Install and setup MySQL on Debian.

Parameters

Provide the number of the version to install:

VERSION="1.3.0"

Installation

Download the Debian package fitting your architecture:

ARCH="$(command dpkg --print-architecture)"
OS="Debian_5.0"
if [ -z "$(command apt-cache search libevent1)" ]; then
  # For Debian Squeeze (and Ubuntu).
  OS="xUbuntu_10.04"
fi
command wget "http://www.greensql.net/download/get?os=${OS}&platform=${ARCH}&filename=greensql-fw_${VERSION}_${ARCH}.deb" \
    --output-document="/tmp/greensql-fw.deb"

Install software dependencies:

if [ -z "$(command apt-cache search libevent1)" ]; then
  # For Debian Squeeze (and Ubuntu).
  command apt-get install libevent-1.4-2
else
  command apt-get install libevent1
fi

Create the greensql system user (the user created by the Debian package is not a system one):

command adduser --system --shell /bin/sh --home /var/lib/greensql greensql

Install the software:

DEBIAN_FRONTEND='noninteractive' command dpkg -i "/tmp/greensql-fw.deb"

Create a MySQL database for GreenSQL configuration (ask for the MySQL "root" account password):

MYSQL_PARAMS=$(command mysql-tools create-db GREENSQL)

Fetch the database connexion parameters:

MYSQL_HOST="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_HOST" \
    | cut --delimiter="=" --fields="2-")"
MYSQL_DB="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_DB" \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_USER" \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(echo "${MYSQL_PARAMS}" | command grep -e "^MYSQL_PASSWORD" \
    | cut --delimiter="=" --fields="2-")"
echo "${MYSQL_PARAMS}"

Initialize GreenSQL database contents:

command mysql --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" \
     --host="${MYSQL_HOST}" "${MYSQL_DB}" < "/usr/share/doc/greensql-fw/greensql-mysql-db.txt"

Update GreenSQL configuration file:

command sed -i \
    -e "s/.*dbhost.*/dbhost=${MYSQL_HOST}/" \
    -e "s/.*dbname.*/dbname=${MYSQL_DB}/" \
    -e "s/.*dbuser.*/dbuser=${MYSQL_USER}/" \
    -e "s/.*dbpass.*/dbpass=${MYSQL_PASSWORD}/" \
  "/etc/greensql/greensql.conf"

Restart the GreenSQL server:

/etc/init.d/greensql-fw restart

GreenSQL is ready and listen on port 3305.

Further reading

Disable PostgreSQL proxy

If you don't want to use GreenSQL for a PostgreSQL server, disable the PostgreSQL related GreenSQL configuration:

MYSQL_HOST="$(command grep -e "^dbhost" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_DB="$(command grep -e "^dbname" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(command grep -e "^dbuser" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(command grep -e "^dbpass" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PORT="$(command grep -e "^dbport" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
if [ -z "${MYSQL_PORT}" ]; then
  MYSQL_PORT="3306"
fi
command mysql --host="${MYSQL_HOST}" --port="${MYSQL_PORT}" \
   --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" "${MYSQL_DB}" \
   --execute="DELETE FROM proxy WHERE dbtype='pgsql';"
/etc/init.d/greensql-fw restart

Setup GreenSQL on MySQL default listening port

If you want GreenSQL to listen on MySQL default port, in order to systematize its use, swap the listening ports of MySQL and GreenSQL:

command sed -i -e '/\[mysqld\]/,/^\(port[\t ]*=\).*$/{s/^\(port[\t ]*=\).*/\1 3305/}' \
    "/etc/mysql/my.cnf"
MYSQL_HOST="$(command grep -e "^dbhost" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_DB="$(command grep -e "^dbname" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(command grep -e "^dbuser" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(command grep -e "^dbpass" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PORT="$(command grep -e "^dbport" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
if [ -z "${MYSQL_PORT}" ]; then
  MYSQL_PORT="3306"
fi
command mysql --host="${MYSQL_HOST}" --port="${MYSQL_PORT}" \
   --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" "${MYSQL_DB}" \
   --execute="UPDATE proxy SET frontend_port=3306, backend_port=3305 WHERE dbtype='mysql';"
command sed -i -e 's/.*dbport.*/dbport=3305/' "/etc/greensql/greensql.conf"
/etc/init.d/greensql-fw stop
/etc/init.d/mysql restart
/etc/init.d/greensql-fw start

Allow network access to MySQL through GreenSQL

By default, the MySQL server is only available for the local host. To allow other host to use its databases, set up GreenSQL to listen on all network interfaces:

MYSQL_HOST="$(command grep -e "^dbhost" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_DB="$(command grep -e "^dbname" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(command grep -e "^dbuser" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(command grep -e "^dbpass" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PORT="$(command grep -e "^dbport" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
if [ -z "${MYSQL_PORT}" ]; then
  MYSQL_PORT="3306"
fi
command mysql --host="${MYSQL_HOST}" --port="${MYSQL_PORT}" \
   --user="${MYSQL_USER}" --password="${MYSQL_PASSWORD}" "${MYSQL_DB}" \
   --execute="UPDATE proxy SET frontend_ip='0.0.0.0' WHERE dbtype='mysql';"
/etc/init.d/greensql-fw restart

Enable GreenSQL web administration

Install a HTTP server if necessary. For example; install LigHTTPd as presented in Install LigHTTPd On Debian.

Copy the GreenSQL web administration application sources:

command cp -r "/usr/share/greensql-fw" "/opt/greensql-fw"

Create a cache folder for the application:

command mkdir --parent "/var/cache/greensql-fw"
command chown -R www-data:www-data "/var/cache/greensql-fw"

Setup the administration application to connect to the GreenSQL configuration database:

MYSQL_DB="$(command grep -e "^dbname" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_USER="$(command grep -e "^dbuser" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
MYSQL_PASSWORD="$(command grep -e "^dbpass" /etc/greensql/greensql.conf \
    | cut --delimiter="=" --fields="2-")"
command sed -i \
    -e "s/^\$db_name.*/\$db_name = \"${MYSQL_DB}\";/" \
    -e "s/^\$db_user.*/\$db_user = \"${MYSQL_USER}\";/" \
    -e "s/^\$db_pass.*/\$db_pass = \"${MYSQL_PASSWORD}\";/" \
    -e 's|^$cache_dir.*|$cache_dir = "/var/cache/greensql-fw";|' \
  "/opt/greensql-fw/config.php"

Add the administration application to a HTTP server virtual host. If LigHTTPd is the HTTP server, you can use:

if [ -d /etc/lighttpd/conf-available ]; then
  command echo '# Alias for greensql-fw directory
alias.url += ( 
  "/greensql-fw" => "/opt/greensql-fw/",
)' > /etc/lighttpd/conf-available/50-greensql-fw.conf
  command lighty-enable-mod greensql-fw
  /etc/init.d/lighttpd force-reload
fi

The GreenSQL administration interface is not available at the URL provided by (adjust the result to your environment):

echo "http://${HOSTNAME}/greensql-fw"

The default login is:

• Username : admin
• Password : pwd

Please choose a new password at your first connexion.

Thanks

• Thanks to GreenSQL developers.