PostgreSQL授权普通用户kill会话权限


作者:吴聪


作为DBA,可能经常回听到开发人员说“我有个SQL执行太久了,帮我kill下”“我有张表好像锁住了,帮我kill下”。时间久了确实挺烦的,此时我们肯定想的是让他们自己去kill,但是又不能直接给开发或者用户超级用户的权限,那么怎么只授权普通用户kill会话的权限呢?


—方法1:


PostgreSQL9.6开始,新增了默认角色pg_signal_backend,这个角色具有 cancel query、terminate 其它会话的权限。


例子:


bill@bill=>create user user01 login;
CREATE ROLE
bill@bill=>create user user02 login;
CREATE ROLE


会话1:


bill@bill=>\c - user01
You are now connected to database 'bill' as user 'user01'.
user01@bill=>select pg_backend_pid();
 pg_backend_pid
----------------
          18238
(1 row)


会话2:


bill@bill=>\c - user02
You are now connected to database 'bill' as user 'user02'.
user02@bill=>select pg_cancel_backend(18238);
ERROR:  must be a member of the role whose query is being canceled or member of pg_signal_backend


可以看到,普通用户是没法kill会话的。当然这里的提示也很明显了,普通用户要么只能kill自己的会话,要么属于pg_signal_backend组中。


授权:


这下可以kill会话了。


bill@bill=>grant pg_signal_backend to user02;
GRANT ROLE
bill@bill=>\c - user02
You are now connected to database 'bill' as user 'user02'.
user02@bill=>select pg_cancel_backend(18238);
 pg_cancel_backend
-------------------
 t
(1 row)


但是需要注意,不能去kill超级用户的会话,只有超级用户才能kill超级用户的会话。


user02@bill=>select pg_cancel_backend(10852);
ERROR:  must be a superuser to cancel superuser query


那如果用的是9.6之前的版本呢?虽然现在应该很少有人用9.6之前的版本了,但是任然还是会有的,比如我自己这边就有套GP6的库,内核版本就是PG9.4,那么该怎么办呢?


这种情况我们可以通过UDF来实现。


—方法2:


创建UDF函数:


bill@bill=>CREATE SCHEMA query_admin;
CREATE SCHEMA
bill@bill=>CREATE OR REPLACE FUNCTION query_admin.kill_process(userpid integer)
bill-# RETURNS boolean AS $body$
bill$# DECLARE
bill$#     qry boolean;
bill$# BEGIN
bill$#     qry := (SELECT pg_catalog.pg_cancel_backend(pid)
bill$#             FROM pg_stat_activity
bill$#             WHERE usename=(select session_user)
bill$#             AND pid=userpid);
bill$#     RETURN qry;
bill$# END;
bill$# $body$
bill-# LANGUAGE plpgsql
bill-# SECURITY DEFINER
bill-# VOLATILE
bill-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION


授权:


bill@bill=>GRANT USAGE ON SCHEMA query_admin TO user02;
GRANT
bill@bill=>GRANT EXECUTE ON FUNCTION query_admin.kill_process(pid integer) TO user02;
GRANT


使用普通用户去调用该函数就可以kill会话了:


user02@bill=>SELECT *FROM query_admin.kill_process(22734);
 kill_process
--------------
(1 row)


总结:


对于给普通用户授权kill会话权限,建议:


  • 版本 >= 9.6:授权普通用户pg_signal_backend角色;

  • 版本 < 9.6:使用自定义函数。



规模空前,再创历史 | 2020 PG亚洲大会圆满结束
PG ACE计划的正式发布
三期PostgreSQL国际线上沙龙活动的举办
六期PostgreSQL国内线上沙龙活动的举办

中国PostgreSQL分会与腾讯云战略合作协议签订


PostgreSQL 13.0 正式版发布通告

深度报告:开源协议那些事儿

从“非主流”到“潮流”,开源早已值得拥有

Oracle中国正在进行新一轮裁员,传 N+6 补偿

PostgreSQL与MySQL版权比较

新闻|Babelfish使PostgreSQL直接兼容SQL Server应用程序

四年三冠,PostgreSQL再度荣获“年度数据库”


更多新闻资讯行业动态技术热点,请关注中国PostgreSQL分会官方网站

https://www.postgresqlchina.com

中国PostgreSQL分会生态产品

https://www.pgfans.cn

中国PostgreSQL分会资源下载站

https://www.postgreshub.cn


点赞在看分享收藏

本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部