角色权限及客户端认证管理
GP数据库逻辑结构
在GP中，Database（数据库）、Schema（模式）以及Role（角色）三者之间的关系如下所示：
- 一个数据库下可以有多个模式，一个模式只属于一个数据库。模式在GP中也被称为Namespace，不同数据库之间的模式没有关系，可以重名；
- 语言在使用之前必须创建，一个语言只属于一个数据库；
- 表、视图、索引、序列、函数必须属于一个模式；
- 一个文件空间可以有多个表空间，一个表空间只属于一个文件空间，文件空间和角色之间没有关系；
- 表空间和表时一对多的关系，一个模式下的表可以分布在多个表空间下；
- 除了文件空间之外，其他的权限管理都是通过角色来实现，在这些层次结构中，用户必须对上一层有访问权限才能够访问该层的内容；
什么是角色(role)
- Role的组成：由用户(User)和组(Group)组成；
- 跟OS的role没有关系；
- User通过Master节点登录和认证的；
- Role是定义在GPDB系统级别的；
- 初始化SUPERUSERROLE：gpadmin。
角色与权限安全的最佳实践
- 保护系统gpadmin的用户；
- 为每个登录的User分配不同的角色；
- 使用组来管理权限从而实现管理组；
- 控制具备SUPERUSER属性的User数量。
创建Role
创建用户User Role
使用CREATE ROLE创建一个User Role，语法如下所示：
template1-# ]h create role Command: CREATE ROLE Description: define a new database role Syntax: CREATE ROLE name [[WITH] option [ ... ]] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEEXTTABLE | NOCREATEEXTTABLE [ ( attribute='value'[, ...] ) ] where attributes and values are: type='readable'|'writable' protocol='gpfdist'|'http'|'gphdfs' | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE rolename [, ...] | ROLE rolename [, ...] | ADMIN rolename [, ...] | RESOURCE QUEUE queue_name
例如： # CREATE ROLE mavshuangWITH LOGIN;
template1=# CREATE ROLE mavshuang WITH LOGIN; NOTICE: resource queue required -- using default resource queue "pg_default" CREATE ROLE
ALTER ROLE属性
使用ALTER ROLE修改角色的属性，语法如下所示：
template1-# h alter role Command: ALTER ROLE Description: change a database role Syntax: ALTER ROLE name RENAME TO newname ALTER ROLE name SET config_parameter {TO | =} {value | DEFAULT} ALTER ROLE name RESET config_parameter ALTER ROLE name RESOURCE QUEUE {queue_name | NONE} ALTER ROLE name [ [WITH] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEEXTTABLE | NOCREATEEXTTABLE [ ( attribute='value'[, ...] ) ] where attributes and values are: type='readable'|'writable' protocol='gpfdist'|'http'|'gphdfs' | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ENCRYPTED | UNENCRYPTED] PASSWORD 'password' | VALID UNTIL 'timestamp'
创建组Group Role
使用CREATE ROLE创建一个GroupRole:
=#CREATE ROLE admin CREATEROLE CREATEDB;
template1=# du List of roles Role name | Attributes | Member of -----------+--------------------------------------+----------- admin | Create role, Create DB, Cannot login | gpadmin | Superuser, Create role, Create DB | mavshuang | |
添加或删除Member(UserRole)
使用GRANT关键字授权，使用REVOKE关键字取消授权；
template1=# h GRANT Command: GRANT Description: define access privileges Syntax: GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] GRANT role [, ...] TO username [, ...] [ WITH ADMIN OPTION ] GRANT { SELECT | INSERT | ALL [PRIVILEGES] } ..ON PROTOCOL protocolname ..TO username
template1=# h REVOKE Command: REVOKE Description: remove access privileges Syntax: REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] FROM { username | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ] REVOKE [ ADMIN OPTION FOR ] role [, ...] FROM username [, ...] [ CASCADE | RESTRICT ]
template1=# GRANT admin TO mavshuang; GRANT ROLE template1=# du List of roles Role name | Attributes | Member of -----------+--------------------------------------+----------- admin | Create role, Create DB, Cannot login | gpadmin | Superuser, Create role, Create DB | mavshuang | | {admin} template1=# REVOKE admin FROM mavshuang; REVOKE ROLE template1=# du List of roles Role name | Attributes | Member of -----------+--------------------------------------+----------- admin | Create role, Create DB, Cannot login | gpadmin | Superuser, Create role, Create DB | mavshuang | |
赋予合适的权限给GROUP ROLE
=#GRANT ALL ON TABLE mytable TO admin;
=# GRANT ALL ON SCHEMA myschema TO admin;
=# GRANT ALL ONDATABASE mydb TO admin;
获取管理属性
testdw=# h SET ROLE Command: SET ROLE Description: set the current user identifier of the current session Syntax: SET [ SESSION | LOCAL ] ROLE rolename SET [ SESSION | LOCAL ] ROLE NONE RESET ROLE
管理对象权限
|
对象类型 |
权限 |
|
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 |
每个对象的权限必须被独立的授权
使用GRANT SQL命令给指定的Role授权一个对象
=# GRANT INSERT ON mytable TO mavshuang
使用DROP OWNED和REASSIGN OWNED命令来取消Role的Owner权限
=# REASSIGNOWNED BY mavshuang TO tom;
=# DROP OWNED BYmavshuang;
testdw=# d 通过d命令行查看表相关信息包括模式，名称、类型、所有者以及存储方式 List of relations Schema | Name | Type | Owner | Storage --------+-------------+-------+---------+--------- public | tb1_test_01 | table | gpadmin | heap (1 row)
模拟Row或者Column级别的权限控制:本身不支持Row和Column级别的访问控制，可以通过View方式模拟。
密码加密
GPDB4.2.1版本之前，密码默认使用MD5加密,MD5加密目前可以被破解但消耗成本太高。从4.2.1版本开始，开始使用SHA-256加密，美国国家安全局使用此方法加密。
基于时间的登录认证
访问限制可以控制到具体时间点。时间约束仅仅对于设置的Role有效。比如晚上8点到凌晨6点用于跑批处理程序；早8点到下午6点用于开发用户使用等；
需要的权限：只有SUPERUSER（比如gpadmin）或者具备CREATEROLE权限是必须的。
如何添加时间约束：在CREATE ROLE或者ALTER ROLE的时候使用DENY关键字来实现：
1、某天或者某个时间访问限制；
2、一个有开始时间和结束时间的访问控制
指明日期和时间:
|
英文表述 |
数字表述 |
|
DAY 'Sunday' |
DAY 0 |
|
DAY 'Monday' |
DAY 1 |
|
DAY 'Tuesday' |
DAY 2 |
|
DAY 'Wednesday' |
DAY 3 |
|
DAY 'Thursday' |
DAY 4 |
|
DAY 'Friday' |
DAY 5 |
|
DAY 'Saturday' |
DAY 6 |
TIME ‘14:00’ (24小时格式的时间)
TIME '02:00 PM' (12小时格式的时间)
TIME ‘02:00’ (24小时格式的时间) 等价于 TIME '02:00 AM'.
如何添加时间约束
指定时间间隔
通过BETWEEN和AND关键字连接两个日期/时间。
BETWEEN DAY 'Monday' AND DAY 'Tuesday'
BETWEEN DAY 'Monday' TIME '00:00' ANDDAY 'Monday' TIME '01:00'
BETWEEN DAY 'Monday' TIME '12:00 AM'AND DAY 'Tuesday' TIME '02:00 AM'
BETWEEN DAY 'Monday' TIME '00:00' ANDDAY 'Tuesday' TIME '02:00'
BETWEEN DAY 1 TIME '00:00' AND DAY 2TIME '02:00'
注意：日期间隔不能跨Saturday(周六)
Incorrect: DENY BETWEENDAY 'Saturday' AND DAY 'Sunday‘
testdw=# ! date 通过该命令查看当前日期 Mon Mar 23 21:58:25 CST 2015
删除时间约束
使用ALTER ROLE命令接DROP DENYFOR关键字
原则：有交集即移出
ALTER ROLE mavshuang DROP DENYFOR DAY ‘Monday’;
配置客户端认证
允许连接到GPDB，通过pg_hba.conf文件；该文件在Master和Segment节点上都存在；
包含每行一条记录的平面文件，格式：
Remote:host database role CIDR-address authentication-method
Unix-domain:local database role authentication-method
默认Master主机pg_hba.conf文件
允许SUPERUSER的本地连接；但不允许远程连接。