postgresql用户和角色

简述

PostgreSQL 通过角色的概念来控制数据库的访问权限。角色又包含了两种概念，具有登录
权限的角色称为用户，包含其他成员（也是角色）的角色称为组（group）。因此，一个角色可
以是一个用户，也可以是一个组，或者两者都是。

角色可以拥有数据库对象（例如表和函数），并且可以将这些对象上的权限授予其他角色，
从而控制对象的访问。此外，一个组中的成员可以拥有该组所拥有的权限

创建角色

在 PostgreSQL 中，使用 create role 语句创建角色：

-- 创建角色 name
-- name 指定了要创建的角色名称
create role name;
1
2
3

-- 显示当前数据库集群中已有的角色，可以查询系统目录 pg_roles：
-- 系统默认提供的角色名称,用于提供针对一些特定的常用特权和信息的访问权限
select rolname from pg_roles;
1
2
3

其中的 postgres 是系统初始化数据库时创建的默认角色，它是一个超级用户
默认角色包含的具体权限

角色属性

角色可以拥有属性，属性确定了角色拥有的特权，并且在登录时与客户端认证系统进行交互。
常见的角色属性包括

登录特权

-- 具有 login 属性的角色才能连接数据库。具有 login 角色的用户可以被看作一个"数据库用户"
create role name LOGIN;
1
2

-- 默认包含LOGIN权限，而 CREATE ROLE 没有
create user name;
1
2

超级用户

/*，数据的超级用户可以避开所有的权限检查，只验证登录权限。因此，这是一
个很危险的特权，使用时需要特别小心；最好在日常的操作中避免使用超级用户。
只有超级用户才能创建其他的超级用户。
*/
create role name SUPERUSER;
1
2
3
4
5

创建数据库

--只有明确授权的角色才能够创建数据库（超级用户除外，因为他们可以避开权限检查
create role name CREATEDB;
1
2

创建角色

--只有明确授权的角色才能够创建其他角色（超级用户除外，因为他们可以避开权限检查
--具有 CREATEROLE 特权的角色还可以修改或删除其他角色，以及为这些角色授予或者
--撤销成员角色。但是，针对超级用户的创建、修改、删除，以及它的成员变更，需要超
--级用户特权；CREATEROLE 特权无法针对超级用户执行这些操作
create role name CREATEROLE;
1
2
3
4
5

启动复制

--只有明确授权的角色才能够启动流复制（超级用户除外，因为他们可以避开权限检查）。用于流复制的角色还需要拥有 LOGIN 特权
create role name REPLICATION LOGIN;
1
2

密码

-- 只有当用户连接数据库使用的客户端认证方法要求提供密码时，密码属性才有意义
-- password 和 md5 认证方法需要使用密码。数据库的密码与操作系统的密码相互独立
-- 创建角色的同时，同时指定角色密码
create role name password '123456';
1
2
3
4

--在创建角色时，可以根据需要指定某些属性
create role tony with LOGIN password 'Pass2022' valid until '2025-01-01';
1
2

-- 创建一个管理角色 admin，它具有创建数据库和创建角色的特权
--最好创建一个拥有 CREATEDB 和 CREATEROLE 特权，但不具有超级用户特权
--的管理角色，然后使用该角色执行日常的数据库和角色的管理。这种方式可以避免过度使用超级
--用户可能带来的风险
create role admin CREATEDB CREATEROLE;
1
2
3
4
5

修改角色属性

alter role admin NOCREATEROLE;
1

对象授权

PostgreSQL 使 GRANT 语句进行数据库对象的授权操作。以表为例，基本的授权语法如下

grant privilege_list | ALL
 on [ table ] table_name
 to role_name;
1
2
3

privilege_list 权限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE
等，ALL 表示表上的所有权限

--将 employees、departments 和 jobs 表上的增删改查权限授予了 tony 用户
-- 此时 tony用户就可以访问这些表中的数据
 grant select, insert, update, delete
 on employees, departments, jobs 
 to tony;
1
2
3
4
5

对表进行授权的 GRANT 语句还支持一些其他选项：

GRANT privilege_list | ALL
 ON ALL TABLES IN SCHEMA schema_name
 TO role_name;
1
2
3

ALL TABLES IN SCHEMA 表示某个模式中的所有表，可以方便批量授权操作。例如：

--该语句将 public 模式中所有表的查询权限授予 tony 用户。
grant select
on all tables in schema public
to tony;
1
2
3
4

也可以在 GRANT 语句的最后指定一个 WITH GRANT OPTION，意味着被授权的角色
可以将该权限授权其他角色

-- tony 用户不但拥有这些表上的访问权限，还可以将这些权限授予其他角色
GRANT SELECT, INSERT, UPDATE, delete
ON employees, departments, jobs
TO tony WITH GRANT OPTION;
1
2
3
4

除了授权表的访问权限之外，GRANT 语句还支持字段、视图、序列、数据库、函数、过程、
模式等对象的授权操作。授权操作的语句基本都类似
官网介绍

撤销授权

PostgreSQL 使 REVOKE 语句撤销数据库对象上的权限。同样以表为例，基本的撤销授权语句如下：

REVOKE privilege_list | ALL
 ON TABLE table_name
 FROM role_name;
1
2
3

--其中的参数和 grant 语句一致。例如：
revoke select, insert, update, delete
on employees, departments, jobs
from tony;
1
2
3
4

REVOKE 语句也支持对某个模式中的所有对象进行操作：

REVOKE privilege_list | ALL
 ON ALL TABLES IN SCHEMA schema_name
 FROM role_name;
1
2
3

撤销了用户 tony 在 public 模式中所有表上的查询权限

revoke select
on all tables in schema public
from tony;

1
2
3
4

与 GRANT 语句对应，REVOKE 语句还支持字段、视图、序列、数据库、函数、过程、模
式等对象的撤销授权操作。官网介绍

组和成员

在现实的环境中，管理员通常需要管理大量的用户和对象权限。为了便于权限管理，减少复
杂度，可以将用户进行分组，然后以组为单位进行权限的授予和撤销操作。

为此，PostgreSQL 引入了组（group）角色的概念。具体来说，就是创建一个代表组的角色，
然后将该组的成员资格授予其他用户，让其成为该组的成员。

--使用以下创建一个组角色
create role group_name;
1
2

按照习惯，组角色通常不具有 LOGIN 特权，也就是不能作为一个用户登录

--创建一个组 managers
create role managers;
1
2

--使用与对象授权操作相同的 grant 和 revoke 语句为组添加和删除成员：
-- managers组添加用户tony
grant managers to tony
1
2
3

--PostgreSQL 不允许设置循环的成员关系，也就是两个角色互相为对方的成员
 GRANT tony TO managers;
1
2

不能将特殊角色 PUBLIC 设置为任何组的成员
组角色中的成员可以通过以下方式使用该组拥有的特权：

• 首先，组中的成员可以通过 SET ROLE 命令将自己的角色临时性“变成”该组角色。此时，
  当前数据库会话拥有该组角色的权限，而不是登录用户的权限；并且会话创建的任何数
  据库对象归组角色所有，而不是登录用户所有。
• 其次，对于具有 INHERIT 属性的角色，将会自动继承它所属的组的全部特权，包括这些
  组通过继承获得的特权

CREATE ROLE user1 LOGIN INHERIT;
CREATE ROLE net_admins NOINHERIT;
CREATE ROLE sys_admins NOINHERIT;
GRANT net_admins TO user1;
GRANT sys_admins TO net_admins;
1
2
3
4
5

使用角色 user1 登录之后，数据库会话将会拥有 user1 自身的特权和 net_admins 所有的特权，
因为 user1“继承”了 net_admins 的特权。但是，会话还不具有 sys_admins 所有的特权，因为即使
user1 间接地成为了 sys_admins 的成员，通过 net_admins 获得的成员资格具有 NOINHERIT 属性，
也就不会自动继承权限。

删除角色

-- drop role 角色名称;
DROP ROLE name;
1
2

如果删除的是组角色，该组中的成员关系会自动从组中删除，但是这些成员角色自身不会受
到任何影响

由于角色可以拥有数据库中的对象，也可以拥有访问其他对象的权限，删除角色通常不仅仅
只是一个简单的 DROP ROLE 语句。在删除角色之前，需要删除它所拥有的对象，或者将这些对
象重新赋予其他的角色；同时还需要撤销授予该角色的权限。详细信息可以参考官方文档