ProxySQL 2.0.9 Introduces Firewall Whitelist Capabilities

ProxySQL Firewall WhitelistIn this blog, we will test a new security feature added in ProxySQL 2.0.9. Since a time ago, we have had the ability to block queries using mysql_query_rules table matching a group of queries using reg exp like a blacklist. Check out a previous blog for how to config “ProxySQL Firewalling” using the mysql_query_rules table.

You can improve a whitelist using the mysql_query_rules table, but it is difficult if you have hundreds of queries.

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

How Does It Work?

If you have the param mysql-query_digests enabled, you have all your queries logged in the stats.stats_mysql_query_digest table. The idea is to collect as much traffic as possible to identify normal traffic.

Example

Let’s gets started, so I’ll show you how to configure it using a little example to simulate your traffic:

Now we will prepare some databases and tables, and we are going to insert some data to log that traffic into the stats.stats_mysql_query_digest table. Remember, we need to connect through ProxySQL.

After that, I recommend checking if those queries are in the stats_history.history_mysql_query_digest table and you should see something like this for the MySQL user “user1”:

Pay attention to the above list, to the column “digest“, as we will need it to create the whitelist.

After the collection, we can now continue and configure the firewall tables. ProxySQL introduces two new tables for firewall whitelist.

The first one “mysql_firewall_whitelist_rules” identifies a specific user to apply the firewall whitelist algorithm and determines the default action for each user. There are three modes for each user (from ProxySQL documentation):

We will enable the firewall on ProxySQL:

Note: Take care – if you enabled the firewall, before configuring the above tables, that you will block all users and queries. I’m doing this only for test purposes.

We can continue and enable the MySQL user “user1” to accept all traffic.

Now this “user1” can run any query, existing or not, in the table mysql_firewall_whitelist_rules table.

We will update the rule to mode = DETECTING, and in this case, “user1” can run any query, but if the query doesn’t exist in the table mysql_firewall_whitelist_rules, it will generate an error entry in the proxysql.log file.

And we can run any query, for example:

This is the warning we can see in the proxysql.log file. We cannot see the query statement but we can see the digest:

Anyway, “user1” can run any query and new queries will be logged into the stats_history.history_mysql_query_digest table.

Finally, the last option is mode = PROTECTING, and it will check if the query (in this case, it will check digest) we are running exists in mysql_firewall_whitelist_rules, and we are going to update the mode.

And now run any query that exists or not into stats_history.history_mysql_query_digest and check the output. Let’s take a look:

Perfect! As we can see, those queries are blocked and showing the message “ProxySQL Firewall blocked this query“.

Also, there are warnings in the proxysql.log file. The three first warnings are from this statement “use db_firewall_test1”, as the “use” statement will not be logged into the stats_history.history_mysql_query_digest table.

Now, we can choose which queries from stats_history.history_mysql_query_digest we want to permit to run from the user “user1”.

The next step is to copy one or all the digests and insert them into mysql_firewall_whitelist_rules table. For this test, I’ll just copy/insert only three digests for test purposes.

From stats_history.history_mysql_query_digest:

Insert into mysql_firewall_whitelist_rules:

And test again…

It works! My last query was to check if it’s working, because that query was not added in the mysql_firewall_whitelist_rules table.

If you want to add all your users and queries in the whitelist, I recommend the following queries to collect all your traffic for a long time:

Conclusion

This is a solid approach to build a good whitelist, but remember, before/after any deploy from your application with new queries keep in mind you need to add those new queries into the mysql_firewall_whitelist_rules table.

I think it’s good to add permission to one user to run only one or more queries.

Last but not least, take care that the next two queries are the same for you but have a different digest. Check the following example:

I hope this is helpful to you. Thanks so much.

Interested in learning more?

Be sure to get in touch with Percona’s Training Department to schedule a hands-on tutorial session for all things ProxySQL. Our instructors will guide you and your team through the install and setup processes, learn how to manage read-write splitting, chain rules together, implement query firewalling, query caching, high-availability and much more.

Share this post

Leave a Reply