Not long ago we had an internal discussion about security, and how to enforce a stricter set of rules to prevent malicious acts and block other undesired queries. ProxySQL came up as a possible tool that could help us in achieving what we were looking for. Last year I wrote about how to use ProxySQL to stop a single query.
That approach may be good for few queries and as a temporary solution. But what can we do when we really want to use ProxySQL as an SQL-based firewall? And more importantly, how to do it right?
First of all, let us define what “right” can be in this context.
For right I mean an approach that allows us to have rules matching as specifically as possible, and impacting the production system as little as possible.
To make this clearer, let us assume I have three schemas:
I want to have my firewall block/allow SQL access independently by each schema, user, eventually by source, and so on.
There are a few case where this is not realistic, like in SaaS setups where each schema represents a customer. In this case, the application will have exactly the same kind of SQL – just pointing to different schemas depending the customer.
Anyhow… ProxySQL allows you to manage query firewalling in a very simple and efficient way using the query rules.
In the mysql_query_rules table, we can define a lot of important things – one being setting our SQL firewall.
Let us take a look to the mysql_query_rules table:
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
flagIN INT NOT NULL DEFAULT 0,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
We can define rules around almost everything: connection source, port, destination IP/Port, user, schema, SQL text or any combination of them.
Given we may have quite a large set of queries to manage, I prefer to logically create “areas” around which add the rules to manage SQL access.
For instance, I may decide to allow a specific set of SELECTs to my schema windmills, but nothing more.
Given that, I allocate the set of rule IDs from 100 to 1100 to my schema, and add my rules in three groups.
- The exception that will bypass the firewall
- The blocking rule(s) (the firewall)
- The managing rules (post-processing, like sharding and so on)
There is a simple thing to keep in mind when you design rules for firewalling: do you need post-processing of the query or not?
In the case that you DON’T need post-processing, the rule can simply apply and exit the QueryProcessor. That is probably the most common scenario, and read/write splits can be defined in the exception rules assigned to the rule for the desired HostGroup.
If you DO need post-processing, the rule MUST have apply=0 and the FLAGOUT must be defined. That allows you to have additional actions once the query is beyond the firewall. An example is in case of sharding, where you need to process the sharding key/comment or whatever.
I will use the simple firewall scenario, given this is the topic of the current article.
Let us start with the easy one, set 2, the blocking rule:
insert into mysql_query_rules (rule_id,username,schemaname,match_digest,error_msg,active,apply) values(1000,'pxc_test','windmills','.','You cannot pass.....I am a servant of the Secret Fire, wielder of the flame of Anor,. You cannot pass.',1, 1);
In this query rule, I had defined the following:
- User connecting
- Schema name
- Any query
- Message to report
That rule will block ANY query that tries to access the schema windmills from application user pxc_test.
Now in set 1, I will add all the rules I want to let pass. I will report here one only, but all can be found in GitHub here (https://github.com/Tusamarco/blogs/tree/master/proxysql_firewall).
insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,schemaname,active,retries,apply,flagout,match_digest) values(101,6033,'pxc_test',52,'windmills',1,3,1,1000,'SELECT wmillAUTOINC.id,wmillAUTOINC.millid,wmillAUTOINC.location FROM wmillAUTOINC WHERE wmillAUTOINC.millid=.* and wmillAUTOINC.active=.*');
That is quite simple and straightforward, but there is an important element that you must note. In this rule, apply must have value of =1 always, to allow the query rule to bypass without further delay the firewall.
(Side Note: if you need post-processing, the flagout needs to have a value (like flagout=1000) and apply must be =0. That allows the query to jump to set 3, the managing rules.)
This is it, ProxySQL will go to the managing rules as soon as it finds a matching rule that allows the application to access my database/schema, or it will exit if apply=1.
A graph will help to understand better:
Rule set 3 has the standard query rules to manage what to do with the incoming connection, like sharding or redirecting SELECT FOR UPDATE, and so on:
insert into mysql_query_rules (rule_id,proxy_port,schemaname,username,destination_hostgroup,active,retries,match_digest,apply,flagin) values(1040,6033,'windmills','pxc_test',50,1,3,'^SELECT.*FOR UPDATE',1,1000);
Please note the presence of the flagin, which matches the flagout above.
Setting rules, sometimes thousands of them, can be very confusing. It is very important to correctly plan what should be in as an excluding rule and what should not. Do not rush, take your time and identify the queries you need to manage carefully.
Once more ProxySQL can help us. Querying the table stats_mysql_query_digest tells us exactly what queries were sent to ProxySQL:
email@example.com) [main]>select hostgroup,schemaname,digest,digest_text,count_star from stats_mysql_query_digest where schemaname='windmills' order by count_star desc;
The above query shows us all the queries hitting the windmills schema. From there we can decide which queries we want to pass and which not.
>select hostgroup,schemaname,digest,digest_text,count_star from stats_mysql_query_digest where schemaname='windmills' order by count_star desc limit 1G
*************************** 1. row ***************************
digest_text: SHOW GLOBAL STATUS
Once we have our set done (check on github for an example), we are ready to check how our firewall works.
By default, I suggest you to keep all the exceptions (in set 1) with active=0, just to test the firewall.
For instance, my application generates the following exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You cannot pass.....I am a servant of the Secret Fire, wielder of the flame of Anor,. You cannot pass.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
Activating the rules, will instead allow your application to work as usual.
What is the impact?
First, let’s define the baseline by running the application without any rule blocking (but only the r/w split (set 3)).
Using two application servers:
- Server A: Total Execution time = 213
- Server B: Total Execution time = 209
Queries/sec per server
As we can see, queries are almost equally distributed.
QueryProcessor time taken/Query processed total
All queries are processed by QueryProcessor in ~148ms AVG (total).
QueryProcessor efficiency per query
The single query cost is in nanoseconds (avg 10 us).
Once we’ve defined the baseline, we can go ahead and activate all the rules using the match_digest. Run the same tests again and… :