非super user管理会话，非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 | <insufficient privilege> | | | | | | 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 | <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=>
最后检查下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-
一个管理手机系统ROOT权限的系统工具,如果你手机里已经安装了的话，在应用程序列表中会出现一个图标为戴着海盗眼罩的安卓机器人图标
你好，SUPERUSER是一款应用软件，如果你手机里已经安装了的话，在应用程序列表中会出现一个图标为戴着海盗眼罩的安卓机器人图标（3.0以上版本，如果是2.0以下版本的话为安卓机器人的骷髅旗标志），如果没有出现该图标，则说明没有安装。
另外，进入RE管理器并不需要SUPERUSER的权限管理，但是只能进入根目录，要继续查看根目录下其他文件夹内的数据内容，则需要获取ROOT权限，一旦获取之后，在打开RE管理器时，SUPERUSER会弹出窗口询问是否要授予RE管理器最高权限，选择同意之后就能用RE管理器查看根目录下所有文件夹内的数据，但是如果要更改系统数据，需要将系统数据的只读属性去掉，在完成更改之后再改回来。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。