說明

本文描述問題及解決方法同樣适用于騰訊雲雲資料倉庫 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。
如果提供的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基于角色的細粒度權限控制說明背景一、Greenplum的角色二、Greenplum權限操作執行個體三、權限參數

說明

背景