典型场景

有一个共享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语法。