ProxySQL 2.0.9 introduces firewall capabilities.

Since early releases, ProxySQL has had the ability to block queries using query rules, setting mysql_query_rules.error_msg.
This feature allows us 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 the same number of rules may need to be created. This becomes even more complicated if whitelist needs to be granular per user and/or schema, or even per client address.

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

The implementation is inspired by the 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

Table history_mysql_query_digest.history_mysql_query_digest (on disk) is an extension of stats.stats_mysql_query_digest (in memory) : it contains all the same columns, plus an extra column called 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 manner 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 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 the 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 this way.

Firewall whitelist tables

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

Table mysql_firewall_whitelist_users

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 represents 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 : allows any query
    • DETECTING : allows 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 blocks any other query

Table mysql_firewall_whitelist_rules

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 : defines 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 in 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 on the user mode defined in mysql_firewall_whitelist_users.mode :

  • DETECTING: it allows the query, but generates an entry in the error log
  • PROTECTING : it blocks the query and returns 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:

Firewall whitelist global variables

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

  • 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 has already set 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 also apply to the [SQL injection engine][1]

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 it is 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 configure all users in DETECTING mode, the following query can be executed:

  • 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:

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

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

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