首页 > 代码库 > 非super user管理会话
非super user管理会话
首先我们创建两个用户t1、t2,而且使用t1登录到数据库。
[gpadmin@wx60 ~]$ psql gtlions psql (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 ROLE gtlions=# create user t2; NOTICE: resource queue required -- using default resource queue "pg_default" CREATE ROLE gtlions=# \c gtlions t1 You are now connected to database "gtlions" as user "t1".
接下来我们使用用户t2登录到数据库。检查当前会话并尝试取消或者中断用户t1的会话。
[gpadmin@wx60 ~]$ psql -U t2 gtlions psql (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 processes gtlions=>
会发现非超级用户无法运行取消或者中断其它用户的会话操作。
解决的方法是自己定义一个函数,并授权给t2用户运行权限。这样就能够实现上述操作了。
create or replace function session_mgr(procpid integer, opertype character) returns boolean as $BODY$ declare ret boolean; begin if opertype = ‘c‘ then ret := (select pg_catalog.pg_cancel_backend(procpid)); elsif opertype = ‘k‘ then ret := (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; GRANT gtlions=# \c gtlions t1 You are now connected to database "gtlions" as user "t1". gtlions=>
接着使用用户t2进行相关操作。
[gpadmin@wx60 ~]$ psql -U t2 gtlions psql (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 command server 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-
非super user管理会话