greenplum role /user 管理

greenplum role 其实是一个可inherit的user，要可以在客户端使用需要
1、在gp里面创建并赋予login等权限
2、在master的pg_hba.conf里面赋予允许登录ip

查看全部role可以通过以下sql之一

SELECT * FROM  pg_catalog.pg_roles;
SELECT * FROM  pg_catalog.pg_user；
1
2

pg_hba.conf的用途：PostgreSQL Client Authentication Configuration File，可以参考：
https://blog.csdn.net/yaoqiancuo3276/article/details/80404883?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165939278816780357238380%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=165939278816780357238380&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_blogtop_positive~default-1-80404883-null-null.nonecase&utm_term=pg_hba.conf&spm=1018.2226.3001.4450

依据 vmware的文档role的主要内容包括

• Role Attributes ：允许登录、inherit、链接限制、归属资源队列、超级用户、密码（可以用ENCRYPTED PASSWORD加强密码管理）、创建db、创建角色等等
• Membership： grant/revoke 其它角色
• 对象权限
• 不允许登录时间

不允许登录时间

可以按每星期的哪天（0-6 ，sunday-Saturday）不允许登录进行限制

test=# create role denytest deny day  'Saturday'; 
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
test=# alter  role denytest deny between day 'Monday' Time '15:00' AND day 'Sunday' TIME '10:00';  
ERROR:  time interval must not wrap around
test=# alter  role denytest deny between day 'Monday' Time '15:00' AND day 'Friday' TIME '10:00';   
ALTER ROLE

test=# alter role denytest drop deny for day  'Saturday'; 
NOTICE:  dropping DENY rule for "denytest" between Saturday 00:00:00 and Saturday 24:00:00
ALTER ROLE
test=# alter role denytest drop deny for day 'Monday' 
test-# ;
NOTICE:  dropping DENY rule for "denytest" between Monday 15:00:00 and Friday 10:00:00
ALTER ROLE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看role inherit

查看全部role inherit 关系

select a.oid as user_role_id
, a.rolname as user_role_name
, b.roleid as other_role_id
, c.rolname as other_role_name
from pg_roles a
inner join pg_auth_members b on a.oid=b.member
inner join pg_roles c on b.roleid=c.oid 
--where a.rolname = 'user_1'
1
2
3
4
5
6
7
8

也可以使用

SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'mch', oid, 'member');
1
2

查看单个。

测试sql如下：

test=#  SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'mch', oid, 'member');     
  oid  | rolname 
-------+---------
 19164 | mch
 19166 | admin
(2 rows)

test=# revoke admin from mch;
REVOKE ROLE
test=#  SELECT oid, rolname FROM pg_roles WHERE
   pg_has_role( 'mch', oid, 'member');
  oid  | rolname 
-------+---------
 19164 | mch
(1 row)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16