非super user管理会话
来源:互联网 发布:为什么要过年呢 知乎 编辑:程序博客网 时间:2024/05/16 17:55
在gp中取消或者中断某个用户的超长时间或者SQL存在问题的会话,如果无法拥有超级用户将无法执行该类操作。
首先我们创建两个用户t1、t2,并且使用t1登录到数据库。
接下来我们使用用户t2登录到数据库,检查当前会话并尝试取消或者中断用户t1的会话。
会发现非超级用户无法执行取消或者中断其他用户的会话操作。
解决办法是自定义一个函数,并授权给t2用户执行权限,这样就可以实现上述操作了。
接着使用用户t2进行相关操作。
最后检查下t1当前进程。
-EOF-
首先我们创建两个用户t1、t2,并且使用t1登录到数据库。
[gpadmin@wx60 ~]$ psql gtlionspsql (8.2.15)Type "help" for help. gtlions=# select version(); version ------------------------------------------------------------------------------------------------------------------------------------------------------ PostgreSQL 8.2.15 (Greenplum Database 4.2.7.2 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 25 2014 18:05:04(1 row) gtlions=# \du List of roles Role name | Attributes | Member of -----------+-----------------------------------+----------- gpadmin | Superuser, Create role, Create DB | gtlions=# \dn List of schemas Name | Owner --------------------+--------- gp_toolkit | gpadmin information_schema | gpadmin pg_aoseg | gpadmin pg_bitmapindex | gpadmin pg_catalog | gpadmin pg_toast | gpadmin public | gpadmin(7 rows) gtlions=# create user t1 ;NOTICE: resource queue required -- using default resource queue "pg_default"CREATE ROLEgtlions=# create user t2;NOTICE: resource queue required -- using default resource queue "pg_default"CREATE ROLEgtlions=# \c gtlions t1You are now connected to database "gtlions" as user "t1".
接下来我们使用用户t2登录到数据库,检查当前会话并尝试取消或者中断用户t1的会话。
[gpadmin@wx60 ~]$ psql -U t2 gtlionspsql (8.2.15)Type "help" for help. gtlions=> select * from pg_stat_activity ; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start -------+---------+---------+---------+----------+---------+----------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+------------------------------- 16992 | gtlions | 3395 | 13 | 25881 | t2 | select * from pg_stat_activity ; | f | 2014-10-11 09:25:56.197394+08 | 2014-10-11 09:25:43.293684+08 | | -1 | psql | 2014-10-11 09:25:56.197394+08 16992 | gtlions | 3384 | 12 | 25880 | t1 | <insufficient privilege> | | | | | | psql | (2 rows) gtlions=> select pg_cancel_backend(3384);ERROR: must be superuser to signal other server processesgtlions=>
会发现非超级用户无法执行取消或者中断其他用户的会话操作。
解决办法是自定义一个函数,并授权给t2用户执行权限,这样就可以实现上述操作了。
create or replace function session_mgr(procpid integer, opertype character)returns booleanas$BODY$declareret boolean;beginif opertype = 'c' thenret := (select pg_catalog.pg_cancel_backend(procpid));elsif opertype = 'k' thenret := (select pg_catalog.pg_terminate_backend(procpid));end if;return ret;end;$BODY$ LANGUAGE plpgsql security definer; gtlions=# grant execute on function session_mgr(integer, character) to t2;GRANTgtlions=# \c gtlions t1You are now connected to database "gtlions" as user "t1".gtlions=>
接着使用用户t2进行相关操作。
[gpadmin@wx60 ~]$ psql -U t2 gtlionspsql (8.2.15)Type "help" for help. gtlions=> select * from pg_stat_activity ; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start -------+---------+---------+---------+----------+---------+----------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+----------------------------+------------------------------- 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity ; | f | 2014-10-11 09:48:53.767859+08 | 2014-10-11 09:48:51.285594+08 | | -1 | psql | 2014-10-11 09:48:53.767859+08 16992 | gtlions | 3678 | 15 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ????????? | 16992 | gtlions | 3704 | 16 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ???????????? | 16992 | gtlions | 4023 | 18 | 25880 | t1 | <insufficient privilege> | | | | | | psql | (4 rows)gtlions=> select session_mgr(4023,'c'); session_mgr ------------- t(1 row) gtlions=> select * from pg_stat_activity ; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start -------+---------+---------+---------+----------+---------+----------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+----------------------------+------------------------------- 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity ; | f | 2014-10-11 09:52:03.279186+08 | 2014-10-11 09:48:51.285594+08 | | -1 | psql | 2014-10-11 09:52:03.279186+08 16992 | gtlions | 4065 | 20 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ???????????? | 16992 | gtlions | 3678 | 15 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ????????? | 16992 | gtlions | 3704 | 16 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ???????????? | 16992 | gtlions | 4023 | 18 | 25880 | t1 | <insufficient privilege> | | | | | | psql | (5 rows) gtlions=> select session_mgr(4023,'k'); session_mgr ------------- t(1 row) gtlions=> select * from pg_stat_activity ; datid | datname | procpid | sess_id | usesysid | usename | current_query | waiting | query_start | backend_start | client_addr | client_port | application_name | xact_start -------+---------+---------+---------+----------+---------+----------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+----------------------------+------------------------------- 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity ; | f | 2014-10-11 09:52:28.473137+08 | 2014-10-11 09:48:51.285594+08 | | -1 | psql | 2014-10-11 09:52:28.473137+08 16992 | gtlions | 4065 | 20 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ???????????? | 16992 | gtlions | 3678 | 15 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ????????? | 16992 | gtlions | 3704 | 16 | 10 | gpadmin | <insufficient privilege> | | | | | | pgAdmin III - ???????????? | 16992 | gtlions | 4189 | 21 | 25880 | t1 | <insufficient privilege> | | | | | | psql | (5 rows) gtlions=>
最后检查下t1当前进程。
gtlions=> select version();FATAL: terminating connection due to administrator commandserver closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Succeeded.
-EOF-
0 0
- 非super user管理会话
- about RMS super user
- 管理会话
- 管理会话
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- 管理会话
- 会话管理
- 会话管理
- 会话管理
- 会话管理
- js 获取最后一个字符
- 数组
- Android下拉刷新完全解析,教你如何实现下拉刷新功能
- 安装Sitescope老是提示重启解决方案
- 从输入网址到网页显示:DNS查找与请求发送 - 浏览器工作原理
- 非super user管理会话
- V4L2用户空间和kernel层driver的交互过程
- APK反编译得到UI资源
- MonkeyTalk自动化之——Scripting in JavaScript
- 【iOS开发-35】有了ARC内存管理机制,是否还需要担心内存溢出等问题?——面试必备
- android launchmodel的使用
- Android 保证listview中的在getview中,保证setText成功设置
- mac 下javaHL的解决 no svnjavahl in java.library.path
- 常见算法笔试或面试题