非superuser管理会话 - 数据库编程

在gp中取消或者中断某个用户的超长时间或者SQL存在问题的会话，如果无法拥有超级用户将无法执行该类操作。

首先我们创建两个用户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      | 




         |         |                               |                               
|             |             | 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           
-------+---------+---------+---------+----------+---------+----------------------------------+---------

非superuser管理会话(一)