PostgreSQL 9.0 在 grant 命令上得到了增强。它提供一条 grant命令，能够将某个schema下的全部表赋给用户, 这在工作中带来了便利。来自PG9.0官网文档,语法如下

1
2
3
4
5

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
 [,...] | ALL [ PRIVILEGES ] }  
 ON { [ TABLE ] table_name [, ...]  
 | ALL TABLES IN SCHEMA schema_name [, ...] }  
 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

其中 “ALL TABLES IN SCHEMA schema_name” 就为上面的这种用法。今天正好有项目有查询需求，需要新增一查询帐号，能够查询生产库上所有的业务表。以下是详细步骤

开通数据库连接权限

1
2

skytf=#grant connect on database skytf to select_csl;  
GRANT

开通SCHEMA使用权限

数据库 skytf下业务SCHEMA的使用权限

1

grant select on all tables in schema skytf to select_csl;

开通帐号对表的只读权限

给查询帐号 select_csl赋予 schemal skytf 下所有表的只读权限

1
2

skytf=# grant select on all tables in schema skytf to select_csl;  
GRANT

检查权限是否已加

1
2
3
4
5
6
7
8
9
10
11

skytf=> select grantor,grantee,table_schema,table_name from information_schema.table_privileges where grantee='select_csl'  
and table_schema='skytf' order by table_name ;  
grantor | grantee | table_schema | table_name  
---------+------------+--------------+-----------------------------  
skytf | select_csl | skytf | skytf_user  
skytf | select_csl | skytf | albumcheck_log  
skytf | select_csl | skytf | building_guest_log  
..  
..  
省略  
(15 rows)

上面部分结果已经省略，从结果可以看出 帐号select_csl 获得了skytf下所有的表的查询权限。

官网解释

There is also an option to grant privileges on all objects of the same type within one or more schemas. T
his functionality is currently supported only for tables, sequences, and functions
(but note that ALL TABLES is considered to include views).

上面说 tables,sequences,functions 支持这种用法。

更多用法

1
2
3
4
5
6
7
8
9
10

GRANT { { USAGE | SELECT | UPDATE }  
 [,...] | ALL [ PRIVILEGES ] }  
 ON { SEQUENCE sequence_name [, ...]  
 | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]  
 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }  
 TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

总结

PostgrSQL 提供的 ALL ... IN SCHEMA schema_name 用法使给DBA在日常工作中只需要一条命令就能给指定用户授权，打破了传统的每张表单独赋权的模式。

上面只演示 tables 的授权方法，有兴趣的朋友可能对　functions 和 sequences 做下类似的实验。这里不再详细描述。