在greenplum后续版本中，已经将使用role取代了user，所以创建用户就是create role：
[gpadmin@dwhm01_2_111 ~]$ psql -d yueworld_db psql (8.2.15) Type "help" for help. yueworld_db=# yueworld_db=# create role mch with login password 'timisgood'; NOTICE: resource queue required -- using default resource queue "pg_default" CREATE ROLE yueworld_db=#\q [gpadmin@dwhm01_2_111 ~]$ |
添加配置：
[gpadmin@dwhm01_2_111 ~]$ vim /data/master/gpseg-1/pg_hba.conf host yueworld_db mch 10.254.2.111/32 md5 |
重新加载配置：
[gpadmin@dwhm01_2_111 ~]$ gpstop -u 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Starting gpstop with args: -u 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Gathering information and validating the environment... 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Obtaining Segment details from master... 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.12.0 build 1' 20170503:11:30:57:054638 gpstop:dwhm01_2_111:gpadmin-[INFO]:-Signalling all postmaster processes to reload . [gpadmin@dwhm01_2_111 ~]$ |
然后再次登录：
[gpadmin@dwhm01_2_111 ~]$ psql -U mch -W -h 10.254.2.111 -d yueworld_db Password for user mch: psql (8.2.15) Type "help" for help.
yueworld_db=> |
You can set theseattributes when you create the role, or later using the ALTER ROLE command. For example:
=# ALTER ROLEjsmith WITH PASSWORD 'passwd123';
=# ALTER ROLEadmin VALID UNTIL 'infinity';
=# ALTER ROLEjsmith LOGIN;
=# ALTER ROLEjsmith RESOURCE QUEUE adhoc;
=# ALTER ROLEjsmith DENY DAY 'Sunday';
登陆失败，或者以及遗忘了密码，就可以直接用超级管理员登陆修改密码
[gpadmin@dwhm01_2_111 ~]$ psql -U dw -W -h 10.254.2.111 -d yueworld_db Password for user dw: psql: FATAL: password authentication failed for user "dw" [gpadmin@dwhm01_2_111 ~]$ |
修改密码：
yueworld_db=# alter role dw with password 'gpmon_ckys0718'; ALTER ROLE yueworld_db=# yueworld_db-> exit yueworld_db-> \q [gpadmin@dwhm01_2_111 ~]$ |
A role can also have role-specificdefaults for many of the server configuration settings. For example, to set thedefault schema search path for a role:
=# ALTER ROLEadmin SET search_path TO myschema, public;
创建角色admin，并赋予给角色mch,dw：
[gpadmin@dwhm01_2_111 ~]$ psql -d yueworld_db psql (8.2.15) Type "help" for help.
yueworld_db=# create role admin createrole createdb; NOTICE: resource queue required -- using default resource queue "pg_default" CREATE ROLE yueworld_db=# yueworld_db=# grant admin to john,sally; ERROR: role "john" does not exist yueworld_db=# grant admin to mch,dw; GRANT ROLE yueworld_db=# yueworld_db=# revoke admin from mch,dw; REVOKE ROLE yueworld_db=# |
然后建立表数据t1，并将t1的操作权限赋予给角色admin：
yueworld_db=# CREATE TABLE t1 (ID int); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. CREATE TABLE yueworld_db=# insert into t1 select 1; INSERT 0 1 yueworld_db=# commit; WARNING: there is no transaction in progress COMMIT yueworld_db=# select * from t1; id ---- 1 (1 row)
yueworld_db=# grant all on table t1 to admin; GRANT yueworld_db=# |
然后用mch登陆，看是否可以对t1表进行操作，如果能操作，证明角色从属关系建立成功。
[gpadmin@dwhm01_2_111 ~]$ psql -U mch -W -h 10.254.2.111 -d yueworld_db Password for user mch: psql: FATAL: password authentication failed for user "mch" [gpadmin@dwhm01_2_111 ~]$ psql -U mch -W -h 10.254.2.111 -d yueworld_db Password for user mch: psql (8.2.15) Type "help" for help.
yueworld_db=> select * from t1; id ---- 1 (1 row)
yueworld_db=> insert into t1 select 2; INSERT 0 1 yueworld_db=> select * from t1; id ---- 2 1 (2 rows)
yueworld_db=> |
OK，可以看到操作成功了，把admin角色赋予了mch角色，从而使得mch拥有了admin的对t1表的操作权限。
Table 2. Object Privileges | |
Object Type | Privileges |
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 |
Custom Protocol | SELECT INSERT UPDATE DELETE RULE ALL |
Note: Privilegesmust be granted for each object individually. For example, granting ALL on a database does not grant fullaccess to the objects within that database. It only grants all of thedatabase-level privileges (CONNECT, CREATE, TEMPORARY) to the database itself.
使用grant语句赋予角色对象权限，使用revoke收回权限
（1）赋予权限 yueworld_db=# grant insert,delete,update,select on table t1 to mch; GRANT yueworld_db=# （2）收回权限 yueworld_db=# revoke insert,delete,update,select on table t1 from mch; REVOKE yueworld_db=# |
不支持行级别row-level、列级别column-level权限控制，因为不太安全，但是可以使用view来间接实现对行和列的权限授予。
当使用如下任何一种生成密码的时候，HASH方法会执行而生成HASH字符串
· CREATE USER name WITH ENCRYPTED PASSWORD 'password'
· CREATE ROLE name WITH LOGIN ENCRYPTED PASSWORD 'password'
· ALTER USER name WITH ENCRYPTED PASSWORD 'password'
· ALTER ROLE name WITH ENCRYPTED PASSWORD 'password'
加密方式有如下几种：
（1） MD5
（2） SHA-256
Pg默认采用的md5密码机制，存储在表pg_authid里面，只要有查看pg_authid表权限的，就可以看到role的密码hash字符串。如果想改变md5的话 ，需要做以下操作生效：
（1） shell命令行里面 gpconfig -c password_hash_algorithm -v 'sha-256'gpstop -u （2） pg窗口里面 =# SET password_hash_algorithm = 'sha-256'; |
参考文档：http://gpdb.docs.pivotal.io/43130/admin_guide/roles_privs.html