SQL Firewalling Made Easy with ClusterControl & ProxySQL

Reading the title of this blog post may raise some questions. SQL firewall – what is that? What does it do? Why would I need something like that in the first place? Well, the ability to block certain queries could come in handy in certain situations. When using ProxySQL in front of your database servers, the proxy is able to inspect all SQL statements being sent. ProxySQL has a sophisticated rules engine, and can match queries that are to be allowed, blocked, re-written on the fly or routed to a specific database server. Let’s go through some examples.

You have a dedicated slave which is used by developers to test their queries against production data. You want to make sure the developers can only connect to that particular host and execute only SELECT queries.

Another case, let’s say that you encountered one too many accidents with people running schema changes and you would like to limit which users which can execute ALTER statement.

Finally, let’s think about a paranoid approach in which users are allowed to execute just a pre-defined whitelisted set of queries.

In our environment we have a replication setup with the master and two slaves.

In front of our databases, wee have three ProxySQL nodes with Keepalived managing Virtual IP. We also have ProxySQL cluster configured (as explained in this previous blog) so we don’t have to worry about making configuration or query rule changes three times on all three ProxySQL nodes. For the query rules, a simple read-write split is set up:

Let’s take a look at how ProxySQL, with its extensive query rules mechanism, can help us to achieve our goals in all those three cases.

Locking user access to a single hostgroup

A dedicated slave available to developers – this is not uncommon practice. As long as your developers can access production data (and if they are not allowed, e.g., due to compliance reasons, data masking as explained in our ProxySQL tutorial may help), this can help them to test and optimize queries on the real world data set. It may also help to verify data before executing some of the schema changes. For example, is my column really unique before adding a unique index?

With ProxySQL it is fairly easy to restrict access. For starters, let’s assume that the hostgroup 30 contains the slave we want developers to access.

We need an user which will be used by the developers to access that slave. If you have it already in ProxySQL, that’s fine. If not, you may either need to import it to ProxySQL (if it is created in MySQL but not in ProxySQL) or create it in both locations (if you’ll be creating a new user). Let’s go with the last option, creating a new user.

Let’s create a new user with limited privileges on both MySQL and ProxySQL. We will use it in query rules to identify traffic coming from the developers.

In this query rule we are going to redirect all of the queries which are executed by dev_test user to the hostgroup 30. We want this rule to be active and it should be the final one to parse, therefore we enabled ‘Apply’. We also configured RuleID to be smaller than the ID of the first existing rule as we want this query to be executed outside of the regular read/write split setup.

As you can see, we used an username but there are also other options.

If you can predict which development hosts will send the traffic to the database (for example, you can have developers use a specific proxy before they can reach the database), you can also use the “Client Address” option to match queries executed by that single host and redirect them to a correct hostgroup.

Disallowing user from executing certain queries

Now, let’s consider the case where we want to limit execution of some particular commands to a given user. This could be handy to ensure that the right people can run some of the performance impacting queries like schema changes. ALTER will be the query which we will use in this example. For starters, let’s add a new user which will be allowed to run schema changes. We will call it ‘admin_user’. Next, we need to create the required query rules.

We will create a query rule which uses ‘.*ALTER TABLE.*’ regular expression to match the queries. This query rule should be executed before other, read/write split rules. We assigned a rule ID of 20 to it. We define an error message that will be returned to the client in case this query rule will be triggered. Once done, we proceed to another query rule.

Here we use the same regular expression to catch the query but we don’t define any error text (which means that query will not return an error). We also define which user is allowed to execute it (admin_user in our case). We make sure this query is checked before the previous one, so we assigned a lower rule ID of 19 to it.

Once these two query rules are in place, we can test how they work. Let’s try to log in as an application user and run an ALTER TABLE query:

root@vagrant:~# mysql -P6033 -usbtest -ppass -h10.0.0.111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 43160
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table sbtest1 add index (pad);
ERROR 1148 (42000): You are not allowed to execute ALTER
mysql> ^DBye

As expected, we couldn’t execute this query and we received an error message. Let’s now try to connect using our ‘admin_user’:

root@vagrant:~# mysql -P6033 -uadmin_user -ppass -h10.0.0.111
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 43180
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table sbtest1 add index (pad);
Query OK, 0 rows affected (0.99 sec)
Records: 0  Duplicates: 0  Warnings: 0

We managed to execute the ALTER as we logged in using ‘admin_user’. This is a very simple way of ensuring that only appointed people can run schema changes on your databases.

Creating a whitelist of allowed queries

Finally, let’s consider a tightly locked environment where only predefined queries can be executed. ProxySQL can be easily utilized to implement such setup.

First of all, we need to remove all existing query rules before we can implement what we need. Then, we need to create a catch-all query rule, which will block all the queries:

The rest we have to do is to create query rules for all of the queries which are allowed. You can do one rule per query. Or you can use regular expressions if, for example, SELECTs are always ok to run. The only thing you have to remember is that the rule ID has to be smaller than the rule ID of this catch-all rule, and ensure that the query will eventually hit the rule with ‘Apply’ enabled.

We hope that this blog post gave you some insight into how you can utilize ClusterControl and ProxySQL to improve security and ensure compliance of your databases.