sql_firewall

Step 1: Load the library

Add sql_firewall to the shared_preload_libraries parameter in the PolarDB console, then restart the cluster.

Step 2: Create the extension

CREATE EXTENSION sql_firewall;

Step 3: Enable learning mode

Contact Alibaba Cloud support to set sql_firewall.firewall to learning, then restart the cluster.

Run your application normally for a representative period — long enough to cover your full range of SQL workloads. The extension records every statement it sees and adds it to the whitelist.

How long should you run in learning mode? Run long enough to cover all code paths your application exercises: routine queries, batch jobs, scheduled tasks, and any workflows that run infrequently. A learning period that misses legitimate statements will cause false positives in enforcing mode.

Step 4: Switch to permissive mode

Set sql_firewall.firewall to permissive and restart the cluster.

In this mode, statements not on the whitelist are still executed, but alerts are generated. Review the alerts and determine whether flagged statements are legitimate:

• If a statement is legitimate, switch back to learning mode temporarily to add it to the whitelist.

• If a statement looks like an attack, note it for investigation.

Before switching to enforcing mode, confirm that the alert rate has dropped to near zero during normal application operation. Residual alerts in permissive mode become blocked queries in enforcing mode.

Step 5: Switch to enforcing mode

Set sql_firewall.firewall to enforcing and restart the cluster.

In enforcing mode, any statement not on the whitelist is blocked. The cluster is now protected against SQL injection.

If a legitimate statement is blocked after switching to enforcing mode, switch back to permissive mode, let the application run the statement (it will be logged as a warning), then switch to learning mode briefly to add it to the whitelist. Return to enforcing mode when ready.

Management functions

Views

sql_firewall.sql_firewall_statements

Returns all SQL statements in the whitelist, along with the number of times each statement has been executed.

sql_firewall.sql_firewall_stat

Returns two counters:

• sql_warning: the number of alerts generated in permissive mode

• sql_error: the number of errors returned in enforcing mode

Permissive mode

The following example shows how sql_firewall behaves in permissive mode. The whitelist contains one statement. Three queries are then run against a table named k1.

-- Check the current whitelist: one statement has been learned
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)

-- Whitelisted statement: executes normally, no warning
SELECT * FROM k1 WHERE uid = 1;
 uid |    uname
-----+-------------
   1 | Park Gyu-ri
(1 row)

-- Whitelisted statement (same query, different parameter): executes normally
-- sql_firewall normalizes parameters, so uid = 3 matches the same whitelist entry
SELECT * FROM k1 WHERE uid = 3;
 uid |   uname
-----+-----------
   3 | Goo Ha-ra
(1 row)

-- SQL injection attempt (OR 1 = 1 is an always-true condition that returns all rows)
-- Not on the whitelist: executes but triggers a WARNING
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)

In permissive mode, the injection attempt executes and returns all rows, but the WARNING signals that the statement is not on the whitelist.

Enforcing mode

The following example shows the same queries in enforcing mode. Whitelisted statements run normally; the injection attempt is blocked before execution.

-- Whitelisted statement: executes normally
SELECT * FROM k1 WHERE uid = 3;
 uid |   uname
-----+-----------
   3 | Goo Ha-ra
(1 row)

-- SQL injection attempt: blocked with an ERROR, no data returned
SELECT * FROM k1 WHERE uid = 3 OR 1 = 1;
ERROR:  Prohibited SQL statement

In enforcing mode, the injection attempt is blocked before execution. No data is returned.