In this blog post, we’ll look at ProxySQL firewalling (how to use ProxySQL as a firewall).
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.
Using ProxySQL
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.
How?
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,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT,
replace_pattern VARCHAR,
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,
error_msg VARCHAR,
OK_msg VARCHAR,
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,
comment VARCHAR)
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.
The rules
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
- Rule_id
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:
admin@127.0.0.1) [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 ***************************
hostgroup: 50
schemaname: windmills
digest: 0x18CA8FF2C9C53276
digest_text: SHOW GLOBAL STATUS
count_star: 141
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)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
at net.tc.stresstool.statistics.providers.MySQLStatus.getStatus(MySQLStatus.java:48)
at net.tc.stresstool.statistics.providers.MySQLSuper.collectStatistics(MySQLSuper.java:92)
at net.tc.stresstool.statistics.StatCollector.collectStatistics(StatCollector.java:258)
at net.tc.stresstool.StressTool.<init>(StressTool.java:198)
at net.tc.stresstool.StressTool.main(StressTool.java:282)
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)).
Queries/sec:
Queries/sec global
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).
Use match_digest
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… :
Using two application servers:
- Server A: Total Execution time = 207
- Server B: Total Execution time = 204
First of all, we notice that the execution time did not increase. This is mainly because we have CPU cycles to use in the ProxySQL box:
Here we have a bit of unbalance. We will investigate that in a separate session, but all in all, time/effort looks ok:
Here we have the first thing to notice. Comparing this to the baseline we defined, we can see that using the rules as match_digest significantly increased the execution time to 458ms:
Also notice that if we are in the range of nanoseconds, the cost of processing the query is now three times that of the baseline. Not too much, but if you add a stone to another stone and another stone and another stone … you end up building a huge wall.
So, what to do? Up to now, we saw that managing the firewall with ProxySQL is easy and it can be set at very detailed level – but the cost may not be what we expect it to be.
What can be done? Use DIGEST instead
The secret is to not use match_digest (which implies interpretation of the string) but to use the DIGEST of the query (which is calculated ahead and remains constant for that query).
Let us see what happens if we run the same load using DIGEST in the MYSQL_QUERY_RULES table:
Using two application servers:
- Server A: Total Execution time = 213
- Server B: Total Execution time = 209
No, this is not an issue with cut and paste. I had more or less the same execution time as without rules, at the seconds (different millisecond though):
Again, there is some unbalance, but a minor thing:
And we drop to 61ms for execution of all queries. Note that we improve the efficiency of the Query Processor from 148ms AVG to 61ms AVG.
Why? Because our rules using the DIGEST also have the instructions for read/write split, so requests can exit the Query Processor with all the information required at this stage (much more efficient).
Finally, when using the DIGEST the cost for query drops to 4us which is … LOW!
That’s it! ProxySQL using the DIGEST field from mysql_query_rules performs much better given that it doesn’t need to analyze the whole SQL string with regular expressions – it just matches the DIGEST.
Conclusions
ProxySQL can be effectively used as an SQL firewall, but some best practices should be taken in to consideration. First of all, try to use specific rules and be specific on what should be filtered/allowed. Use filter by schema or user or IP/port or combination of them. Always try to avoid match_digest and use digest instead. That allows ProxySQL to bypass the call to the regularExp lib and is far more efficient. Use stats_mysql_query_digest to identify the correct DIGEST.
Regarding this, it would be nice to have a GUI interface that allows us to manage these rules. That would make the usage of the ProxySQL much easier, and the maintenance/creation of rule_chains friendlier.
