PostgreSQL用户与角色简述

简述

• PostgreSQL通过角色（role）来控制数据库的访问权限。角色可以拥有数据库对象（比如表、函数等），并允许将这些对象的权限授予其他角色，从而实现对象访问的控制。
• 角色（role）包含了两种概念：①具有登录权限的角色称为用户（user）；②包含其他成员的角色称为组（group）。
  

用户和组

• 数据库使用过程中，DBA通常会创建多个用户，并根据用户的实际情况赋予权限。为了便于权限管理，可创建角色，然后将角色赋予用户。通过角色权限的授予和撤销操作，批量控制用户权限。此时的角色充当的是组（group）的概念，用户加入到这个组便拥有了这个组的权限。
• PostgreSQL不允许两个角色互为授权。
• PUBLIC为PostgreSQL中的特殊角色，不能将其设置为任何组的成员。

权限与继承

• 在SQL标准中，用户和角色存在明确的差异，用户不会自动继承权限，而角色会继承权限。
• PostgreSQL通过INHERIT和NOINHERIT属性来实现上述标准，只需为角色设置INHERIT属性，为用户设置NOINHERIT属性。为了兼容8.1之前的版本属性，PostgreSQL默认为所有的角色设置了INHERIT属性，因此用户默认会自动继承它所在组的权限。
• 只有数据库的对象上的普通权限可以被继承，角色的LOGIN、SUPERUSER、CREATEDB和CREATEROLE权限被认为是特殊权限，不会被继承。

SET ROLE命令

• 用户在登录后，可使用SET ROLE命令，让当前会话临时拥有某个角色的权限；
• 执行该命令后，此时会话将拥有角色的权限，而不是登录用户的权限；
• 会话过程创建的数据库对象将归角色所有，而不是登录用户所有。

案例说明

创建三个角色db_user、net_manager、sys_manager

-- 创建角色，并赋予登录权限，此时的db_user就可以用于登录数据库。
CREATE ROLE db_user LOGIN INHERIT;
-- 创建角色，并赋予schema中所有表的查询权限。
CREATE ROLE net_manager NOINHERIT;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO net_manager;
-- 创建角色，并赋予schema中所有表的插入权限。
CREATE ROLE sys_manager NOINHERIT;
GRANT INSERT ON ALL TABLES IN SCHEMA public TO net_manager;
-- 将net_mananger权限授予db_user
GRANT net_manager to db_user;
-- 将sys_mananger权限授予sys_mananger
GRANT sys_manager to net_manager;

使用db_user登录后，数据库会话会同时拥有db_user和net_manager的权限，但不具备sys_manager的权限，即只能执行查询操作，无法执行插入操作。
虽然db_user间接成为了sys_manager的成员，但由于net_manager获得的成员资格具有NOINHERIT属性，不会自动继承权限。

-- 执行下述指令，会话将拥有net_manager的权限，但不再具备db_user权限，同时也无法继承sys_manager的特权
SET ROLE net_manager
-- 执行下述指令中的任意一个，可将会话权限恢复至初始状态
SET ROLE db_user;
SET ROLE NONE;
RESET ROLE;

角色设置的指令

创建角色

create role xxxx;

删除角色

drop role xxxx;

删除角色前需要删除角色拥有的对象，或将对象的所有权赋予其他角色，同时还需要撤销授予该角色的权限，否则删除会报错。

为角色赋予属性

属性名	说明
LOGIN	具备LOGIN属性的角色才能连接数据库
SUPERUSER	超级用户属性，请谨慎使用或不使用 。只有超级用户才能创建、修改其他超级用户
CREATEDB	除超级用户外，只有拥有该属性的角色才能创建数据库
CREATEROLE	除超级用户外，只有拥有该属性的角色才能创建、修改、删除其他角色，并为这些角色授权或撤销权限。该特权无法管控超级用户
REPLICATION	启动流复制权限，拥有该属性的角色必须同时拥有LOGIN属性
password	为角色设置密码，只有当角色用于连接数据库时，该属性才有意义。可追加配置valid until设置有效期

-- 创建角色并赋予属性
CREATE ROLE xxx WITH LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION PASSWORD 'xxxx' VALID UNTIL '2099-01-01'
-- 修改用户属性,移除其创建角色的属性
ALTER ROLE xxx NOCREATEROLE;

为角色授权

PostreSQL中可使用GRANT语句为角色授权，以表为例，语法如下：

-- privilege_list 可以是SELECT、INSERT、UPDATE、DELETE、TRUNCATE
-- WITH GRANT OPTION意味着被授权的角色可以将该权限再授权其他角色。
-- 将某张表的XX权限赋予某个角色
GRANT privilege_list | ALL ON [TABLE] table_name TO role_name WITH GRANT OPTION;
-- 将某个schema下所有表的XX权限赋予角色
GRANT privilege_list | ALL ON ALL TABLES IN SCHEMA schema_name TO role_name;

撤销角色授权

PostreSQL中可使用REVOKE语句撤销角色，以表为例，语法如下：

-- privilege_list 可以是SELECT、INSERT、UPDATE、DELETE、TRUNCATE
-- 撤销某张表的XX权限
REVOKE privilege_list | ALL ON TABLE table_name FROM role_name;
-- 撤销某个schema下所有表的XX权限
REVOKE privilege_list | ALL ON ALL TABLES IN SCHEMA schema_name FROM role_name;