Postgresql跟踪plpgsql插件plpgsql_check（转载）

最新推荐文章于 2024-06-19 17:07:49 发布

forest_tree

最新推荐文章于 2024-06-19 17:07:49 发布

阅读量565

点赞数

原文链接：https://blog.csdn.net/dazuiba008/article/details/108213810?utm_medium=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&depth_1-utm_source=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&request_id=5f457c570388ae0b5643d835

版权

plpgsql_check插件，用于解决plpgsql相关的问题，比如，对象是否定义，检测类型的一致性，类型定义错误，SQL注入，性能分析，未使用变量或者参数等等

PostgreSQL 9.5以上均支持改插件

git地址：
https://github.com/okbob/plpgsql_check#tracer
安装：

unzip /home/postgres/plpgsql_check-master.zip
chown -R postgres.postgres plpgsql_check-master
cd plpgsql_check-master
make USE_PGXS=1 clean
make USE_PGXS=1 install
1
2
3
4
5
主动模式：
以下例子说明了，f1函数调用了t1的c列，但是该表没有c列，主动使用plpgsql_check_function_tb可以检测出该错误

postgres=# CREATE EXTENSION plpgsql_check;
LOAD
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE

postgres=#
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $f u n c t i o n$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
RAISE NOTICE ‘%’, r.c; – there is bug - table t1 missing “c” column
END LOOP;
END;
$f u n c t i o n$ ;

CREATE FUNCTION

postgres=# select f1(); – execution doesn’t find a bug due to empty table t1
f1
────

(1 row)

postgres=# \x
Expanded display is on.
postgres=# select * from plpgsql_check_function_tb(‘f1()’);
─[ RECORD 1 ]───────────────────────────
functionid │ f1
lineno │ 6
statement │ RAISE
sqlstate │ 42703
message │ record “r” has no field “c”
detail │ [null]
hint │ [null]
level │ error
position │ 0
query │ [null]

postgres=# \sf+ f1
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
AS $f u n c t i o n$
DECLARE r record;
BEGIN
FOR r IN SELECT * FROM t1
LOOP
RAISE NOTICE ‘%’, r.c; – there is bug - table t1 missing “c” column
END LOOP;
END;
$f u n c t i o n$
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
可以使用以下语句大批量检测相关函数

– check all nontrigger plpgsql functions
SELECT p.oid, p.proname, plpgsql_check_function(p.oid)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = ‘plpgsql’ AND p.prorettype <> 2279;
或者

SELECT p.proname, tgrelid::regclass, cf.*
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
JOIN pg_language l ON p.prolang = l.oid
JOIN pg_namespace n ON p.pronamespace = n.oid,
LATERAL plpgsql_check_function(p.oid, t.tgrelid) cf
WHERE n.nspname = ‘public’ and l.lanname = ‘plpgsql’
或者

– check all plpgsql functions (functions or trigger functions with defined triggers)
SELECT
(pcf).functionid::regprocedure, (pcf).lineno, (pcf).statement,
(pcf).sqlstate, (pcf).message, (pcf).detail, (pcf).hint, (pcf).level,
(pcf).“position”, (pcf).query, (pcf).context
FROM
(
SELECT
plpgsql_check_function_tb(pg_proc.oid, COALESCE(pg_trigger.tgrelid, 0)) AS pcf
FROM pg_proc
LEFT JOIN pg_trigger
ON (pg_trigger.tgfoid = pg_proc.oid)
WHERE
prolang = (SELECT lang.oid FROM pg_language lang WHERE lang.lanname = ‘plpgsql’) AND
pronamespace <> (SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = ‘pg_catalog’) AND
– ignore unused triggers
(pg_proc.prorettype <> (SELECT typ.oid FROM pg_type typ WHERE typ.typname = ‘trigger’) OR
pg_trigger.tgfoid IS NOT NULL)
OFFSET 0
) ss
ORDER BY (pcf).functionid::regprocedure::text, (pcf).lineno
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
被动模式
在启动时就有检查功能-必须加载plpgsql_check模块。

可以在postgresql.conf中加入该参数

plpgsql_check.mode = [ disabled | by_function | fresh_start | every_start ]
plpgsql_check.fatal_errors = [ yes | no ]

plpgsql_check.show_nonperformance_warnings = false
plpgsql_check.show_performance_warnings = false
1
2
3
4
5
还需要shared_preload_libraries加入我们用的插件，如下：

hank=# show shared_preload_libraries ;
shared_preload_libraries

pg_pathman,plpgsql,plpgsql_check
1
2
3
4
如：

#plpgsql_check
plpgsql_check.mode = every_start
plpgsql_check.fatal_errors = on
plpgsql_check.show_nonperformance_warnings = true
plpgsql_check.show_performance_warnings = true
1
2
3
4
5
通过以下方式启动被动模式：

load ‘plpgsql’; – 1.1 and higher doesn’t need it
load ‘plpgsql_check’;
set plpgsql_check.mode = ‘every_start’;

SELECT fx(10); – run functions - function is checked before runtime starts it
1
2
3
4
5
plpgsql_check无法验证对在plpgsql函数运行时中创建的临时表的查询。以下例子，有必要创建一个伪造的临时表或为此功能禁用plpgsql_check。实际中，临时表以比持久表有更高的优先级存储在自己的（每用户）模式中。因此，您可以这样做

CREATE OR REPLACE FUNCTION public.disable_dml()
RETURNS trigger
LANGUAGE plpgsql AS $f u n c t i o n$
BEGIN
RAISE EXCEPTION SQLSTATE ‘42P01’
USING message = format(‘this instance of %I table doesn’‘t allow any DML operation’, TG_TABLE_NAME),
hint = format(‘you should to run “CREATE TEMP TABLE %1 $I (L I K E$ I INCLUDING ALL);” statement’,
TG_TABLE_NAME);
RETURN NULL;
END;
$f u n c t i o n$ ;

CREATE TABLE foo(a int, b int); – doesn’t hold data ever
CREATE TRIGGER foo_disable_dml
BEFORE INSERT OR UPDATE OR DELETE ON foo
EXECUTE PROCEDURE disable_dml();

postgres=# INSERT INTO foo VALUES(10,20);
ERROR: this instance of foo table doesn’t allow any DML operation
HINT: you should to run “CREATE TEMP TABLE foo(LIKE foo INCLUDING ALL);” statement
postgres=#

CREATE TABLE
postgres=# INSERT INTO foo VALUES(10,20);
INSERT 0 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
hank=# CREATE OR REPLACE FUNCTION public.fx1(a integer)
hank-# RETURNS integer
hank-# LANGUAGE plpgsql
hank-# AS $f u n c t i o n$
hank $KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ begin hank$ # if a > 10 then
hank $KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ raise not\dots$ # return -1;
hank $KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ else hank$ # raise notice ‘nazdar’;
hank $KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ return 1;\dots$ # end if;
hank $KaTeX parse error: Expected 'EOF', got '#' at position 1: #̲ end; hank$ # $f u n c t i o n$ ;
CREATE FUNCTION
hank=#
hank=#

每个语句(不是每行)的概要可以通过plpgsql_profiler_function_statements_tb查看

hank=# select stmtid, parent_stmtid, parent_note, lineno, exec_stmts, stmtname
hank-# from plpgsql_profiler_function_statements_tb(‘fx1’);
stmtid | parent_stmtid | parent_note | lineno | exec_stmts | stmtname
--------±--------------±------------±-------±-----------±----------------
0 | | | 2 | 0 | statement block
1 | 0 | body | 3 | 0 | IF
2 | 1 | then body | 4 | 0 | RAISE
3 | 1 | then body | 5 | 0 | RETURN
4 | 1 | else body | 7 | 0 | RAISE
5 | 1 | else body | 8 | 0 | RETURN
(6 rows)

其他有关trace,sql安装，以及安全相关的示例这里不再演示，请参考以下链接：
https://github.com/digoal/blog/blob/master/201908/20190831_01.md
https://github.com/okbob/plpgsql_check#tracer

原文链接:https://blog.csdn.net/dazuiba008/article/details/108213810?utm_medium=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&depth_1-utm_source=distribute.pc_feed.none-task-blog-personrec_tag-7.nonecase&request_id=5f457c570388ae0b5643d835

forest_tree

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Postgresql跟踪plpgsql插件plpgsql_check（转载）

plpgsql_check插件，用于解决plpgsql相关的问题，比如，对象是否定义，检测类型的一致性，类型定义错误，SQL注入，性能分析，未使用变量或者参数等等PostgreSQL 9.5以上均支持改插件git地址：https://github.com/okbob/plpgsql_check#tracer安装：unzip /home/postgres/plpgsql_check-master.zipchown -R postgres.postgres plpgsql_check-master
复制链接

扫一扫