非superuser管理会话

[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 |  | | | 
| | | 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 |  | | | 
| | | pgAdmin III - ????????? | 
 16992 | gtlions | 3704 | 16 | 10 | gpadmin |  | | | 
| | | pgAdmin III - ???????????? | 
 16992 | gtlions | 4023 | 18 | 25880 | t1 |  | | | 
| | | 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 |  | | | 
| | | pgAdmin III - ???????????? | 
 16992 | gtlions | 3678 | 15 | 10 | gpadmin |  | | | 
| | | pgAdmin III - ????????? | 
 16992 | gtlions | 3704 | 16 | 10 | gpadmin |  | | | 
| | | pgAdmin III - ???????????? | 
 16992 | gtlions | 4023 | 18 | 25880 | t1 |  | | | 
| | | 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 |  | | | 
| | | pgAdmin III - ???????????? | 
 16992 | gtlions | 3678 | 15 | 10 | gpadmin |  | | | 
| | | pgAdmin III - ????????? | 
 16992 | gtlions | 3704 | 16 | 10 | gpadmin |  | | | 
| | | pgAdmin III - ???????????? | 
 16992 | gtlions | 4189 | 21 | 25880 | t1 |  | | | 
| | | 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-