PostgreSQL 的审计和权限控制系统

背景介绍

GrowingIO 作为一家大数据公司，核心资产虽然在大数据平台上，但关系型数据库仍扮演着不可或缺的角色。它担负着存储产品逻辑，配置信息等重要数据信息。

GrowingIO 目前的数据库实例较多，例如按环境区分为开发环境，测试环境，生产环境等不同环境。按需求来说，又存在运维人员需要直接修改线上脏数据，开发人员本地访问开发环境的实例进行联调等情况。

而随着公司员工不断发展，人员的流动性会加剧，数据库权限控制会越来越复杂。

这时一个易用的数据库管理工具会减少相当大的工作量。

由于 GrowingIO 选用 PostgreSQL 作为关系型数据库，所以以下内容以 PostgreSQL 作为基础展开。

目前状况

之前 GrowingIO 是使用基于 SSH + Iptables + PostgreSQL 日志的组合架构来实现的 PostgreSQL 的管理。

这种架构只是满足了权限控制和审计的基本需求，但是实际使用中有些问题是很难解决，例如:

• 由于权限控制和日志审计属于独立的系统，造成审计的溯源比较麻烦。

• 权限控制比较粗粒度，只能控制能不能访问，无法做更细粒度的权限划分。

• 无法做到 SQL 级别的限制。

• 没有接到统一账号管理系统，账号管理繁琐。

由于以上种种原因，所以决定将该架构进行一次重构。

基于 PostgreSQL 审计和权限控制系统

前期准备

功能需求

1. 不对 PostgreSQL 数据库做变更或者侵入较小。

2. 支持 RBAC 权限控制。

3. 使用 LDAP 作为 RBAC 中的角色和用户认证系统。

4. 有审计功能。

5. 有 SQL 过滤的功能。

6. 易于使用和管理。

工具调研

由于目前市面上主要数据库使用 MySQL 居多，而 PostgreSQL 使用的较少，扩展工具方面的资料相对较少。

目前找到的可行的方案是 PostgreSQL 本身的 LDAP 功能 + pgAudit 组合来达到权限和审计功能。

该方案可实现的功能包括:

• 基于 LDAP 的用户和角色认证。

• 基于 pgAudit 的日志审计功能。

该方案存在的问题:

• 需要操作 PostgreSQL 本身，对 PostgreSQL 本身有一定的侵入性。

• 各个 PostgreSQL 实例的管理还是割裂的状态。

• 后期功能扩展性不强。

由于该方案每个 PostgreSQL 实例的管理还是割裂的状态，当实例过多时仍会带来管理的复杂，对于不支持公网的实例仍需在 PostgreSQL 实例前端搭建一个 Proxy 程序来做流量转发的工作。

基于以上困难，决定自己开发一个 PostgreSQL 的审计和权限控制系统。

数据库审计系统设计与实现

程序架构设计

整体架构较为简单，整个系统的核心为 Server 端和 Proxy 端。

Server 端需要实现与 PostgreSQL 客户端通过某些认证方式建立连接，并将用户发来的请求进行处理，并通过一些模块来扩展功能，例如：

• 登录和权限通过 Auth 模块来处理，Auth 后面接入 LDAP 模块来进行用户的登录和权限认证。

• Audit 模块记录用户的请求信息，并将用户的行为格式化后输出到日志和其他系统中作为审计记录。

• Filter 模块监测用户的请求，例如可以对 drop database 等语句禁止执行，后端可以对接其他系统进行动态控制。

Proxy 端则主要通过 PostgreSQL 的系统用户与 PostgreSQL 数据库建立连接，作为 Server 端和 PostgreSQL 数据库之间沟通的桥梁。

所以系统的核心为 Server 端和 Proxy 端的实现，当这两块实现后我们就可以在此上做扩展，增加不同的功能。

Server 和 Proxy 实现

这里似乎只要找到 PostgreSQL Server 和 Client 的包然后做一次封装后就差不多可以实现 Server 和 Proxy 了。

但是真正问题的是网上找了一圈没发现有实现 PostgreSQL 服务端的示例，也没找到官方的包。只在 PostgreSQL 官方文档中找到了前后端的通信协议。

最后只能自己实现 PostgreSQL 的前后端。

PostgreSQL 前后端交互的核心为通信协议和会话过程这两部分，下面主要进行这两块的讲解。

PostgreSQL 前后端通信协议

这里介绍一下我个人去了解 PostgreSQL 通信协议的方法。

1. 首先是官方文档中关于 PostgreSQL 前后端协议。

2. 查看 PostgreSQL 的客户端源码进行分析和 Debug，并通过 Wireshark 进行抓包。这里主要查看的客户端为 psycopg2，node-postgres，go-pg，这里发现 psycopg2 其实是封装了 libpq 这个官方库，没有更多的关于协议的细节，帮助不大。但是发现 go-pg 和 node-postgres 是自己做了客户端协议，通过源码可以明白数据包内容的提取方法和协议格式。

PostgreSQL 前后端协议格式中，我们主要介绍如下两种，这两种弄懂后，其他格式其实大同小异。

• 第一种比较特殊的 StartupMessage 消息格式为客户端发送的第一条正式消息。

  
  

• 第二种为普通消息类型也是 PostgreSQL 前后端交互的主要消息格式。

  
  

协议字段说明:

• 内容的长度 标记了这个数据包中消息的长度（包括自身），大部分协议格式中都有这个字段，绝大部分长度为 4 Byte，类型为 Int32 。这里需要注意的是部分协议格式中的长度不是 4 Byte。

• StartupMessage 协议的版本信息 标记客户端请求使用的协议版本，其中前 16bits 为 Major Version，后 16bits 为 Minor Version。

• 消息类型 标记本条消息的类型，在大部分消息中为第一个字节，Char 类型，例如

  'R' 为服务端的认证消息类型。

  'p' 为客户端的认证信息。

  'Q' 为客户端的简单查询消息类型。

• 消息主体内容 消息类型中的主体内容。根据不同的消息类型，需要不同的解析方式，下面会主要介绍 StartupMessage 和 消息类型('R') 中的主体内容格式，其他消息类型请查看官方文档。

StartupMessage 消息

为客户端发送的第一次的消息格式，用于标记客户端的一些信息。

数据类型为 String，使用 key/value 格式，使用 0 做分割符，主要字段有 user database，如下:

user\x00xiaoming\x00database\x00hello\x00application_name\x00navicat…

消息类型(’R‘)

是服务端发送到客户端的一种消息格式，用于通知客户端的认证方式。

主体消息 中前四位使用 Int32，标记认证格式，部分含义如下。

• 0 表示认证成功。

• 2 指定使用 Kerberos V5 认证方式。

• 3 指定明文密码认证方式。

• 5 指定需要 MD5 加密的密码认证方式，其中主体消息的 4-7 位为加密的 SALT 值，加密过程如下。

password = 'md5' + string(md5(md5(USERNAME+PASSWORD) + string(SALT)))

PostgreSQL 前后端会话过程

通过上面的介绍，我们大致对协议格式有了一些了解，下面内容介绍一下 PostgreSQL 的会话过程，同样先放一张图，然后我们再来进行说明。

如图，我们可以将一个前后端会话过程概括为如下四个阶段：

1. 建立连接

主要为建立 TCP 网络连接请求过程，如果启用 SSL，则客户端需要发送一条包含 SSL Requests 信息的 TCP 请求。

SSL Requests 内容为

[]byte{0, 0, 0, 8, 4, 210, 22, 47}

2. 认证阶段

该阶段主要为用户登录的认证阶段，主要过程如下。

1. 客户端发送一条包含用户名，数据库等信息 StartupMessage 的消息。

2. 服务端发送一条包含认证方式的 'R' 格式消息。

3. 客户端根据认证方式发送一条包含认证信息的 'p' 消息进行验证。

4. 服务端根据认证信息进行认证，根据认证结果进行回复，如 'R' 的认证成功格式消息。

3. 会话阶段

认证通过后前后端开始进入会话进行 SQL 的请求操作。

这时客户端会发送例如 'Q' 和 'P' 等格式消息的 SQL 请求，服务端进行应答。

4. 结束会话

客户端发送一条 'X' 的 Close 请求后，进入 SSL/TCP 挥手阶段。

[]byte{'X', '\x00', '\x00', '\x00', '\x04'}

数据库审计系统的会话流程

通过上一节介绍的 PostgreSQL 前后端通信协议格式 和 前后端的会话过程 我们对 Server 和 Proxy 端的开发已经不存在障碍了，下面我会简单的介绍一下数据库审计系统的会话流程，同样还是先放一张图。

整个数据库审计系统的处理流程大体如下:

1. PostgreSQL 客户端建立与系统的 TCP 连接。

2. 系统通知 PostgreSQL 客户端使用 SSL 连接。

3. PostgreSQL 客户端开始与系统建立 SSL 连接。

4. 连接建立后，所有的客户端数据都会先经过 Parser 模块进行消息解析。

5. 如果发现客户端没有登录，那么系统会走到认证过程进行登录认证。

6. 认证通过后系统中的 Proxy 模块会与 PostgreSQL 服务端进行连接建立，并完成认证过程。

7. 后续客户端的请求通过 Parser 解析后会进行认证信息校验。

8. 认证信息校验通过后，请求会进入到 Filter 模块进行 SQL 的过滤。

9. Filter 过滤后，将请求通过 Proxy 模块转发到 PostgreSQL 服务端上。

10. 服务端返回的信息到达 Proxy 后，数据在转回 Server 模块将消息发回 PostgreSQL 客户端。

11. 当会话达到超时或者 PostgreSQL 客户端发起关闭请求等状态，则进入到关闭会话阶段。

12. 最后关闭会话，关闭 TCP/SSL 会话。

通过上面的介绍，我们可以知道数据库审计系统的核心还是在于 PostgreSQL 的前后端协议和会话过程，其他功能相对比较简单，所以对于系统更多的细节介绍这里不在展开。

总结

目前该系统已经稳定运行了一年多的时间，通过这段时间的使用可以确认该系统达到了当初的设计目标，完全满足了目前的使用场景，并且大大减轻了日常中关于数据库管理的相关工作，并且由于没有对 PostgreSQL 数据库做任何方面的改动，从而没有对线上的业务产生任何影响。

参考

• https://www.postgresql.org/docs/current/protocol-message-formats.html

• https://github.com/go-pg/pg

• https://github.com/psycopg/psycopg2

• https://github.com/brianc/node-postgres

作者：邢建辉 

GrowingIO 运维开发工程师，主要负责平台化，自动化方向的设计与开发。

招聘信息

GrowingIO 技术团队是一个活力四射、对技术充满激情的团队，多个岗位持续招聘中！诚招前端工程师/大数据工程师/Java 工程师等
，欢迎有兴趣的同学投递简历至：jianli@growingio.com（邮件标题请注明具体岗位名称）
，更多职位及信息可进入招聘官网查看
。

关于 GrowingIO

GrowingIO 是国内领先的一站式数据增长引擎整体方案服务商，创立于 2015 年，以数据智能分析能力为核心，通过构建客户数据平台，打造增长营销闭环，帮助企业提升数据驱动能力，赋能商业决策、实现业务增长。