修改search_path导致的审计与安全函数不可用

环境

系统平台：N/A
版本：4.3.4.6

症状

使用 set_secure_param 等函数时报错该函数不存在：

highgo=> select set_secure_param('hg_ShowLoginInfo','off');

错误:  函数 set_secure_param(unknown, unknown) 不存在

LINE 1: select set_secure_param('hg_ShowLoginInfo','off');

               ^

HINT:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.

highgo=> select show_secure_param();

错误:  函数 show_secure_param() 不存在

LINE 1: select show_secure_param();

               ^

HINT:  没有匹配指定名称和参数类型的函数. 您也许需要增加明确的类型转换.

问题原因

search_path参数被修改，public选项被删除。

[root@localhost data]# psql -U syssso  -d highgo

Password for user syssso:

注意:

-------------------------------------------

Login User: syssso

Login time: 2019-11-28 10:25:00.636237+08

Login Address: [local]

Last Login Status: SUCCESS

Login Failures: 0

Valied Until: infinity

-------------------------------------------



psql (4.3.4.6)

Type "help" for help.



highgo=> show search_path;

search_path

-------------

"$user"

(1 row)

解决方案

修改search_path，添加public如下：

search_path = '"$user",public'  

[root@localhost data]# psql -U syssso  -d highgo

Password for user syssso:

注意:

-------------------------------------------

Login User: syssso

Login time: 2019-11-28 10:24:08.47413+08

Login Address: [local]

Last Login Status: FAILED

Login Failures: 1

Valied Until: infinity

-------------------------------------------



psql (4.3.4.6)

Type "help" for help.



highgo=> select show_secure_param();

      show_secure_param

-----------------------------

Secure level = table,      +

   hg_SepOfPowers = on,     +

   hg_MAControl = on,       +

   hg_RowSecure = off,      +

hg_PwdValidUntil = 5,      +

hg_PwdErrorLock = 5,       +

hg_ShowLoginInfo = on,     +

hg_ClientNoInput = 30 min, +

hg_PwdRule = on,           +

(1 row)



highgo=> select set_secure_param('hg_ShowLoginInfo','off');

        set_secure_param

---------------------------------

set configuration successfully.

(1 row)

至此可正常使用审计与安全相关操作函数。