Maxscale-SQL防火墙(3) – 运维生存时间

Maxscale-SQL防火墙(3)已关闭评论

A+

所属分类：MySQL

这篇文章的目的主要是稍微介绍一些Maxscale有SQL防火墙这个功能，具体更多关于Maxscale如何使用SQL防火墙，或配置SQL匹配过滤等功能还得去浏览一下它的手册

https://github.com/mariadb-corporation/MaxScale/tree/2.0.1/Documentation

我们经常说禁止线上写没有WHERE的QDL、MDL，SQL防火墙就能很好的做这样的事情。

Maxscale的SQL防火墙需要有：

防火墙规则文件
将规则文件配置进 /etc/maxscale.cnf

实现

防火墙规则文件

vim /usr/local/maxscale/etc/blacklists.rule
# limit Query times in Table t1 限制 20 秒内 某语句只能查询 5 次 超过则冻结 10 秒
# rule limit_rate_of_queries deny limit_queries 5 20 10
# rule query_regex deny regex '.*select.*from.*t1.*'
#
# users %@% match all rules limit_rate_of_queries query_regex
 
# Deny delete table t1 no WHERE clause 在某一时间段不允许删除语句没有WHERE
rule safe_delete deny no_where_clause at_times 14:40:00-14:47:00 on_queries delete
rule managers_table deny regex '.*[fF][rR][oO][mM].*[tT]1.*'
 
users %@% match all rules safe_delete managers_table

vim /usr/local/maxscale/etc/blacklists.rule

# limit Query times in Table t1 限制 20 秒内某语句只能查询 5 次超过则冻结 10 秒

# rule limit_rate_of_queries deny limit_queries 5 20 10

# rule query_regex deny regex '.*select.*from.*t1.*'

# users %@% match all rules limit_rate_of_queries query_regex

# Deny delete table t1 no WHERE clause 在某一时间段不允许删除语句没有WHERE

rule safe_delete deny no_where_clause at_times 14:40:00-14:47:00 on_queries delete

rule managers_table deny regex '.*[fF][rR][oO][mM].*[tT]1.*'

users %@% match all rules safe_delete managers_table

/etc/maxscale.cnf 配置文件主要配置

这边使用Maxscale只读服务来说明配置现象

# 配置使用 规则的服务
[Read-Only Service]
...
filters=dbfw-blacklist
 
# 配置规则
[dbfw-blacklist]
type=filter
module=dbfwfilter
action=block
rules=/usr/local/maxscale/etc/blacklists.rule

# 配置使用规则的服务

[Read-Only Service]

...

filters=dbfw-blacklist

# 配置规则

[dbfw-blacklist]

type=filter

module=dbfwfilter

action=block

rules=/usr/local/maxscale/etc/blacklists.rule

/etc/maxscale.cnf 完整配置

[root@normal_11 tmp]# cat /etc/maxscale.cnf 
###################################################
# CREATE USER maxscale@'%' IDENTIFIED BY "123456";
# GRANT replication slave, replication client ON *.* TO maxscale@'%';
# GRANT SELECT ON mysql.* TO maxscale@'%';
# GRANT ALL ON maxscale_schema.* TO maxscale@'%';
# GRANT SHOW DATABASES ON *.* TO maxscale@'%';
# groupadd maxscale
# useradd -g maxscale maxscale
# cd /opt
# tar -zxf maxscale-2.0.1.rhel.7.tar.gz
# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale
# chown -R maxscale:maxscale /usr/local/maxscale
# mkdir -p /u01/maxscale/{data,cache,logs,tmp}
# mkdir -p /u01/maxscale/logs/{binlog,trace}
# chown -R maxscale:maxscale /u01/maxscale
# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/
# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456
###################################################
 
[maxscale]
# 开启线程个数，默认为1.设置为auto会同cpu核数相同
threads=auto
# timestamp精度
ms_timestamp=1
# 将日志写入到syslog中
syslog=1
# 将日志写入到maxscale的日志文件中
maxlog=1
# 不将日志写入到共享缓存中，开启debug模式时可打开加快速度
log_to_shm=0
# 记录告警信息
log_warning=1
# 记录notice
log_notice=1
# 记录info
log_info=1
# 不打开debug模式
log_debug=0
# 日志递增
log_augmentation=1
 
# 相关目录设置
basedir=/usr/local/maxscale/
logdir=/u01/maxscale/logs/trace/
datadir=/u01/maxscale/data/
cachedir=/u01/maxscale/cache/
piddir=/u01/maxscale/tmp/
 
[server1]
type=server
address=192.168.137.21
port=3306
protocol=MySQLBackend
serv_weight=1
 
[server2]
type=server
address=192.168.137.22
port=3306
protocol=MySQLBackend
serv_weight=3
 
[server3]
type=server
address=192.168.137.23
port=3306
protocol=MySQLBackend
serv_weight=3
 
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
# 监控心态为 10s
monitor_interval=10000
# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点
detect_stale_master=true
# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)
# detect_replication_lag=true
 
[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
router_options=slave
# 允许root用户登录执行
enable_root_user=1
# 查询权重
weightby=serv_weight
filters=dbfw-blacklist
 
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=1D30C1E689410756D7B82C233FCBF8D9
max_slave_connections=100%
# sql语句中的存在变量只指向master中执行
use_sql_variables_in=master
# 允许root用户登录执行
enable_root_user=1
# 允许主从最大间隔(s)
max_slave_replication_lag=3600
filters=Hint
 
[MaxAdmin Service]
type=service
router=cli
 
[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
 
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
 
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=/u01/maxscale/tmp/maxadmin.sock
port=6603
 
[dbfw-blacklist]
type=filter
module=dbfwfilter
action=block
rules=/usr/local/maxscale/etc/blacklists.rule

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

[root@normal_11 tmp]# cat /etc/maxscale.cnf

###################################################

# CREATE USER maxscale@'%' IDENTIFIED BY "123456";

# GRANT replication slave, replication client ON *.* TO maxscale@'%';

# GRANT SELECT ON mysql.* TO maxscale@'%';

# GRANT ALL ON maxscale_schema.* TO maxscale@'%';

# GRANT SHOW DATABASES ON *.* TO maxscale@'%';

# groupadd maxscale

# useradd -g maxscale maxscale

# cd /opt

# tar -zxf maxscale-2.0.1.rhel.7.tar.gz

# ln -s /opt/maxscale-2.0.1.rhel.7 /usr/local/maxscale

# chown -R maxscale:maxscale /usr/local/maxscale

# mkdir -p /u01/maxscale/{data,cache,logs,tmp}

# mkdir -p /u01/maxscale/logs/{binlog,trace}

# chown -R maxscale:maxscale /u01/maxscale

# /usr/local/maxscale/bin/maxkeys /u01/maxscale/data/

# /usr/local/maxscale/bin/maxpasswd /u01/maxscale/data/.secrets 123456

###################################################

[maxscale]

# 开启线程个数，默认为1.设置为auto会同cpu核数相同

threads=auto

# timestamp精度

ms_timestamp=1

# 将日志写入到syslog中

syslog=1

# 将日志写入到maxscale的日志文件中

maxlog=1

# 不将日志写入到共享缓存中，开启debug模式时可打开加快速度

log_to_shm=0

# 记录告警信息

log_warning=1

# 记录notice

log_notice=1

# 记录info

log_info=1

# 不打开debug模式

log_debug=0

# 日志递增

log_augmentation=1

# 相关目录设置

basedir=/usr/local/maxscale/

logdir=/u01/maxscale/logs/trace/

datadir=/u01/maxscale/data/

cachedir=/u01/maxscale/cache/

piddir=/u01/maxscale/tmp/

[server1]

type=server

address=192.168.137.21

port=3306

protocol=MySQLBackend

serv_weight=1

[server2]

type=server

address=192.168.137.22

port=3306

protocol=MySQLBackend

serv_weight=3

[server3]

type=server

address=192.168.137.23

port=3306

protocol=MySQLBackend

serv_weight=3

[MySQL Monitor]

type=monitor

module=mysqlmon

servers=server1,server2,server3

user=maxscale

passwd=1D30C1E689410756D7B82C233FCBF8D9

# 监控心态为 10s

monitor_interval=10000

# 当复制slave全部断掉时，maxscale仍然可用，将所有的访问指向master节点

detect_stale_master=true

# 监控主从复制延迟，可用后续指定router service的(配置此参数请求会永远落在 master)

# detect_replication_lag=true

[Read-Only Service]

type=service

router=readconnroute

servers=server1,server2,server3

user=maxscale

passwd=1D30C1E689410756D7B82C233FCBF8D9

router_options=slave

# 允许root用户登录执行

enable_root_user=1

# 查询权重

weightby=serv_weight

filters=dbfw-blacklist

[Read-Write Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=maxscale

passwd=1D30C1E689410756D7B82C233FCBF8D9

max_slave_connections=100%

# sql语句中的存在变量只指向master中执行

use_sql_variables_in=master

# 允许root用户登录执行

enable_root_user=1

# 允许主从最大间隔(s)

max_slave_replication_lag=3600

filters=Hint

[MaxAdmin Service]

type=service

router=cli

[Read-Only Listener]

type=listener

service=Read-Only Service

protocol=MySQLClient

port=4008

[Read-Write Listener]

type=listener

service=Read-Write Service

protocol=MySQLClient

port=4006

[MaxAdmin Listener]

type=listener

service=MaxAdmin Service

protocol=maxscaled

socket=/u01/maxscale/tmp/maxadmin.sock

port=6603

[dbfw-blacklist]

type=filter

module=dbfwfilter

action=block

rules=/usr/local/maxscale/etc/blacklists.rule

查询结果

HH@192.168.137.11 03:08:26 [(none)]>SELECT * FROM test.t1;
ERROR 1141 (HY000): Access denied for user 'HH'@'192.168.137.11': Permission denied, query matched regular expression.
 
# 日志输出
2016-11-05 15:09:46.045   [5]  info   : (rule_matches): dbfwfilter: rule 'managers_table': regex matched on query

HH@192.168.137.11 03:08:26 [(none)]>SELECT * FROM test.t1;

ERROR 1141 (HY000): Access denied for user 'HH'@'192.168.137.11': Permission denied, query matched regular expression.

# 日志输出

2016-11-05 15:09:46.045 [5] info : (rule_matches): dbfwfilter: rule 'managers_table': regex matched on query

提示：虽然上面的设置以及达到了无WHERE不能执行的目的，当时其实这样跟现实的程序的编写还是有出入了。往往程序员基本都知道为了让查询不报错都会写成 WHERE 1=1，这是一个小技巧啦。当是这样的语句就没办法通过Maxscale来过滤了。

期待

还是很之后Maxscale能将WHERE 1=1 也过滤去了，可以通过一个Hint来解决能执行没有WHERE的SQL。

昵称: HH

QQ: 275258836

ttlsa群交流沟通(QQ群②: 6690706 QQ群③: 168085569 QQ群④: 415230207（新）微信公众号: ttlsacom)

感觉本文内容不错，读后有收获？

逛逛衣服店，鼓励作者写出更好文章。

微信公众号

扫一扫关注运维生存时间公众号，获取最新技术文章~

实现

期待

登录 注册 找回密码

微信

登录注册找回密码