gp基础篇-用户与资源队列管理

灰太狼 2022-05-15 00:26 282阅读 0赞

author:skate  
time:2012/11/16

gp基础篇-用户与资源队列管理

gp像oracle一样，有自己的独立的用户管理模块，但gp不像oracle那样，把用户和用户组分的那么细；gp中的用户，即是用户，又是用户组gp中的用户是与操作系统的用户是独立的，但是为了管理方便，我们可以把gp和os中用户创建成相同的用户名；在是初始安装时，在os上创建gpadmin用户，用其来安装、初始化gp数据库。

如果通过用户来控制gp的访问？

一般每个应用或服务，出于审计和安全，创建单独的用户，为用户分配合适权限

创建用户一般如下：  
gptest=\# create role username  with login createdb createrole password 'password'  resource queue pg\_default;  
CREATE ROLE

创建用户选项说明：  
SUPERUSER/NOSUPERUSER  
指定了用户是否是超级管理员，默认是NOSUPERUSER。 如果是超级管理员则没有访问限制。一般应用用户不要赋予这项权限。

CREATEDB/NOCREATEDB  
指定了用户是否可以创建数据库。默认是NOCREATEDB

CREATEROLE/NOCREATEROLE  
指定了用户是否可以创建新的用户，默认是NOCREATEROLE

INHERIT/NOINHERIT  
在GP中，没有独立的角色概念，用户既是角色，也是用户。

CONNECTION LIMIT connlimit  
限制并发连接的数量，默认值是-1,表示没有限制。在GP中有其它更合适的方式限制并发连接的数量，因此建议不要修改该设置。

PASSWORD 'password'  
设置用户的口令

VALID UNTIL 'timestamp'  
指定口令的生命周期。默认是永远不过期。

RESOURCE QUEUE queue\_name  
为用户建立资源队列后，控制用户连接的资源消耗。一个用户只能分配一个资源队列。

在实际使用中，我们先创建权限管理role，然后把这个role赋予给不用的具体用户，例如

gptest=\# create role useradmin with createdb createrole superuser VALID UNTIL '20121030120000' password 'password';  
CREATE ROLE  
gptest=\#

这里创建的这个role没有login的权限，所以gp把它当作用户组，可以通过如下三种方式查看到  
gptest=\# select \* from pg\_catalog.pg\_roles;  
或  
gptest=\# select \* from pg\_user;  
或  
gptest=\# \\du;

在创建了用户组useradmin后，接下来我们就可以根据应用创建具体的用户，比如

gptest=\# create role skate with login password 'password';  
NOTICE:  resource queue required -- using default resource queue "pg\_default"  
CREATE ROLE  
gptest=\#

再把组权限赋予给用户：  
gptest=\# grant  useradmin to skate;  
GRANT ROLE  
gptest=\# \\du skate  
                                List of roles  
 Role name | Superuser | Create role | Create DB | Connections |  Member of   
\-----------+-----------+-------------+-----------+-------------+-------------  
 skate     | no        | no          | no        | no limit    | \{useradmin\}  
(1 row)

gptest=\#

上面是把创建系统级权限组赋予给用户，一般为了细化权限管理，还应创建一个对象权限组，方便对象权限管理

gptest=\# create role objectadmin ;  
NOTICE:  resource queue required -- using default resource queue "pg\_default"  
CREATE ROLE  
gptest=\# grant all on  table tab1  to  objectadmin;  
GRANT  
gptest=\#

回收权限  
gptest=\# revoke all privileges on tab1 from objectadmin;

还可以对schema和database授权限  
gptest=\# GRANT ALL ON SCHEMA myschema TO admin;  
gptest=\# GRANT ALL ON DATABASE mydb TO admin;

**gp中各种Object对应的权限**

Tables, Views, Sequences：  
SELECT  
INSERT  
UPDATE  
DELETE  
RULE  
ALL

External Tables：  
SELECT  
RULE  
ALL

Databases：  
CONNECT  
CREATE  
TEMPORARY | TEMP  
ALL

Functions：  
EXECUTE

Procedural Languages：  
USAGE  
Schemas  
CREATE  
USAGE  
ALL

**用户管理命令**

创建用户  
create role username with login;

更改用户属性  
alter role username createdb

删除用户  
drop role username;

把组权限赋予给用户  
grant grouprole to username;

回收权限  
revoke grouprole from username;  
revoke all privileges on tab1 from objectadmin;

**gp的资源管理队列**  
gp可以通过resource queue来限制用户使用系统的资源，gp是通过用户可以执行并发的sql数量、每个sql的cost、sql的优先级来限制用户对gp资源的使用。

一般，系统根据公司对gp的使用情况分为：  
系统报表计算：为业务系统计算报表，需要特殊保证的，不会阻塞影响，优先级居中  
bi个性化分析：为做个性化计算分析准备，属于内部使用，优先级可以最低  
管理员权限：可以使用所有权限，优先级最高

**创建resource queue**

限制并发sql的数量为2  
gptest=\# create resource queue adhoc with(active\_statements=2);  
CREATE QUEUE

限制sql的cost的阀值为10000.0  
gptest=\# create resource queue adhoc1 with(max\_cost=10000.0);  
CREATE QUEUE

限制并发sql的数量为2，sql的cost小于100.0的可以不受限制  
gptest=\# create resource queue adhoc2 with(active\_statements=2,min\_cost=100.0);  
CREATE QUEUE  
   
设置优先级为max  
gptest=\# create resource queue adhoc3 with(active\_statements=2,min\_cost=100.0,priority=max);  
CREATE QUEUE  
gptest=\#

更改资源管理队列（resource queue）              ^  
gptest=\# alter resource queue adhoc with(active\_statements=3);  
ALTER QUEUE  
gptest=\#

把resource queue分配给用户  
gptest=\# alter role skate resource queue adhoc;  
ALTER ROLE  
gptest=\# create role skate1 with login resource queue adhoc;  
CREATE ROLE  
gptest=\#

把用户的resource queue恢复系统默认的pg\_default  
gptest=\# alter role skate resource queue none;  
NOTICE:  resource queue required -- using default resource queue "pg\_default"  
ALTER ROLE  
gptest=\#

删除resource queue，但队列必须没有被任何role使用  
gptest=\# drop resource queue adhoc;  
ERROR:  resource queue "adhoc" is used by at least one role  
gptest=\#

查看resource queue的状态  
select \* from pg\_resqueue\_status;  
   
查看role分配的resource queue  
SELECT rolname, rsqname FROM pg\_roles, pg\_resqueue  
 WHERE pg\_roles.rolresqueue=pg\_resqueue.oid;  
   
   
查询所有的resource queue的当前活动sql  
 SELECT usename, rsqname, locktype, objid, transaction,  
 pid, mode, granted, waiting  
 FROM pg\_stat\_activity, pg\_resqueue, pg\_locks  
 WHERE pg\_stat\_activity.procpid=pg\_locks.pid  
 AND pg\_locks.objid=pg\_resqueue.oid;  
   
清空等待的sql  
 SELECT rolname, rsqname, pid, granted,  
 current\_query, datname  
 FROM pg\_roles, pg\_resqueue, pg\_locks, pg\_stat\_activity  
 WHERE pg\_roles.rolresqueue=pg\_locks.objid  
 AND pg\_locks.objid=pg\_resqueue.oid  
 AND pg\_stat\_activity.procpid=pg\_locks.pid;  
查到pid后，直接kill即可。

为了简单，可以为sql创建视图，如下：  
 create view resource\_proc as SELECT rolname, rsqname, pid, granted,  
 current\_query, datname  
 FROM pg\_roles, pg\_resqueue, pg\_locks, pg\_stat\_activity  
 WHERE pg\_roles.rolresqueue=pg\_locks.objid  
 AND pg\_locks.objid=pg\_resqueue.oid  
 AND pg\_stat\_activity.procpid=pg\_locks.pid;

\----end---