SQL异常行为感知

查看SQL异常行为检测基本配置

• 设置并查看SQL异常行为检测功能是否已开启。

  将autonomy_risky_operation参数设置为“large_affected,large_return,no_where,tautology,inference”，具体设置请参见《参考》中“数据库运行参数说明 > GUC参数说明 > SQL异常行为感知参数”章节。

  autonomy_risky_operation取值包含需要检测的异常行为类型时，则表示对应异常行为类型检测开关开启，否则表示关闭。

  gaussdb=# SHOW autonomy_risky_operation;
   autonomy_risky_operation
  -----------------------
   large_affected,large_return,no_where,tautology,inference
  (1 row)

• 设置并查看大规模影响和大规模返回阈值。
  将autonomy_affected_threshold参数设置为100，将autonomy_return_threshold参数设置为100，具体设置请参见《参考》中“数据库运行参数说明 > GUC参数说明 > SQL异常行为感知参数”章节。

  gaussdb=# SHOW autonomy_affected_threshold;
   autonomy_affected_threshold 
  -----------------------------
   100
  (1 row)
  gaussdb=# SHOW autonomy_return_threshold;
   autonomy_return_threshold 
  -----------------------------
   100
  (1 row)

执行异常行为操作

1. 创建数据表risky_table。

   gaussdb=# CREATE TABLE if not exists risky_table(id serial primary key,num int,ch char,word varchar(20),txt text);

2. 插入数据。

   CREATE OR REPLACE PROCEDURE fill_test_space()
   AS
   BEGIN
   FOR i IN 1..20 LOOP
   INSERT INTO risky_table(num, ch, word, txt) VALUES (11, 'a', 'aaa', 'AAAA');
   INSERT INTO risky_table(num, ch, word, txt) VALUES (11, 'a', 'aaa', 'AAAA');
   INSERT INTO risky_table(num, ch, word, txt) VALUES (11, 'a', 'aaa', 'AAAA');
   INSERT INTO risky_table(num, ch, word, txt) VALUES (11, 'a', 'aaa', 'AAAA');
   INSERT INTO risky_table(num, ch, word, txt) VALUES (11, 'a', 'aaa', 'AAAA');
   PERFORM * FROM risky_table LIMIT 1;
   COMMIT;
   END LOOP;
   END;
   /
   CALL fill_test_space();

3. 执行无条件更新操作并查询是否记录审计日志。

   gaussdb=# UPDATE risky_table SET num = num + 1;
   UPDATE 100
   gaussdb=# SELECT * FROM pg_query_audit(sysdate-0.1, sysdate+0.1) WHERE type = 'risky_no_where';
             time          |      type      | result | userid | username | database | client_conninfo | object_name |              detail_info              | node_name |            thread_id            | local_port | remote_port 
   ------------------------+----------------+--------+--------+----------+----------+-----------------+-------------+---------------------------------------+-----------+---------------------------------+------------+-------------
    2025-12-12 10:45:13+08 | risky_no_where | ok     | 10     | user001      | postgres | gsql@[local]    | risky_table | UPDATE risky_table SET num = num + 1; | datanode  | 140244490385152@818822713787704 | 38100      | null
   (1 row)

4. 执行影响行数超过100条的UPDATE语句并查询是否记录审计日志。

   gaussdb=#  UPDATE risky_table SET num = num + 1 WHERE ch = 'a';
   UPDATE 100
   gaussdb=# SELECT * FROM pg_query_audit(sysdate-0.1, sysdate+0.1) WHERE type = 'risky_large_affected';
             time          |         type         | result | userid | username | database | client_conninfo | object_name |                     detail_info                      | node_name |            thread_id            | local_port | remote_po
   rt 
   ------------------------+----------------------+--------+--------+----------+----------+-----------------+-------------+------------------------------------------------------+-----------+---------------------------------+------------+----------
   ---
    2025-12-12 10:56:07+08 | risky_large_affected | ok     | 10     | user001      | postgres | gsql@[local]    | risky_table | UPDATE risky_table SET num = num + 1 WHERE ch = 'a'; | datanode  | 140244490385152@818823367878126 | 38100      | null
   (1 row)

5. 执行返回行数超过100条的SELECT语句并查询是否记录审计日志。

   gaussdb=#  SELECT num FROM risky_table WHERE ch = 'a';
   gaussdb=#  SELECT * FROM pg_query_audit(sysdate-0.1, sysdate+0.1) WHERE type = 'risky_large_return';
             time          |        type        | result | userid | username | database | client_conninfo | object_name |                 detail_info                 | node_name |            thread_id            | local_port | remote_port 
   ------------------------+--------------------+--------+--------+----------+----------+-----------------+-------------+---------------------------------------------+-----------+---------------------------------+------------+-------------
    2025-12-12 11:02:35+08 | risky_large_return | ok     | 10     | user001      | postgres | gsql@[local]    | risky_table | select num from risky_table where ch = 'a'; | datanode  | 140244490385152@818823755641249 | 38100      | null
   (1 row)

6. 执行WHERE从句的condition为永真的SELECT语句并查询是否记录审计日志。

   gaussdb=# SELECT num FROM risky_table WHERE 1=1;
   gaussdb=# SELECT * FROM pg_query_audit(sysdate-0.1, sysdate+0.1) WHERE type = 'risky_tautology';
             time          |      type       | result | userid | username | database | client_conninfo | object_name |              detail_info               | node_name |            thread_id            | local_port | remote_port 
   ------------------------+-----------------+--------+--------+----------+----------+-----------------+-------------+----------------------------------------+-----------+---------------------------------+------------+-------------
    2025-12-12 11:07:19+08 | risky_tautology | ok     | 10     | user001      | postgres | gsql@[local]    | risky_table | select num from risky_table where 1=1; | datanode  | 140244490385152@818824039616318 | 38100      | null
   (1 row)

7. 执行调用PG_SLEEP函数的语句并查询是否记录审计日志。

   gaussdb=# SELECT PG_SLEEP(5);
    txt 
   -----
   (0 rows)
   gaussdb=# SELECT * FROM pg_query_audit(sysdate-0.1, sysdate+0.1) WHERE type = 'risky_inference';
             time          |      type       | result | userid | username | database | client_conninfo | object_name |                                                                                                          detail_info            
                                                                                                  | node_name |            thread_id            | local_port | remote_port 
   ------------------------+-----------------+--------+--------+----------+----------+-----------------+-------------+---------------------------------------------------------------------------------------------------------------------------------
   -----------------------------------------------------------------------------------------------+-----------+---------------------------------+------------+-------------
    2025-12-12 11:09:12+08 | risky_inference | ok     | 10     | user001      | postgres | gsql@[local]    | t           | select PG_SLEEP(5);
    | datanode  | 140244490385152@818824152400597 | 38100      | null
   (1 row)