摘要： 一种典型客户场景是一些用户是数据的生产方，需要在 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
点击关注，第一时间了解华为云新鲜技术~
评论