Building a MySQL Firewall with ProxySQL

Apr 21, 2020

Mins to Read

All

We are aware of ProxySQL as an SQL load balancing tool with a lot of features bundled in it.Now it has added firewall rules integrated too. Below are a few interesting features explained and explored by our Mydbops team.

Proxysql is being released in minor versions with new features and bug fixes.

In this blog post, we look into one of the new security features released from 2.0.9 versions, which is whitelisting of queries and users in the proxy layer.

Below the two tables introduced for firewall whitelisting are,

 
ProxySQL> show tables like 'mysql_firewall_white%';
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| mysql_firewall_whitelist_users             |
| mysql_firewall_whitelist_rules             |
+--------------------------------------------+
2 rows in set (0.01 sec)

Now lets us explore this firewall tables , which will helps us enhancing our security.

mysql_firewall_whitelist_users

This table specifies the user for which the firewall whitelist algorithm will be applied, and determines the default action for defined user.

 
ProxySQL> show create table mysql_firewall_whitelist_users\G
*************************** 1. row ***************************
       table: mysql_firewall_whitelist_users
Create Table: 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) )
1 row in set (0.00 sec)

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

mysql_firewall_whitelist_rulesThis table mysql_firewall_whitelist_rules completes the algorithm introduced in mysql_firewall_whitelist_users .

 
ProxySQL> show create table mysql_firewall_whitelist_rules\G
*************************** 1. row ***************************
       table: mysql_firewall_whitelist_rules
Create Table: 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) )
1 row in set (0.00 sec)

More info in Proxysql documentation

Configuring Firewall Tables

Add user in mysql_firewall_whitelist_users table with respective modes based the on the use case.

Types of modes with example.

OFF

By default OFF will allow the queries to pass through the ProxySQL.

2. DETECTING

This mode will allow the queries, but the queries which are explicitly defined in mysql_firewall_whitelist_rules table are detected and logged in the error log.‍

 
INSERT INTO mysql_firewall_whitelist_users (active, username, client_address, mode, comment) VALUES (1, 'proxysql', '', 'DETECTING', 'test_query');
LOAD MYSQL FIREWALL TO RUNTIME;
SAVE MYSQL FIREWALL TO DISK;

Once the user is added , we can add the query in mysql_firewall_whitelist_rules table which needs to be detected,i.e respective digest has to be added.

We can get digest from runtime in stats_mysql_query_digest table or we can store the runtime queries in table history_mysql_query_digest which will be persisted in disk ,below parameter has to be enabled.

 
set mysql-query_digests = 1;
set admin-stats_mysql_query_digest_to_disk = 1;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL DIGEST TO DISK

Lets add the query(digest) into the table

I have taken one sample query from stats_mysql_query_digest digest table .

 
ProxySQL> select hostgroup,schemaname,username,digest_text,digest from stats.stats_mysql_query_digest order by digest desc limit 1;
+-----------+------------+----------+-----------------------------------------------------+--------------------+
| hostgroup | schemaname | username | digest_text                                         | digest             |
+-----------+------------+----------+-----------------------------------------------------+--------------------+
| 101       | sbtest     | proxysql | SELECT SUM(k) FROM sbtest5 WHERE id BETWEEN ? AND ? | 0xFF6DAE483948806A |
+-----------+------------+----------+-----------------------------------------------------+--------------------+

Now Let us add rules in mysql_firewall_whitelist_rules table

 
INSERT INTO mysql_firewall_whitelist_rules VALUES (1, 'proxysql', '', 'sbtest', 0, '0xFF6DAE483948806A, '');
LOAD MYSQL FIREWALL TO RUNTIME;
SAVE MYSQL FIREWALL TO DISK;

Once the rule is loaded , the proxysql will start detecting the query , this can be seen from proxysql.log

 
[root@vm3 vagrant]# tail -f /var/lib/proxysql/proxysql.log | grep -i 0xFF6DAE483948806A
2020-04-14 10:36:47 Query_Processor.cpp:1905:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0xFF6DAE483948806A from user proxysql@127.0.0.1
2020-04-14 10:36:48 Query_Processor.cpp:1905:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0xFF6DAE483948806A from user proxysql@127.0.0.1
2020-04-14 10:36:48 Query_Processor.cpp:1905:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0xFF6DAE483948806A from user proxysql@127.0.0.1
2020-04-14 10:36:48 Query_Processor.cpp:1905:process_mysql_query(): [WARNING] Firewall detected unknown query with digest 0xFF6DAE483948806A from user proxysql@127.0.0.

3.PROTECTING

It allows only queries which are explicitly enabled in mysql_firewall_whitelist_rules , and all other queries are blocked.

getting the random select digest from history_mysql_query_digest table ,

 
| 0x0250CB4007721D69 | proxysql | SELECT c FROM sbtest4 WHERE id=?

adding a rule in a table.

 
INSERT INTO mysql_firewall_whitelist_rules VALUES (1, 'proxysql', '', 'sbtest', 0, '0x0250CB4007721D69', '');

changing the mode to PROTECTING in mysql_firewall_whitelist_users table

 
UPDATE mysql_firewall_whitelist_users SET mode='PROTECTING' WHERE username='proxysql';

this query seems to be blocked.

 
[root@vm3 vagrant]# mysql -uproxysql -p'Proxysql@123' -h 127.0.0.1 -P6033 -e "SELECT c FROM sbtest.sbtest4 WHERE id=3299;" -vv
--------------
SELECT c FROM sbtest.sbtest4 WHERE id=3299
--------------
+-------------------------------------------------------------------------------------------------------------------------+
| c                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| 01374242749-68927370585-29811523463-21248834027-35674973529-60753820118-40636161838-71322829147-06150438324-74523600223 |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

All other queries will be blocked.

 
[root@vm3 vagrant]# mysql -uproxysql -p'Proxysql@123' -h 127.0.0.1 -P6033 -e "update sbtest.sbtest5 set k='47638' where id='2345'" -v
--------------
update sbtest.sbtest5 set k='47638' where id='2345'
--------------
ERROR 1148 (42000) at line 1: ProxySQL Firewall blocked this query

[root@vm3 vagrant]# mysql -uproxysql -p'Proxysql@123' -h 127.0.0.1 -P6033 -e "SELECT c,k FROM sbtest.sbtest5 WHERE id=3299;" -v
--------------
SELECT c,k FROM sbtest.sbtest5 WHERE id=3299
--------------
ERROR 1148 (42000) at line 1: ProxySQL Firewall blocked this query

Variables To Enable Firewall

Important Note : before enabling the firewall variables , configure the rules in fire tables,if it is enabled prior the proxysql will block all the client connections.

Example:

 
FATAL: mysql_drv_query() returned error 1148 (ProxySQL Firewall blocked this query) for query 'DELETE FROM sbtest12 WHERE id=4982'

FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:487: SQL error, errno = 1148, state = '42000': ProxySQL Firewall blocked this query

FATAL: mysql_drv_query() returned error 1148 (ProxySQL Firewall blocked this query) for query 'SELECT c FROM sbtest10 WHERE id=5010'

FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:419: SQL error, errno = 1148, state = '42000': ProxySQL Firewall blocked this query

variables to be enabled,

 
ProxySQL> select * from global_variables where variable_name like '%firewall%';

+-----------------------------------+--------------------------------------+
| variable_name                     | variable_value                       |
+-----------------------------------+--------------------------------------+
| mysql-firewall_whitelist_enabled  | 0                                    |
| mysql-firewall_whitelist_errormsg | ProxySQL Firewall blocked this query |
+-----------------------------------+--------------------------------------+
2 rows in set (0.01 sec)

ProxySQL> SET mysql-firewall_whitelist_enabled = 1;
Query OK, 1 row affected (0.00 sec)

ProxySQL> SET mysql-firewall_whitelist_errormsg = 'ProxySQL Firewall blocked this query';
Query OK, 1 row affected (0.00 sec)

ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQL> SAVE MYSQL VARIABLES TO DISK;
Query OK, 143 rows affected (0.01 sec)

This firewall feature is more exciting in Proxysql , and the PROTECTING mode can be a attractive where we have pre-defined queries are ran in production servers. Interested in scaling your MySQL /MariaDB databases with these modern load balancers, reach out to Mydbops Team.

Interested in leveraging ProxySQL's firewall to secure your MySQL databases? Reach out to Mydbops for expert MySQL management and consultation!