Creating a PostgreSQL Audit Plugin for SQLE: Step‑by‑Step Guide

Introduction: The article explains the rapid growth of the database industry and the need for multi‑database support in SQLE, describing its plugin architecture that separates business audit workflow from specific SQL audit code, allowing plugins to add support for new databases without upgrading the core.

Goal: Demonstrate how to create a simple PostgreSQL audit plugin with two rules—prohibiting SELECT * and limiting the number of columns in a CREATE TABLE statement—using example code from the repository.

Step 1: Create the plugin project. Initialize a Go module, set GOPROXY, and fetch the SQLE library.

mkdir sqle-pg-plugin
cd sqle-pg-plugin
touch main.go
go mod init sqle-pg-plugin # 初始化go mod
export GOPROXY=goproxy.cn,goproxy.io,direct # 设置 GoProxy，解决SQLE库下载问题，通过IDEA开发的可以在IDEA软件上设置；
go get github.com/actiontech/[email protected] # 此版本为该文章编辑时的最新版本。

Step 2: Write the minimal plugin code that registers a Postgres adaptor and serves it.

package main
import (
adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
)
func main() {
plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
plugin.Serve()
}

Step 3: Add a rule that blocks the use of SELECT * by defining a Rule object and a handler that checks the SQL string.

package main
import (
"context"
"strings"
"github.com/actiontech/sqle/sqle/driver"
adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
)
func main() {
plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
rule1 := &driver.Rule{
Name:     "pg_rule_1", // 规则ID，该值会与插件类型一起作为这条规则在 SQLE 的唯一标识
Desc:     "禁止使用 SELECT *",      // 规则描述
Category: "DQL规范",           // 规则分类，用于分组，相同类型的规则会在 SQLE 的页面上展示在一起
Level:    driver.RuleLevelError,    // 规则等级，表示该规则的严重程度
}
//
rule1Handler := func(ctx context.Context, rule *driver.Rule, sql string) (string, error) {
if strings.Contains(sql, "select *") {
return rule.Desc, nil
}
return "", nil
}
plugin.AddRule(rule1, rule1Handler)
plugin.Serve()
}

Step 4: Add a configurable complex rule that warns when a table has too many columns. This rule uses the pg_query_go parser to inspect the AST, defines a parameter max_column_count , and registers a handler that counts column definitions.

package main
import (
"context"
"fmt"
"strings"
"github.com/actiontech/sqle/sqle/driver"
adaptor "github.com/actiontech/sqle/sqle/pkg/driver"
"github.com/actiontech/sqle/sqle/pkg/params"
parser "github.com/pganalyze/pg_query_go/v2"
)
func main() {
plugin := adaptor.NewAdaptor(&adaptor.PostgresDialector{})
rule1 := &driver.Rule{
Name:     "pg_rule_1",
Desc:     "避免查询所有的列",
Category: "DQL规范",
Level:    driver.RuleLevelError,
}
rule1Handler := func(ctx context.Context, rule *driver.Rule, sql string) (string, error) {
if strings.Contains(sql, "select *") {
return rule.Desc, nil
}
return "", nil
}
// Define second rule
rule2 := &driver.Rule{
Name:     "pg_rule_2",
Desc:     "表字段不建议过多",
Level:    driver.RuleLevelWarn,
Category: "DDL规范",
Params: []*params.Param{ // 自定义参数列表
&params.Param{
Key:   "max_column_count",  // 自定义参数的ID
Value: "50",                // 自定义参数的默认值
Desc:  "最大字段个数",            // 自定义参数在页面上的描述
Type:  params.ParamTypeInt, // 自定义参数的值类型
},
},
}
// Handler for the second rule receives an AST object
rule2Handler := func(ctx context.Context, rule *driver.Rule, ast interface{}) (string, error) {
node, ok := ast.(*parser.RawStmt)
if !ok {
return "", nil
}
switch stmt := node.GetStmt().GetNode().(type) {
case *parser.Node_CreateStmt:
columnCounter := 0
for _, elt := range stmt.CreateStmt.TableElts {
switch elt.GetNode().(type) {
case *parser.Node_ColumnDef:
columnCounter++
}
}
// Read the configured max column count
count := rule.Params.GetParam("max_column_count").Int()
if count > 0 && columnCounter > count {
return fmt.Sprintf("表字段不建议超过%d个，目前有%d个", count, columnCounter), nil
}
}
return "", nil
}
plugin.AddRule(rule1, rule1Handler)
plugin.AddRuleWithSQLParser(rule2, rule2Handler)
// Register the SQL parser with the plugin
plugin.Serve(adaptor.WithSQLParser(func(sql string) (ast interface{}, err error) {
result, err := parser.Parse(sql)
if err != nil {
return nil, fmt.Errorf("parse sql error")
}
if len(result.Stmts) != 1 {
return nil, fmt.Errorf("unexpected statement count: %d", len(result.Stmts))
}
return result.Stmts[0], nil
}))
plugin.Serve()
}

After compiling and deploying the plugin binary to the SQLE server, the new rules appear in the SQLE rule interface and are triggered appropriately during SQL review work orders, as demonstrated by the provided screenshots.

Conclusion: The tutorial provides a practical workflow for developing SQLE audit plugins, enabling teams to create custom rule sets for their specific database policies and integrate them with the SQLE platform.