你應該知道的數倉安全——預設許可權實現共享schema

典型場景

有一個共享schema，多個使用者是資料的生產方，在共享schema中建立表並寫入資料。還有一些使用者是資料的消費方，利用共享schema中的資料做分析。

一種實現方法是資料生產方每次建立新表後告知管理員使用者使用grant select on all tables in schema搞定。這樣的話schema下面又建立了一些新表，對這些新表授權還需要告知管理員使用者再次使用grant all tables。有沒有簡單的應對方案?答案是肯定的，可以使用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

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

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

示例

postgres=# create user creator1 password 'Gauss_234';

CREATE USER

postgres=# create user creator2 password 'Gauss_234';

CREATE ROLE

postgres=# create user user1 password 'Gauss_234';

CREATE USER

--建立共享schema，賦予creator1和creator2建立許可權，賦予user1使用許可權

postgres=# create schema shared_schema;

CREATE SCHEMA

postgres=> grant create, usage on schema shared_schema to creator1;

GRANT

postgres=> grant create, usage on schema shared_schema to creator2;

GRANT

postgres=# grant usage on schema shared_schema to user1;

GRANT

--將creator1和creator2在shared_schema中建立表的select許可權賦予user1

postgres=# alter default privileges for user creator1, creator2 in schema shared_schema grant select on tables to user1;

ALTER DEFAULT PRIVILEGES

--切到creator1，建表

postgres=# \c postgres creator1

You are now connected to database "postgres" as user "creator1".

postgres=> create table shared_schema.t1 (c1 int);

CREATE TABLE

--切到creator2，建表

postgres=> \c postgres creator2

You are now connected to database "postgres" as user "creator2".

postgres=> create table shared_schema.t2 (c1 int);

CREATE TABLE

--切到user1，查詢OK

postgres=> \c postgres user1

You are now connected to database "postgres" as user "user1".

postgres=> 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)。

postgres=# select r.rolname, n.nspname, a.defaclobjtype, a.defaclacl from

postgres-# pg_default_acl a, pg_roles r, pg_namespace n

postgres-# 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預設許可權。

postgres=# create user creator3 password 'Gauss_234';

CREATE USER

postgres=# grant create, usage on schema shared_schema to creator3;

GRANT

postgres=# \c postgres creator3

You are now connected to database "postgres" as user "creator3".

postgres=> create table shared_schema.t3 (c1 int);

CREATE TABLE

postgres=> \c postgres user1

You are now connected to database "postgres" as user "user1".

postgres=> 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，則預設值為當前使用者。

postgres=> \c postgres creator3

You are now connected to database "postgres" as user "creator3".

postgres=> alter default privileges in schema shared_schema grant select on tables to user1;

ALTER DEFAULT PRIVILEGES

postgres=> \c postgres user1

You are now connected to database "postgres" as user "user1".

postgres=> select * from shared_schema.t3;

ERROR: permission denied for relation t3

postgres=> \c postgres creator3

postgres=> create table shared_schema.t4 (c1 int);

CREATE TABLE

postgres=> \c postgres user1

You are now connected to database "postgres" as user "user1".

postgres=> 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語句。

postgres=> \c postgres creator3

You are now connected to database "postgres" as user "creator3".

postgres=> grant select on all tables in schema shared_schema to user1;

ERROR: permission denied for relation t1

postgres=> grant select on table shared_schema.t3 to user1;

GRANT

postgres=> \c postgres user1

You are now connected to database "postgres" as user "user1".

postgres=> 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語法。