Non-Super User session management

Cancel or interrupt a user's session that has been too long or has SQL problems in GP. If a Super User cannot perform this operation.

First, we create two users T1 and T2, and use T1 to log on to the database.

[[email protected] ~]$ 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".

Next, we use user T2 to log on to the database, check the current session, and try to cancel or interrupt the session of user T1.

[[email protected] ~]$ 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=> 

Non-superusers cannot cancel or interrupt session operations of other users.

The solution is to customize a function and grant the T2 user the execution permission so that the above operations can be implemented.

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

Then, use user T2 to perform related operations.

[[email protected] ~]$ 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=> 

Finally, check the current process of 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-

non-Super User management session