Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Getting Started With MariaDB MaxScale Database Firewall Filter

DZone's Guide to

Getting Started With MariaDB MaxScale Database Firewall Filter

Learn how to use MariaDB MaxScale's Database Firewall filter in order to specify which SQL statements are allowed to run and which are not.

· Database Zone
Free Resource

Learn how to create flexible schemas in a relational database using SQL for JSON.

MariaDB Server and MariaDB MaxScale provide a secure, high-performance database platform. Some aspects of security go 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 covered in our 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's 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 so we can 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 the application really should not have to have any special settings to run with MariaDB MaxScale 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. 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 is 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.

Theloopback 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 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:

[mysqld]
bind-address=127.0.0.1

Note that we do not have to set the server to listen on 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 all-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

(Replace 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 needs 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.

Server Configuration

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 the address to 127.0.0.1 — not the address of our host on the network.

Monitor Configuration

Then, 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 of this blog post — the filter — what remains is the router or service.

Service Configuration

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 configuration 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 let's 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 setup 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. 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: 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.

Firewall Rules

There are several means to define the firewall rules; for more information, see here. 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 the rules:

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, as 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. Let's look closer at the rules we have defined here. The first one says this:

  •  ^: The pattern-matching 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 occurrences 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, and 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.

Testing

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.

Conclusion

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.

Create flexible schemas using dynamic columns for semi-structured data. Learn how.

Topics:
database ,mariadb ,firewall ,tutorial ,maxscale ,filter ,sql

Published at DZone with permission of Anders Karlsson, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}