13. 权限管理

在PostgreSQL中是通过角色来控制用户的权限的，这一点我们在上一章已经讲过。即不同的角色拥有不同等级的权限。但是什么是权限，以及怎么修改权限呢？本章来讲解这些内容。
在PostgreSQL中，权限就是用来控制数据库对象（如数据库、模式、表、函数、触发器等）的一种属性。通过这种属性，可以控制对象的增删改查等等操作。在数据库对象创建出来的时候就会自动和相关的权限进行关联。

在前面角色一章里，我们主要讲的是角色对整个数据库模式或者数据库实例的权限，比如它的权限等级分为：

• 超级用户
• 创建数据库用户
• 可连接的用户

这里面设计到的权限主要是针对数据库模式或者数据库实例整体上来讲的。而这一章里，我们则要深入到数据库实例的内部，讲解针对数据库内部对象的权限控制。其中内部对象包括且不限于以下列出的内容：

• 表
• 列
• 临时表
• 视图
• 函数
• 触发器
• 序列
• 表空间

等等。而对这些对象可能的权限控制包括且不限于：

• SELECT：对于表、列，视图和序列是允许读，也允许使用COPY.. TO...方法。对于序列，还允许使用currval方法。对于大对象，则是允许对象可读。
• INSERT：允许插入新行到特定的表里，如果是指定的列，那么只有指定的列允许插入值。允许使用COPY...FROM...方法
• UPDATE：允许更新指定的列、指定的表或所有列。实际使用中，需要同时给予SELECT权限，因为需要选择对应的列。对于序列，则是允许使用nextval和setval函数。对于大对象，则是允许写入或者截断对象。
• DELETE ：允许删除表里指定的行，实际使用中，需要同时给予SELECT权限。因为需要筛选出对应的行。
• TRUNCATE：允许对指定的表进行TRUNCATE操作。
• REFERENCES：要创建外键关联，则必须在引用和被引用的列上具有这个权限。这个权限可能被赋予给表里所有的行，或特定的行。
• TRIGGER：允许在指定的表上面创建触发器。
• CREATE：对于数据库，允许创建新的模式，对于模式，允许在模式里创建新的对象。重命名新的对象，你需要拥有这个对象，并且具有这个权限。其中对象包括：表，视图，触发器等。对于表空间，允许在表空间里创建表、索引和临时文件。允许数据库创建的时候将这个表空间作为它的默认空间。（取消这个权限的时候，不会修改已经存在的对象的位置）
• CONNECT：允许用户连接到指定的数据库，在连接的时候会检查这个权限。
• TEMPORARY/TEMP：允许指定的数据库里创建临时表。
• EXECUTE：允许使用指定的函数以及函数里实现的任何操作符。这是仅为函数实现的权限类型。（这个语法也适用于聚合函数）
• USAGE：对于程序语言，允许使用特定的语言来创建函数。这是仅为程序语言实现的一种权限类型。对于模式，允许访问指定模式里的对象（也需要拥有对象的所有权）。对于序列，允许使用currval和nextval方法。对于类型和领域，允许在创建表、函数和其他模式对象的时候使用指定的类型和领域。
• ALL PRIVILEGES：一次性赋予所有的权限给某个读写。在PostgreSQL里，PRIVILEGES关键字是可选的，但是严格的SQL标准里，是必须要写的。

进行这些对象的权限控制主要通过GRANT 和 REVOKE这两个子命令来控制，这两个子命令的基本格式是：

GRANT/REVOKE some_privileges ON database_object object_name TO rolename;

• some_privileges 指的就是需要进行修改的权限，可以是单独的权限，也可以是所有的权限。
• database_object 指的就是上面列出的数据库对象类型
• object_name 指的是对象名称
• rolename 指的是需要赋权的角色名称。

具体的框架就是这样，下面来看用法示例：

# 1. 设置数据库的权限
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] 
| ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 2. 设置模式的权限
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 3. 设置表的权限
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }[, ...] 
| ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]  | ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 4. 设置表空间的权限
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 5. 设置表内行的权限
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )[, ...] 
| ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 6. 设置序列的权限
GRANT { { USAGE | SELECT | UPDATE }[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 7. 设置函数的权限
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 8. 设置大对象的权限
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

上面是一些常见的数据库对象的操作权限赋权命令用法，此外，对一些不常见的数据库对象赋权命令，这里我们也列出来，后面用到的时候再过来查询即可。如下所示：

# 1. 设置DOMIAN的权限
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 2. 设置外部数据引用的权限
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 3. 设置外部服务器的权限
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 4. 设置语言的权限
GRANT { USAGE | ALL [ PRIVILEGES ] } 
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

# 5. 设置类型的权限
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]

上面的所有命令示例中，role_specification可以是下面的值：

role_name | PUBLIC | CURRENT_USER | SESSION_USER

在上面列出来的角色类型里，各个关键字的含义是：

• role_name：已经创建的角色名称
• PUBLIC：表示所有的角色，包括未创建的角色。即新创建的角色自动继承这条语句赋予的权限。
• CURRENT_USER： 当前用户或角色
• SESSION_USER：当前会话的用户或角色

上面的示例代码中，把每个数据库对象可以赋予的权限都列出来了。因此，在使用上面的命令的时候，不要给一些数据库对象赋予不存在的权限。比如给序列赋予DELETE权限，就会报错，如下面所示：

jiahedc1=# grant DELETE on SEQUENCE ds_area_id_seq to dbuser; 
ERROR:  invalid privilege type DELETE for sequence

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]