Use the sql_firewall extension - ApsaraDB RDS - Alibaba Cloud Documentation Center

The sql_firewall extension is a database-level firewall that prevents against SQL injection. It learns defined SQL rules and stores the rules in your ApsaraDB RDS for PostgreSQL instance as a whitelist. User operations that do not comply with the rules are forbidden.

Prerequisites

Your RDS instance runs one of the following PostgreSQL versions:

PostgreSQL 12
PostgreSQL 11
PostgreSQL 10

Important ApsaraDB RDS for PostgreSQL no longer supports the sql_firewall extension. You can still use the existing sql_firewall extension. However, the extension may cause your system to break down. We recommend that you stop using the extension at the earliest opportunity to prevent potential risks.

If you want to remove the sql_firewall extension, you can execute the DROP EXTENSION sql_firewall; statement to uninstall the extension and remove the value that represents the extension from the shared_preload_libraries parameter. For more information about how to modify the parameter, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.

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

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

Operations

Create the extension
```
create extension sql_firewall;
```
Delete the extension
```
drop extension sql_firewall;
```
Switch the mode
In the ApsaraDB for RDS console, find the sql_firewall.firewall parameter. Modify the parameter value and restart your RDS instance. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
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.
Functionality functions
- sql_firewall_reset()
  This function clears the whitelist. You can call this function only if you are authorized with the rds_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 rds_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 RDS instance. This function also returns the number of times that each SQL statement is executed.
```
postgres=# select * from sql_firewall.sql_firewall_statements;
     userid |  queryid   |              query              | calls
    --------+------------+---------------------------------+-------
         10 | 3294787656 | select * from k1 where uid = ? ; |     4
    (1 row)
```
- 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.
```
postgres=# select * from sql_firewall.sql_firewall_stat;
     sql_warning | sql_error
    -------------+-----------
               2 |         1
    (1 row)
```

Examples

-- Permissive mode

    postgres=# 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)

    postgres=# select * from k1 where uid = 1;
     uid |    uname
    -----+-------------
       1 | Park Gyu-ri
    (1 row)

    postgres=# select * from k1 where uid = 3;
     uid |   uname
    -----+-----------
       3 | Goo Ha-ra
    (1 row)

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

    postgres=# select * from k1 where uid = 3;
     uid |   uname
    -----+-----------
       3 | Goo Ha-ra
    (1 row)

    postgres=# select * from k1 where uid = 3 or 1 = 1;
    ERROR:  Prohibited SQL statement
    postgres=#