lightdb-sql拦截_lightdb sql ddl-CSDN博客

本文链接：https://blog.csdn.net/qq_17713935/article/details/129088270

文章目录

LightDB - sql 审核拦截

LightDB - sql 审核拦截

LightDB 从 23.1 版本开始支持对指定类型的 sql 进行审核拦截（包括分布式模式下）。

一简介

通过新增 lt_sql_inspect 模块的方式对 SQL 进行拦截，目前 t_sql_inspec 通过实现 planner_hook 和 post_parse_analyze_hook 的方式（hook 是一种用来控制 lightdb 表现的方式）来对 SQL 进行拦截。

lt_sql_inspect 模块也包含了之前版本的SQL最佳实际功能（整合到了此模块）。

下面对涉及的参数及使用方式进行介绍:

二参数

lt_sql_inspect.secure_mode
lt_sql_inspect.secure_enable_db
lt_sql_inspect.secure_enable_time
lightdb_best_practice_level 控制拦截处理级别，warn: 打印warning日志，error: 直接报错。

2.1 lt_sql_inspect.secure_mode

lt_sql_inspect.secure_mode 用来指定规则，指定需要拦截那些类型的 sql, 目前支持如下规则：select_without_where, update_without_where, delete_without_where, no_paging, high_risk_ddl。规则具体含义参考规则介绍
session 级别
lt_sql_inspect.secure_mode 指定的是一个字符串，不同规则间用逗号分隔，比如：
```
 set lt_sql_inspect.secure_mode='select_without_where, update_without_where';
```

2.2 lt_sql_inspect.secure_enable_db

用来指定数据库，只有对指定数据库的操作才会去拦截
可以指定多个数据库，通过逗号分隔

可以通过直接修改配置文件或 alter system set ，然后 relaod 的方式修改时间。

alter system set lt_sql_inspect.secure_enable_db = 'lt_test, test2';
select pg_reload_conf();

2.3 lt_sql_inspect.secure_enable_time

用来指定交易时间，在此时间内不能进行一些危险的，高负载的操作。
可以通过直接修改配置文件或 alter system set ，然后 relaod 的方式修改时间。
此参数指定一个字符串，用来表示一个时间范围，且时间范围不能跨天。用- 分隔开始时间和结束时间，例子如下：
```
alter system set lt_sql_inspect.secure_enable_time = '09:00 - 16:00';
select pg_reload_conf();
```

三规则介绍及使用

3.1 select_without_where

对于查询语句，如果只涉及单表，并且没有where条件，且没有 limit ，没有offset，那就会被拦截。

对于 pg_catalog, lt_catalog schema 下的表不会拦截。

3.1.1 案例

lightdb@lt_test=# set lt_sql_inspect.secure_mode=select_without_where;
SET
lightdb@lt_test=# select * from t1;
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
lightdb@lt_test=# select * from (select * from t1);
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
lightdb@lt_test=# select * from t1 limit 1;
key1 | key2 
------+------
(0 rows)

对于多表不生效
lightdb@lt_test=# select * from t1, t2;
key1 | key2 | key1 
------+------+------
(0 rows)

对于视图生效
lightdb@lt_test=# create view t1_v as select * from t1;
CREATE VIEW
lightdb@lt_test=# select * from t1_v;
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
lightdb@lt_test=# select * from t1_v where key1=1;
key1 | key2 
------+------
(0 rows)

对于创建物化视图也会生效
lightdb@lt_test=# create materialized view t1_mv as select * from t1;
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
chuhx@lt_test=#

3.2 update_without_where/delete_without_where

规则与 select_without_where 一致，只是分别作用于 update 和 delete。

3.2.1 案例

update:

lightdb@lt_test=# set lt_sql_inspect.secure_mode = 'update_without_where';
SET
lightdb@lt_test=# update t1 set key1=1;
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
lightdb@lt_test=# update t1 set key1=1 limit 1;
UPDATE 0
lightdb@lt_test=# update t1 set key1=1 offset 1;
UPDATE 0
lightdb@lt_test=# update t1 set key1=1 from t2 ;
UPDATE 0
lightdb@lt_test=# update t1 set key1=1 from t2 where t1.key1=t2.key1;
UPDATE 0

delete:

lightdb@lt_test=# set lt_sql_inspect.secure_mode = 'delete_without_where';
SET
lightdb@lt_test=# delete from t1 ;
ERROR:  lt_sql_inspect: SQL cannot execute, because a full table scan may be performed on the table "t1"
chlightdbuhx@lt_test=# delete from t1 where key1=1;
DELETE 0
lightdb@lt_test=# delete from t1 limit 1;
DELETE 0
lightdb@lt_test=# delete from t1 offset 1;
DELETE 0
lightdb@lt_test=# delete from t1 using t2;
DELETE 0
lightdb@lt_test=# delete from t1 using t2 where t1.key1=t2.key1;
DELETE 0

3.3 no_paging

no_paging 等价与 select_without_where, update_without_where, delete_without_where 的组合。

3.4 high_risk_ddl

这个规则用来拦截一些危险的 DDL 操作，目前包括如下几种：

删表 drop table
清空表 truncate table
加字段 alter table add column
删字段 alter table drop column
加约束 alter table add constraint
删约束 alter table drop constraint
修改字段 alter table modify/alter column
- 修改字段为 null
- 修改字段为 not null
- 设置字段默认值
- 去除字段默认值
- 修改字段类型

3.4.1 案例

lightdb@lt_test=# set lt_sql_inspect.secure_mode = high_risk_ddl;
SET
lightdb@lt_test=# alter table t1 modify key2 bigint;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 type text;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 modify key2 null;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 drop not null;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 modify key2 not null;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 set not null;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 modify key2 default 10;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 set default 10;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 drop default;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#
lightdb@lt_test=# alter table t1 alter  key2 drop default;
ERROR:  lt_sql_inspect: SQL cannot execute, because it is a high risk ddl
lightdb@lt_test=#