PolarDB:sql_firewall

Precautions

The sql_firewall extension consumes shared memory. If the sql_firewall library is not loaded when the cluster starts (which is the default value), the extension can be created but does not work as expected. To use the sql_firewall extension, add sql_firewall to the shared_preload_libraries parameter.

Learning, permissive, and enforcing modes

The sql_firewall extension supports the following modes:

• Learning: The extension records common SQL statements that are executed and adds them to a whitelist.

• Permissive: The extension checks SQL statements that will be executed. If the SQL statements are not in the whitelist, the extension executes the SQL statements but generates alerts.

• Enforcing: The extension checks SQL statements that will be executed. If the SQL statements are not in the whitelist, the extension does not execute the SQL statements and returns errors.

Procedure

1. Enable the learning mode of the sql_firewall extension. Wait for a specific period of time to ensure that the extension learns more SQL statements.

2. Switch the sql_firewall extension to the permissive mode. In this mode, the extension generates alerts for SQL statements that are not in the whitelist. You can check whether these SQL statements are high-risky statements based on your business requirements. If these statements are not high-risky statements, switch to the learning mode and add these SQL statements to the whitelist.

3. Switch the sql_firewall extension to the enforcing mode. In this mode, the extension does not execute SQL statements that are not in the whitelist.

Usage

• Create the dbms_job extension.

  CREATE EXTENSION sql_firewall;

• Delete the dbms_job extension.

  DROP EXTENSION sql_firewall;

• Switch the network isolation mode to the enhanced whitelist mode

  Modify the sql_firewall.firewall parameter value and restart your PolarDB cluster.

  Note

  The sql_firewall.firewall parameter is not displayed in the console. Contact us to modify its value.

  Valid values for the sql_firewall.firewall parameter:

  • disable: disables the sql_firewall extension.

  • learning: enables the learning mode.

  • permissive: enables the permissive mode.

  • enforcing: enables the enforcing mode.

• Functions

  • sql_firewall_reset()

    This function clears the whitelist. You can call this function only if you are authorized with the polar_superuser role and the enforcing mode is disabled.

  • sql_firewall_stat_reset()

    This function deletes statistics. You can call this function only if you are authorized with the polar_superuser role and the enforcing mode is disabled.

• View functions

  • sql_firewall.sql_firewall_statements

    This function returns all SQL statements in the whitelist of your PolarDB cluster. This function also returns the number of times that each SQL statement is executed.

  • sql_firewall.sql_firewall_stat

    This function returns the number of alerts that are generated in permissive mode and the number of errors that are generated in enforcing mode. The first number is measured by sql_warning, and the second number is measured by sql_error.

Examples

• Permissive mode

  SELECT * FROM sql_firewall.sql_firewall_statements;
  WARNING:  Prohibited SQL statement
   userid |  queryid   |              query              | calls
  --------+------------+---------------------------------+-------
       10 | 3294787656 | select * from k1 where uid = 1; |     1
  (1 row)
  
  SELECT * FROM k1 WHERE uid = 1;
   uid |    uname
  -----+-------------
     1 | Park Gyu-ri
  (1 row)
  
  SELECT * FROM k1 WHERE uid = 3;
   uid |   uname
  -----+-----------
     3 | Goo Ha-ra
  (1 row)
  
  SELECT * FROM k1 WHERE uid = 3 OR 1 = 1;
  WARNING:  Prohibited SQL statement
   uid |     uname
  -----+----------------
     1 | Park Gyu-ri
     2 | Nicole Jung
     3 | Goo Ha-ra
     4 | Han Seung-yeon
     5 | Kang Ji-young
  (5 rows)

• Enforcing mode

  SELECT * FROM k1 WHERE uid = 3;
   uid |   uname
  -----+-----------
     3 | Goo Ha-ra
  (1 row)
  
  SELECT * FROM k1 WHERE uid = 3 OR 1 = 1;
  ERROR:  Prohibited SQL statement