proxysql权限控制以及设置账号防火墙白名单

  • 贺子_DBA时代

    2023-07-22 16:46:39
  • MySQL
  • 原创
一、proxysql的账号配置表mysql_users
mysql_users
Table mysql_users defines MySQL users that clients can use to connect to ProxySQL, and then used to connect to backends.
表mysql_users定义了mysql用户，客户端可以使用这些用户连接到ProxySQL，然后再连接到后端。
Admin> SHOW CREATE TABLE mysql_users\G *************************** 1. row ***************************       table: mysql_users Create Table: CREATE TABLE mysql_users (    username VARCHAR NOT NULL,    password VARCHAR,    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,    default_hostgroup INT NOT NULL DEFAULT 0,    default_schema VARCHAR,    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',    comment VARCHAR NOT NULL DEFAULT '',    PRIMARY KEY (username, backend),    UNIQUE (username, frontend)) 1 row in set (0.00 sec)
The fields have the following semantics:
  • username, password – credentials for connecting to the mysqld or ProxySQL instance. Password can be in clear text, or hashed. See also Password management
  • active – the users with active = 0 will be tracked in the database, but will never be loaded in the in-memory data structures
active=0的用户将在数据库中被跟踪，但永远不会加载到内存中的数据结构中
  • use_ssl – if set to 1, the user is forced to authenticate with using an SSL certificate. See also SSL Support
如果设置为1，则强制用户使用SSL证书进行身份验证。另请参阅SSL支持
  • default_hostgroup – if there is no matching rule for the queries sent by this user, the traffic it generates is sent to the specified hostgroup
default_hostgroup–如果此用户发送的查询没有匹配规则，则它生成的流量将发送到指定的主机组
  • default_schema – the schema to which the connection should change by default
default_schema–默认情况下连接应更改到的schema
  • schema_locked – not supported yet (TODO: check)
  • transaction_persistent – if this is set for the user with which the MySQL client is connecting to ProxySQL (thus a “frontend” user – see below), transactions started within a hostgroup will remain within that hostgroup regardless of any other rules
transaction_persistent –如果这是为MySQL客户端连接到ProxySQL的用户（因此是“前端”用户–见下文）设置的，则在主机组内启动的事务将保留在该主机组内，而不考虑任何其他规则
  • fast_forward – if set it bypasses the query processing layer (rewriting, caching) and passes through the query directly as is to the backend server.
fast_forward–如果设置了它，它将绕过查询处理层（重写、缓存），并按原样直接通过查询传递到后端服务器。
  • frontend – if set to 1, this (username, password) pair is used for authenticating to the ProxySQL instance
frontend–如果设置为1，则此（用户名、密码）对用于向ProxySQL实例进行身份验证
  • backend – if set to 1, this (username, password) pair is used for authenticating to the mysqld servers against any hostgroup
backend–如果设置为1，则此（用户名、密码）对用于针对任何主机组向mysqld服务器进行身份验证  
  • max_connections – defines the maximum number of allowable frontend connections for a specific user.
max_connections-定义特定用户允许的最大前端连接数。
  • attributes – ToDo
  • comment – text field that can be used for any purpose defined by the user. Could be a description of what the cluster stores, a reminder of when the hostgroup was added or disabled, or a JSON processed by some checker script.
comment–可用于用户定义的任何目的的文本字段。可以是对集群存储内容的描述，何时添加或禁用主机组的提醒，或者由某些检查器脚本处理的JSON。  
Note, currently all users need both “frontend” and “backend” set to 1 . Future versions of ProxySQL will separate the crendentials between frontend and backend. In this way frontend will never know the credential to connect directly to the backend, forcing all the connections through ProxySQL and increasing the security of the system.
注意，目前所有用户都需要将“前端”和“后端”设置为1。ProxySQL的未来版本将分离前端和后端之间的凭证。通过这种方式，前端永远不会知道直接连接到后端的凭据，从而强制所有连接通过ProxySQL并提高系统的安全性。
Fast forward notes:
  • it doesn’t require a different port : full features proxy logic and “fast forward” logic is implemented in the same code/module
  • fast forward is implemented on a per-user basis : depending on the user that connects to ProxySQL , fast forward is enabled or disabled
  • fast forward algorithm is enabled after authentication : the client still authenticates to ProxySQL, and ProxySQL will create a connection when the client will start sending traffic. This means that the connections’ errors are still handled during the connect phase.
  • fast forward does NOT support SSL
  • if using compression, it must be enabled on both ends
Note: users in mysql_users shouldn’t be used also for admin-admin_credentials and admin-stats_credentials
快进注释：
它不需要不同的端口：全功能代理逻辑和“快进”逻辑在同一代码/模块中实现
快进是在每个用户的基础上实现的：取决于
连接到ProxySQL的用户，启用或禁用快进
身份验证后启用快进算法：客户端
仍然向ProxySQL进行身份验证，并且ProxySQL将创建一个连接
客户端何时开始发送流量。这意味着
连接的错误仍然在连接阶段处理。
快进不支持SSL
如果使用压缩，则必须在两端启用
注意：mysql_users中的用户不应该同时用于admin-admin_credentials和admin-stats_credential
二、如果proxysql管理多套主从架构的mysql，请问如果做权限控制？
1）定义主从架构的读写分离的组信息，一套主从架构的mysql在mysql_replication_hostgroups表中对应一条数据
针对主从架构如下所示设置： 注意如果要管理两套主从架构的mysql集群，那么就需要插入两条数据！ hostgroup_id 分别为 5、6和7、8两组！
mysql_replication_hostgroups中的每一行数据代表一对writer_hostgroup和reader_hostgroup的读写分离组。
insert into  mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)values(5,6,'read_only','MySQL Replication fa
ailover');
insert into  mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)values(7,8,'read_only','MySQL Replication fa
ailover');
2）定义后端MySQL实例信息：建议一套集群的hostgroup_id一样，统一设置成writer_hostgroup的值！我这边测试如果不设置成writer_hostgroup的值，就会报错！不知道咋回事？
主从集群一：这里的hostgroup_id=7，是因为mysql_replication_hostgroup的writer_hostgroup=7的那组读写配置！
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (7,'172.16.196.192',3306,'rpl1');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (7,'172.16.196.192',3307,'rpl1');
主从集群二：这里的hostgroup_id=5，是因为mysql_replication_hostgroup的writer_hostgroup=5的那组读写配置！
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.60',3306,'rpl2');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.61',3306,'rpl2');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.62',3306,'rpl2');
3）假设集群一有库：liu ，集群二有库：liuwenhe
添加proxysql的账号，注意当前版本2.5.2-217-g7f727b3中，需要在后端MySQL实例也创建上如下所示的两个账号！
ProxySQL Admin> select username,default_hostgroup,default_schema from  mysql_users  where  username in ('app_liuwenhe','app_liu');
+--------------+-------------------+----------------+
| username     | default_hostgroup | default_schema |
+--------------+-------------------+----------------+
| app_liu       | 7                      | liu            |
| app_liuwenhe | 5                  |    liuwenhe            |
+--------------+-------------------+----------------+
2 rows in set (0.00 sec)
4）添加读写分离的规则：规则中明确说明那个用户，那个schema的，并且针对那个组的什么规则！
查询的sql路由到只读节点组,根据前面配置可知，只读组的id包括：6、8
INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,active,match_pattern,destination_hostgroup,apply) VALUES
(2,1,'app_liuwenhe','liuwenhe',1,'^select',6,1),
(3,1,'app_liu','liu',1,'^select',8,1);
特殊查询路由到 writer_hostgroup组，写的组包括：5、7
insert into mysql_query_rules(rule_id,active,username,schemaname,match_pattern,destination_hostgroup,apply) values (4,1,'app_liuwenhe','liuwenhe','^SELECT.*FOR UPDATE$',5,1),
(5,1,'app_liu','liu','^SELECT.*FOR UPDATE$',7,1)
结果如下所示：
ProxySQL Admin> select username,schemaname,match_pattern,destination_hostgroup from  mysql_query_rules  where  username in ('app_liuwenhe','app_liu');
+--------------+------------+----------------------+-----------------------+
| username     | schemaname | match_pattern        | destination_hostgroup |
+--------------+------------+----------------------+-----------------------+
| app_liuwenhe | liuwenhe   | ^select              | 6                     |
| app_liuwenhe | liuwenhe   | ^SELECT.*FOR UPDATE$ | 5                     |
| app_liu      | liu        | ^select              | 8                     |
| app_liu      | liu        | ^SELECT.*FOR UPDATE$ | 7                     |
+--------------+------------+----------------------+-----------------------+
4 rows in set (0.01 sec)
5）这样就可以通过如下方式访问proxysql了
登录hostgroup_id=7的这套集群一中的库 liu，方式如下：
mysql -uapp_liu  -pLiuwenhe#123 -h172.16.196.192  -P6033
登录hostgroup_id=5的这套集群二中的库 liuwenhe，方式如下：
mysql -uapp_liuwenhe  -pLiuwenhe#123 -h172.16.196.192  -P6033
二、权限如果做proxysql的账号白名单
之前版本，如果查询限制ip、定向的根据指纹进行路由，则通过mysql_query_rules可能需要定义很多复杂的规则，从ProxySQL2.0.9开始，启用了防火墙功能。其简化了这类的需求配置。但是当前proxysql还不支持网段级别的限制！
1）防火墙的两张表
mysql_firewall_whitelist_users
设置防火墙用户的表，一个用户由username和client_address作为唯1主键；
主要字段
active：表示是否是否生效；
username：指定ProxySQL中mysql_users表定义的用户名；
client_address：表示连接ProxySQL的客户端IP，为空表示匹配任意ip；需注意其只支持ip，暂不支持子网段；
mode：
OFF模式表示允许定义的username@client_address用户执行任意的查询，完全忽略mysql_firewall_whitelist_rules中定义的规则；
DETECTING模式下，允许用户查询，但当执行的SQL指纹不在mysql_firewall_whitelist_rules中时，则会在错误日志中输出一条告警；
PROTECTING模式下，只允许用户执行在mysql_firewall_whitelist_rules中定义的SQL指纹语句，如果不在该表里，则将会报错；
注意：
即使之前只指定用户名并设置模式配置了同一用户，也会应用同时指定用户名和client_address的情况。
即：如果仅使用mode=PROTECTING配置用户名，但稍后使用client_address和mode=OFF添加相同的用户名，则将应用后一项。
mysql_firewall_whitelist_rules
定义防火墙SQL指纹规则，一条规则由username、client_address、schemaname、flagIN、digest共同组成；如果mysql_firewall_whitelist_users中有用户匹配执行的SQL，且其mode为DETECTING或者PROTECTING模式，则将会在该表中查找SQL指纹规则，以决定执行的SQL是否会被执行；
说明：通常建议对已经运行一段时间的业务SQL进行收集整理，挑选出受信SQL后存放到该表中。
INSERT  INTO mysql_firewall_whitelist_rules(active, username, client_address,
schemaname, flagIN, digest, comment)SELECT DISTINCT 1, username,
client_address, schemaname, 0, digest, '' FROM
stats_history.history_mysql_query_digest;
如果该表为空，则在PROTECTING模式下，将拒绝任何SQL的执行。
2）防火墙相关参数
mysql-firewall_whitelist_enabled
是否开启防火墙。
mysql-firewall_whitelist_errormsg
用来返回给执行被阻止SQL的客户端信息。
3）测试防火墙白名单功能：限制前面app_liuwenhe这个用户，只允许在172.16.197.167客户端ip登录：
3.1、 开启白名单规则：
set mysql-firewall_whitelist_enabled=1;
load mysql variables to runtime;
save mysql variables to disk;
3.2、添加用户白名单规则：
OFF模式表示允许定义的username@client_address用户执行任意的查询，完全忽略mysql_firewall_whitelist_rules中定义的规则；
ProxySQL Admin> INSERT INTO mysql_firewall_whitelist_users(active, username, client_address, mode,comment) values(1,' app_liuwenhe', '172.16.197.167', 'OFF', '');
LOAD MYSQL FIREWALL TO RUNTIME ;
SAVE MYSQL FIREWALL TO DISK;
3.3、在172.16.197.167上登录，是可以正常查询的！
[root@b-js25-base79-01 ~]# mysql -uapp_liuwenhe  -p123456 -h172.16.196.192  -P6033
mysql> select  count( ) from liuwenhe.liu;
+----------+
| count() |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)
3.4、在172.16.196.192上登录，无法执行！
[root@B-JS25-BASE79-00 ~]# mysql -uapp_liuwenhe  -p123456 -h172.16.196.192  -P6033
mysql> select  count() from liuwenhe.liu;
ERROR 1148 (42000): Firewall blocked this query
总结：针对proxysql防火墙的限制，一般就用到限制某个用户只允许在某个特定的ip执行query就可以了，至于复杂的 mysql_firewall_whitelist_rules表的设置来限制具体的查询内容，谨慎在生产上使用，也就是使用OFF模式在mysql_firewall_whitelist_users表中设置对应的白名单即可！


请使用浏览器的分享功能分享到微信等