Demonstration of using SQL Protect with that Postgres cluster

Objective

Quickly (less than five minutes) install and run EPAS 9.5/9.6 on RHEL 5/6 and demonstrate using SQL Protect with that Postgres cluster.

Prerequisites

Running instance of RHEL 6/7 with root access

Overview

SQL Protect is a tool that helps prevent SQL Injection attacks.  SQL Protect can be run in three modes:

• learn - keeps track of what tables users query to learn what is considered normal
• passive - logs suspicious behavior to be looked into, but doesn't stop suspicious queries
• active - logs and denies suspicious behavior

The general steps for using SQL Protect (which will be demonstrated below) are as follows:

• Configure SQL Protect infrastructure
• Add users/roles to be protected
• Run in learn mode to learn what tables users normally query
• Run in passive mode if you want to log suspicious behavior, but not stop it
• Run in active mode if you want to deny suspicious behavior
• Regularly view the status table to see what behavior has been reported
• Clean/delete the history if deemed not to be an issue

Steps (as root):

#!/bin/bash
#
# Setup YUM repository for installing EPAS
#
rpm -Uvh
#
# Set YUM username/password in edb.repo
#
export YUM_USER=<yum user>
export YUM_PASSWORD=<yum password>
sed -i "s/<username>:<password>/$YUM_USER:$YUM_PASSWORD/g" /etc/yum.repos.d/edb.repo

#
# Enable the EPAS 9.5 repo in edb.repo
#
sed -i "\/ppas95/,/gpgcheck/ s/enabled=0/enabled=1/" /etc/yum.repos.d/edb.repo

#
# Install EPAS 9.5
#
yum -y install ppas95-server

#
# Start the EPAS server and initialize a new Postgres
# data directory using the appropriate mechanism
# for the operating system
#
if [ `cat /etc/redhat-release | grep "release 7" | wc -l` = 1 ]
  then
    /usr/lib/systemd/system/ppas-9.5.sh initdb
    systemctl start ppas-9.5.service
elif [ `cat /etc/redhat-release | grep "release 6" | wc -l` = 1 ]
  then
    service ppas-9.5 initdb
    service ppas-9.5 start
else
  su - enterprisedb -c "/usr/ppas-9.5/bin/initdb -D /var/lib/ppas/9.5/data"
  su - enterprisedb -c "/usr/ppas-9.5/bin/pg_ctl -w -D /var/lib/ppas/9.5/data start"
fi

#
# Add the sqlprotect shared library, allow users to connect without
# a password (just to make the script below easier) and restart the database.
#
sed -i "s/shared_preload_libraries = '/shared_preload_libraries = '\$libdir\/sqlprotect,/" /var/lib/ppas/9.5/data/postgresql.conf
sed -i "s/peer/trust/g" /var/lib/ppas/9.5/data/pg_hba.conf
service ppas-9.5 restart

#
# Connect to the database as enterprisedb and start setting up the scenario
#
su - enterprisedb -c "psql -d edb"

--
-- Run the sqlprotect SQL to setup the infrastructure for SQL Protect
--
\i /usr/ppas-9.5/share/contrib/sqlprotect.sql

--
-- Create a user that will act as the normal user that
-- our application is connecting as.
--
CREATE USER appuser identified by appuser;

--
-- Turn on SQL Protect in learn mode
--
ALTER SYSTEM SET edb_sql_protect.enabled = on;
ALTER SYSTEM SET edb_sql_protect.level = learn;
SELECT pg_reload_conf();

--
-- Add appuser as a protected user, and show
-- that appuser is the only protected user.
--
SELECT sqlprotect.protect_role('appuser');
SELECT * FROM sqlprotect.edb_sql_protect;
SELECT * FROM sqlprotect.list_protected_users;

--
-- Connect as the application user.
--
\c edb appuser

--
-- Create two tables. T1 will simulate normal tables
-- that should be queried. T2 will simulate a table that
-- should not normally be queried by the app user user.
-- You'll notice that in learn mode this is allowed, but in
-- active mode the user can't create new tables.
--
CREATE TABLE t1 (name TEXT);
CREATE TABLE t2 (name TEXT);

--
-- Teach SQL protect what is normal. Generally this would
-- include much more than a couple queries, it would include
-- something like running a unit test or integrated test that
-- exercises the queries typically done by the system. Notice
-- that we will not be selecting from t2 in learn mode.
--
SELECT * FROM t1;

--
-- Now that the system has learned what is normal, let's
-- become enterprisedb and turn the system into passive
-- mode, meaning that it will notify us of suspicious
-- behavior, but not block it. When reloaded, switch back
-- to the application user.
--
\c edb enterprisedb
ALTER SYSTEM SET edb_sql_protect.level = passive;
SELECT pg_reload_conf();
\c edb appuser

--
-- The same queries work fine but the last two
-- queries with suspicious where clauses and queries
-- against new tables raise warnings.
--
SELECT * FROM t1;
SELECT * FROM t1 WHERE 'x' = 'x';
DELETE FROM t1;
SELECT * FROM t2;

--
-- Now, when we are pretty sure that only bad behavior will
-- raise errors, we can turn on active mode.
--
\c edb enterprisedb

ALTER SYSTEM SET edb_sql_protect.level = active;
SELECT pg_reload_conf();
\c edb appuser

--
-- Now notice that the suspicious queries are blocked, not
-- just logged.
--
SELECT * FROM t1;
SELECT * FROM t1 WHERE 'x' = 'x';
DELETE FROM t1;
SELECT * FROM t2;

--
-- Now, let's switch back enterprisedb and look at some of the
-- SQL Protect history that was logged including how many suspicion
-- events occurred and what they were.
--
\c edb enterprisedb
SELECT * FROM sqlprotect.edb_sql_protect_stats;
SELECT * FROM sqlprotect.edb_sql_protect_queries;

--
-- To clear the history of suspicious behavior, just drop the statistics.
-- and note that the stats and logged queries have been removed.
--
SELECT sqlprotect.drop_stats('appuser');
SELECT sqlprotect.drop_queries('appuser');

SELECT * FROM sqlprotect.edb_sql_protect_stats;
SELECT * FROM sqlprotect.edb_sql_protect_queries;

--
-- Congratulations! You have successfully enabled and configured SQL Protect to
-- decrease risk of SQL Injection attacks!
--
exit