Non-superuser 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. [Gpadmin @ wx60 ~] $ Psqlgtlionspsql (8.2.15) Typehelpforhelp. gtlions # selectversion

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. [Gpadmin @ wx60 ~] $ Psql gtlionspsql (8.2.15) Type help for help. gtlions = # select version

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.

[Gpadmin @ wx60 ~] $ Psql gtlionspsql (8.2.15) Type "help" for help. gtlions = # select version (); version versions --------------------- 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 catalog + --------- gp_toolkit | gpadmin information_schema | gpadmin catalog | gpadmin pg_bitmapindex | gpadmin pg_catalog | gpadmin pg_toast | gpadmin public | 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.

[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 | restart | application_name | xact_start ------- + --------- + hour ---------------------------------- + --------- + hour + ----------------------------- + ------------- + --------------- + ------------------ + hour 16992 | gtlions | 3395 | 13 | 25881 | t2 | select * from pg_stat_activity; | f | 09:25:56. 197394 + 08 | 09:25:43. 293684 + 08 |-1 | psql | 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 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 transform (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 for related operations.

[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 | restart | application_name | xact_start ------- + --------- + hour ---------------------------------- + --------- + response + ------------- + -------------------------- + response 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity; | f | 09:48:53. 767859 + 08 | 09:48:51. 285594 + 08 |-1 | psql | 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 | restart | application_name | xact_start ------- + --------- + hour ---------------------------------- + --------- + response + ------------- + -------------------------- + response 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity; | f | 09:52:03. 279186 + 08 | 09:48:51. 285594 + 08 |-1 | psql | 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 | restart | application_name | xact_start ------- + --------- + hour ---------------------------------- + --------- + response + ------------- + -------------------------- + response 16992 | gtlions | 4034 | 19 | 25881 | t2 | select * from pg_stat_activity; | f | 09:52:28. 473137 + 08 | 09:48:51. 285594 + 08 |-1 | psql | 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 => Finally, check the current t1 process.
           
          
         
        
       
      
     
    
   
  
 

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-