MariaDB Server and MariaDB MaxScale provides a secure, high-performance database platform. Some aspects of security goes into MariaDB Server and some into MariaDB MaxScale. This blog post describes one of the security features of MariaDB MaxScale, the Database Firewall filter.
MariaDB MaxScale is a powerful tool mostly used for database load balancing, and as such has many benefits, which we’ll cover in our upcoming webinar about advanced database proxies. Another aspect of MariaDB MaxScale though is that there are many additional modules that can be used, in particular a range of filters that can be applied, and in this blog we are looking at the Database Firewall filter.
The Database Firewall filter allows you to specify which SQL statements are allowed to run and which are not, by using what are called whitelists and blacklists respectively. You can combine blacklists and whitelists also. In addition to this, the Database Firewall filter also allows a number of other rules to be applied.
Configuring MariaDB MaxScale and MariaDB Server
Before we start working with configuring the specifics of the Database Firewall filter in MariaDB MaxScale, let us have a look at how to set up a basic configuration of MaxScale and how to configure MariaDB Server to work with MariaDB MaxScale when the latter is used to implement security features.
For MariaDB MaxScale to add a level of security, we have to make sure that we don’t have traffic bypassing MariaDB MaxScale and access MariaDB Server directly. There are several means of doing this, but in this case I’m going to choose the easy way out. We will run MariaDB MaxScale and MariaDB Server on the same server. Another assumption is that we want to maximize application transparency as much as possible, so application really should not have to have any special settings to run with MariaDB MaxScale as compared to when connecting directly to MariaDB Server.
For this to work, obviously, we are going to work with an environment where MariaDB Server and MariaDB MaxScale are installed on the same server.
Configuring MariaDB Server
What we are going to do here is to make sure that only MaxScale, or any other service with an appropriate username and password that runs on the same server as MariaDB Server, can connect to MariaDB Server. This we are going to do by changing the bind-address of MariaDB Server. So what is this, you ask? A bind address is the network interface that a program that listens to the network listens on. Usually you don’t care much about this, there is just one interface in most cases anyway, right?
Well no, the by far most common number of interfaces are 2! So where is that second RJ-45 connector on your box then? The answer is that there is none, this is a virtual interface called the loopback. The bind-address is always associated with a network address, which in the case of your normal network interface is the node address of the server on the network, you connect to something using that address and the traffic is directed there through some kind of magic.
The loopback interface is only available on the box itself and it is always associated with the address 127.0.0.1. This address is not going through any kind of network hardware, it is all in software! I told you this was magic, right!
For a service running on a server, such as MariaDB Server, MaxScale or Apache, they are by default set up to listen or bind on IPADDR_ANY, which means that they listen on any interface on a given port, including the loopback interface. Note that you can listen on the same port on the same box, as long as they are on different interfaces. What we are going to do first is to have MariaDB Server listen only on connections on the loopback interface. Head off to edit your MariaDB Server configuration file, like /etc/my.cnf.d/server.cnf if you are running CentOS / Red Hat and set up bind-address in the mysqld section like this:
Note that we do not have to set the server to listen to a different port, the default 3306 is just fine as even though we are about to set up MariaDB MaxScale to listen to the same port, we are also to set up MariaDB MaxScale to listen bind on another interface, i.e. the normal ethernet interface.
Configuring MariaDB MaxScale to work with MariaDB Server
We now have to force MariaDB MaxScale to listen to the ethernet interface only, so it doesn’t collide with MariaDB Server, and also to listen on port 3306. This is for the listener, and we are to give that an appropriate name as we in this case are using MaxScale as a firewall only. So head off to that old time favorite text editor of yours and edit the MariaDB MaxScale configuration file, which is probably in /etc/maxscale.cnf and add a section like this:
[Firewall Listener] type=listener service=Firewall Service protocol=MySQLClient address=192.168.0.170 port=3306
Replacing the address with the address of your server you are testing this on, obviously.
Starting up MariaDB MaxScale and MariaDB Server
We are soon ready to start up, but MariaDB MaxScale need some more work on the configuration. The default configuration that comes with MariaDB MaxScale has several different services in it and a lot of comments, here I will provide a configuration that is the bare minimum for MariaDB MaxScale to work. We need the listener specific above of course, but also a few other things in our MariaDB MaxScale configuration file /etc/maxscale.cnf.
This section in MariaDB MaxScale defines how MaxScale talks to the different servers. In this case, we will connect to just one server, so that is easy:
[server1] type=server address=127.0.0.1 port=3306 protocol=MySQLBackend
The thing to note here is that as we have MariaDB MaxScale talking to MariaDB Server on the loopback interface, we set address to 127.0.0.1, not the address of our host on the network.
The we configure a monitor that checks the status of our server, again, this is a bare minimum configuration:
[MySQL Monitor] type=monitor module=mysqlmon servers=server1 user=myuser passwd=mypwd
The servers parameter points to the server defined in the section above. The user and password arguments are used by MaxScale to connect to MariaDB Server to check the status of it. This user is created like this (but you can, and should, use a different username and password than the one used here). From the MariaDB command line on the server we are working with:
$ mysql -u root Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 14 Server version: 10.2.7-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> CREATE USER 'myuser'@'127.0.0.1' IDENTIFiED BY 'mypwd'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SELECT ON mysql.user TO 'myuser'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'myuser'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO 'myuser'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'myuser'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec)
OK, that’s it for the monitor and the listener, so what remains before we start looking at the focal point for this blog post, the filter, what remains is the router or service.
As we are focusing on the Database Firewall filter here, we are using just a single basic router for this, mainly to have something to attach our filter to. We will be using the readconnroute service router here. The configurations looks like this:
[Firewall Service] type=service router=readconnroute servers=server1 user=myuser passwd=mypwd router_options=running filters=Firewall
Most of these parameters are rather obvious I guess. The username and password are used to be able to extract username and passwords from the server to use for authentication. In the Monitor configuration above we showed how to create a database user with appropriate privileges, and this included the necessary privileges both for the Monitor as well as for the Service. Also, you would want to use a different username and password from my example here. The router_options set to running means that we can connect to any server, as long as it is running. And finally the filters setting points to the filter we will be using, and lets move on to this.
Firewall filter configuration
The firewall filter is configured in two places, first it is configured in the maxscale configuration just as usual, and then there is a separate file with the firewall rules. Let’s start with the maxscale.cnf settings first:
[Firewall] type=filter module=dbfwfilter action=allow rules=/etc/maxscale.modules.d/rules.txt
There are only two settings here that are interesting, one is the action=allow setting. What this means is that the rules we are to set up define the SQL statements that are allowed, and any other are disallowed, this is called whitelisting. You can define a set up rules for statements that you want to prohibit, and in that case you set allow=block, and this is then called blacklisting. In some cases you might want to do both blacklisting and whitelisting, and to achieve this you create two filters, and then you pipe one into the other in the filters setting in the services.
One more parameter is interesting for this filter, and this is the rules setting, which points to the file where the firewall rules defined, into case /etc/maxscale.modules.d/rules.txt, which is a file that we will create now.
There are several means to define the firewall rules, for more information see https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-21-database-firewall-filter/. In this example, I will set up a few very basic firewall rules and put them in the file /etc/maxscale.modules.d/rules.txt, let’s look at it first and then I’ll explain them:
rule allow_select deny regex '^(?i)select .*$' rule allow_show deny regex '^(?i)show .*$' users %@% match any rules allow_select allow_show
The first two defines SQL statements that we are allowed to run, that it says “deny” is not relevant here, that is just part of the syntax, instead it is the “allow” setting for the filter instance that is in effect. As you can image, the SQL statement is matched using a regular expression, in this case PCRE (Perl Regular Expression) is used, see https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions for more details. Let’s look closer at the three rules we have defined here. The first one says this:
- ^ – The pattern matchin starts at the beginning of the string.
- (?i) – Perform case insensitive matching
- select – The exact word “select” followed by a space.
- .* – Followed by 0 or more occurences of any character
- $ – Followed by end of line
Given this, the second pattern should be obvious. In short, we allow any command that starts with the keyword SELECT or SHOW.
The “users” statement is used to map rules to users, in this case we are matching all users, you can have any kind of wildcards here. Then we say that a command is allowed that matches any of the given patterns.
Having set up the /etc/maxscale.cnf and the rules in /etc/maxscale.modules.d/rules.txt, we are ready to test it. First we restart MaxScale (again this is for CentOS 7):
$ sudo systemctl restart maxscale
Following that, let’s connect to MariaDB through MaxScale and see what happens. Note that you have to connect as a non-root user, as root access is blocked by MariaDB MaxScale by default. Also, remember not to connect to the MySQL socket. So:
$ mysql -h 192.168.0.170 test Welcome to the MariaDB monitor. Commands end with ; or g. Your MySQL connection id is 8266 Server version: 10.0.0 2.1.6-maxscale MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MySQL [test]> select user(); +------------------+ | user() | +------------------+ | anders@localhost | +------------------+ 1 row in set (0.00 sec) MySQL [test]> select c1 from t1; Empty set (0.00 sec) MySQL [test]> insert into t1 values(1); ERROR 1141 (HY000): Access denied for user 'anders'@'192.168.0.170': Permission denied, query matched regular expression.
In this blog, I have shown how to set up a very basic configuration, just to get started. I will follow up later with a blog that includes some details and more advanced configurations. As we have seen, this isn’t really complicated per se, what takes some time is to develop a set of rules that match the SQL that is executed by your application.
If you want to dive deeper into advanced database proxies and MariaDB MaxScale, please join our upcoming webinar on Why Innovative Enterprises Deploy Advanced Database Queries on October 18.