基于PostgreSQL数据库插件的SQL规范审核工具

  • 时间:2018-06-20 22:14 作者:IT大咖说 来源:IT大咖说 阅读:177
  • 扫一扫,手机访问
摘要:内容来源:2017 年 10 月 21 日,平安科技数据库架构师陈刚在“PostgreSQL 2017中国技术大会”进行《基于PG数据库插件的SQL规范审核工具》演讲分享。IT 大咖说作为独家视频合作方,经主办方和讲者审阅受权发布。阅读字数:6453 | 17分钟阅读嘉宾演讲视频及PPT回顾:htt
基于PostgreSQL数据库插件的SQL规范审核工具

内容来源:2017 年 10 月 21 日,平安科技数据库架构师陈刚在“PostgreSQL 2017中国技术大会”进行《基于PG数据库插件的SQL规范审核工具》演讲分享。IT 大咖说作为独家视频合作方,经主办方和讲者审阅受权发布。

阅读字数:6453 | 17分钟阅读

嘉宾演讲视频及PPT回顾:http://t.cn/RBe0WUN

基于PostgreSQL数据库插件的SQL规范审核工具

摘要

此议题的主题是PG数据库插件和SQL规范审核相关的内容。首先理解一下hook技术的基本原理。接下来将详情一下SQL语句在PG数据库的分析解析和执行过程。而后结合hook和SQL执行过程详情一下SQL规范审核这个插件,聊一聊该插件的实现原理。最后做一下展望。

内容概览

这个是本次分享的主要内容:

基于PostgreSQL数据库插件的SQL规范审核工具

Hook技术基础简介

Hook中文的意思是钩子,它的概念主要是,能让使用户有机会切入到PG数据库的内部运行机制中,进行中断、添加或者者修改原来的程序逻辑,从而实现少量使用户自己设置的功可以。单独看文字可可以了解不是很直观,我们来看看示用意。

基于PostgreSQL数据库插件的SQL规范审核工具

这个蓝色的箭头方块表示某一个软件的原始的程序逻辑,默认情况下是无中断的顺序执行。但是我们原程序在设计的时候,能在适当的位置定义一个钩子,加入少量判断机制,当满足肯定条件时,允许跳转到一段使用户自己设置的程序,使用户自己设置程序运行完成之后再回到原程序继续执行下去,这种技术就叫做hook机制。

当然,在设计这个钩子的时候,也允许使用户用自己设置程序来替代原始的程序,以达到改变或者优化原程序的业务逻辑的目的。这就是hook技术的感念。

要实现这个hook机制,需要满足四方面的要素:

第一,原始程序中要设计有这么一个钩子,后续才可以让使用户有机会利使用这个钩子。这个是前提条件,假如原程序中根本就没有设计这种机制,那么我们也就无从谈起利使用这个钩子了。

第二,要开发一段自己设置的程序,使用来完成某些特殊的功可以,这点也就不使用过多解释。

第三,现在原程序的钩子已经定义好了,自己设置程序也已经实现,那么什么时候将这段自己设置程序加入到原程序的执行流程中呢?默认情况下,原程序是不会理会我们定义的这段程序的,需要我们人为的设置一下,这就是hook的安装,只有安装好了这个钩子,且满足肯定的条件,原程序才会流转到我们自己设置的这段程序中。

第四、有了钩子的安装,自然就有钩子的卸载,就是在不需要再执行自己设置程序的时候,将这个关联关系卸载掉,让程序恢还原状,这就是钩子的卸载。当然这个卸载的操作不肯定是必需的,由于有些hook机制会一直用下去,直到系统停机就自然而然的卸载掉了。也就不使用我们特意的来实施这个卸载的操作。这点需要根据不同的业务场景而定。

hook机制实现方法

刚才我们大致理解了hook技术的概念和基本原理,下面我们就使用实际的代码来说明hook机制的实现方法。这些是摘自PG数据库的源码,是使用C语言开发的。

基于PostgreSQL数据库插件的SQL规范审核工具

首先,第一点是钩子的定义,主要是在原程序中定义一个函数指针,并且默认情况下该函数指针赋一个空值。而后在原始代码的合适的地方,添加一个if判断,当原始程序在执行这段代码时,会判断这个指针函数能否为空,假如为空的话,就跳过,继续执行后续的代码。假如不为空那么就执行这个函数指针所指向的那段代码。那么显而易见,默认情况下,这个函数指针始终为空,所以原系统会不间断的执行原始的代码,就当作这个钩子不存在。

基于PostgreSQL数据库插件的SQL规范审核工具

第二点,需要开发一段程序使用来实现自己设置的业务逻辑,这点不需要做过多解释。大家注意到没有,这段自己设置代码的函数中,还有另外一个判断,这个待会儿再做解释。

基于PostgreSQL数据库插件的SQL规范审核工具

第三点,hook的安装。就是在适当的时候,将原程序中的那个函数指针指向我们自己设置的这个函数上,当然在修改原函数指针的时候,还要将原函数指针所指向的其余函数方保存下来。

大家想想,原来的函数指针不是默认为空吗?为什么还要保存原来的函数指针呢?这就涉及到hook的嵌套机制,由于当我们在安装我们自己开发的插件的时候,有可可以其余插件也在这个钩子上安装了他们的自己设置程序,这就形成了一个钩子链条。

为了不妨碍其余插件的运行,我们在修改函数指针之前要将原函数指针也保存下来,而后在我们的自己设置程序中再加上一个判断,看看原函数指针能否为空,假如不为空还需要执行其余插件中自己设置的程序。这点尤其重要,千万不可以忽略掉了。

另外,这个hook安装的操作是在这个_PG_int函数中完成的。这个是PG插件开发的规范接口名称。当数据库在加载这个插件的时候,会默认调使用这个函数做少量初始化的操作,所以我们就将hook的安装放在这个函数中。

基于PostgreSQL数据库插件的SQL规范审核工具

第四点,就是钩子的卸载,尽管这点不是必需的,我们也稍加解释一下,就是在_PG_fini函数中,做少量收尾的操作,比方将之前修改过的原程序的指针复原。这个_PG_fini函数名也是商定好了的,在插件卸载的时候由数据库自动调使用。

基于PostgreSQL数据库插件的SQL规范审核工具

有了这4要素之外,还需要其余少量辅助的操作,才可以让这个插件真正的运行起来,比方要修改参数文件,将这个插件名加入到shared_preload_libraries这个参数中,或者者用load命令手工加载这个插件。另外在必要的时候可可以还需要重启数据库实例。

注意事项

下面再来看看,我们在开发某个插件的时候,要注意哪些事项。尽管我们开发的插件与数据库的内核程序是松耦合关系,我们很方便的安装或者者卸载这个插件,但是一旦这个插件被安装上了之后,这段自己设置程序就成为整个数据库的一部分了,比方这段原始程序执行多少次,那么我们的这段自己设置程序就会执行多少次,这段自己设置的程序质量的高低也会影响数据库核心的原程序。所以也需要确保这段自己设置程序的健壮性、性可以以及容错机制。同时还要检查能否有内存泄漏的问题。另外假如这段程序需要占使用大量内存,还需要做好内存耗费的评估等等。

Auth_delay案例

下面我们来详情一个实际的插件的例子,这个例子是来自于随PG一道发布的工程中的。叫做auth_delay。

可可以大家都知道这个插件的作使用,就是当用使用户名和密码连接PG数据库时,假如当使用户名和密码错误,能让数据库推迟几秒再返回给前台,这样是防止利使用穷举密码的方式来尝试登陆数据库。

我们就来看看这个插件的原理。首先第一点,在PG核心源码中的使用户登陆验证的程序中,定义了这么一个函数指针,默认情况下该函数指针为空。而后在使用户名和密码验证结束的这个函数中添加了一个if判断,假如这个函数指针不为空,则调使用这个函数指针指向的程序。

第二点,开发了这个自己设置函数,这个函数的逻辑非常简单,首先判断原函数指针能否为空,假如不为空则先执行这个函数指针指向的其余函数。而后就是运行这个插件要完成的主要任务,即判断这个使用户名和密码验证的结果能否为OK这个状态,假如不是OK说明用了错误的使用户名和密码登陆,那么此时将sleep几秒,也就是推迟返回。这个就是该插件的主要功可以。

而后第三点就是在这个PG_INIT这个函数中安装这个钩子,即首先将原函数指针保留在一个函数指针变量中,而后将这个自己设置的函数赋值给原函数指针这个变量。

基于PostgreSQL数据库插件的SQL规范审核工具

这个插件非常简单,简短的几行代码就完成了密码验证错误后推迟返回的这么一个需求。所以我把它称为史上最简单的PG插件。

常使用hook

刚才我们详情了HOOK技术的第一个要素,就是PG原程序中要已经预约义了这个钩子,假如原程序中根本就没有定义这个钩子,那我们就无可以为力了。

那么目前PG原程序代码中究竟有多少个这种钩子呢?我之前在基于10.0这个新版本中大致搜了一下,假如没有遗漏的话大概有26个。

基于PostgreSQL数据库插件的SQL规范审核工具

我们能根据这些hook名称和所在的位置大致猜到它们的作使用,比方这个第4行这个钩子check_password_hook是允许我们校验密码的复杂度。这5、6、7、8是在执行SQL语句的前后允许我们记录少量什么东西,比方SQL执行的开始、结束时间等等。还有第10行这个ClientAuthentication_hook就是我们刚才详情的这个auth_delay插件所利使用的钩子。还有第19行这个shmem_startup_hook是在数据库启动过程中,当完成共享内存初始化之后,允许我们自己设置少量共享内存,使用来存储自己设置的数据。

这里我就不逐个详情每一个hook的作使用了,大家后续能根据每一个hook所处的源码位置来确定具体的作使用,而后大家自由发挥,想想这些hook能应使用在哪些场景中,而后开发出对应的插件。

SQL执行过程

也许有朋友研究过SQL语句执行的过程,主要包括以下几个阶段:

SQL语句的词法、语法解析、SQL语义分析生成查询树,而后对这个查询树进行优化重写。接下来生成执行计划,而后就根据这个执行计划进行实际的执行,最后将执行结果返回。

在这一整个SQL执行过程中,PG数据库在如下阶段安装有HOOK,比方在查询树重写之后有一个hook,允许我们对所生成的查询树进行分析。再比方在sql执行的前后均有几个hook允许我们对SQL执行的前后做少量记录或者其余事情。

基于PostgreSQL数据库插件的SQL规范审核工具

Query Tree

下面就重点详情一下SQL语义分析之后所生成的QUERY TREE,看看QUERY TREE是长什么样子,都记录哪些信息。

基于PostgreSQL数据库插件的SQL规范审核工具

query tree在C语言中就是一个结构体,该结构体中的每个变量也都有其特定的含义,每个结构体的变量也都包含其余结构体,这样就现成了一个多叉树的结构,这个多叉树的每个节点都包含这个SQL语句的某一组成部分的详细信息。

上面的截图是PG数据库将某一个SQL语句解析后的query tree的各个节点的内容打印在后端日志中。

就拿这个简单的查询SQL为例,首先这个根节点中的commandType为1的属性就表示这个SQL是一个SELECT语句。还有的表示能否有聚合函数,能否有窗口函数以及能否有子查询,能否有distinct运算,能否有for update等等。

下面这个节点,使用来形容from后面的表,从哪张表中查询数据,该表有哪些字段等等形容都在这个节点中。

再往下个节点是关于表的join的信息,后面跟的这个节点是where条件的表达式,再后面这个返回列的信息,这个SQL是总共返回两列,所以这里有两个节点。

再后来就是关于排序的节点信息,这里有一个order by ,所以这个排序的节点就不为空。下面就是返回数据行的形容,比方limit offset和limit count,由于这个语句中没有写limit,所以这两个节点都是为空。这个就是SQL语句语义解析后的这个QUERY TREE的大致的样子。

基于PostgreSQL数据库插件的SQL规范审核工具

这张图是执行计划生成出来之后的一个多叉树,它记录的信息和查询树有些区别,比方其中的某个节点记录了表的扫描方式,是全表还是根据索引。

SQL规范审核插件

有了前面的HOOK技术和SQL解析的基础之后,接下来就详情本次议题的主角了。SQL规范审核插件。

首先,我们需要定义少量SQL的审核规则,这里罗列的是部分规则,有些是已经实现了的,有些还在开发测试中。

基于PostgreSQL数据库插件的SQL规范审核工具

这些规则中有些是针对DML,还有些规则是针对于DCL和DDL的。表中二三行表示的是当一个UPDATE语句没有where条件,或者者where条件始终为true时,需要给出警告的审核。再下面是关于受权的审核,比方有些dml权限不可以受权给查询使用户。下面这些是关于命名方面的规范。

规范审核案例

有了这些规则之后,我们就来针对某些规则详情一下实现原理。

基于PostgreSQL数据库插件的SQL规范审核工具

首先看看这个规则,update语句必需出现where子句。还是回到这个SQL执行过程,在SQL语义解析后会生成QUERY TRUE,而且在这个位置又提供了一个hook,那么我们即可以利使用这个hook来分析这个查询树。针对这个语句,我们即可以找到查询树中关于where子句的这个节点,看看这个节点能否为空就可。

有时候可可以对这个QUERY TREE结构不理解的初学者来说,一下子找不到相应的节点。那么这里有个小技巧,就是能写一个另外一个类似的SQL语句,一个带where条件,一个不带where条件,而后使用相似于UE文本比较的工具,看看这两个SQL语句打印出来的查询树有些什么差异,通过这种方法能快速定位到所需要的Node。

基于PostgreSQL数据库插件的SQL规范审核工具

再来看看下一个规则,刚才的这个规则是判断where条件能否为空,接下来这个规则是update语句有写where条件,但是有可可以这个where条件的表达式始终为true,这样就相当于没有where条件,也会做全表update。比方这两个语句:一个where条件为1=1,另一个where条件就直接是true。

假如我们这个用依然向上一个规则一样来分析query true行不行呢?显然是不行的,由于在这个查询树中,where条件这个节点并不为空,它是包含where条件的,所以我们需要换一种思路。

前面我们理解到,在执行计划生成后也会产生一个执行计划plan tree,我们能看看这个plan tree中的这个where条件的节点长什么样子。

通过比照,我们能发现假如where条件表达式的逻辑始终为true的话,在执行计划树中的这个节点也为空,这说明优化器在生成执行计划的时候就直接忽略掉这个where条件。所以通过这种方法,我们也能达到我们判断业务逻辑的目的。当然这个规则的判断需要安装在SQL语句执行前的这个钩子上。

基于PostgreSQL数据库插件的SQL规范审核工具

下面再来看看第三个规则,关于受权。这个规则的意思是不可以将某些DML权限受权给查询使用户,这些查询使用户只可以授与这些白名单上的权限,比方select、usage等,相似与这个语句。

要审核这个规则,我们就需要用这个钩子,这个钩子是定义在执行相似于这种受权语句的代码中。我们能去分析这个受权SQL语句语义解析后的这个结构体。具体判断这个结构体中受权对象是不是QRY角色,以及是不是有超出白名单的权限。从而判断能否违规受权。

查询树遍历

基于PostgreSQL数据库插件的SQL规范审核工具

前面我们详情了三条规则审核的例子。其中前两个例子中都有提到那颗多叉树。尽管我们人眼一眼即可以定位到我们关系的树中的某个node,但是程序程序是如何定位的呢。这就涉及到树的遍历的问题了。

还记得上图图中这一大段的内容是从哪里来的吗,对了就是从后端日志中获取到的,PG程序一定有地方也会遍历这课树,把这个树中的每个节点的内容打印出来。

在源码的src/backend/nodes/outfuncs.c位置能找到遍历树方法。我们能仿照着这个遍历的过程也写一个遍历数的函数,当遍历某个节点时再根据规则名称来判断对应节点的内容能否符合违规条件。比方这个是遍历plan tree树中对表进行全表扫描的节点,看看这个节点中过滤条件的节点能否为空,假如为空则说明该SQL语句会影响整表的数据。

下面显示的就是审核插件的最终效果。

基于PostgreSQL数据库插件的SQL规范审核工具

特点总结

下面总结一下审核插件的特点:

第一点,这个插件非常轻量,由于它用的是数据库内核原生的SQL解析器,不需要额外开发的SQL解析器对SQL语句进行解析。所以对SQL语句执行的响应时间影响极小。

第二点只读、安全。由于整个过程对内核没有任何修改操作,只是读取内核SQL解析器解析后的查询树等中间产物,所以是安全可靠的。

第三点,兼容性强,这个插件对PG社区版的9.4、9.5、9.6以及最新的10.0都是兼容的。对于企业版的EDB9.4、9.5、9.6版本也是兼容的。第四点,灵活可控。我们能很方便的启使用或者禁使用某些SQL审核项。并且能灵活的控制违规后的阻拦级别,是仅仅警告还是阻拦,都能灵活设置。

基于PostgreSQL数据库插件的SQL规范审核工具

未来展望

最后看看未来的展望。一方面是添加更多的SQL规范审核项,另一方面是希望能添加少量SQL语句优化的建议的功可以,当出现违规SQL之后同时可以给出优化后的SQL语句。再一个就是今后还会开发出更多用的插件出来。

本次分享的主要内容讲完了,谢谢大家!

编者:IT大咖说,转载请标明版权和出处

  • 全部评论(0)
最新发布的资讯信息
【系统环境|】什么是Web?零基础能不能学Web前端开发?(2019-07-19 07:04)
【网页前端|HTML】为什么学Web前端?选择Web前端理由(2019-07-17 20:28)
【网页前端|JS】Java面经-百度新入职老哥整理近半年学习经验,面试刷题路线!(2019-07-16 22:16)
【网页前端|HTML】最全面的前端开发指南(2019-07-15 12:57)
【系统环境|数据库】零基础如何快速学好大数据?(2019-06-29 12:27)
【系统环境|Linux】零基础如何学好大数据?必备需要学习知识(2019-06-18 11:54)
【系统环境|】Hadoop环境中管理大数据存储八大技巧(2019-06-15 11:01)
【系统环境|服务器应用】现在国内IT行业是不是程序员过多了?(2019-06-11 06:34)
【系统环境|服务器应用】新贵 Flutter(2) 自己设置 Widget(2019-06-11 06:34)
【系统环境|服务器应用】Android完整知识体系路线(菜鸟-资深-大牛必进之路)(2019-06-11 06:34)
手机二维码手机访问领取大礼包
返回顶部