Bytebase 实现Postgres 代码审核自动化

大家好，今天和大家聊聊SQL review 审核自动化的话题。

我们先来安装一下Bytebase, 我们采用最为简单的docker 的安装方式。

参考文档：https://www.bytebase.com/docs/get-started/self-host/

我们直接粘贴官网上的命令：

docker run --init \
--name bytebase \
--restart always \
--publish 5678:8080 \
--health-cmd "curl --fail http://localhost:5678/healthz || exit 1" \
--health-interval 5m \
--health-timeout 60s \
--volume ~/.bytebase/data:/var/opt/bytebase \
bytebase/bytebase:2.8.0 \
--data /var/opt/bytebase \
--port 8080

Bytebase组件的默认数据库是postgres, 如果不指定外挂数据库，则会在容器内创建一个内嵌的PG数据库。 生产环境建议外挂数据库。

--pg postgresql://user:secret@host:port/dbname

容器启动成功后，你会看见 bytebase的 大logo:

___________________________________________________________________________________________


██████╗ ██╗   ██╗████████╗███████╗██████╗  █████╗ ███████╗███████╗
██╔══██╗╚██╗ ██╔╝╚══██╔══╝██╔════╝██╔══██╗██╔══██╗██╔════╝██╔════╝
██████╔╝ ╚████╔╝    ██║   █████╗  ██████╔╝███████║███████╗█████╗
██╔══██╗  ╚██╔╝     ██║   ██╔══╝  ██╔══██╗██╔══██║╚════██║██╔══╝
██████╔╝   ██║      ██║   ███████╗██████╔╝██║  ██║███████║███████╗
╚═════╝    ╚═╝      ╚═╝   ╚══════╝╚═════╝ ╚═╝  ╚═╝╚══════╝╚══════╝


Version 2.8.0 has started on port 8080 🚀
___________________________________________________________________________________________


time=2023-09-26T08:56:13.018Z level=INFO source=taskrun/schedulerv2.go:513 msg="TaskSkippedOrDoneListener started"

我们尝试访问一下 ：http://10.67.200.39:5678/auth/signup （5678是容器内部8080的端口映射）

画面兼顾了艺术和科技感，第一次登陆我们需要设置管理员账号和密码

首次登录后：

我们来添加一个已有的测试环境的PG数据库到bytebase 平台中：点击添加实例

新的实例创建好以后，我们可以使用SQL 编辑器的功能， 实现postgres的客户端的功能。

我们还可以打开慢查询的检测功能： 方便在测试环境中提前发现慢SQL

接下来我们需要测试一下 期待很久的postgres 审核功能：

我们首先需要创建一个 review的 rule:
参考文档 https://www.bytebase.com/docs/sql-review/review-policy/create-schema-review-policy/

我们选择基础模板：开启59条审核规则

我们可以在模板上选择具体的review的规则，里面包含了大家日常工作用到的很多种的数据库

我们发布一个数据库的变更，创建一张新的表：点击按钮 “变更schema”

创建一个工单：

我们可以看到SQL审核的结果： 强制表包含主键 #601

至此我们实现了SQL review的图形化配置， 接下来使我们的重点，我们要测试一下review 的API的调用。

官网文档关于API的调用：https://www.bytebase.com/docs/api/sql-advise/

目前这个API 审核的接口，还是处于 Alpha 版本的阶段，不建议在重用的场合中使用，未来接口的参数，方法名字都有可能会改变

API的调用步骤分为2步：
1）账号登录拿到token
2）通过token认证，传入SQL语句进行SQL审核，返回审核结果

我们先测试账号登录： Linux 下直接 curl 或者 postman UI 调用都可

curl -v $ http://localhost:5678/v1/auth/login --data ‘{“email”:“jason.chentj@homecredit.cn”,“password”:"*******",“web”:true}’
几个必要的参数是:
email: 注册的邮箱
password: 登录的密码
返回的参数 token:
{“token”:“eyJhbGciOiJIUzI1NiIsImtpZCI6InYxIiwidHlwIjoiSldUIn0.eyJuYW1lIjoiSmFzb24gQ2hlbnRqIiwiaXNzIjoiYnl0ZWJhc2UiLCJzdWIiOiIxMDEiLCJhdWQiOlsiYmIudXNlci5hY2Nlc3MucHJvZCJdLCJleHAiOjE2OTgzMDkwMjksImlhdCI6MTY5NzcwNDIyOX0.Y5FsS6MMlg2QmJrrNlBBqjZQrG4iaqvWHAluQLRwV-o”}

TEST mongo@wqdcsrv3067[16:30:18]:/home/mongo $ curl -v $ http://localhost:5678/v1/auth/login --data '{"email":"jason.chentj@homecredit.cn","password":"*********","web":true}'
* Could not resolve host: $; Unknown error
* Closing connection 0
curl: (6) Could not resolve host: $; Unknown error
* About to connect() to localhost port 5678 (#1)
*   Trying 127.0.0.1...
* Connected to localhost (127.0.0.1) port 5678 (#1)
> POST /v1/auth/login HTTP/1.1
> User-Agent: curl/7.29.0
> Host: localhost:5678
> Accept: */*
> Content-Length: 71
> Content-Type: application/x-www-form-urlencoded
>
* upload completely sent off: 71 out of 71 bytes
< HTTP/1.1 200 OK
< Content-Type: application/json
< Grpc-Metadata-Bytebase-Access-Token: eyJhbGciOiJIUzI1NiIsImtpZCI6InYxIiwidHlwIjoiSldUIn0.eyJuYW1lIjoiSmFzb24gQ2hlbnRqIiwiaXNzIjoiYnl0ZWJhc2UiLCJzdWIiOiIxMDEiLCJhdWQiOlsiYmIudXNlci5hY2Nlc3MucHJvZCJdLCJleHAiOjE2OTgzMDkwMjksImlhdCI6MTY5NzcwNDIyOX0.Y5FsS6MMlg2QmJrrNlBBqjZQrG4iaqvWHAluQLRwV-o
< Grpc-Metadata-Bytebase-User: 101
< Grpc-Metadata-Content-Type: application/grpc
< Set-Cookie: access-token=eyJhbGciOiJIUzI1NiIsImtpZCI6InYxIiwidHlwIjoiSldUIn0.eyJuYW1lIjoiSmFzb24gQ2hlbnRqIiwiaXNzIjoiYnl0ZWJhc2UiLCJzdWIiOiIxMDEiLCJhdWQiOlsiYmIudXNlci5hY2Nlc3MucHJvZCJdLCJleHAiOjE2OTgzMDkwMjksImlhdCI6MTY5NzcwNDIyOX0.Y5FsS6MMlg2QmJrrNlBBqjZQrG4iaqvWHAluQLRwV-o; Path=/; Expires=Thu, 26 Oct 2023 08:30:28 GMT; HttpOnly; SameSite=Strict
< Set-Cookie: user=101; Path=/; Expires=Thu, 26 Oct 2023 08:30:28 GMT; SameSite=Strict
< Date: Thu, 19 Oct 2023 08:30:29 GMT
< Content-Length: 263
<
* Connection #1 to host localhost left intact
{"token":"eyJhbGciOiJIUzI1NiIsImtpZCI6InYxIiwidHlwIjoiSldUIn0.eyJuYW1lIjoiSmFzb24gQ2hlbnRqIiwiaXNzIjoiYnl0ZWJhc2UiLCJzdWIiOiIxMDEiLCJhdWQiOlsiYmIudXNlci5hY2Nlc3MucHJvZCJdLCJleHAiOjE2OTgzMDkwMjksImlhdCI6MTY5NzcwNDIyOX0.Y5FsS6MMlg2QmJrrNlBBqjZQrG4iaqvWHAluQLRwV-o"}

下一步尝试调用SQL审核的接口：/sql/check
传入参数解释：
–header ：需要传入我们之前登陆成功后取得的token
–data: 传入参数键值对 {“database”: “instances/{resource ID}/database/{databaseName}”,“statement”: “create table t(id int);”}
其中 resource ID 是来源于数据表： select resource_id from instance ;
其中databaseName 是源于数据表： select name from db;
其中statement 就是需要审核的SQL文本

PS： 如果你是通过docker部署的，并且使用了内置的数据库：可以直接进入容器中访问这个数据

我们可以看到容器里面一共启动了3个postgres实例:
postgres -D /var/opt/bytebase/pgdata-sample/test -p 8083 和 postgres -D /var/opt/bytebase/pgdata-sample/prod -p 8084
（以上这2个实例属于bytebase默认安装的测试和生产的PG实例，本质上可以理解为测试管理的PG实例，并不是真正的bytebase系统本身的数据库）

INFRA [root@wqdcsrv3067 ~]# docker exec -it ab2adacc7ceb /bin/bash
root@ab2adacc7ceb:/# ps -ef | grep postgres

bytebase     23      1  0 06:43 ?        00:00:00 /var/opt/bytebase/resources/postgres-linux-amd64/bin/postgres -D /var/opt/bytebase/pgdata-sample/test -p 8083 -k /tmp -N 500 -h  -c stats_temp_directory=/tmp
bytebase     36      1  0 06:43 ?        00:00:00 /var/opt/bytebase/resources/postgres-linux-amd64/bin/postgres -D /var/opt/bytebase/pgdata-sample/prod -p 8084 -k /tmp -N 500 -h  -c stats_temp_directory=/tmp
bytebase     49      1  0 06:43 ?        00:00:00 /var/opt/bytebase/resources/postgres-linux-amd64/bin/postgres -D /var/opt/bytebase/pgdata -p 8082 -k /tmp -N 500 -h  -c stats_temp_directory=/tmp

bytebase的mata元数据库是 postgres -D /var/opt/bytebase/pgdata -p 8082 -k /tmp -N 500 -h -c stats_temp_directory=/tmp
默认登录账户是 bb , 默认数据库是bb

[local:/tmp]:8082 bb@bb=# \l
                                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider | Access privileges
-----------+-------+----------+-------------+-------------+------------+-----------------+-------------------
bb        | bb    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
postgres  | bb    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
template0 | bb    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/bb            +
           |       |          |             |             |            |                 | bb=CTc/bb
template1 | bb    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/bb            +
           |       |          |             |             |            |                 | bb=CTc/bb
(4 rows)

我们可以查看一下创建实例的resource_id和database的相关信息:

[local:/tmp]:8082 bb@bb=# select resource_id from instance limit 10;
     resource_id      
----------------------
test-sample-instance
prod-sample-instance
pg15ruat
(3 rows)

[local:/tmp]:8082 bb@bb=# select name from db;
          name          
------------------------
employee
employee
postgres
repmgr
db_blaze
...
(23 rows)

返回的是审核的结果：
1）requires PRIMARY KEY： 缺失主键
2）requires columns: created_ts, creator_id, updated_ts, updater_id： 缺失必要的4个审计列 （created_ts, creator_id, updated_ts, updater_id）
3）ID cannot have NULL value： id 列不可以为空
4）ID doesn’t have DEFAULT : id 列不存在默认值

TEST mongo@wqdcsrv3067[17:09:42]:/home/mongo $ curl --request POST http://localhost:5678/v1/sql/check \
>   --header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsImtpZCI6InYxIiwidHlwIjoiSldUIn0.eyJuYW1lIjoiSmFzb24gQ2hlbnRqIiwiaXNzIjoiYnl0ZWJhc2UiLCJzdWIiOiIxMDEiLCJhdWQiOlsiYmIudXNlci5hY2Nlc3MucHJvZCJdLCJleHAiOjE2OTgzMDkwMjksImlhdCI6MTY5NzcwNDIyOX0.Y5FsS6MMlg2QmJrrNlBBqjZQrG4iaqvWHAluQLRwV-o' \
>   --data '{
>     "database":"instances/pg15ruat/databases/db_blaze",
>     "statement": "create table t(id int);"
> }'
{"advices":[{"status":"ERROR","code":601,"title":"table.require-pk","content":"Table \"public\".\"t\" requires PRIMARY KEY, related statement: \"create table t(id int);\"","line":1,"column":0,"detail":""},{"status":"WARNING","code":401,"title":"column.required","content":"Table \"t\" requires columns: created_ts, creator_id, updated_ts, updater_id","line":1,"column":0,"detail":""},{"status":"WARNING","code":402,"title":"column.no-null","content":"Column \"id\" in \"public\".\"t\" cannot have NULL value","line":1,"column":0,"detail":""},{"status":"WARNING","code":420,"title":"column.require-default","content":"Column \"t\".\"id\" in schema \"public\" doesn't have DEFAULT","line":1,"column":0,"detail":""}]}

关于这些审核策略都是存放在表policy中的： payload 的列采用PG的 jsonb格式

[local:/tmp]:8082 bb@bb=# select payload from policy limit 10;
{"name": "Postgres 审核策略", "ruleList": [{"type": "database.drop-empty-database", "level": "ERROR", "engine": "MYSQL", "payload": "{}"}, {"type": "database.drop-empty-database", "level": "ERROR", "engine": "TIDB", "
payload": "{}"}, {"type": "database.drop-empty-database", "level": "ERROR", "engine": "OCEANBASE", "payload": "{}"}, {"type": "system.charset.allowlist", "level": "WARNING", "engine": "MYSQL", "payload": "{\"li
st\":[\"utf8mb4\"]}"}, {"type": "system.charset.allowlist", "level": "WARNING", "engine": "TIDB", "payload": "{\"list\":[\"utf8mb4\"]}"}, {"type": "system.charset.allowlist", "level": "WARNING", "engine": "POST
GRES", "payload": "{\"list\":[\"utf8mb4\"]}"}, {"type": "system.charset.allowlist", "level": "WARNING", "engine": "OCEANBASE", "payload": "{\"list\":[\"utf8mb4\"]}"}, {"type": "system.collation.allowlist", "lev
el": "WARNING", "engine": "MYSQL", "payload": "{\"list\":[\"utf8mb4_0900_ai_ci\"]}"}, {"type": "system.collation.allowlist", "level": "WARNING", "engine": "TIDB", "payload": "{\"list\":[\"utf8mb4_0900_ai_ci\"]}
"}, {"type": "system.collation.allowlist", "level": "WARNING", "engine": "POSTGRES", "payload": "{\"list\":[\"utf8mb4_0900_ai_ci\"]}"}, {"type": "system.collation.allowlist", "level": "WARNING", "engine": "OCEA
NBASE", "payload": "{\"list\":[\"utf8mb4_0900_ai_ci\"]}"}, {"type": "system.comment.length", "level": "WARNING", "engine": "POSTGRES", "payload": "{\"number\":64}"}, {"type": "column.required", "level": "WARNIN
G", "engine": "MYSQL", "payload": "{\"list\":[\"id\",\"created_ts\",\"updated_ts\",\"creator_id\",\"updater_id\"]}"}, {"type": "column.required", "level": "WARNING", "engine": "TIDB", "payload": "{\"list\":[\"i
d\",\"created_ts\",\"updated_ts\",\"creator_id\",\"updater_id\"]}"}, {"type": "column.required", "level": "WARNING", "engine": "POSTGRES", "payload": "{\"list\":[\"id\",\"created_ts\",\"updated_ts\",\"creator_i
d\",\"updater_id\"]}"}, {"type": "column.required", "level": "WARNING", "engine": "ORACLE", "payload": "{\"list\":[\"ID\"]}"}, {"type": "column.required", "level": "WARNING", "engine": "OCEANBASE", "payload": "
{\"list\":[\"id\",\"created_ts\",\"updated_ts\",\"creator_id\",\"updater_id\"]}"},

可能有的测试同学更喜欢用 postman来调试API，我们也来测试一下：

登录模块：

SQL审核模块：

配置登录的token:

调用sql check:

写到最后， 本文采用docker部署的方式，简单介绍了关于bytebase SQL审核的使用以及如何使用API调用.
如果你的公司还没有实现数据库管理和审核的通用平台管理，可以尝试使用bytebase来帮你集中式管理数据库。
如果你的公司已经存在数据库管理平台，可以尝试介入SQL审核的API，帮你实现数据库SQL审核的自动化。

最后感谢bytebase的老板天舟，还有热心的同学CY,Danny的帮助! 🙂

Have a fun with bytebase!