ProxySQL Firewall Overview

ProxySQL’s flexible query rules engine has many uses, from Read/Write splitting, sharding and even creating firewall blacklist. This allows ProxySQL to be loved by both Performance and Security-minded engineers.

Starting in ProxySQL 2.0.9, ProxySQL has another Security feature: the Firewall Whitelist.

Modeled on MySQL Enterprise Firewall, this allows a security-conscious administrator to tune access to only allow certain queries.

Imagine a situation where your webapp gets hacked, which exposes your user’s database credentials.

If your webapp connects directly to the database, the malicious user can do what they want to your data with the same permissions your webapp has.

So perhaps they can’t just DROP TABLE because you’ve smartly removed DDL permissions from your application user. But they can likely do DELETE FROM mytable after saving a copy to hold for ransom.

ProxySQL Firewall can prevent this situation.

Implementing ProxySQL Firewall

The documentation explains really well how to implement ProxySQL Firewall, but here are the high-level steps on how it works:

Prerequisite Point your webapp to connect to ProxySQL rather than MySQL if you haven’t already. Configure ProxySQL with users and servers to connect to the backend MySQL database.

Step 1. Allow ProxySQL to record the application’s traffic for a period of time to ensure all application queries are captured.
Protip: ProxySQL does this automatically through stats_mysql_digest and (if configured) stats_history.history_mysql_query_digest.

Step 2. Add your app user to mysql_firewall_whitelist_users table through the ProxySQL Admin interface. Let’s say our app user is myapp:

ProxySQL Admin> INSERT INTO mysql_firewall_whitelist_users
(active, username, client_address, mode) VALUES (1, 'myapp', '', 'PROTECTING');

In this example, PROTECTING means ProxySQL will stop all other traffic that is not defined in the mysql_firewall_whitelist_rules for this user in the following step.

It’s also important to note that any user not in this table will be blocked when firewall whitelist is enabled below. You can get around this by adding an entry into mysql_firewall_whitelist_users for all users to allow, with mode=’OFF’.

Step 3. Add all digests from Step 2 into mysql_firewall_whitelist_rules table. This assumes you’ve configured stats_history.history_mysql_query_digest, as this will persist stats across ProxySQL restarts.

ProxySQL Admin> 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 WHERE username='myapp';

Step 4. Load your configuration to runtime.

LOAD MYSQL FIREWALL TO RUNTIME;
SAVE MYSQL FIREWALL TO DISK;

Step 5. Enable the ProxySQL Firewall

SET mysql-firewall_whitelist_enabled = 1;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Congratulations! Now the malicious user can only perform exact queries that your application can perform.

The previous DELETE FROM mytable statement would fail, because your application doesn’t allow doing DELETEs without WHERE clauses, right?

Keeping firewall rules up to date

If you are familiar with ProxySQL query rules, you’ll notice that the mysql_firewall_whitelist_rules table behaves a little bit differently.

You have to populate it with exact digests, rather than relying on regular expressions. If you have thousands of mysql_query_rules, you were likely doing this already though.

This means that you will need to update the firewall rules any time an application deployment introduces new queries or changes query digests.

Our recommendation is to implement a process that records application digest changes before it hits production, such as a staging environment, and then update the production firewall rules as part of the deployment process.

Conclusion

If you want to be absolutely certain of the queries hitting your database from your application are not malicious, ProxySQL Firewall can help with this.

There is a bit of friction to keep this maintained, but if the security of your data is paramount, then the ease of mind ProxySQL Firewall provides is well worth it.

Authored by: Derek Downey