Non-Super User session management

Source: Internet
Author: User
Tags psql

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.