前言

      最近遇到一个客户场景，涉及共享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;

 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
  被授予或者取消权限角色的名称。
  取值范围：已存在的角色名称。

 场景示例

 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)

 查看默认权限的授予现状

 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)

 一些细节

 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

 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)

 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)

 总结

【推荐阅读】

• 华为云GaussDB(DWS)数据仓库以2048大规模节点通过信通院评测认证

• 五大关键能力，华为云原生数据仓库GaussDB(DWS)深度技术解读

• 华为认证GaussDB OLAP数据库高级工程师 HCIP-GaussDB-OLAP V1.5（中文版）发布通知

• 数智金融 使能创新，“2020华为数智金融论坛”在溪村成功举办

• 华为GaussDB(DWS)数据仓库，助力招行“人人用数，创新前行”