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

IT大咖说

发布于 2018-07-30 11:04:11

1.7K0

发布于 2018-07-30 11:04:11

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

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

摘要

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

嘉宾演讲视频及PPT回顾：http://suo.im/5s8NNM

内容概览

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

Hook技术基础简介

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

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

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

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

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

第二，要开发一段自定义的程序，用来完成某些特殊的功能，这点也就不用过多解释。

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

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

hook机制实现方法

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

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

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

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

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

为了不妨碍其他插件的运行，我们在修改函数指针之前要将原函数指针也保存下来，然后在我们的自定义程序中再加上一个判断，看看原函数指针是否为空，如果不为空还需要执行其他插件中自定义的程序。这点尤其重要，千万不能忽略掉了。

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

第四点，就是钩子的卸载，虽然这点不是必须的，我们也稍加解释一下，就是在_PG_fini函数中，做一些收尾的操作，比如将之前修改过的原程序的指针还原。这个_PG_fini函数名也是约定好了的，在插件卸载的时候由数据库自动调用。

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

注意事项

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

Auth_delay案例

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

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

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

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

然后第三点就是在这个PG_INIT这个函数中安装这个钩子，即首先将原函数指针保留在一个函数指针变量中，然后将这个自定义的函数赋值给原函数指针这个变量。

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

常用hook

刚才我们介绍了HOOK技术的第一个要素，就是PG原程序中要已经预定义了这个钩子，如果原程序中根本就没有定义这个钩子，那我们就无能为力了。

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

我们可以根据这些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执行的前后做一些记录或其他事情。

Query Tree

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

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的大致的样子。

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

SQL规范审核插件

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

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

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

规范审核案例

有了这些规则之后，我们就来针对某些规则介绍一下实现原理。

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

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

再来看看下一个规则，刚才的这个规则是判断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语句执行前的这个钩子上。

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

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

查询树遍历

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

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

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

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