cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Protect Quickstart

EDB Team Member

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 http://yum.enterprisedb.com/edbrepos/edb-repo-9.6-4.noarch.rpm

#
# 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

 

Tips

  • More detailed information is available in Section 4 of the EDB™ Postgres (Postgres Plus) Enterprise Edition Guide v9.5 available here:

  • Please note the comments below from the documentation if you are using pg_dump and pg_restore for backup/recovery.  The following does not apply if you are doing backup/restore with BART or pg_basebackup.
    • SQL/Protect uses two tables, edb_sql_protect and edb_sql_protect_rel, to store information on database objects such as databases, roles, and relations. References to these database objects in these tables are done using the objects’ OIDs, and not the objects’ text names. The OID is a numeric data type used by Postgres Plus Advanced Server to uniquely identify each database object.
    • When a database object is created, Postgres Plus Advanced Server assigns an OID to the object, which is then used whenever a reference is needed to the object in the database catalogs. If you create the same database object in two databases, such as a table with the same CREATE TABLE statement, each table is assigned a different OID in each database.
    • If you are using pg_dump/pg_restore to backup/restore the database, this results in the re-creation of the backed up database objects, the restored objects end up with different OIDs in the new database than what they were assigned in the original database.

Summary

This guide is intended to get you up and started with a simple SQL Protect example as quickly as possible.  There is much more that can be done with the tool as documented in the Enterprise Edition Guide listed above.