PostgreSQL服务器管理:数据库角色

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: PostgreSQL使用角色的概念管理数据库访问权限。一个角色可以被看成是一个数据库用户或者是一个数据库用户组,这取决于角色被怎样设置。角色可以拥有数据库对象(例如,表和函数)并且能够把那些对象上的权限赋予给其他角色来控制谁能访问哪些对象。

本文档为PostgreSQL 9.6.0文档,本转载已得到原译者彭煜玮授权。

1. 数据库角色

数据库角色在概念上已经完全与操作系统用户独立开来。事实上可能维护一个对应关系会比较方便,但是这并非必需。数据库角色在一个数据库集簇安装范围内是全局的(而不是独立数据库内)。要创建一个角色,可使用CREATE ROLE SQL 命令:


CREATE ROLE name;

name遵循 SQL 标识符的规则:或是未经装饰没有特殊字符,或是用双引号包围(实际上,你将总是给该命令要加上额外选项,例如LOGIN。更多细节可见下文)。要移除一个已有的角色,使用相似的DROP ROLE命令:


DROP ROLE name;

为了方便,createuser和dropuser程序被提供作为这些 SQL 命令的包装器,它们可以从 shell 命令行调用:


createuser name
dropuser name

要决定现有角色的集合,检查pg_roles系统目录,例如:


SELECT rolname FROM pg_roles;

psql程序的du元命令也可以用来列出现有角色。

为了引导数据库系统,一个刚刚被初始化好的系统总是包含一个预定义角色。这个角色总是一个"superuser",并且默认情况下(除非在运行initdb时修改)它的名字和初始化数据库集簇的操作系统用户相同。习惯上,这个角色将被命名为postgres。为了创建更多角色,你首先必须以初始角色的身份连接。

每一个到数据库服务器的连接都是使用某个特定角色名建立的,并且这个角色决定发起连接的命令的初始访问权限。要使用一个特定数据库连接的角色名由客户端指示,该客户端以一种应用相关的风格发起连接请求。例如,psql程序使用-U命令行选项来指定要以哪个角色连接。很多应用假定该名字默认是当前操作系统用户(包括createuser和psql)。因此在角色和操作系统用户之间维护一个名字对应关系通常是很方便的。

一个给定客户端连接能够用来连接的数据库角色的集合由该客户端的认证设置决定,这些在Chapter 20中有解释(因此,一个客户端不止限于以匹配其操作系统用户的角色连接,就像一个人的登录名不需要匹配她的真实名字一样)。因为角色身份决定一个已连接客户端可用的权限集合,在设置一个多用户环境时要小心地配置权限。

2. 角色属性

一个数据库角色可以有一些属性,它们定义角色的权限并且与客户端认证系统交互。

login privilege

只有具有LOGIN属性的角色才能被用于一个数据库连接的初始角色名称。一个带有LOGIN属性的角色可以被认为和一个"数据库用户"相同。要创建一个带有登录权限的角色,使用两者之一:


CREATE ROLE name LOGIN;
CREATE USER name;

(CREATE USER和CREATE ROLE等效,除了CREATE USER默认假定有LOGIN,而CREATE ROLE不这样认为)。

superuser status

一个数据库超级用户会绕开所有权限检查,除了登入的权利。这是一个危险的权限并且应该小心使用,最好用一个不是超级用户的角色来完成你的大部分工作。要创建一个新数据库超级用户,使用CREATE ROLE name SUPERUSER。你必须作为一个超级用户来完成这些。

database creation

一个角色必须被显式给予权限才能创建数据库(除了超级用户,因为它们会绕开所有权限检查)。要创建这样一个角色,使用CREATE ROLE name CREATEDB。

role creation

一个角色必须被显式给予权限才能创建更多角色(除了超级用户,因为它们会绕开所有权限检查)。要创建这样一个角色,使用CREATE ROLE name CREATEROLE。一个带有CREATEROLE权限的角色也可以修改和删除其他角色,还可以授予或回收角色中的成员关系。然而,要创建、修改、删除或修改一个超级用户角色的成员关系,需要以超级用户的身份操作。CREATEROLE不足以完成这一切。

initiating replication

一个角色必须被显式给予权限才能发起流复制(除了超级用户,因为它们会绕开所有权限检查)。一个被用于流复制的角色必须也具有LOGIN权限。要创建这样一个角色,使用CREATE ROLE name REPLICATION LOGIN。

password

只有当客户端认证方法要求用户在连接数据库时提供一个口令时,一个口令才有意义。password和md5认证方法使用口令。数据库口令与操作系统命令独立。在角色创建时指定一个口令:CREATE ROLE name PASSWORD 'string'。

Tip: 一个好习惯是创建一个具有CREATEDB和CREATEROLE权限的角色,而不是创建一个超级用户,并且然后用这个角色来完成对数据库和角色的例行管理。这种方法避免了在非必要时作为超级用户操作任务的风险。

对于Chapter 19中描述的运行时配置设置,一个角色也可以有角色相关的默认值。例如,如果出于某些原因你希望在每次连接时禁用索引扫描(提示:不是好主意),你可以使用:


ALTER ROLE myname SET enable_indexscan TO off;

这将保存设置(但是不会立刻设置它)。在这个角色的后续连接中,它就表现得像在会话开始之前执行过SET enable_indexscan TO off。你也可以在会话期间改变该设置,它将只是作为默认值。要移除一个角色相关的默认设置,使用ALTER ROLE rolename RESET varname。注意附加到没有LOGIN权限的角色的角色相关默认值相当无用,因为它们从不会被调用。

3. 角色成员关系

把用户分组在一起来便于管理权限常常很方便:那样,权限可以被授予一整个组或从一整个组回收。在PostgreSQL中通过创建一个表示组的角色来实现,并且然后将在该组角色中的成员关系授予给单独的用户角色。

要建立一个组角色,首先创建该角色:


CREATE ROLE name;

通常被用作一个组的角色不需要有LOGIN属性,不过如果你希望你也可以设置它。

一旦组角色存在,你可以使用GRANT和REVOKE命令增加和移除成员:


GRANT group_role TO role1, ... ;
REVOKE group_role FROM role1, ... ;

你也可以为其他组角色授予成员关系(因为组角色和非组角色之间其实没有任何区别)。数据库将不会让你设置环状的成员关系。另外,不允许把一个角色中的成员关系授予给PUBLIC。

组角色的成员可以以两种方式使用角色的权限。第一,一个组的每一个成员可以显式地做SET ROLE来临时"称为"组角色。在这种状态中,数据库会话可以访问组角色而不是原始登录角色的权限,并且任何被创建的数据库对象被认为属于组角色而不是登录角色。第二,有INHERIT属性的成员角色自动地具有它们所属角色的权限,包括任何组角色继承得到的权限。作为一个例子,假设我们已经有:


CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
CREATE ROLE wheel NOINHERIT;
GRANT admin TO joe;
GRANT wheel TO admin;

在作为角色joe连接后,一个数据库会话将立即拥有直接授予给joe的权限,外加任何授予给admin的权限,因为joe"继承了" admin的权限。然而,授予给wheel的权限不可用,因为即使joe是wheel的一个间接成员,但是该成员关系是通过带NOINHERIT属性的admin得到的。在:


SET ROLE admin;

之后,该会话将只拥有授予给admin的权限,但是没有授予给joe的权限。在执行:


SET ROLE wheel;

之后,该会话将只拥有授予给wheel的权限,但是没有授予给joe或admin的权限。初始的权限状态可以使用下面命令之一恢复:


SET ROLE joe;
SET ROLE NONE;
RESET ROLE;

Note:
SET ROLE命令总是允许选择原始登录角色的直接或间接组角色。因此,在上面的例子中,在成为wheel之前不必先成为admin。

Note:
在 SQL 标准中,用户和角色之间的区别很清楚,并且用户不会自动继承权限而角色会继承。这种行为在PostgreSQL中也可以实现:为要用作 SQL 角色的角色给予INHERIT属性,而为要用作 SQL 用户的角色给予NOINHERIT属性。不过,为了向后兼容 8.1 以前的发布(在其中用户总是拥有它们所在组的权限),PostgreSQL默认给所有的角色INHERIT属性。

角色属性LOGIN、SUPERUSER、CREATEDB和CREATEROLE可以被认为是一种特殊权限,但是它们从来不会像数据库对象上的普通权限那样被继承。要使用这些属性,你必须实际SET ROLE到一个有这些属性之一的特定角色。继续上述例子,我们可以选择授予CREATEDB和CREATEROLE给admin角色。然后一个以joe角色连接的会话将不会立即有这些权限,只有在执行了SET ROLE admin之后才会拥有。

要销毁一个组角色,使用DROP ROLE:


DROP ROLE name;

任何在该组角色中的成员关系会被自动撤销(但是成员角色不会受到影响)。

4. 删除角色

由于角色可以拥有数据库对象并且能持有访问其他对象的特权,删除一个角色 常常并非一次DROP ROLE就能解决。任何被该用户所拥有 的对象必须首先被删除或者转移给其他拥有者,并且任何已被授予给该角色的 权限必须被收回。

对象的拥有关系可以使用ALTER命令一次转移出去,例如:


ALTER TABLE bobs_table OWNER TO alice;

此外,REASSIGN OWNED命令可以被用来把要被删除的 角色所拥有的所有对象的拥有关系转移给另一个角色。由于 REASSIGN OWNED不能访问其他数据库中的对象,有必要 在每一个包含该角色所拥有对象的数据库中运行该命令(注意第一个这样的 REASSIGN OWNED将更改任何在数据库间共享的该角色拥 有的对象的拥有关系,即数据库或者表空间)。

一旦任何有价值的对象已经被转移给新的拥有者,任何由被删除角色拥有的剩余对象 就可以用DROP OWNED命令删除。再次,由于这个命令不能 访问其他数据库中的对象, 有必要在每一个包含该角色所拥有对象的数据库中运行 该命令。还有,DROP OWNED将不会删除整个数据库或者表空间, 因此如果该角色拥有任何还没有被转移给新拥有者的数据库或者表空间,有必要手工 删除它们。

DROP OWNED也会注意移除为不属于目标角色的对象授予给目标 角色的任何特权。因为REASSIGN OWNED不会触碰这类对象,通 常有必要运行REASSIGN OWNED和 DROP OWNED(按照这个顺序!)以完全地移除要被删除对象的 从属物。

总之,移除曾经拥有过对象的角色的方法是:


REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- 在集簇中的每一个数据库中重复上述命令
DROP ROLE doomed_role;

如果不是所有的拥有对象都被转移给了同一个后继拥有者,最好手工处理异常 然后执行上述步骤直到结束。

如果在依赖对象还存在时尝试了DROP ROLE,它将发出 消息标识哪些对象需要被重新授予或者删除。

5. 默认角色

=====PostgreSQL provides a set of default roles which provide access to certain, commonly needed, privileged capabilities and information. Administrators can GRANT these roles to users and/or other roles in their environment, providing those users with access to the specified capabilities and information.

The default roles are described in Table 21-1. Note that the specific permissions for each of the default roles may change in the future as additional capabilities are added. Administrators should monitor the release notes for changes.

Table 21-1. Default Roles


image

Administrators can grant access to these roles to users using the GRANT command:


GRANT pg_signal_backend TO admin_user;

6. 函数和触发器安全性

函数和触发器允许用户在后端服务器中插入代码,其他用户不会注意到这些代码的执行。因此,两种机制允许用户相对容易地为其他人设置"特洛伊木马"。唯一真正的保护是收紧对能定义函数的用户的控制。

在后端服务器进程中运行的函数带有数据库服务器守护进程的操作系统权限。如果用于函数的编程语言允许非检查的内存访问,它就可能改变服务器的内部数据结构。因此,在很多其他事情中,这些函数可能绕开任何系统访问控制。允许这种访问的函数语言被认为是"不可信的",并且PostgreSQL只允许超级用户创建用这些语言编写的函数。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
19天前
|
关系型数据库 分布式数据库 数据库
成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
近日,成都晨云信息技术有限责任公司(以下简称晨云信息)与阿里云PolarDB PostgreSQL版数据库产品展开产品集成认证。测试结果表明,晨云信息旗下晨云-站群管理系统(V1.0)与阿里云以下产品:开源云原生数据库PolarDB PostgreSQL版(V11),完全满足产品兼容认证要求,兼容性良好,系统运行稳定。
|
26天前
|
关系型数据库 分布式数据库 数据库
PolarDB常见问题之数据库不能自己减少节点如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
26天前
|
缓存 关系型数据库 分布式数据库
PolarDB常见问题之数据库cpu突然飙高如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
27天前
|
存储 人工智能 算法
一文了解向量数据库在RAG中扮演的角色
向量数据库在处理高维数据和实现精准信息检索方面发挥关键作用,成为AI时代数据基础设施的核心部分。
|
9天前
|
关系型数据库 MySQL 数据库
卸载云服务器上的 MySQL 数据库
卸载云服务器上的 MySQL 数据库
28 0
|
3天前
|
关系型数据库 MySQL 数据库连接
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
26 11
|
4天前
|
运维 关系型数据库 分布式数据库
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙,报名中!
4月19日周五,PolarDB开源社区联合科大讯飞共同举办开源数据库技术沙龙,本次沙龙我们邀请了众多数据库领域的专家,期待大家的参与!
「合肥 * 讯飞」4 月 19 日 PolarDB 开源数据库沙龙,报名中!
|
13天前
|
存储 缓存 NoSQL
Redis 服务器指南:高性能内存数据库的完整使用指南
Redis 服务器指南:高性能内存数据库的完整使用指南
|
13天前
|
弹性计算 关系型数据库 MySQL
阿里云数据库服务器价格表,数据库创建、连接和使用教程
阿里云数据库使用流程包括购买和管理。选择所需数据库类型如MySQL,完成实名认证后购买,配置CPU、内存和存储。确保数据库地域与ECS相同以允许内网连接。创建数据库和账号,设置权限。通过DMS登录数据库,使用账号密码连接。同一VPC内的ECS需添加至白名单以进行内网通信。参考官方文档进行详细操作。
71 3
|
20天前
|
SQL 关系型数据库 数据库
OceanBase数据库常见问题之OAT添加服务器预检查的时候报错如何解决
OceanBase 是一款由阿里巴巴集团研发的企业级分布式关系型数据库,它具有高可用、高性能、可水平扩展等特点。以下是OceanBase 数据库使用过程中可能遇到的一些常见问题及其解答的汇总,以帮助用户更好地理解和使用这款数据库产品。