Configuring SQL/Protect v17
You can configure how SQL/Protect operates.
Prerequisites
Meet the following prerequisites before configuring SQL/Protect:
The library file (
sqlprotect.so
on Linux,sqlprotect.dll
on Windows) needed to run SQL/Protect is installed in thelib
subdirectory of your EDB Postgres Advanced Server home directory. For Windows, the EDB Postgres Advanced Server installer does this. For Linux, install theedb-as<xx>-server-sqlprotect
RPM package, where<xx>
is the EDB Postgres Advanced Server version number.You need the SQL script file
sqlprotect.sql
located in theshare/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:
- You must modify the database server configuration file
postgresql.conf
by adding and enabling configuration parameters used by SQL/Protect. - Install database objects used by SQL/Protect in each database that you want SQL/Protect to monitor.
- You must modify the database server configuration file
Configuring the module
- Edit the following configuration parameters in the
postgresql.conf
file located in thedata
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 SQL/Protect is actively monitoring protected roles by analyzing SQL statements issued by those roles and reacting according to the setting ofedb_sql_protect.level
. When you're ready to begin monitoring with SQL/Protect, set this parameter toon
. The default isoff
.edb_sql_protect.level
. Sets the action taken by SQL/Protect when a SQL statement is issued by a protected role. The default behavior ispassive
. Initially, set this parameter tolearn
. See Setting the protection level for more information.edb_sql_protect.max_protected_roles
. Sets the maximum number of roles to protect. The default is64
.edb_sql_protect.max_protected_relations
. Sets the maximum number of relations to protect per role. The default is1024
.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 theedb_sql_protect_queries
view. The default is5000
. If the number of offending queries reaches the limit, additional queries aren't saved in the view but are accessible in the database server log file.The minimum valid value for this parameter is
100
. If you specify a value less than100
, the database server starts using the default setting of5000
. A warning message is recorded in the database server log file.This 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
- After you modify the
postgresql.conf
file, restart the database server.
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-17
On Windows: Use the Windows Services applet to restart the service named
edb-as-14
.
- For each database that you want to protect from SQL injection attacks, connect to the database as a superuser (either
enterprisedb
orpostgres
, depending on your installation options). Then run the scriptsqlprotect.sql
, located in theshare/contrib
subdirectory of your EDB Postgres Advanced Server home directory. The script creates the SQL/Protect database objects in a schema namedsqlprotect
.
This example shows the process to set up protection for a database named edb
:
$ /usr/edb/as17/bin/psql -d edb -U enterprisedb Password for user enterprisedb: psql.bin (17.2.0, server 17.2.0) Type "help" for help. edb=# \i /usr/edb/as17/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 you create the SQL/Protect database objects in a database, you can select the roles for which to monitor SQL queries for protection and the level of protection to assign 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.
- Connect as a superuser to a database that you want to protect with either
psql
or the Postgres Enterprise Manager client:
$ /usr/edb/as17/bin/psql -d edb -U enterprisedb Password for user enterprisedb: psql.bin (17.2.0, server 17.2.0) Type "help" for help. edb=#
- Since the SQL/Protect tables, functions, and views are built under the
sqlprotect
schema, use theSET search_path
command to include thesqlprotect
schema in your search path. Doing so eliminates the need to schema-qualify any operation or query involving SQL/Protect database objects:
edb=# SET search_path TO sqlprotect; SET
You must add each role that you want to protect 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')
. This example protects a role namedappuser
:
edb=# SELECT protect_role('appuser');
protect_role -------------- (1 row)
You can list the roles that were added to the protected roles list with 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.
You can set the edb_sql_protect.level
configuration parameter in the postgresql.conf
file to one of the following values to specify learn, passive, or active mode:
learn
. Tracks the activities of protected roles and records the relations used by the roles. Use this mode when first 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 doesn't stop any SQL statements from executing. This mode is the next step after SQL/Protect learns the expected behavior of the protected roles. It essentially behaves in intrusion detection mode. You can run this mode in production when proper monitoring is in place.active
. Stops all invalid statements for a protected role. This mode behaves as a SQL firewall, preventing dangerous queries from running. This approach is particularly effective against early penetration testing when the attacker is trying to find the vulnerability point and the type of database behind the application. Not only does SQL/Protect close those vulnerability points, it tracks the blocked queries. This tracking can alert administrators before the attacker finds another way to penetrate the system.
The default mode is passive
.
If you're using SQL/Protect for the first time, set edb_sql_protect.level
to learn
.
Monitoring protected roles
After you configure SQL/Protect in a database, add roles to the protected roles list, and set the desired protection level, you can activate SQL/Protect in learn
, passive
, 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 are allowed 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 you run SQL/Protect in passive or active mode. In passive and active modes, the role is permitted to access the relations in its protected relations list. These are the specified relations the role can access during typical usage.
However, if a role attempts to access a relation that isn't in its protected relations list, SQL/Protect returns a WARNING
or ERROR
severity-level message. The role’s attempted action on the relation might not be carried out, depending on whether the mode is passive or active.
Learn mode
To activate SQL/Protect in learn mode:
- Set the parameters in the
postgresql.conf
file:
edb_sql_protect.enabled = on edb_sql_protect.level = learn
Reload the
postgresql.conf
file. From the EDB Postgres Advanced Server application menu, select Reload Configuration > Expert Configuration.For an alternative method of reloading the configuration file, use the
pg_reload_conf
function. Be sure you're connected to a database as a superuser, and executefunction pg_reload_conf
:
edb=# SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
Allow the protected roles to run their applications.
For example, the following queries are issued in the
psql
application by the protected roleappuser
:
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 was added to the role’s protected relations list.
In SQL/Protect learn mode, SQL statements that are cause for suspicion aren't prevented from executing. However, a message is issued to alert the user to potentially dangerous statements:
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
- As a protected role runs applications, you can query the SQL/Protect tables to see that relations were added to the role’s protected relations list. Connect as a superuser to the database you're 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
After a role’s applications have accessed all relations they need, you can change the protection level so that SQL/Protect can actively monitor the incoming SQL queries and protect against SQL injection attacks.
Passive mode is a less restrictive protection mode than active.
- To activate SQL/Protect in passive mode, set the following parameters in the
postgresql.conf
file:
edb_sql_protect.enabled = on edb_sql_protect.level = passive
Reload the configuration file as shown in Step 2 of Learn mode.
Now SQL/Protect is in passive mode. For relations that were learned, such as the
dept
andemp
tables of the prior examples, SQL statements are permitted. No special notification to the client by SQL/Protect is required, as shown by the following queries run by userappuser
:
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 doesn't prevent any SQL statement from executing. However, it issues a message of WARNING
severity level for SQL statements executed against relations that weren't learned. It also issues a warning for SQL statements that contain a prohibited signature:
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)
Monitor the statistics for suspicious activity.
By querying the view
edb_sql_protect_stats
, you can see the number of times SQL statements executed that referenced relations that weren't in a role’s protected relations list or contained SQL injection attack signatures.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)
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 weren't in a role’s protected relations list or that contained SQL injection attack signatures.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
.
- To activate SQL/Protect in active mode, set the following parameters in the
postgresql.conf
file:
edb_sql_protect.enabled = on edb_sql_protect.level = active
- Reload the configuration file as shown in Step 2 of Learn mode.
This example shows SQL statements similar to those given in the examples of Step 2 in Passive mode. These statements are executed by the 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';