Configuring SQL/Protect v14

Ensure the following prerequisites are met before configuring SQL/Protect:

The library file (sqlprotect.so on Linux, sqlprotect.dll on Windows) necessary to run SQL/Protect should be installed in the lib subdirectory of your EDB Postgres Advanced Server home directory. For Windows, this should be done by the EDB Postgres Advanced Server installer. For Linux, install the edb-asxx-server-sqlprotect RPM package where xx is the EDB Postgres Advanced Server version number.
You also need the SQL script file sqlprotect.sql located in the share/contrib subdirectory of your EDB Postgres Advanced Server home directory.
You must configure the database server to use SQL/Protect, and you must configure each database that you want SQL/Protect to monitor:
- The database server configuration file, postgresql.conf, must be modified by adding and enabling configuration parameters used by SQL/Protect.
- Database objects used by SQL/Protect must be installed in each database that you want SQL/Protect to monitor.

Step 1: Edit the following configuration parameters in the postgresql.conf file located in the data subdirectory of your EDB Postgres Advanced Server home directory.

shared_preload_libraries. Add $libdir/sqlprotect to the list of libraries.
edb_sql_protect.enabled. Controls whether or not SQL/Protect is actively monitoring protected roles by analyzing SQL statements issued by those roles and reacting according to the setting of edb_sql_protect.level. When you are ready to begin monitoring with SQL/Protect set this parameter to on. If this parameter is omitted, the default is off.
edb_sql_protect.level. Sets the action taken by SQL/Protect when a SQL statement is issued by a protected role. If this parameter is omitted, the default behavior is passive. Initially, set this parameter to learn.
See Setting the Protection Level for more information.
edb_sql_protect.max_protected_roles. Sets the maximum number of roles that can be protected. If this parameter is omitted, the default setting is 64.
edb_sql_protect.max_protected_relations. Sets the maximum number of relations that can be protected per role. If this parameter is omitted, the default setting is 1024.
The total number of protected relations for the server is the number of protected relations times the number of protected roles. Every protected relation consumes space in shared memory. The space for the maximum possible protected relations is reserved during database server startup.
edb_sql_protect.max_queries_to_save. Sets the maximum number of offending queries to save in the edb_sql_protect_queries view. If this parameter is omitted, the default setting is 5000. If the number of offending queries reaches the limit, additional queries are not saved in the view, but are accessible in the database server log file.
The minimum valid value for this parameter is 100. If a value less than 100 is specified, the database server starts using the default setting of 5000. A warning message is recorded in the database server log file.

The following example shows the settings of these parameters in the postgresql.conf file:

shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/sqlprotect'
                                        # (change requires restart)
                          .
                          .
                          .
edb_sql_protect.enabled = off
edb_sql_protect.level = learn
edb_sql_protect.max_protected_roles = 64
edb_sql_protect.max_protected_relations = 1024
edb_sql_protect.max_queries_to_save = 5000

Step 2: Restart the database server after you have modified the postgresql.conf file.

On Linux: Invoke the EDB Postgres Advanced Server service script with the restart option.

On a Redhat or CentOS 7.x installation, use the command:

systemctl restart edb-as-14

On Windows: Use the Windows Services applet to restart the service named edb-as-14.

Step 3: For each database that you want to protect from SQL injection attacks, connect to the database as a superuser (either enterprisedb or postgres, depending upon your installation options) and run the script sqlprotect.sql located in the share/contrib subdirectory of your EDB Postgres Advanced Server home directory. The script creates the SQL/Protect database objects in a schema named sqlprotect.

The following example shows this process to set up protection for a database named edb:

$ /usr/edb/as14/bin/psql -d edb -U enterprisedb
Password for user enterprisedb:
psql.bin (14.0.0, server 14.0.0)
Type "help" for help.

edb=# \i /usr/edb/as14/share/contrib/sqlprotect.sql
CREATE SCHEMA
GRANT
SET
CREATE TABLE
GRANT
CREATE TABLE
GRANT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
DO
CREATE FUNCTION
CREATE FUNCTION
DO
CREATE VIEW
GRANT
DO
CREATE VIEW
GRANT
CREATE VIEW
GRANT
CREATE FUNCTION
CREATE FUNCTION
SET

Selecting roles to protect

After the SQL/Protect database objects have been created in a database, you can select the roles for which SQL queries are to be monitored for protection, and the level of protection that is assigned to each role.

Setting the protected roles list

For each database that you want to protect, you must determine the roles you want to monitor and then add those roles to the protected roles list of that database.

Step 1: Connect as a superuser to a database that you wish to protect with either psql or Postgres Enterprise Manager Client:

$ /usr/edb/as14/bin/psql -d edb -U enterprisedb
Password for user enterprisedb:
psql.bin (14.0.0, server 14.0.0)
Type "help" for help.

edb=#

Step 2: Since the SQL/Protect tables, functions, and views are built under the sqlprotect schema, use the SET search_path command to include the sqlprotect schema in your search path. This eliminates the need to schema-qualify any operation or query involving SQL/Protect database objects:

edb=# SET search_path TO sqlprotect;
SET

Step 3: Each role that you wish to protect must be added to the protected roles list. This list is maintained in the table edb_sql_protect.

To add a role, use the function protect_role('rolename'). The following example protects a role named appuser:

edb=# SELECT protect_role('appuser');
 protect_role
--------------

(1 row)

You can list the roles that have been added to the protected roles list by issuing the following query:

edb=# SELECT * FROM edb_sql_protect;
 dbid  | roleid | protect_relations | allow_utility_cmds | allow_tautology |
 allow_empty_dml
-------+--------+-------------------+--------------------+-----------------+--
-------------
 13917 |  16671 | t                 | f                  | f               | f
(1 row)

A view is also provided that gives the same information using the object names instead of the Object Identification numbers (OIDs):

edb=# \x
Expanded display is on.
edb=# SELECT * FROM list_protected_users;
-[ RECORD 1 ]------+--------
dbname             | edb
username           | appuser
protect_relations  | t
allow_utility_cmds | f
allow_tautology    | f
allow_empty_dml    | f

Setting the protection level

The edb_sql_protect.level configuration parameter sets the protection level, which defines the behavior of SQL/Protect when a protected role issues a SQL statement. The defined behavior applies to all roles in the protected roles lists of all databases configured with SQL/Protect in the database server.

The edb_sql_protect.level configuration parameter (in the postgresql.conf file) can be set to one of the following values to use either learn mode, passive mode, or active mode:

learn. Tracks the activities of protected roles and records the relations used by the roles. This is used when initially configuring SQL/Protect so the expected behaviors of the protected applications are learned.
passive. Issues warnings if protected roles are breaking the defined rules, but does not stop any SQL statements from executing. This is the next step after SQL/Protect has learned the expected behavior of the protected roles. This essentially behaves in intrusion detection mode and can be run in production when properly monitored.
active. Stops all invalid statements for a protected role. This behaves as a SQL firewall preventing dangerous queries from running. This is particularly effective against early penetration testing when the attacker is trying to determine the vulnerability point and the type of database behind the application. Not only does SQL/Protect close those vulnerability points, but it tracks the blocked queries allowing administrators to be alerted before the attacker finds an alternate method of penetrating the system.

If the edb_sql_protect.level parameter is not set or is omitted from the configuration file, the default behavior of SQL/Protect is passive.

If you are using SQL/Protect for the first time, set edb_sql_protect.level to learn.

Monitoring protected roles

Once you have configured SQL/Protect in a database, added roles to the protected roles list, and set the desired protection level, you can then activate SQL/Protect in either learn mode, passive mode, or active mode. You can then start running your applications.

With a new SQL/Protect installation, the first step is to determine the relations that protected roles should be permitted to access during normal operation. Learn mode allows a role to run applications during which time SQL/Protect is recording the relations that are accessed. These are added to the role’s protected relations list stored in table edb_sql_protect_rel.

Monitoring for protection against attack begins when SQL/Protect is run in passive or active mode. In passive and active modes, the role is permitted to access the relations in its protected relations list as these were determined to be the relations the role should be able to access during typical usage.

However, if a role attempts to access a relation that is not in its protected relations list, a WARNING or ERROR severity level message is returned by SQL/Protect. The role’s attempted action on the relation may or may not be carried out depending upon whether the mode is passive or active.

Learn mode

Step 1: To activate SQL/Protect in learn mode, set the parameters in the postgresql.conf file as shown below:

edb_sql_protect.enabled = on
edb_sql_protect.level = learn

Step 2: Reload the postgresql.conf file.

Choose Expert Configuration, then Reload Configuration from the EDB Postgres Advanced Server application menu.

For an alternative method of reloading the configuration file, use the pg_reload_conf function. Be sure you are connected to a database as a superuser and execute function pg_reload_conf as shown by the following example:

edb=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Step 3: Allow the protected roles to run their applications.

As an example the following queries are issued in the psql application by protected role appuser:

edb=> SELECT * FROM dept;
NOTICE:  SQLPROTECT: Learned relation: 16384
 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

edb=> SELECT empno, ename, job FROM emp WHERE deptno = 10;
NOTICE:  SQLPROTECT: Learned relation: 16391
 empno | ename  |    job
-------+--------+-----------
  7782 | CLARK  | MANAGER
  7839 | KING   | PRESIDENT
  7934 | MILLER | CLERK
(3 rows)

SQL/Protect generates a NOTICE severity level message indicating the relation has been added to the role’s protected relations list.

In SQL/Protect learn mode, SQL statements that are cause for suspicion are not prevented from executing, but a message is issued to alert the user to potentially dangerous statements as shown by the following example:

edb=> CREATE TABLE appuser_tab (f1 INTEGER);
NOTICE: SQLPROTECT: This command type is illegal for this user
CREATE TABLE
edb=> DELETE FROM appuser_tab;
NOTICE: SQLPROTECT: Learned relation: 16672
NOTICE: SQLPROTECT: Illegal Query: empty DML
DELETE 0

Step 4: As a protected role runs applications, the SQL/Protect tables can be queried to observe the addition of relations to the role’s protected relations list.

Connect as a superuser to the database you are monitoring and set the search path to include the sqlprotect schema:

edb=# SET search_path TO sqlprotect;
SET

Query the edb_sql_protect_rel table to see the relations added to the protected relations list:

edb=# SELECT * FROM edb_sql_protect_rel;
 dbid   | roleid | relid
--------+--------+-------
 13917  | 16671  | 16384
 13917  | 16671  | 16391
 13917  | 16671  | 16672
(3 rows)

The list_protected_rels view provides more comprehensive information along with the object names instead of the OIDs:

edb=# SELECT * FROM list_protected_rels;
 Database | Protected User | Schema |    Name     | Type  |    Owner
----------+----------------+--------+-------------+-------+------------
 edb      | appuser        | public | dept        | Table | enterprisedb
 edb      | appuser        | public | emp         | Table | enterprisedb
 edb      | appuser        | public | appuser_tab | Table | appuser
(3 rows)

Passive mode

Once you have determined that a role’s applications have accessed all relations they need, you can now change the protection level so that SQL/Protect can actively monitor the incoming SQL queries and protect against SQL injection attacks.

Passive mode is the less restrictive of the two protection modes, passive and active.

Step 1: To activate SQL/Protect in passive mode, set the following parameters in the postgresql.conf file as shown below:

edb_sql_protect.enabled = on
edb_sql_protect.level = passive

Step 2: Reload the configuration file as shown in Step 2 of the Learn Mode section.

Now SQL/Protect is in passive mode. For relations that have been learned such as the dept and emp tables of the prior examples, SQL statements are permitted with no special notification to the client by SQL/Protect as shown by the following queries run by user appuser:

edb=> SELECT * FROM dept;
 deptno |   dname    |   loc
--------+------------+----------
    10  | ACCOUNTING | NEW YORK
    20  | RESEARCH   | DALLAS
    30  | SALES      | CHICAGO
    40  | OPERATIONS | BOSTON
(4 rows)

edb=> SELECT empno, ename, job FROM emp WHERE deptno = 10;
 empno | ename  |    job
-------+--------+-----------
  7782 | CLARK  | MANAGER
  7839 | KING   | PRESIDENT
  7934 | MILLER | CLERK
(3 rows)

SQL/Protect does not prevent any SQL statement from executing, but issues a message of WARNING severity level for SQL statements executed against relations that were not learned, or for SQL statements that contain a prohibited signature as shown in the following example:

edb=> CREATE TABLE appuser_tab_2 (f1 INTEGER);
WARNING: SQLPROTECT: This command type is illegal for this user
CREATE TABLE
edb=> INSERT INTO appuser_tab_2 VALUES (1);
WARNING: SQLPROTECT: Illegal Query: relations
INSERT 0 1
edb=> INSERT INTO appuser_tab_2 VALUES (2);
WARNING: SQLPROTECT: Illegal Query: relations
INSERT 0 1
edb=> SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';
WARNING: SQLPROTECT: Illegal Query: relations
WARNING: SQLPROTECT: Illegal Query: tautology
f1
----
  1
  2
(2 rows)

Step 3: Monitor the statistics for suspicious activity.

By querying the view edb_sql_protect_stats, you can see the number of times SQL statements were executed that referenced relations that were not in a role’s protected relations list, or contained SQL injection attack signatures. See Attack Attempt Statistics for more information on view edb_sql_protect_stats.

The following is a query on edb_sql_protect_stats:

edb=# SET search_path TO sqlprotect;
SET
edb=# SELECT * FROM edb_sql_protect_stats;
 username  | superusers | relations | commands | tautology | dml
-----------+------------+-----------+----------+-----------+-----
 appuser   |     0      |     3     |    1     |     1     | 0
(1 row)

Step 4: View information on specific attacks.

By querying the edb_sql_protect_queries view, you can see the SQL statements that were executed that referenced relations that were not in a role’s protected relations list, or contained SQL injection attack signatures. See Attack Attempt Queries for more information on view edb_sql_protect_queries.

The following code sample shows a query on edb_sql_protect_queries:

edb=# SELECT * FROM edb_sql_protect_queries;
-[ RECORD 1 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:21:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (1);
-[ RECORD 2 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:21:00 -04:00
 query        | CREATE TABLE appuser_tab_2 (f1 INTEGER);
-[ RECORD 3 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:22:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (2);
-[ RECORD 4 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:22:00 -04:00
 query        | SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';

Note

The ip_address and port columns don't return any information if the attack originated on the same host as the database server using the Unix-domain socket (that is, pg_hba.conf connection type local).

Active mode

In active mode, disallowed SQL statements are prevented from executing. Also, the message issued by SQL/Protect has a higher severity level of ERROR instead of WARNING.

Step 1: To activate SQL/Protect in active mode, set the following parameters in the postgresql.conf file as shown below:

edb_sql_protect.enabled = on
edb_sql_protect.level = active

Step 2: Reload the configuration file as shown in Step 2 of the Learn Mode section.

The following example illustrates SQL statements similar to those given in the examples of Step 2 in Passive Mode, but executed by user appuser when edb_sql_protect.level is set to active:

edb=> CREATE TABLE appuser_tab_3 (f1 INTEGER);
ERROR: SQLPROTECT: This command type is illegal for this user
edb=> INSERT INTO appuser_tab_2 VALUES (1);
ERROR: SQLPROTECT: Illegal Query: relations
edb=> SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';
ERROR: SQLPROTECT: Illegal Query: relations

The following shows the resulting statistics:

edb=# SELECT * FROM sqlprotect.edb_sql_protect_stats;
 username  | superusers | relations | commands | tautology | dml
-----------+------------+-----------+----------+-----------+-----
 appuser   |      0     |     5     |     2    |     1     | 0
(1 row)

The following is a query on edb_sql_protect_queries:

edb=# SELECT * FROM sqlprotect.edb_sql_protect_queries;
-[ RECORD 1 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:21:00 -04:00
 query        | CREATE TABLE appuser_tab_2 (f1 INTEGER);
-[ RECORD 2 ]+---------------------------------------------
 username     | appuser
 ip_address   |
 port         |
 machine_name |
 date_time    | 20-JUN-14 13:22:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (2);
-[ RECORD 3 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | CREATE TABLE appuser_tab_3 (f1 INTEGER);
-[ RECORD 4 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | INSERT INTO appuser_tab_2 VALUES (1);
-[ RECORD 5 ]+---------------------------------------------
 username     | appuser
 ip_address   | 192.168.2.6
 port         | 50098
 machine_name |
 date_time    | 20-JUN-14 13:39:00 -04:00
 query        | SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';

← Prev

SQL/Protect overview

Common maintenance operations

Configuring SQL/Protect v14

Selecting roles to protect

Setting the protected roles list

Setting the protection level

Monitoring protected roles

Learn mode

Passive mode

Note

Active mode

← Prev

Next →