3.1.2 Configuring SQL/Protect

Table of Contents Previous Next


3 Security : 3.1 Protecting Against SQL Injection Attacks : 3.1.2 Configuring SQL/Protect

The library file (sqlprotect.so on Linux, sqlprotect.dll on Windows) necessary to run SQL/Protect should already be installed in the lib subdirectory of your Advanced Server home directory.
You will also need the SQL script file sqlprotect.sql located in the share/contrib subdirectory of your Advanced Server home directory.
The database server configuration file, postgresql.conf, must be modified by adding and enabling configuration parameters used by SQL/Protect.
Step 1: Edit the following configuration parameters in the postgresql.conf file located in the data subdirectory of your 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 Section 3.1.2.1.2 for further explanation of this parameter.
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. See Section 2.1.3.12.8 for information on the maximum range of this parameter.
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.
See Section 2.1.3.12.7 for information about the maximum range of this parameter.
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. Note: 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. See Section 2.1.3.12.9 for information on the maximum range of this parameter.
.
.
.
Step 2: Restart the database server after you have modified the postgresql.conf file.
On Linux: Invoke the Advanced Server service script with the restart option:
On Windows: Use the Windows Services applet to restart the service named edb-as-9.6.
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 Advanced Server home directory. The script creates the SQL/Protect database objects in a schema named sqlprotect.
Step 1: Connect as a superuser to a database that you wish to protect using either psql or Postgres Enterprise Manager Client.
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.
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').
Configuration parameter edb_sql_protect.level 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.
In the postgresql.conf file the edb_sql_protect.level configuration parameter 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.
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.
3.1.2.2.1 Learn Mode
Step 1: To activate SQL/Protect in learn mode, set the following parameters in the postgresql.conf file as shown below:
Step 2: Reload the postgresql.conf file.
Note: 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:
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:
SQL/Protect generates a NOTICE severity level message indicating the relation has been added to the role’s protected relations list.
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.
Query the edb_sql_protect_rel table to see the relations added to the protected relations list:
The view list_protected_rels is provided that gives more comprehensive information along with the object names instead of the OIDs.
3.1.2.2.2 Passive Mode
Step 1: To activate SQL/Protect in passive mode, set the following parameters in the postgresql.conf file as shown below:
Step 2: Reload the configuration file as shown in Step 2 of Section 3.1.2.2.1.
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:
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:
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 Section 3.1.1.2.2 for more information on view edb_sql_protect_stats.
The following is a query on edb_sql_protect_stats:
Step 4: View information on specific attacks.
By querying the view edb_sql_protect_queries, 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 Section 3.1.1.2.3 for more information on view edb_sql_protect_queries.
The following is a query on edb_sql_protect_queries:
Note: The ip_address and port columns do not 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).
3.1.2.2.3 Active Mode
Step 1: To activate SQL/Protect in active mode, set the following parameters in the postgresql.conf file as shown below:
Step 2: Reload the configuration file as shown in Step 2 of Section 3.1.2.2.1.
The following is a query on edb_sql_protect_queries:

3 Security : 3.1 Protecting Against SQL Injection Attacks : 3.1.2 Configuring SQL/Protect

Table of Contents Previous Next