说明

本文描述问题及解决方法同样适用于 腾讯云云数据仓库 PostgreSQL（CDWPG）。

背景

Greenplum使用角色(role)管理数据库访问权限。
Greenplum的鉴权系统在数据库中存储了角色以及访问数据库对象的权限，并且使用SQL语句或者命令行工具来管理它们。
在greenplum中新建的用户默认是无法直接连接到数据库的，因此，想要使用greenplum，了解权限管理必不可少。

一、Greenplum的角色

Greenplum是通过roles来管理数据的访问控制，它包含2个概念：Users 和 Groups，一个role可以是一个数据库的user或group，也可以是两者兼备。

Role能拥有数据库的对象（例如：tables），并且能够把访问数据库对象权限开放给其它的role。一个Role也可是另一个角色的成员，子role可以继承父role的权限。

1. 角色对Greenplum对象的操作权限列表

对象类型	特权
表、视图、序列	SELECT
	INSERT
	UPDATE
	DELETE
	RULE
	ALL
外部表	SELECT
	RULE
	ALL
数据库	CONNECT
	CREATE
	TEMPORARY \| TEMP
	ALL
函数	EXECUTE
过程语言	USAGE
方案	CREATE
	USAGE
	ALL

2. 角色的特殊属性

角色属性	描述
SUPERUSER \| NOSUPERUSER	决定角色是否为一个超级用户。要创建一个新的超级用户，用户本身必须是超级用户。NOSUPERUSER是默认值。
CREATEDB \| NOCREATEDB	决定该角色是否被允许创建数据库。NOCREATEDB是默认值。
CREATEROLE \| NOCREATEROLE	决定该角色是否被允许创建和管理其他角色。NOCREATEROLE是默认值。
INHERIT \| NOINHERIT	决定一个角色是否从它的父角色继承特权。一个带有INHERIT属性的角色可以自动地使用授予给其所有直接父角色以及间接父角色的任何数据库特权。INHERIT是默认值。
LOGIN \| NOLOGIN	决定一个角色是否被允许登入。一个带有LOGIN属性的角色可以被认为是一个用户。没有这个属性的角色对于管理数据库特权有用（组）。NOLOGIN是默认值。
CONNECTION LIMITconnlimit	如果角色能够登入，这指定该角色能建立多少并发连接。-1（默认）表示没有限制。
CREATEEXTTABLE \| NOCREATEEXTTABLE	决定一个角色是否被允许创建外部表。NOCREATEEXTTABLE是默认值。对于一个带有CREATEEXTTABLE属性的角色，默认的外部表类型是readable，而默认的协议是gpfdist。注意使用file或execute协议的外部表只能由超级用户创建。
PASSWORD ‘password’	设置角色的口令。如果没有计划使用口令认证则可以省略这个选项。如果没有指定口令，口令将被设置为空并且该用户的口令认证总是会失败。也可以有选择地使用PASSWORD NULL显式地写入一个空口令。
ENCRYPTED \| UNENCRYPTED	控制新口令是否在pg_authid系统目录中存储为一个哈希字符串。如果既没有指定ENCRYPTED也没有指定UNENCRYPTED，默认行为由password_encryption配置参数决定，这个参数默认是on。
ENCRYPTED \| UNENCRYPTED	如果提供的password字符串已经是哈希过的格式，就会原样存储，而不管指定的是ENCRYPTED还是UNENCRYPTED。有关保护登录口令的额外信息请见在Greenplum数据库中保护口令。
VALID UNTIL ‘timestamp’	设置一个日期和时间，在此之后该角色的口令不再有效。如果省略，则口令将会永久有效。
RESOURCE QUEUE queue_name	为负载管理的目的将角色分配到提及的资源队列。然后该角色发出的任何语句都服从于该资源队列的限制。注意RESOURCE QUEUE属性不会被继承，必须在每个用户级（LOGIN）角色上设置它。
DENY {deny_interval \| deny_point}	在一个间隔期间限制访问，用日或者日和时间指定。更多信息请见基于时间的认证。

角色的特殊属性说明

1）role的superuser与createuser属性不能同时拥有；

2）有superuser属性的用户实际可以创建库和创建用户，且nocreateuser nocreatedb 对superuser属性没有约束；

3）create role创建用户，alter role修改用户属性。删除用户drop role，同理删除数据库是drop database；

4）拥有资源的用户不能被drop，提示错误。但是资源可以被superuser drop掉；

5）修改用户属性用alter role。

postgres=# \du+
                                                                   List of roles
  Role name   |                                                Attributes                                                | Member of | Description 
--------------+----------------------------------------------------------------------------------------------------------+-----------+-------------
 cdwadmin     | Create role, Create DB, Ext gpfdist Table                                                                | {}        | 
 gpadmincloud | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}        | 
 gpmon        | Superuser, Create DB                                                                                     | {}        |

这里可以看到，cdwadmin用户有创建角色、创建数据库的权限。权限足够大，但并不是superuser，完全足够使用。

二、Greenplum权限操作实例

1. 角色特殊属性

创建用户，观察角色属性：

postgres=# CREATE USER gp_dy WITH PASSWORD 'gp_dy';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
postgres=# \du+
                                                                   List of roles
  Role name   |                                                Attributes                                                | Member of | Description 
--------------+----------------------------------------------------------------------------------------------------------+-----------+-------------
 cdwadmin     | Create role, Create DB, Ext gpfdist Table                                                                | {}        | 
 gp_dy        |                                                                                                          | {}        | 
 gpadmincloud | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}        | 
 gpmon        | Superuser, Create DB                                                                                     | {}        |

这里创建了一个普通用户，可以看到普通用户默认是没有任何属性的。

2. 数据库的权限

创建数据库：

postgres=# CREATE DATABASE dy_demo;
CREATE DATABASE

首先切换到普通用户，测试普通用户对数据库默认的权限：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> CREATE SCHEMA test_schema;
ERROR:  permission denied for database dy_demo

可以看到，普通用户默认对数据库是没有创建schema权限的。

现在我们切回管理员用户，将数据库的CREATE权限赋予用户：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# GRANT CREATE ON DATABASE dy_demo TO gp_dy;
GRANT

切换普通用户，再次尝试创建schema：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> CREATE SCHEMA dy;
CREATE SCHEMA

创建成功。

3. Schema的权限

使用管理员角色新创建一个schema：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \c dy_demo
You are now connected to database "dy_demo" as user "gpadmincloud".
dy_demo=# CREATE SCHEMA super_dy;
CREATE SCHEMA
dy_demo=# \dn
      List of schemas
    Name    |    Owner     
------------+--------------
 dy         | gp_dy
 gp_toolkit | gpadmincloud
 public     | gpadmincloud
 super_dy   | gpadmincloud
(4 rows)

可以看到，新创建的schema super_dy的owner是管理员角色。

现在我们切换至普通用户，在该schema下尝试创建表：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> SET search_path TO super_dy;
SET  
dy_demo=> CREATE TABLE dy_test(id int, name varchar) DISTRIBUTED BY (id);
ERROR:  no schema has been selected to create in

执行报错，默认不能在别人的schema里创建表。

我们切换至管理员，将该schema的相关权限赋予普通用户gp_dy：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \c dy_demo
You are now connected to database "dy_demo" as user "gpadmincloud".
dy_demo=# GRANT USAGE,CREATE ON SCHEMA super_dy to gp_dy;
GRANT

需要注意的是，USAGE权限必不可少，否则无法查看到任何表，也无法查询表中的数据。

我们再切换gp_dy用户，再次尝试进行建表：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> SET search_path TO super_dy;
SET
dy_demo=> CREATE TABLE dy_test(id int, name varchar) DISTRIBUTED BY (id);
CREATE TABLE

4. 表的增删改查权限

使用管理员角色新创建一张表：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \c dy_demo
You are now connected to database "dy_demo" as user "gpadmincloud".
dy_demo=# SET search_path TO super_dy;
SET
dy_demo=# CREATE TABLE super_test(id int, name varchar) DISTRIBUTED BY (id);
CREATE TABLE
dy_demo=# \dt
                   List of relations
  Schema  |    Name    | Type  |    Owner     | Storage 
----------+------------+-------+--------------+---------
 super_dy | dy_test    | table | gp_dy        | heap
 super_dy | super_test | table | gpadmincloud | heap
(2 rows)

可以看到，新创建的table super_test的owner是管理员角色。

现在我们切换至普通用户，对该表尝试插入：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> SET search_path TO super_dy;
SET
dy_demo=> INSERT INTO super_test VALUES (1, 'dy');
ERROR:  permission denied for relation super_test

执行报错，默认不能操作别人的表。

我们切换至管理员，将该表的相关权限赋予普通用户gp_dy：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \c dy_demo
You are now connected to database "dy_demo" as user "gpadmincloud".
dy_demo=# SET search_path TO super_dy;
SET
dy_demo=# GRANT SELECT,UPDATE,DELETE,INSERT ON super_test TO gp_dy;
GRANT

我们再切换gp_dy用户，再次尝试操作表：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> SET search_path TO super_dy;
SET
dy_demo=> INSERT INTO super_test VALUES (1, 'dy');
INSERT 0 1
dy_demo=> SELECT * FROM super_test;
 id | name 
----+------
  1 | dy
(1 row)

dy_demo=> UPDATE super_test SET name='Daemonyue' WHERE name='dy';
UPDATE 1
dy_demo=> SELECT * FROM super_test;
 id |   name    
----+-----------
  1 | Daemonyue
(1 row)

dy_demo=> DELETE FROM super_test WHERE id=1;
DELETE 1
dy_demo=> SELECT * FROM super_test;
 id | name 
----+------
(0 rows)

增删改查操作都没有问题。

5. 字段的权限

使用管理员用户，限制普通用户gp_dy对表super_test的字段级别权限，让其只能访问到name字段：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ psql -d postgres
psql (9.4.24)
Type "help" for help.

postgres=# \c dy_demo
You are now connected to database "dy_demo" as user "gpadmincloud".
dy_demo=# SET search_path TO super_dy;
SET
dy_demo=# INSERT INTO super_test VALUES (2, 'dy');
INSERT 0 1
dy_demo=# SELECT * FROM super_test;
 id |   name    
----+-----------
  1 | Daemonyue
  2 | dy
(2 rows)

dy_demo=# REVOKE SELECT ON super_test FROM gp_dy;
REVOKE
dy_demo=# GRANT SELECT(name) ON super_test to gp_dy;
GRANT

我们再切换gp_dy用户，尝试查询表：

[gpadmincloud@mdw-snova-aqfhzkhe ~]$ PGPASSWORD=gp_dy psql -d postgres -h 10.0.25.4 -U gp_dy
psql (9.4.24)
Type "help" for help.

postgres=> \c dy_demo
You are now connected to database "dy_demo" as user "gp_dy".
dy_demo=> SET search_path TO super_dy;
SET
dy_demo=> SELECT * FROM super_test;
ERROR:  permission denied for relation super_test
dy_demo=> SELECT name FROM super_test;
   name    
-----------
 Daemonyue
 dy
(2 rows)

可以看到，第一次 SELECT * 会支持报错，因为 * 标识所有的列，而用户gp_dy只对name字段有访问权限。

三、权限参数

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { [ TABLE ] table_name [, ...]
 | ALL TABLES IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
 [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
 ON [ TABLE ] table_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { SEQUENCE sequence_name [, ...]
 | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
 ON DATABASE database_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON DOMAIN domain_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON FOREIGN DATA WRAPPER fdw_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON FOREIGN SERVER server_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON LANGUAGE lang_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
 ON LARGE OBJECT loid [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schema_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
 ON TABLESPACE tablespace_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON TYPE type_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
 [ GROUP ] role_name
 | PUBLIC
 | CURRENT_USER
 | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

Greenplum基于角色的细粒度权限控制

说明

背景