[zz]greenplum resource queue

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

 

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中，没有独立的角色概念，用户既是角色，也是用户。

LOGIN/NOLOGIN
设定用户是否有登录系统的权限。默认是NOLOGIN。一般用来管理权限的角色（即用户组）分配NOLOGIN权限。

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;