你應該知道的數倉安全——預設權限實作共享schema

摘要： 一種典型客戶場景是一些使用者是資料的生産方，需要在schema中建立表并寫入資料；而另一些使用者是資料的消費方，讀取schema中的資料做分析。使用Alter default privilege文法可以實作這種共享schema的權限管理問題。通過簡單示例示範了Alter default privilege文法處理這種典型場景的細節和有效性。

前言

最近遇到一個客戶場景，涉及共享schema的權限問題。場景簡單可以描述為：一些使用者是資料的生産方，需要在schema中建立表并寫入資料；另一些使用者是資料的消費方，讀取schema中的資料做分析。對于該schema權限管理的一種實作方法是資料生産方在每次建立新表後告知管理者使用者使用grant select on all tables in schema文法來授予消費方權限。這種方法有一定的局限性。如果生産方在schema下面又建立了一些新表，為了授權消費方使用這些新表還需要告知管理者使用者再次使用grant select on all tables in schema來授權。有沒有簡單的應對方案？答案是肯定的，可以使用Alter default privilege。Alter default privilege用于将來建立的對象的權限的授予或回收。

文法介紹

ALTER DEFAULT PRIVILEGES
     [ FOR { ROLE | USER } target_role [, ...] ]
     [ IN SCHEMA schema_name [, ...] ]
     abbreviated_grant_or_revoke;      

其中abbreviated_grant_or_revoke子句用于指定對哪些對象進行授權或回收權限。對表授權文法是：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } 
     [, ...] | ALL [ PRIVILEGES ] }
     ON TABLES 
     TO { [ GROUP ] role_name | PUBLIC } [, ...]      

參數說明

• target_role

  已有角色的名稱。如果省略FOR ROLE/USER，則預設值為目前角色/使用者。

  取值範圍：已有角色的名稱。

• schema_name

  現有模式的名稱。

  target_role必須有schema_name的CREATE權限。

  取值範圍：現有模式的名稱。

• role_name

  被授予或者取消權限角色的名稱。

  取值範圍：已存在的角色名稱。

詳見https://support.huaweicloud.com/devg-dws/dws_04_0241.html

場景示例

testdb=# create user creator1 password 'Gauss_234';  
 CREATE USER
 testdb=# create user creator2 password 'Gauss_234';  
 CREATE ROLE
 testdb=# create user user1 password 'Gauss_234';
 CREATE USER
 --建立共享schema，授予creator1和creator2建立權限，授予user1使用權限
 testdb=# create schema shared_schema;  
 CREATE SCHEMA
 testdb=> grant create, usage on schema shared_schema to creator1;
 GRANT
 testdb=> grant create, usage on schema shared_schema to creator2;
 GRANT
 testdb=# grant usage on schema shared_schema to user1;
 GRANT
 --将creator1和creator2在shared_schema中建立表的select權限授予user1
 testdb=# alter default privileges for user creator1, creator2 in schema shared_schema grant select on tables to user1;
 ALTER DEFAULT PRIVILEGES
 --切到creator1，建表
 testdb=# \c testdb creator1
 You are now connected to database "testdb" as user "creator1".
 testdb=> create table shared_schema.t1 (c1 int);
 CREATE TABLE
 --切到creator2，建表
 testdb=> \c testdb creator2
 You are now connected to database "testdb" as user "creator2".
 testdb=> create table shared_schema.t2 (c1 int);
 CREATE TABLE
 --切到user1，查詢OK
 testdb=> \c testdb user1
 You are now connected to database "testdb" as user "user1".
 testdb=> select * from shared_schema.t1 union select * from shared_schema.t2;
  c1 
 ----
 (0 rows)      

檢視預設權限的授予現狀

查詢系統表pg_default_acl可以檢視目前哪些schema被授予了預設權限。從defaclacl字段可以看到creator1和creator2分别授予了user1對shared_schema中對象的select權限（r表示read）。

testdb=# select r.rolname, n.nspname, a.defaclobjtype, a.defaclacl from
 testdb-#     pg_default_acl a, pg_roles r, pg_namespace n
 testdb-#     where a.defaclrole=r.oid and a.defaclnamespace=n.oid;
  rolname  |    nspname    | defaclobjtype |     defaclacl      
 ----------+---------------+---------------+--------------------
  creator1 | shared_schema | r             | {user1=r/creator1}
  creator2 | shared_schema | r             | {user1=r/creator2}
 (2 rows)      

一些細節

所有在共享schema中建立對象的使用者都應該出現在alter default privileges for user之後的清單中。否則，如果有使用者creator3沒有在清單中，其在共享schema中建立的對象或者說那些Owner是creator3的對象将不能被user1查詢。因為共享schema中creator3使用者建立的表沒有授予user1預設權限。

testdb=# create user creator3 password 'Gauss_234';
 CREATE USER
 testdb=# grant create, usage on schema shared_schema to creator3;
 GRANT
 testdb=# \c testdb creator3
 You are now connected to database "testdb" as user "creator3".
 testdb=> create table shared_schema.t3 (c1 int);
 CREATE TABLE
 testdb=> \c testdb user1
 You are now connected to database "testdb" as user "user1".
 testdb=> select * from shared_schema.t3;
 ERROR:  permission denied for relation t3      

管理者可以通過alter default privileges for user将creator3放入清單中為user1授予通路creator3使用者建立表的預設權限，也可以由creator3使用者自己通過alter default privileges授權給user1. 前面文法參數說明中有如果省略FOR ROLE/USER，則預設值為目前使用者。

testdb=> \c testdb creator3
 You are now connected to database "testdb" as user "creator3".
 testdb=> alter default privileges in schema shared_schema grant select on tables to user1;
 ALTER DEFAULT PRIVILEGES
 testdb=> \c testdb user1
 You are now connected to database "testdb" as user "user1".
 testdb=> select * from shared_schema.t3;
 ERROR:  permission denied for relation t3
 testdb=> \c testdb creator3
 testdb=> create table shared_schema.t4 (c1 int);
 CREATE TABLE
 testdb=> \c testdb user1
 You are now connected to database "testdb" as user "user1".
 testdb=> select * from shared_schema.t4;
  c1 
 ----
 (0 rows)      

上述代碼第3行為目前使用者在shared_schema下面建立的表的select權限授予user1。第7行user1查詢shared_schema.t3報權限不足，是因為alter default privileges隻處理将來的對象。shared_schema.t3在是之前建立的。我們建立表shared_schema.t4，user1使用者查詢正常。

如果要處理已有表的權限，使用grant語句。參見https://support.huaweicloud.com/devg-dws/dws_04_0334.html。

testdb=> \c testdb creator3
 You are now connected to database "testdb" as user "creator3".
 testdb=> grant select on all tables in schema shared_schema to user1;
 ERROR:  permission denied for relation t1
 testdb=> grant select on table shared_schema.t3 to user1;
 GRANT
 testdb=> \c testdb user1
 You are now connected to database "testdb" as user "user1".
 testdb=> select * from shared_schema.t3;
  c1 
 ----
 (0 rows)      

代碼第3行中shared_schema中包含有3個使用者建立的表，而creator3隻是表t3的建立者(Owner)。是以授予整個schema的權限會報錯，隻授予creator3是Owner的表t3之後，user1使用者查詢正常。

總結

alter default privileges隻處理将來的對象，grant隻處理已有的對象。進一步的，這兩種文法授予權限時涉及的對象僅包括Owner是目前使用者的對象。如果要為共享schema下面所有Owner的對象授予權限，需要使用管理者使用者使用alter default privileges for user文法和grant文法。

本文分享自華為雲社群《你應該知道的數倉安全——預設權限實作共享schema》，作者：zhangkunhn

點選關注，第一時間了解華為雲新鮮技術~