# CREATE BLOCK RULE #### 功能描述 该语法用于创建**一条查询过滤规则**，包括查询过滤规则名称、绑定的客户端名称、客户端IP、用户以及匹配方式等。在日常数据库操作中，异常SQL的执行可能会导致资源过度消耗，影响数据库性能和稳定性，通过创建查询过滤规则，可以将此类SQL语句进行拦截。 当前支持**指定某个SQL语句的正则匹配** 进行拦截，例如针对某用户对某张表的SELECT进行拦截；也支持**指定某SQL语句的unique_sql_id或sql_hash取值** 进行拦截，例如已知某SQL语句的**unique_sql_id**值，将此SQL语句直接拦截。 本文档提供基础SQL语法，您也可以通过控制台进行配置，参见[使用DWS查询过滤器拦截慢SQL](https://support.huaweicloud.com/mgtg-dws/dws_01_72368.html)。 #### 注意事项 - 该语法仅9.1.0.100及以上集群版本支持。 - 只有拥有数据库所有者权限的用户或者授予gs_role_block角色权限的用户才能执行CREATE BLOCK RULE命令，系统管理员默认拥有此权限。 - 普通用户无法执行CREATE BLOCK RULE，可通过系统管理员进行授权，授权语法： ``` GRANT gs_role_block TO user; ``` 建议**创建查询过滤规则时尽量缩小适用范围，避免误过滤，或者范围过大导致性能劣化**。 #### 语法格式 ``` CREATE BLOCK RULE [ IF NOT EXISTS ] block_name [ [ TO user_name@'host' ] | [ TO user_name ] | [ TO 'host' ] ] | [ FOR UPDATE | SELECT | INSERT | DELETE | MERGE ] | FILTER BY { SQL ( 'text' ) | TEMPLATE ( template_parameter = value ) } [ WITH ( { with_parameter = value }, [, ... ] ) ]; ``` #### 参数说明 表1CREATE BLOCK RULE参数说明 | 参数 | 描述 | 取值范围或示例 | |:---|:---|:---| | block_name | 要创建的查询过滤规则名称。 | 字符串，需符合[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | | TO | 对查询过滤规则生效的对象或客户端IP，支持以下方式： - **TO user_name@'host'**：用户名以及客户端IP，两者都要满足，查询过滤规则才能生效。 - **TO user_name**：用户名，即只判定满足用户名条件，查询过滤规则就生效，不区分客户端IP。 - **TO 'host'**：客户端IP，即只判定满足客户端IP条件，查询过滤规则就生效，不区分用户。 | 举例：查询过滤规则对来自客户端IP为192.168.0.59且登录用户名为user1时生效。 ``` CREATE BLOCK RULE query_block TO user1@'192.168.0.59' FOR SELECT FILTER BY SQL('test.test'); ``` 查询过滤规则对登录用户名为user1时生效（无论客户端IP是哪个）。 ``` CREATE BLOCK RULE query_block TO user1 FOR SELECT FILTER BY SQL('test.test'); ``` 查询过滤规则对来自客户端IP 192.168.0.59的访问时都生效（无论哪个用户登录）。 ``` CREATE BLOCK RULE query_block TO '192.168.0.59' FOR SELECT FILTER BY SQL('test.test'); ``` | | FOR | 语句类型，支持对UPDATE/SELECT/INSERT/DELETE/MERGE INTO五种类型语句进行限制。 | - | | FILTER BY | 过滤方法，支持两种形式： - **SQL**：根据SQL语句内容进行过滤，通过关键词对语句进行正则匹配，例如表名，其长度不能超过1024，建议尽量精简。 - **TEMPLATE** ：根据模板进行过滤，**此方法不区分用户，针对所有用户执行的特定语句都实施拦截，拦截效果更高效** ，取值如下： - **unique_sql_id**：归一化的64位哈希值，重复概率较sql_hash大一些。 - **sql_hash**：归一化的哈希值（MD5），一般不会重复，相较unique_sql_id更推荐使用。 | unique_sql_id和sql_hash取值，可以通过EXPLAIN VERBOSE查询，例如： ``` EXPLAIN VERBOSE SELECT * FROM test.test; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503084135.png "点击放大") | | with_parameter | 查询过滤规则选项参数。 | 以下选项可以一起设置，满足任何一个参数的限制查询将会被过滤。 - application_name，客户端名称。 - query_band，负载标识。 - table_num，语句扫描的基表个数。 - partition_num，算子预估扫描的最大分区数。 - estimate_row，算子预估扫描的最大表行数。 - resource_pool，需要切换到的资源池名称。 - max_active_num，规则对应语句的最大并发数。 - is_warning，控制拦截到语句的行为是告警还是报错，on表示告警，off表示报错，默认为off。 | #### 示例：基于匹配SQL语句进行SQL拦截 以系统管理员连接数据库执行以下SQL，创建一个名为src的临时表，为后续的INSERT操作提供数据源。 ``` DROP SCHEMA IF EXISTS test CASCADE; CREATE SCHEMA test; CREATE TABLE test.src AS SELECT 1; ``` 执行以下SQL，创建表test.test。 ``` DROP TABLE IF EXISTS test.test; CREATE TABLE test.test(a int, b numeric(15,2)) WITH(orientation=column); ``` 执行以下SQL，分别为两张表插入2000万行数据。执行时间约半分钟，请等待。 ``` INSERT INTO test.test SELECT generate_series(1,20000000) % 1000,generate_series(1,20000000) FROM test.src; ``` 创建用户user1，并授权该用户可以访问表test.test。 ``` DROP USER IF EXISTS user1 CASCADE; CREATE USER user1 PASSWORD '{password}'; GRANT USAGE ON SCHEMA test TO user1; GRANT SELECT ON TABLE test.test TO user1; ``` 以用户user1连接数据库，查询表数据，结果表明在没有创建过滤规则的情况下，用户user1可以正常查询表test.test数据。 ``` SELECT sum(b) FROM test.test GROUP BY a; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503311155.png "点击放大") 以系统管理员连接数据库，创建名称为query_block的查询过滤规则，对于用户user1查询的SQL语句进行拦截，过滤方式为SQL语句，SQL语句中出现关键字test(即表名)会被过滤。 ``` CREATE BLOCK RULE query_block TO user1@'192.168.0.59' --规则作用于用户user1，且客户端IP为192.168.0.59。 FOR SELECT --过滤SELECT的相关操作。 FILTER BY SQL('test.test') --过滤方式为SQL语句，SQL语句中出现关键字test(即表名)会被过滤。 WITH --满足以下条件即被过滤。 ( application_name='gsql', --客户端名称为gsql table_num='1', --扫描基表次数为1 estimate_row='10', --扫描基表行数为10 is_warning='off' --关闭告警，直接报错 ) ; ``` 通过视图**pg_get_blockruledef**查询已创建的过滤规则，从回显判断出规则已生效。 ``` SELECT * FROM pg_get_blockruledef('query_block'); ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503311557.png "点击放大") 切换回user用户连接数据库，再次查询以下SQL，报以下类似信息错误，ERROR: hit block rule query_block xxx，表示语句被拦截了。 ``` SELECT sum(b) FROM test.test GROUP BY a; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503311235.png "点击放大") #### 示例：基于语句的unique_sql_id值或sql_hash值进行SQL拦截 继续如上示例，切换回系统管理员连接数据库，执行以下命令查询unique_sql_id和sql_hash。 ``` EXPLAIN VERBOSE SELECT sum(b) FROM test.test GROUP BY a; ``` 从回显中记录unique_sql_id和sql_hash。 ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002470231862.png "点击放大") 删除原来的过滤规则，创建基于unique_sql_id的过滤规则。 ``` DROP BLOCK RULE query_block; CREATE BLOCK RULE query_block FILTER BY TEMPLATE(unique_sql_id='3324222175'); ``` 再次切到user1用户，查询报错，语句被拦截了。 ``` SELECT sum(b) FROM test.test GROUP BY a; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503151925.png) 同样，以系统管理员去执行一遍，语句会被拦截，由此可知，**通过unique_sql_id进行过滤拦截，并不区分用户，对所有用户执行此类SQL语句都会进行拦截，拦截效果更优。** ``` SELECT sum(b) FROM test.test GROUP BY a; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503313043.png) 删除过滤规则，创建基于sql_hash的过滤规则。 ``` DROP BLOCK RULE query_block; CREATE BLOCK RULE query_block FILTER BY TEMPLATE(sql_hash='sql_170d0c75c472043b0cd35e05903c382f'); ``` 再次切到user1用户，查询报错，语句被拦截了。 ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002470232168.png "点击放大") #### 相关链接 [ALTER BLOCK RULE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0291.html)，[DROP BLOCK RULE](https://support.huaweicloud.com/sqlreference-dws/dws_06_0293.html) 本文档提供基础SQL语法，您也可以通过控制台进行配置，参见[使用DWS查询过滤器拦截慢SQL](https://support.huaweicloud.com/mgtg-dws/dws_01_72368.html)。 #### 常见问题：创建了查询过滤规则后，并未成功拦截语句 不同的计划，可能部分字段无法按照预期进行拦截，例如： ``` CREATE BLOCK RULE test filter BY SQL('test')with(estimate_row='3'); SELECT * FROM test; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002470517408.png "点击放大") 此时，语句关键字是匹配的，查询的行数也超过了3行的限制，但是没有成功拦截。 ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503437699.png "点击放大") 通过计划可以看出，此时是**FQS（fast_query_shipping）** 计划，即参数[enable_fast_query_shipping](https://support.huaweicloud.com/devg-dws/dws_04_0936.html)设置为on，导致没有估算信息。因此无法进行拦截，对于CN轻量化的计划也是一样的，如果让语句强制使用stream计划，那么就可以拦截成功： ``` SET enable_stream_operator=on; SET enable_fast_query_shipping=off; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503597679.png "点击放大") 综上所述，如果估算信息不准确，也会导致误拦截或者漏拦截的情况，因为计划的信息是通过估算所得，通常这种情况无法避免。 #### 常见问题：如何查看拦截记录 创建查询过滤规则后会拦截很多慢SQL，可以通过TopSQL系统表[GS_WLM_SESSION_INFO](https://support.huaweicloud.com/devg-dws/dws_04_0566.html)查看已拦截的语句，abort_info会记录拦截信息，也就是查询的报错信息。 **需连接postgres数据库**执行： ``` SELECT abort_info,query FROM GS_WLM_SESSION_INFO WHERE abort_info like '%hit block rule%'; --查询所有拦截记录 SELECT abort_info,query FROM GS_WLM_SESSION_INFO WHERE abort_info like '%hit block rule query_block_new%' --只查询规则名称为query_block_new的拦截记录 ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002503439459.png "点击放大")