如何基於PostgreSQL標準模型（專家許可權模型）進行授權 - Hologres

本文為您介紹在Hologres中，如何基於PostgreSQL標準模型（專家許可權模型）進行授權的最佳實務。協助您簡化授權操作並使用更細粒度的許可權管理。

背景資訊

Hologres相容PostgreSQL生態，支援PostgreSQL的標準許可權模型（簡稱專家許可權模型）。同時，Hologres提供了一套簡單許可權模型的授權模式，詳情請參見簡單許可權模型概述。

簡單許可權模型的許可權劃分粒度比較粗，不太適用於細粒度的許可權管理情境。PostgreSQL的標準授權對於許可權的劃分非常細緻，如果您需要使用更細粒度的許可權管理，請參照本文基於PostgreSQL授權的最佳實務進行操作。

PostgreSQL許可權模型簡介

標準PostgreSQL授權擁有詳細的許可權管理體系，詳情請參見Postgresql授權。

PostgreSQL授權的限制如下：

PostgreSQL授權僅支援對現有對象授權，對未來的對象不生效。樣本如下。
1. User1執行了GRANT SELECT ON ALL TABLES IN SCHEMA public TO User2;語句對User2授予了public Schema中所有表的SELECT許可權。
2. User1在public Schema中建立了一張新表table_new。
3. User2執行SELECT * FROM table_new語句時顯示報錯Permission denied。
  由於User1對User2授予SELECT許可權時，僅包含授權時刻public Schema中的所有表，而不包含未來在public Schema中建立的表，因此會產生上述報錯。
您可以使用ALTER DEFAULT PRIVILEGES語句，對未來對象設定預設許可權，詳情請參見ALTER DEFAULT PRIVILEGES。該許可權僅對未來對象生效。樣本語句如下。
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC; --當前授權的人對在public Schema中新建立的表具有讀許可權。
```
您也可以使用ALTER DEFAULT PRIVILEGES FOR ROLE xxx語句，為其他角色建立預設許可權。目前使用者和xxx滿足以下關係時才能成功設定預設許可權：
- 目前使用者是xxx組的成員。
- 目前使用者是超級使用者（Superuser）時，xxx可以為使用者或組。
您可以使用psql命令\ddp查看（系統資料表為pg_catalog.pg_default_acl）ALTER DEFAULT PRIVILEGES是否設定成功。
ALTER DEFAULT PRIVILEGES類似於一個觸發器。建立新表時，Hologres會使用目前使用者和Schema去檢查pg_catalog.pg_default_acl系統資料表中是否有匹配項。如果存在匹配項，則系統自動添加匹配項規則。
說明
- 您只能使用目前使用者與匹配項規則進行匹配，不能使用目前使用者的使用者組去匹配。
- alter default privileges規則匹配只能在建立表時執行，在建立表之後修改表Owner（ alter table tablename owner to）不會觸發alter default privileges。
例如，使用者User1屬於Group1，如果要給Group1匹配規則，授予未來表全許可權。情況如下：
- 如果目前使用者是User1，則建立表時匹配不到規則。
- 如果建立表之前先執行set session role group1; ，改變目前使用者為Group1，則建立表時就可以匹配到規則，系統自動為新建立的表授權。
只有表Owner才可以刪除表。
在標準的PostgreSQL授權語句中，您需要根據Ownership來判斷是否可以刪除表。可以刪除表的角色如下：
- 表的Owner（即建表者）。
- 表所在Schema的Owner。
- Superuser
在PostgreSQL中，系統會預設建立表的使用者為表的Owner，擁有該表的所有許可權，包括刪除表的許可權。
修改表Owner，樣本如下語句。
```
ALTER TABLE <tablename> OWNER TO user2; //將表Owner由User1修改為User2。
ALTER TABLE <tablename> OWNER TO group1;//將表Owner修改為Group1。
```
修改表Owner的操作限制如下：
- User1為表的Owner。
- User1必須是Group1的直接或者間接成員。
  例如，User1是Group1的成員，或User1是Group1中某個組的成員。
- Group1必須在表所在的Schema中有建立表的許可權。
- Superuser可以修改任意表的表Owner。

PostgreSQL許可權模型規劃

標準的PostgreSQL（專家許可權模型）許可權粒度劃分比較細緻，在使用之前需要對現有執行個體對象做如下許可權規劃：

總共有多少個許可權組。
每個組的作用是什麼。
每個組包含哪些使用者。
哪些角色在什麼時候可以刪除表。
每個組在哪些Schema中工作。

建議您執行如下操作，規劃執行個體對象：

確定許可權組以及組的作用。
許可權組分為以下類型：
- XX_DEV_GROUP：表的Owner，擁有表的所有操作許可權。
- XX_WRITE_GROUP：表的寫入許可權，可以寫入資料至相應表。
- XX_VIEW_GROUP：查看錶資料許可權，可以查看相應表的資料。
XX表示一個專案。例如PROJ1專案的許可權組包括PROJ1_DEV_GROUP、 PROJ1_WRITE_GROUP及PROJ1_VIEW_GROUP。
說明
組名的命名規範僅為建議參考，不做強制要求。
確定許可權組所在的Schema。
推薦每個專案的許可權組使用一個Schema。
一個DEV_GROUP可以擁有多張表，但每張表只能屬於一個DEV_GROUP。例如，表TABLE1屬於PROJ1_DEV_GROUP，則該表就不屬於PROJ2_DEV_GROUP。
一個使用者可以屬於多個DEV_GROUP。例如，User1可以既是PROJ1_DEV_GROUP的成員，也可以是PROJ2_DEV_GROUP的成員。

Hologres專家許可權模型最佳實務一

本次實踐以表對象為例，您也可以選用其他對象進行實驗。

表的Owner是對應的XXX_DEV_GROUP許可權組，因此，DEV_GROUP組中的任意成員都可以管理或刪除該表。

例如，使用者被添加到PROJ1_DEV_GROUP使用者組之後，就擁有PROJ1專案中表的管理或刪除許可權。具體操作步驟如下：

建立使用者組。

您可以根據業務需求劃分許可權模型，由Superuser建立相應的使用者組，以PROJ1專案為例，樣本語句如下。

CREATE ROLE PROJ1_DEV_GROUP; //表的Owner，擁有表的所有操作許可權。
CREATE ROLE PROJ1_WRITE_GROUP; //表的寫入許可權，可以寫入資料至相應表。
CREATE ROLE PROJ1_VIEW_GROUP; //查看錶資料許可權，可以查看相應表的資料。

授權使用者組Schema的許可權。
您需要授予建立完成的使用者組Schema的許可權，樣本專案PROJ1可以在Schema1中工作，語句如下。
```
授權PROJ1擁有SCHEMA1中的相關許可權。
GRANT CREATE,usage ON schema SCHEMA1 TO PROJ1_DEV_GROUP;
GRANT usage ON schema SCHEMA1 TO PROJ1_WRITE_GROUP;
GRANT usage ON schema SCHEMA1 TO PROJ1_VIEW_GROUP;
```
說明
- 一個專案可以對應多個Schema，一個Schema也可以對應多重專案。
- 預設public Schema中所有使用者都有CREATE和USAGE許可權。
建立使用者並系統管理使用者組。
授權使用者組Schema許可權後，Superuser需要建立使用者並添加使用者至對應的使用者組，樣本語句如下。
```
CREATE USER "USER1";
GRANT PROJ1_DEV_GROUP TO "USER1"; 

CREATE USER "USER2";
GRANT PROJ1_VIEW_GROUP TO "USER2";
```

建立表並授權。

建立表等對象時，由表的建立者（必須為PROJ1_DEVE_GROUP的成員）或Superuser執行相應的授權語句（假設新建立的表為TABLE1）。樣本如下。

GRANT ALL ON TABLE SCHEMA1.TABLE1 TO PROJ1_WRITE_GROUP; //授予PROJ1_WRITE_GROUP寫入資料至TABLE1的許可權。
GRANT SELECT ON TABLE SCHEMA1.TABLE1 TO PROJ1_VIEW_GROUP; //授予PROJ1_VIEW_GROUP TABLE1的SELECT許可權。
ALTER TABLE SCHEMA1.TABLE1 owner TO PROJ1_DEV_GROUP; //修改TABLE1的Owner為PROJ1_DEV_GROUP。

Hologres專家許可權模型最佳實務二

本次實踐，使用ALTER DEFAULT PRIVILEGES語句簡化對每張表的授權操作。

您需要提前確定建立的表預設屬於哪個專案。具體操作步驟如下：

建立使用者組。

您可以根據業務需求劃分許可權模型，由Superuser建立相應的使用者組，以PROJ1專案為例，樣本語句如下。

CREATE ROLE PROJ1_DEV_GROUP; //表的Owner，擁有表的所有操作許可權。
CREATE ROLE PROJ1_WRITE_GROUP; //表的寫入許可權，可以寫入資料至相應表。
CREATE ROLE PROJ1_VIEW_GROUP; //查看錶資料許可權，可以查看相應表的資料。

授權使用者組Schema的許可權。
您需要授予建立完成的使用者組Schema的許可權，樣本專案PROJ1可以在Schema1中工作，語句如下。
```
--授權PROJ1擁有SCHEMA1中的相關許可權。
GRANT CREATE,USAGE ON SCHEMA SCHEMA1 TO PROJ1_DEV_GROUP;
GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_WRITE_GROUP;
GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_VIEW_GROUP;
```
說明
- 一個專案可以對應多個Schema，一個Schema也可以對應多重專案。
- 預設public Schema中所有使用者都有CREATE和USAGE許可權。

建立使用者並設定預設授權。

完成Schema授權後，需要Superuser建立使用者並添加使用者至相應的組中，同時設定該使用者建立表時擁有的預設許可權。

USER1建立的表預設屬於PROJ1_DEV_GROUP，並且USER1為合法的阿里雲帳號。樣本設定授權語句如下。

CREATE USER "USER1";

ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON  TABLES TO PROJ1_DEV_GROUP; //設定USER1建立的表，PROJ1_DEV_GROUP預設都有讀寫權限。
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_WRITE_GROUP; //設定USER1建立的表，PROJ1_WRITE_GROUP預設都有讀寫權限。
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ1_VIEW_GROUP; //設定USER1建立的表，PROJ1_VIEW_GROUP預設都有讀許可權。

GRANT PROJ1_DEV_GROUP TO "USER1"; //添加USER1至PROJ1_DEV_GROUP。

修改表的Owner。
如果您希望DEV_GROUP中的其他使用者也可以管理或刪除建立的表，則可以修改表的Owner為對應專案的DEV_GROUP，例如PROJ1_DEV_GROUP。
修改表Owner的語句必須由表的建立者或Superuser執行。例如，樣本中表的建立者必須是PROJ1_DEV_GROUP的成員。假設新建立的表為TABLE1。樣本修改表Owner的語句如下。
```
ALTER TABLE SCHEMA1.TABLE1 OWNER TO PROJ1_DEV_GROUP; //修改TABLE1的Owner為PROJ1_DEV_GROUP。
```
如下情況，您可以修改表的Owner：
- 新建立的表，由Superuser定期修改表Owner。
- 在需要管理或刪除表之前修改表Owner。
說明
如果您可以確認表的管理或刪除操作是由表的建立者或Superuser執行的，您也可以不執行上述命令。

修改使用者的預設專案。

調整使用者的預設專案，需要Superuser或使用者本人執行alter default privileges命令，撤銷已設定的預設許可權後，使用新的alter default privileges建立預設授權。

將USER1的預設專案由PROJ1改為PROJ2，該操作不影響現有的表。樣本語句如下。

取消原有預設授權。
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_DEV_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_WRITE_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE SELECT ON TABLES FROM PROJ1_VIEW_GROUP;

建立新的預設授權。
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_DEV_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_WRITE_GROUP;
ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ2_VIEW_GROUP;