LightDB - sql 审核拦截

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

一 简介

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

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

二 参数

• lightdb_sql_mode
• lt_firewall.lightdb_business_time

2.1 lightdb_sql_mode

• lightdb_sql_mode 用来指定规则，指定需要拦截那些类型的 sql, 目前支持如下规则：select_without_where, update_without_where, delete_without_where, high_risk_ddl。规则具体含义参考规则介绍

• session 级别

• lightdb_sql_mode 指定的是一个字符串，不同规则间用逗号分隔， 比如：

   set lightdb_sql_mode='select_without_where, update_without_where';
  

2.2 lt_firewall.lightdb_business_time

• 用来指定交易时间，在此时间内不能进行一些危险的，高负载的操作。

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

• 此参数指定一个字符串，用来表示一个时间范围，且时间范围不能跨天。用- 分隔开始时间和结束时间，例子如下：

  alter system set lt_firewall.lightdb_business_time = '09:00 - 16:00';
  select pg_reload_conf();
  

三 规则介绍及使用

3.1 select_without_where

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

3.1.1 案例

lightdb@lt_test=# set lightdb_sql_mode=select_without_where;
SET
lightdb@lt_test=# select * from t1;
ERROR:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
lightdb@lt_test=# select * from (select * from t1);
ERROR:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
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:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
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:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
chuhx@lt_test=# 

3.2 update_without_where/delete_without_where

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

3.2.1 案例

update:

lightdb@lt_test=# set lightdb_sql_mode = 'update_without_where';
SET
lightdb@lt_test=# update t1 set key1=1;
ERROR:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
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 lightdb_sql_mode = 'delete_without_where';
SET
lightdb@lt_test=# delete from t1 ;
ERROR:  SQL cannot execute, because a full table scan may be performed on the table "t1", please check lightdb_sql_mode
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 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.3.1 案例

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