Skip to content
BranchesTags

Repository files navigation

Sample Database Firewall with Maxscale

Architecture

architecture

Setup

Run a mariadb server and a maxscale server

$ docker-compose up -d

Create a sample database

$ mysql -uroot -h 127.0.0.1 -p < sample.sql

Check maxscale state

$ docker-compose exec mxs maxctrl list servers
┌─────────┬─────────┬──────┬─────────────┬─────────────────────┬──────┐ 
│ Server  │ Address │ Port │ Connections │ State               │ GTID │
├─────────┼─────────┼──────┼─────────────┼─────────────────────┼──────┤
│ server1 │ db      │ 3306 │ 0           │ Auth Error, Running │      │
└─────────┴─────────┴──────┴─────────────┴─────────────────────┴──────┘

Seup dummy data

$ pipenv run python3 dummy_data.py

Filters

Query Log All Filter

https://maxscale.readthedocs.io/en/stable/Documentation/Filters/Query-Log-All-Filter/

sample configration

[Sample-Service]
type=service
router=readconnroute
router_options=running
servers=server1
user=maxscale
password=maxscale_password
filters=SampleLogFilter

[SampleLogFilter]
type=filter
module=qlafilter
match=SELECT.*FROM.*users
filebase=/tmp/sample
log_type=unified
flush=true

sample run

# Run sample query
$ mysql -usample -h 127.0.0.1 -P 4008 -p sampledb -e "SELECT * FROM users"

# Check query log
$ docker-compose exec mxs /bin/bash
root@882ee08125a4:/# tail -f /tmp/sample.unified
Date,User@Host,Query
2019-02-09 01:35:56,sample@172.18.0.1,SELECT * FROM users
2019-02-09 01:38:32,sample@172.18.0.1,select * from users

Database Firewall filter

https://maxscale.readthedocs.io/en/stable/Documentation/Filters/Database-Firewall-Filter/

sample configration

  • /etc/maxscale.cnf.d/example_maxscale.cnf
[Sample-Service]
type=service
router=readconnroute
router_options=running
servers=server1
user=maxscale
password=maxscale_password
filters=dbfw-blacklist

[dbfw-blacklist]
type=filter
module=dbfwfilter
action=block
rules=/etc/maxscale.modules.d/blacklist-rules.txt

sample run (deny select with regex)

# Rules file
$ cat /etc/maxscale.modules.d/blacklist-rules.txt
rule deny_select match regex '(i?).*select.*from.*users.*'
users %@% match any rules deny_select

# Run sample query
$ mysql -usample -h 127.0.0.1 -P 4008 -p sampledb -e "SELECT * FROM users"
ERROR 1141 (HY000) at line 1: Access denied for user 'sample'@'172.18.0.1' to database 'sampledb': Permission denied, query matched regular expression.

sample run (deny select with on_queries)

# Rules file
$ cat /etc/maxscale.modules.d/blacklist-rules.txt
rule deny_select match on_queries select
users %@% match any rules deny_select

# Run sample query
$ mysql -usample -h 127.0.0.1 -P 4008 -p sampledb -e "SELECT * FROM users"
ERROR 1141 (HY000) at line 1: Access denied for user 'sample'@'172.18.0.1' to database 'sampledb': Permission denied at this time.

sample run (deny select with columns)

# Rules file
$ cat /etc/maxscale.modules.d/blacklist-rules.txt
rule deny_select match columns name
users %@% match any rules deny_select

# Run sample query
$ mysql -usample -h 127.0.0.1 -P 4008 -p sampledb -e "SELECT name FROM users"
ERROR 1141 (HY000) at line 1: Access denied for user 'sample'@'172.18.0.1' to database 'sampledb': Permission denied to column 'name'.

$ mysql -usample -h 127.0.0.1 -P 4008 -p sampledb -e "SELECT * FROM users"
+-----+------------------+--------------------------------+----------+---------------+---------------------+
| id  | name             | email                          | zip      | tel           | credit_card_number  |
+-----+------------------+--------------------------------+----------+---------------+---------------------+
| 101 | 杉山 結衣        | hidaka@gmail.com               | 487-6450 | 080-2450-8701 | 4114080591739765    |
| 102 | 渚 真綾          | minoru40@yahoo.com             | 269-5000 | 57-1339-7205  | 3553715906133671    |
| 103 | 井上 修平        | taichikudo@hotmail.com         | 268-3379 | 06-1226-7644  | 4414568334566054    |
..snip..

sample run (deny delete with no_where_clause)

# Rules file
$ cat /etc/maxscale.modules.d/blacklist-rules.txt
rule deny_delete match no_where_clause on_queries delete
users %@% match any rules deny_delete

# Run sample query
# NG
$ mysql -usample -h 127.0.0.1 -P 4008 -psample_password sampledb -e "DELETE FROM users"
ERROR 1141 (HY000) at line 1: Access denied for user 'sample'@'172.18.0.1' to database 'sampledb': Required WHERE/HAVING clause is missing.

# OK
$ mysql -usample -h 127.0.0.1 -P 4008 -psample_password sampledb -e "DELETE FROM users WHERE id = 1"

Masking

https://maxscale.readthedocs.io/en/stable/Documentation/Filters/Masking/

sample configration

  • /etc/maxscale.cnf.d/example_maxscale.cnf
[Sample-Service]
type=service
router=readconnroute
router_options=running
servers=server1
user=maxscale
password=maxscale_password
filters=SampleMasking

[SampleMasking]
type=filter
module=masking
rules=/etc/maxscale.modules.d/sample-masking-rules.json

sample run

# Rule
$ cat /etc/maxscale.modules.d/sample-masking-rules.json
{
    "rules": [
        {
            "replace": {
                "column": "tel"
            },
            "with": {
                "value": "xxx-xxxx-xxxx"
            }
        },
        {
            "replace": {
                "column": "zip"
            },
            "with": {
                "value": "xxx-xxxx"
            }
        },
        {
            "replace": {
                "column": "credit_card_number"
            },
            "with": {
                "fill": "*"
            }
        }
    ]
}

# Run
$ mysql -usample -h 127.0.0.1 -P 4008 -psample_password sampledb -e "SELECT * FROM users limit 10"
+-----+------------------+-----------------------------+----------+---------------+--------------------+
| id  | name             | email                       | zip      | tel           | credit_card_number |
+-----+------------------+-----------------------------+----------+---------------+--------------------+
| 101 | 杉山 結衣        | hidaka@gmail.com            | xxx-xxxx | xxx-xxxx-xxxx | ****************   |
| 102 | 渚 真綾          | minoru40@yahoo.com          | xxx-xxxx | XXXXXXXXXXXX  | ****************   |
| 103 | 井上 修平        | taichikudo@hotmail.com      | xxx-xxxx | XXXXXXXXXXXX  | ****************   |
| 104 | 中津川 和也      | nuno@yahoo.com              | xxx-xxxx | XXXXXXXXXXXX  | ****************   |
| 105 | 杉山 春香        | mai42@gmail.com             | xxx-xxxx | xxx-xxxx-xxxx | ****************   |
| 106 | 三宅 桃子        | hirokawatomoya@hotmail.com  | xxx-xxxx | xxx-xxxx-xxxx | ****************   |
| 107 | 小林 英樹        | nagisaasuka@gmail.com       | xxx-xxxx | xxx-xxxx-xxxx | ***************    |
| 108 | 松本 学          | sugiyamaasuka@hotmail.com   | xxx-xxxx | xxx-xxxx-xxxx | ****************   |
| 109 | 加納 太一        | manabunomura@gmail.com      | xxx-xxxx | xxx-xxxx-xxxx | ************       |
| 110 | 中津川 健一      | atsushimiyagawa@hotmail.com | xxx-xxxx | XXXXXXXXXXXX  | ****************   |
+-----+------------------+-----------------------------+----------+---------------+--------------------+

Maxrows

https://maxscale.readthedocs.io/en/stable/Documentation/Filters/Maxrows/

sample configration

  • /etc/maxscale.cnf.d/example_maxscale.cnf
[Sample-Service]
type=service
router=readconnroute
router_options=running
servers=server1
user=maxscale
password=maxscale_password
filters=SampleMaxRows

[SampleMaxRows]
type=filter
module=maxrows
max_resultset_rows=10

Reference

About

Sample Database Firewall with Maxscale

Resources

Readme
Activity

Stars

2 stars

Watchers

1 watching

Forks

1 fork
Report repository

Releases

Packages

Contributors

Languages