你应该知道的数仓安全——默认权限实现共享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语法。

你应该知道的数仓安全——默认权限实现共享schema

原文：https://www.cnblogs.com/hzcyaw/p/13951336.html