Firewall whitelist

Please note: the project WIKI documentation has been moved to the ProxySQL website

---

ProxySQL 2.0.9 introduces firewall capabilities.

Since early releases, ProxySQL has the ability to block queries using query rules, setting mysql_query_rules.error_msg.
This feature allows to create a blacklist of queries to block, or to define a generic catch all rule to block all the queries that have not been explicitly enabled (whitelisted).

Nonetheless, the implementation of whitelist using mysql_query_rules can be challenging if a system has thousands or millions of distinct queries, because as many rules may need to be created. This become even more complicated if whitelist needs to be granular per user and/or schema, or even client address.

These challenges are solved in ProxySQL 2.0.9, as it introduces a new algorithm specifically designed for whitelist.

Implementation is inspired by MySQL Enterprise Firewall:

https://dev.mysql.com/doc/refman/8.0/en/firewall.html

Table history_mysql_query_digest

A new table was introduced in stats_history schema: history_mysql_query_digest

Admin> SHOW TABLES FROM stats_history;
+----------------------------+
| tables                     |
+----------------------------+
| history_mysql_query_digest |
...

Admin> SHOW CREATE TABLE stats_history.history_mysql_query_digest\G
*************************** 1. row ***************************
       table: history_mysql_query_digest
Create Table: CREATE TABLE history_mysql_query_digest (
    dump_time INT,
    hostgroup INT,
    schemaname VARCHAR NOT NULL,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    digest VARCHAR NOT NULL,
    digest_text VARCHAR NOT NULL,
    count_star INTEGER NOT NULL,
    first_seen INTEGER NOT NULL,
    last_seen INTEGER NOT NULL,
    sum_time INTEGER NOT NULL,
    min_time INTEGER NOT NULL,
    max_time INTEGER NOT NULL,
    sum_rows_affected INTEGER NOT NULL,
    sum_rows_sent INTEGER NOT NULL)

Table history_mysql_query_digest.history_mysql_query_digest (on disk) is an extension of stats.stats_mysql_query_digest (on memory) : it contains all the same columns, plus an extra column dump_time.

This table can be used to persist on disk the metrics collected by stats_mysql_query_digest. Although it is possible to copy data from stats_mysql_query_digest to history_mysql_query_digest with a simple INSERT ... SELECT ... , ProxySQL introduces the ability to perform this operation in a more efficient way in two different ways:

• Using the command SAVE MYSQL DIGEST TO DISK : it atomically copies all the data from stats_mysql_query_digest to history_mysql_query_digest and it resets the content of stats_mysql_query_digest .
• setting variable admin-stats_mysql_query_digest_to_disk : ProxySQL will automatically dump data data from stats_mysql_query_digest to history_mysql_query_digest and it resets the content of stats_mysql_query_digest every admin-stats_mysql_query_digest_to_disk seconds.

Note that using the command SAVE MYSQL DIGEST TO DISK or setting variable admin-stats_mysql_query_digest_to_disk is the preferred way to persist data on disk, as data is copied more efficiently and a lot faster.

Firewall whitelist tables

ProxySQL 2.0.9 introduces 2 new tables for the firewall whitelist algorithm:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('mysql_firewall_whitelist_rules','mysql_firewall_whitelist_users') ORDER BY name;
+--------------------------------+
| tables                         |
+--------------------------------+
| mysql_firewall_whitelist_rules |
| mysql_firewall_whitelist_users |
+--------------------------------+
2 rows in set (0.00 sec)

Table mysql_firewall_whitelist_users

CREATE TABLE mysql_firewall_whitelist_users (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    mode VARCHAR CHECK (mode IN ('OFF','DETECTING','PROTECTING')) NOT NULL DEFAULT ('OFF'),
    comment VARCHAR NOT NULL,
    PRIMARY KEY (username, client_address) )

This table identifies a specific user for which the firewall whitelist algorithm applies, and determines the default action for such user:

• active defines if the entry is active or not
• username is the MySQL user
• client_address represent the IP of the client, or the empty string to match any IP (subnets are not supported yet)
• mode defines the firewall user mode, that can be:
  • OFF : allow any query
  • DETECTING : allow any query, but queries not explicitly enabled in table mysql_firewall_whitelist_rules generate an error entry in the error log
  • PROTECTING : allows only queries explicitly enabled in mysql_firewall_whitelist_rules , and block any other query

table mysql_firewall_whitelist_rules

CREATE TABLE mysql_firewall_whitelist_rules (
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    username VARCHAR NOT NULL,
    client_address VARCHAR NOT NULL,
    schemaname VARCHAR NOT NULL,
    flagIN INT NOT NULL DEFAULT 0,
    digest VARCHAR NOT NULL,
    comment VARCHAR NOT NULL,
    PRIMARY KEY (username, client_address, schemaname, flagIN, digest) )

Table mysql_firewall_whitelist_rules completes the algorithm introduced in mysql_firewall_whitelist_users .

If a user is found in mysql_firewall_whitelist_users and its mode is either DETECTING or PROTECTING , a lookup is performed on table mysql_firewall_whitelist_rules to determine if the query executed by that user is whitelisted.

• active : the entry is active
• username and client_address : define the user in the same way as in table mysql_firewall_whitelist_users
• schemaname is the default schema in which the query is trying to be executed
• digest is the digest of the query, like in stats_mysql_query_digest.digest
• flagIN : the lookup on whitelist tables is performed after processing rules on mysql_query_rules . It is possible to create rules in mysql_query_rules that set flagOUT as a way of tagging specific queries: this tag becomes flagIN in mysql_firewall_whitelist_rules

If the query is found in mysql_firewall_whitelist_rules , the query is whitelisted and therefore executed. If the query is not found in mysql_firewall_whitelist_rules, the action performed depends from the user mode defined in mysql_firewall_whitelist_users.mode :

• DETECTING: it allows the query, but generate an entry in the error log
• PROTECTING : it blocks the query and return an error to the client

whitelist runtime tables

The firewall whitelist tables have runtime tables too, following the same naming convention of other configuration tables:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('runtime_mysql_firewall_whitelist_rules','runtime_mysql_firewall_whitelist_users') ORDER BY name;
+----------------------------------------+
| tables                                 |
+----------------------------------------+
| runtime_mysql_firewall_whitelist_rules |
| runtime_mysql_firewall_whitelist_users |
+----------------------------------------+
2 rows in set (0.02 sec)

Firewall whitelist global variables

Two new global variables are introduced to configure firewall whitelist algorithm:

Admin> SHOW VARIABLES LIKE '%whitelis%';
+-----------------------------------+-----------------------------+
| Variable_name                     | Value                       |
+-----------------------------------+-----------------------------+
| mysql-firewall_whitelist_enabled  | 0                           |
| mysql-firewall_whitelist_errormsg | Firewall blocked this query |
+-----------------------------------+-----------------------------+
2 rows in set (0.00 sec)

• mysql-firewall_whitelist_enabled globally toggles the firewall whitelist algorithm on or off
• mysql-firewall_whitelist_errormsg : the error message that will be returned to the client, unless mysql_query_rules.error_msg already sets one.

Firewall whitelist commands

New commands are introduced in ProxySQL 2.0.9 to manage firewall whitelist:

• LOAD MYSQL FIREWALL TO RUNTIME (or LOAD MYSQL FIREWALL FROM MEMORY) : it loads the content of firewall whitelist tables from memory to runtime
• SAVE MYSQL FIREWALL TO DISK (or SAVE MYSQL FIREWALL FROM MEMORY) : it saves the content of the firewall whitelist tables from memory to disk
• LOAD MYSQL FIREWALL TO MEMORY (or LOAD MYSQL FIREWALL FROM DISK) : it loads the content of firewall whitelist tables from disk to memory
• SAVE MYSQL FIREWALL TO MEMORY (or SAVE MYSQL FIREWALL FROM RUNTIME) : it saves the content of the firewall whitelist tables from runtime to memory

Note that these commands apply also to SQL injection engine

Configuring firewall whilelist

There are several steps to configure firewall whitelist.

• collect as much traffic as possible to identify normal traffic. Runtime traffic information will be stored in stats_mysql_query_digest, but also stored long term in stats_history.history_mysql_query_digest (for example, if admin-stats_mysql_query_digest_to_disk is enabled)

• configure table mysql_firewall_whitelist_users with the users for which whitelist will be enabled. For example, to configured all users in DETECTING mode, the following query can be executed:

  INSERT INTO mysql_firewall_whitelist_users
  (active, username, client_address, mode)
  SELECT DISTINCT 1, username, '', 'DETECTING', '' FROM mysql_users;
  

• configure table mysql_firewall_whitelist_rules with all the users and digests that needs to be whitelisted. For example, to whitelist all the queries already known to ProxySQL, the following query can be executed:

  INSERT INTO mysql_firewall_whitelist_rules
  (active, username, client_address, schemaname, flagIN, digest, comment)
  SELECT DISTINCT 1, username, client_address, schemaname, 0, digest, ''
  FROM stats_history.history_mysql_query_digest; 
  

• load the configuration to runtime using LOAD MYSQL FIREWALL TO RUNTIME

• (optionally) save the configuration to disk using SAVE MYSQL FIREWALL TO DISK

• enabled firewall whitelist globally, setting variable mysql-firewall_whitelist_enabled to 1